[
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)
