[ https://issues.apache.org/jira/browse/DRILL-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15624610#comment-15624610 ]
Khurram Faraaz commented on DRILL-4985: --------------------------------------- Parquet schema details for tables t1 and t2. {noformat} [root@centos-01 drill4985]# ~/parquet-tools/parquet-schema t1/0_0_0.parquet message root { optional int32 col_int; optional binary col_chr (UTF8); optional binary col_vrchr1 (UTF8); optional binary col_vrchr2 (UTF8); optional int32 col_dt (DATE); optional int32 col_tim (TIME_MILLIS); optional int64 col_tmstmp (TIMESTAMP_MILLIS); optional float col_flt; optional fixed_len_byte_array(12) col_intrvl_yr (INTERVAL); optional fixed_len_byte_array(12) col_intrvl_day (INTERVAL); optional boolean col_bln; } {noformat} {noformat} [root@centos-01 drill4985]# ~/parquet-tools/parquet-schema t2/0_0_0.parquet message root { optional int32 col_int; optional binary col_chr (UTF8); optional binary col_vrchr1 (UTF8); optional binary col_vrchr2 (UTF8); optional int32 col_dt (DATE); optional int32 col_tim (TIME_MILLIS); optional int64 col_tmstmp (TIMESTAMP_MILLIS); optional float col_flt; optional fixed_len_byte_array(12) col_intrvl_yr (INTERVAL); optional fixed_len_byte_array(12) col_intrvl_day (INTERVAL); optional boolean col_bln; } {noformat} > 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)