[
https://issues.apache.org/jira/browse/HIVE-11335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
fatkun updated HIVE-11335:
--------------------------
Description:
test step
{code}
create table log (uid string, uid2 string);
insert into log values ('1', '1');
create table user (uid string, name string);
insert into user values ('1', "test1");
{code}
(Query1)
{code}
select b.name, c.name from log a
left outer join (select uid, name from user) b on (a.uid=b.uid)
left outer join user c on (a.uid2=c.uid);
{code}
return wrong result：
1 test1
It should be both return test1
(Query2)I try to find error, if I use this query, return right result.（join key
different）
{code}
select b.name, c.name from log a
left outer join (select uid, name from user) b on (a.uid=b.uid)
left outer join user c on (a.uid=c.uid);
{code}
The explain is different,Query1 only select one colum. It should select uid and
name.
{code}
b:user
TableScan
alias: user
Statistics: Num rows: 1 Data size: 7 Basic stats: COMPLETE Column
stats: NONE
Select Operator
expressions: uid (type: string)
outputColumnNames: _col0
{code}
It may relate HIVE-10996
=========UPDATE1===================
(Query3) this query return correct result
{code}
select b.name, c.name from log a
left outer join (select user.uid, user.name from user) b on (a.uid=b.uid)
left outer join user c on (a.uid2=c.uid);
{code}
the operator tree
TS[0]-SEL[1]-RS[5]-JOIN[6]-RS[7]-JOIN[9]-SEL[10]-FS[11]
TS[2]-RS[4]-JOIN[6]
TS[3]-RS[8]-JOIN[9]
the Query1 SEL[1] rowSchema is wrong, cannot detect the tabAlias
was:
test step
{code}
create table log (uid string, uid2 string);
insert into log values ('1', '1');
create table user (uid string, name string);
insert into user values ('1', "test1");
select b.name, c.name from log a
left outer join (select uid, name from user) b on (a.uid=b.uid)
left outer join user c on (a.uid2=c.uid);
{code}
return wrong result：
1 test1
It should be both return test1
I try to find error, if I use this query, return right result.（join key
different）
{code}
select b.name, c.name from log a
left outer join (select uid, name from user) b on (a.uid=b.uid)
left outer join user c on (a.uid=c.uid);
{code}
The explain is different,Query1 only select one colum. It should select uid and
name.
{code}
b:user
TableScan
alias: user
Statistics: Num rows: 1 Data size: 7 Basic stats: COMPLETE Column
stats: NONE
Select Operator
expressions: uid (type: string)
outputColumnNames: _col0
{code}
I think there is something wrong in ColumnPruner.But i cannot find it out.
It may relate HIVE-10996
> Multi-Join Inner Query producing incorrect results
> --------------------------------------------------
>
> Key: HIVE-11335
> URL: https://issues.apache.org/jira/browse/HIVE-11335
> Project: Hive
> Issue Type: Bug
> Components: Query Planning
> Affects Versions: 1.1.0
> Environment: CDH5.4.0
> Reporter: fatkun
> Attachments: query1.txt, query2.txt
>
>
> test step
> {code}
> create table log (uid string, uid2 string);
> insert into log values ('1', '1');
> create table user (uid string, name string);
> insert into user values ('1', "test1");
> {code}
> (Query1)
> {code}
> select b.name, c.name from log a
> left outer join (select uid, name from user) b on (a.uid=b.uid)
> left outer join user c on (a.uid2=c.uid);
> {code}
> return wrong result：
> 1 test1
> It should be both return test1
> (Query2)I try to find error, if I use this query, return right result.（join
> key different）
> {code}
> select b.name, c.name from log a
> left outer join (select uid, name from user) b on (a.uid=b.uid)
> left outer join user c on (a.uid=c.uid);
> {code}
> The explain is different,Query1 only select one colum. It should select uid
> and name.
> {code}
> b:user
> TableScan
> alias: user
> Statistics: Num rows: 1 Data size: 7 Basic stats: COMPLETE Column
> stats: NONE
> Select Operator
> expressions: uid (type: string)
> outputColumnNames: _col0
> {code}
> It may relate HIVE-10996
> =========UPDATE1===================
> (Query3) this query return correct result
> {code}
> select b.name, c.name from log a
> left outer join (select user.uid, user.name from user) b on (a.uid=b.uid)
> left outer join user c on (a.uid2=c.uid);
> {code}
> the operator tree
> TS[0]-SEL[1]-RS[5]-JOIN[6]-RS[7]-JOIN[9]-SEL[10]-FS[11]
> TS[2]-RS[4]-JOIN[6]
> TS[3]-RS[8]-JOIN[9]
> the Query1 SEL[1] rowSchema is wrong, cannot detect the tabAlias
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
