[
https://issues.apache.org/jira/browse/CALCITE-3101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16851485#comment-16851485
]
Laurent Goujon commented on CALCITE-3101:
-----------------------------------------
One could argue that in Calcite dialect/Rel tree, {{IS NOT NULL}} is a valid
operator, but it should be the responsability of the target {{SqlDialect}} to
convert it back into something understood by the RDBMS (Oracle in that case).
> 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
> Priority: Major
>
> 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)
