Khurram Faraaz created DRILL-4985: ------------------------------------- Summary: 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
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} -- This message was sent by Atlassian JIRA (v6.3.4#6332)