Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3101

PushDownJoinConditions is not always a valid transformation

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.16.0
    • Fix Version/s: 1.21.0
    • Component/s: core
    • Environment:

      Java app pointing to Oracle database.

      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"
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                pauljackson123 Paul Jackson
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 10m
                  1h 10m