[
https://issues.apache.org/jira/browse/DRILL-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Khurram Faraaz updated DRILL-4985:
----------------------------------
Priority: Critical (was: Major)
> CAST in join predicate in null equality join - "query cannot be planned error"
> ------------------------------------------------------------------------------
>
> Key: DRILL-4985
> URL: https://issues.apache.org/jira/browse/DRILL-4985
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.9.0
> Reporter: Khurram Faraaz
> Priority: Critical
>
> Use of CAST in join predicate results in, "query cannot be planned error", in
> a Null Equality Join.
> Drill 1.9.0, git commit ID: a29f1e29
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT t1.col_int , t2.col_int FROM typeall_l
> t1, typeall_r t2 WHERE CAST(t1.col_int AS BIGINT)= CAST(t2.col_int AS BIGINT)
> OR ( t1.col_int IS NULL AND t2.col_int IS NULL );
> Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due
> to either a cartesian join or an inequality join
> [Error Id: 10452a20-c0a7-45b1-8ee7-065b28484738 on centos-01.qa.lab:31010]
> (state=,code=0)
> {noformat}
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT t1.col_int , t2.col_int FROM typeall_l
> t1, typeall_r t2 WHERE CAST(t1.col_int AS BIGINT)= CAST(t2.col_int AS BIGINT)
> OR ( CAST(t1.col_int AS BIGINT) IS NULL AND CAST(t2.col_int AS BIGINT) IS
> NULL );
> Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due
> to either a cartesian join or an inequality join
> [Error Id: ca8eba6c-b8c0-42d9-a0a0-16e94f887280 on centos-01.qa.lab:31010]
> (state=,code=0)
> {noformat}
> Whereas, without the CAST to BIGINT in the join predicate, query returns
> correct results.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT t1.col_int , t2.col_int FROM typeall_l
> t1, typeall_r t2 WHERE t1.col_int = t2.col_int OR ( t1.col_int IS NULL AND
> t2.col_int IS NULL );
> +----------+-----------+
> | col_int | col_int0 |
> +----------+-----------+
> | 30410 | 30410 |
> | 37420 | 37420 |
> | 58583 | 58583 |
> | 1 | 1 |
> | null | null |
> | null | null |
> | null | null |
> | 20326 | 20326 |
> | null | null |
> | null | null |
> | null | null |
> | 19 | 19 |
> | 60718 | 60718 |
> | null | null |
> | null | null |
> | null | null |
> +----------+-----------+
> 16 rows selected (0.304 seconds)
> {noformat}
> And without the IS NULL check in ( t1.col_int IS NULL AND t2.col_int IS NULL )
> Equality join query returns correct results.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT t1.col_int , t2.col_int FROM typeall_l
> t1, typeall_r t2 WHERE CAST(t1.col_int AS BIGINT)= CAST(t2.col_int AS BIGINT);
> +----------+-----------+
> | col_int | col_int0 |
> +----------+-----------+
> | 30410 | 30410 |
> | 37420 | 37420 |
> | 58583 | 58583 |
> | 1 | 1 |
> | 20326 | 20326 |
> | 19 | 19 |
> | 60718 | 60718 |
> +----------+-----------+
> 7 rows selected (0.424 seconds)
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
