I find that using a subquery for additional join clauses throws an exception.
(SELECT naam, max(aud_id) as aud_id
WHERE GELDIG_VANAF < cast('2016-11-26' as timestamp))
) as validdoms on dom.aud_id = validdoms.aud_id
LEFT OUTER JOIN t2 res ON t1.rsr_id = t2.rsr_id
and res.aud_id = (select max(aud_id)
Using a subquery causes and IllegalStateException
When using a value such as "and res.aud_id = 1234" the query works.
Is using a subquery in combination with additional join clauses not supported?
this the log output:
1127 16:58:08.307395 2900 jni-util.cc:169] com.cloudera.impala.common.AnalysisException
Caused by: java.lang.IllegalStateException
... 4 more
I1127 16:58:08.314187 2900 status.cc:114] AnalysisException: null
CAUSED BY: IllegalStateException: null
@ 0x84ed69 (unknown)
@ 0xba2fd0 (unknown)
@ 0xab80c4 (unknown)
@ 0xad4357 (unknown)
@ 0xad9d08 (unknown)
@ 0xb084ab (unknown)
@ 0xd7e6b6 (unknown)
@ 0xd782d4 (unknown)
@ 0x81c66c (unknown)
@ 0x1b5135b (unknown)
@ 0x1b38f09 (unknown)
@ 0xa0ecb9 (unknown)
@ 0xa0f712 (unknown)
@ 0xbf5c39 (unknown)
@ 0xbf65d4 (unknown)
@ 0xe5cc0a (unknown)
@ 0x7f08600e6e9a start_thread
@ 0x7f085fe1436d (unknown)
It looks like you ran into a known issue: https://issues.cloudera.org/browse/IMPALA-1478
Aside from the error message, I don't think we support executing subqueries in that location (definitely not in a way that will perform well).
Would rewriting it by moving the condition into the where clause work for you?
ah not supported, too bad.
When i rewrite the query and move the subquery to the WHERE clause i get no results while it should return result rows.