hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "balaswamy vaddeman (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-13977) nvl funtion not working after left outer join
Date Fri, 10 Jun 2016 05:47:21 GMT

    [ https://issues.apache.org/jira/browse/HIVE-13977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15323915#comment-15323915
] 

balaswamy vaddeman commented on HIVE-13977:
-------------------------------------------

Plan not optimized by CBO.

Vertex dependency in root stage
Map 1 <- Map 2 (BROADCAST_EDGE)

Stage-0
   Fetch Operator
      limit:-1
      Stage-1
         Map 1
         File Output Operator [FS_10]
            compressed:false
            Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
            table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"}
            Select Operator [SEL_9]
               outputColumnNames:["_col0","_col1","_col2","_col3","_col4"]
               Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
               Map Join Operator [MAPJOIN_14]
               |  condition map:[{"":"Left Outer Join0 to 1"}]
               |  HybridGraceHashJoin:true
               |  keys:{"Map 2":"_col0 (type: bigint)","Map 1":"_col0 (type: bigint)"}
               |  outputColumnNames:["_col0","_col2","_col3"]
               |  Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats:
NONE
               |<-Map 2 [BROADCAST_EDGE]
               |  Reduce Output Operator [RS_7]
               |     key expressions:_col0 (type: bigint)
               |     Map-reduce partition columns:_col0 (type: bigint)
               |     sort order:+
               |     Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats:
NONE
               |     value expressions:_col1 (type: string)
               |     Select Operator [SEL_5]
               |        outputColumnNames:["_col0","_col1"]
               |        Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats:
NONE
               |        Filter Operator [FIL_13]
               |           predicate:false (type: boolean)
               |           Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column
stats: NONE
               |           TableScan [TS_3]
               |              alias:tabletest
               |              Statistics:Num rows: 2 Data size: 10 Basic stats: COMPLETE Column
stats: NONE
               |<-Select Operator [SEL_2]
                     outputColumnNames:["_col0"]
                     Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats:
NONE
                     Filter Operator [FIL_12]
                        predicate:(n = 1) (type: boolean)
                        Statistics:Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats:
NONE
                        TableScan [TS_0]
                           alias:tabletest
                           Statistics:Num rows: 2 Data size: 10 Basic stats: COMPLETE Column
stats: NONE

Time taken: 0.196 seconds, Fetched: 49 row(s)

> nvl funtion not working after left outer join 
> ----------------------------------------------
>
>                 Key: HIVE-13977
>                 URL: https://issues.apache.org/jira/browse/HIVE-13977
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 1.2.1
>            Reporter: balaswamy vaddeman
>
> Recreating problem.
> 1).Create table with sample data.
> create table tabletest (n bigint, t string); 
> insert into tabletest values (1, 'one'); 
> insert into tabletest values(2, 'two'); 
> 2) Run leftouter join query on single table.
> select a.n as leftHandN 
> , b.n as rightHandN 
> , b.t as rightHandT 
> , nvl(b.t,"empty") as rightHandTnvl -- Expected empty --> received empty
> , nvl(b.n,-1) as rightHandNnvl -- Expected -1 --> received 1 
> from 
> (
> select *
> from tabletest 
> where n=1
> ) a
> left outer join
> (
> select *
> from tabletest 
> where 1=2
> ) b
> on a.n = b.n;
> nvl(b.n,-1) should return -1 but returns 1.
> I have found b.n always returning a.n value.if a.n is 1 ,b.n is returning 1 and if it
is 2,same 2 will be returned.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message