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.

IllegalStateException when JOIN table with additional exprssion

IllegalStateException when JOIN table with additional exprssion

Contributor

I find that using a subquery for additional join clauses throws an exception.

 

select *
from t1
JOIN
   (SELECT naam, max(aud_id) as aud_id
   FROM t2 
   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)
from drp_reseller_t
WHERE ....
)

 

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
at com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:382)
at com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:337)
at com.cloudera.impala.service.Frontend.analyzeStmt(Frontend.java:887)
at com.cloudera.impala.service.Frontend.createExecRequest(Frontend.java:916)
at com.cloudera.impala.service.JniFrontend.createExecRequest(JniFrontend.java:150)
Caused by: java.lang.IllegalStateException
at com.google.common.base.Preconditions.checkState(Preconditions.java:129)
at com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:376)
... 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)

2 REPLIES 2

Re: IllegalStateException when JOIN table with additional exprssion

Master Collaborator

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?

Highlighted

Re: IllegalStateException when JOIN table with additional exprssion

Contributor

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.