[jira] [Created] (CALCITE-3101) PushDownJoinConditions is not always a valid transformation

["Paul Jackson (JIRA)" <ji...@apache.org>]

Paul Jackson created CALCITE-3101:
-------------------------------------

             Summary: PushDownJoinConditions is not always a valid transformation
                 Key: CALCITE-3101
                 URL: https://issues.apache.org/jira/browse/CALCITE-3101
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.16.0
         Environment: Java app pointing to Oracle database.
            Reporter: Paul Jackson


SqlToRelConverter can create a plan that is invalid when converted back to SQL in cases where the expression that is pushed to the projection returns a Boolean. The following example pushes IS NOT NULL to a select. Several SQL dialects do not support this. Oracle, for example, sees IS NOT NULL as a condition rather than an expression. It returns a Boolean data type, which is not supported. Likewise, Microsoft SQL Server does not support IS NOT NULL in a projection expression.

Steps to reproduce (Oracle) :

DDL:

{{CREATE TABLE "EMP" (}}
{{ "empno" INTEGER PRIMARY KEY NOT NULL,}}
{{ "ename" VARCHAR(100),}}
{{"deptno" INTEGER);}}

Start with this query:

{{SELECT "EMP"."empno", "t"."ename" "ename0"}}
{{FROM "EMP"}}
{{INNER JOIN "EMP" "t"}}
{{ON "EMP"."deptno" = "t"."deptno" AND "t"."ename" IS NOT NULL}}

Parse using SqlToRelConverter.convertQuery()

At his point in the stack trace:

{{org.apache.calcite.plan.RelOptUtil.pushDownJoinConditions(RelOptUtil.java:3222)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2414)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2056)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:641)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:622)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3057)}}
{{org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:558)}}

the RelNode is:

{{LogicalJoin(condition=[AND(=($3, $8), IS NOT NULL($6))], joinType=[inner])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}

After pushDownJoinConditions the RelNode is:

{{LogicalJoin(condition=[AND(=($3, $8), $10)], joinType=[inner])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}
{{ LogicalProject(empno=[$0], ename=[$1], job=[$2], deptno=[$3], etype=[$4], $f5=[IS NOT NULL($1)])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}

Which leads to invalid SQL (ORA-00923: FROM keyword not found where expected) :

{{SELECT "EMP"."empno", "t"."ename" "ename0"}}
{{FROM "XYZ"."EMP"}}
{{INNER JOIN (SELECT "empno", "ename", "job", "deptno", "etype", "ename" IS NOT NULL "$f5"}}
{{FROM "XYZ"."EMP") "t" ON "EMP"."deptno" = "t"."deptno" AND "t"."$f5"}}

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)