Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Impala excute sql explain abnormal

Impala excute sql explain abnormal

New Contributor

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

 

4 REPLIES 4

Re: Impala excute sql explain abnormal

Master Collaborator
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.

Re: Impala excute sql explain abnormal

New Contributor
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

Re: Impala excute sql explain abnormal

Cloudera Employee

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.

Re: Impala excute sql explain abnormal

New Contributor
Hive version is 1.1.0,and it's ok in Hive exactly
Don't have an account?
Coming from Hortonworks? Activate your account here