drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DRILL-4985) CAST in join predicate in null equality join - "query cannot be planned error"
Date Tue, 01 Nov 2016 07:40:00 GMT

     [ https://issues.apache.org/jira/browse/DRILL-4985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Khurram Faraaz updated DRILL-4985:
----------------------------------
    Description: 
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}

  was:
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}


> 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}
> 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)

Mime
View raw message