[jira] [Commented] (HIVE-13977) nvl funtion not working after left outer join
[ https://issues.apache.org/jira/browse/HIVE-13977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15347644#comment-15347644 ] balaswamy vaddeman commented on HIVE-13977: --- This issue is same as https://issues.apache.org/jira/browse/HIVE-14027 > 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. > More information: > length(b.n) ---gives--->1 > cast(b.n as string) --gives--->1 > ascii(b.n) --gives>49 i.e 1 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-13977) nvl funtion not working after left outer join
[ https://issues.apache.org/jira/browse/HIVE-13977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15323978#comment-15323978 ] Gopal V commented on HIVE-13977: [~vaddebalu]: I can't reproduce this issue in the latest hive, so it is likely to have been fixed between 1.2.0 and 2.1.0 My query runs return {code} 1 NULLNULLempty -1 Time taken: 2.526 seconds, Fetched: 1 row(s) {code} > 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. > More information: > length(b.n) ---gives--->1 > cast(b.n as string) --gives--->1 > ascii(b.n) --gives>49 i.e 1 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-13977) nvl funtion not working after left outer join
[ 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)
[jira] [Commented] (HIVE-13977) nvl funtion not working after left outer join
[ https://issues.apache.org/jira/browse/HIVE-13977?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15322138#comment-15322138 ] Gopal V commented on HIVE-13977: [~vaddebalu]: can you post the output of "explain " with {{set hive.execution.engine=tez;}} ? > 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 > ) > 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)