[ https://issues.apache.org/jira/browse/CALCITE-3101?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Haisheng Yuan resolved CALCITE-3101. ------------------------------------ Resolution: Fixed Fix Version/s: 1.21.0 Fixed in https://github.com/apache/calcite/commit/0bbbae54dbac2bc8f738e091167dd9b027ad4f20. > 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 > Labels: pull-request-available > Fix For: 1.21.0 > > Time Spent: 1h 10m > Remaining Estimate: 0h > > 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: > {code:java} > CREATE TABLE "EMP" ( > "empno" INTEGER PRIMARY KEY NOT NULL, > "ename" VARCHAR(100), > "deptno" INTEGER);{code} > Start with this query: > {code:java} > SELECT "EMP"."empno", "t"."ename" "ename0" > FROM "EMP" > INNER JOIN "EMP" "t" > ON "EMP"."deptno" = "t"."deptno" AND "t"."ename" IS NOT NULL{code} > Parse using {{SqlToRelConverter.convertQuery()}}. At this point in the stack > trace: > {noformat} > 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) > {noformat} > the {{RelNode}} is: > {noformat} > LogicalJoin(condition=[AND(=($3, $8), IS NOT NULL($6))], joinType=[inner]) > JdbcTableScan(table=[[XYZ, EMP]]) > JdbcTableScan(table=[[XYZ, EMP]]) > {noformat} > After {{pushDownJoinConditions}} the {{RelNode}} is: > {noformat} > 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]]) > {noformat} > Which leads to invalid SQL ("ORA-00923: FROM keyword not found where > expected"): > {code:java} > 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" > {code} -- This message was sent by Atlassian JIRA (v7.6.14#76016)