[CALCITE-3101] PushDownJoinConditions is not always a valid transformation

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.16.0
• Fix Version/s: 1.21.0
• Component/s: core
• Labels: pull-request-available

Environment

Java app pointing to Oracle database.

• Assignee: Unassigned
• Reporter: Paul Jackson
• Created: 29/May/19 23:52
• Updated: 10/Sep/19 21:36
• Resolved: 07/Aug/19 07:06

Description

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 this 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"

Issue Links

• links to: GitHub Pull Request #1256