Hive support for subquery in Join condition?

Question

Error: Error while compiling statement: FAILED: SemanticException in encountered with 0 children (state=42000,code=40000)

Do I need to find a solution to getting the subquery out of the on condition?

select
-- a bunch of stuff min,max,sum and case statements
from tbl0 t0
inner join tbl4  t4  on (t4.aKey = t0.aKey)  
left outer join tbl1  t1  on (t0.col0 = t1.col0 and t1.someKey in (select t3.aKey from tbl3 t3 where t3.someCode in ('A1','A2','A3')))
where
not(t4.aCode in ('string1' , 'strin2' , 'string3' , 'string4') and t1.someKey is null) and not (t4.bCode in ('string1' , 'string2') and t1.someCol = 0)

@DuduMarkovitz there are 26 joins, i only put the ones in that are causing the issue as it can get a little crazy...The query is 192 lines in sublime...And yes it serves a unique purpose and is run once an evening. It's an aggregate for ETL replacement...Also why the names are funky don't want to use vendor logic/naming — AM_Hawk, Mar 23 '17 at 16:45
This wasn't a style remark. As you noticed, `t4` was used not defined in your query. — David דודו Markovitz, Mar 23 '17 at 16:47
@DuduMarkovitz yup apologies i threw it in to avoid confusion going forward. — AM_Hawk, Mar 23 '17 at 16:48
collect data of all the sub query in some other table and put it in join. If it works for your usecase. — Ashish Singh, Mar 24 '17 at 05:05

score 0 · Answer 1 · answered Feb 08 '18 at 19:49

I'm also getting this error in Hive 1.1.

I think Ashish Singh likely has the best suggestion: change tbl1 to a subquery by moving the IN (SELECT ...) statement inside of that table definition.

Your query would then become:

select -- a bunch of stuff min,max,sum and case statements from tbl0 t0 inner join tbl4 t4 on (t4.aKey = t0.aKey) left outer join (SELECT col0 FROM tbl1 WHERE somekey IN (SELECT t3.aKey FROM tbl3 t3 WHERE t3.someCode in ('A1','A2','A3'))) t1 on (t0.col0 = t1.col0) where not(t4.aCode in ('string1' , 'strin2' , 'string3' , 'string4') and t1.someKey is null) and not (t4.bCode in ('string1' , 'string2') and t1.someCol = 0)

Hive support for subquery in Join condition?

1 Answers1