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: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single node

Solved Go to solution

IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single node

New Contributor

I have enable stats using `COMPUTE STATS` on a table. After this when I re-run the query, it's giving me an error `A RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single node plan`. Dropping the stats is execuring the query. I'm trying to improve the query performance.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single n

New Contributor

By default, Impala uses something called as a “broadcast join” which assumes that the left table is larger than the right. Which is not true in my case.

 

It does the join inversion inherently if this is not done in the query.

When such an inversion is to be overridden, it is suggested to be overriden by the keyword “STRAIGHT_JOIN“.

 

Or re-write the query to swap the join sets and make the left join a right join.

Since in this case it’s non-equi join, right outer joins do not support a right outer join in Impala yet. Replaced the left join with an INNER JOIN  and swapping the datasets.

 

4 REPLIES 4

Re: IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single n

New Contributor
Note: My query has a left join and doesn’t have a right join.

Select f1, f2, f3 from ( Select t.t1, t.t2, t.t3 from table1 t Left join Select p.p1, p.p2, p.p3 from table2 p on p.p1=t.t1)i Where condition

Re: IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single n

New Contributor

By default, Impala uses something called as a “broadcast join” which assumes that the left table is larger than the right. Which is not true in my case.

 

It does the join inversion inherently if this is not done in the query.

When such an inversion is to be overridden, it is suggested to be overriden by the keyword “STRAIGHT_JOIN“.

 

Or re-write the query to swap the join sets and make the left join a right join.

Since in this case it’s non-equi join, right outer joins do not support a right outer join in Impala yet. Replaced the left join with an INNER JOIN  and swapping the datasets.

 

Highlighted

Re: IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single n

Master Collaborator

You shouldn't be seeing this error. I think you are probably hitting an old bug where sometimes an invalid execution plan was generated. - https://issues.apache.org/jira/browse/IMPALA-5689 or https://issues.apache.org/jira/browse/IMPALA-3063

 

It should be fixed in CDH5.13.0 or later.

Re: IMPALA: RIGHT OUTER JOIN type with no equi-join predicates can only be executed with a single n

New Contributor

Probably, the version we are using is 5.12.

Don't have an account?
Coming from Hortonworks? Activate your account here