Reply
New Contributor
Posts: 3
Registered: ‎03-13-2019

Impala excute sql explain abnormal

for example:

 

select t.* from (select a.*,b.tid tid from tA a left join tB b on tA.cid = tB.cid ) t where t.tid = 'xxx';

 

excute the sql explain,we found the filter `where t.tid = 'xxx'` excuted  when scan table tB,but that's not what I wanted,I hope the filter excuted after the `left join` action

 

the explain is correct when excuted in hive

 

How to solve this problem in impala?

thanks

 

Master
Posts: 426
Registered: ‎07-01-2015

Re: Impala excute sql explain abnormal

Hi, putting an equal sign on the LEFT JOINed table means that the query planner will switch the execution to INNER JOIN (because t.id has to be equal to something, in this case 'xxx').
And going further, it makes sense to filter the rows out from the table tB and then do the inner join.
Hope it explains the behaviour,
T.
Highlighted
New Contributor
Posts: 3
Registered: ‎03-13-2019

Re: Impala excute sql explain abnormal

If filter the rows out from the table tB firstly,it won't filter t.tid IS NULL after the LEFT JOINed,the problem aroud me
Cloudera Employee
Posts: 9
Registered: ‎03-22-2019

Re: Impala excute sql explain abnormal

Agree with the comment from 

 

You mentioned that things work correctly in Hive, what version of Hive are you using? I checked apache/hive master branch and it follows the same behavior as Impala.

New Contributor
Posts: 3
Registered: ‎03-13-2019

Re: Impala excute sql explain abnormal

Hive version is 1.1.0,and it's ok in Hive exactly