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

FilterProjectTransposeRule needs to avoid filter being pushed down if there is "correlating variable" in the condition

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.4.0-incubating
    • Component/s: None
    • Labels:
      None

      Description

      The logical plan (prior to de-correlation) for the query in JdbcTest.testWithInsideWhereExists is

      LogicalProject(deptno=[$1])
        *LogicalFilter(condition=[IS NOT NULL($5)])
          LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4], $f0=[$7])
            LogicalJoin(condition=[AND(=($1, $5), =($1, $6))], joinType=[left])
              EnumerableTableScan(table=[[hr, emps]])
              LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
                LogicalProject(deptno1=[$1], deptno0=[$2], $f0=[$0])
                  LogicalProject($f0=[true], deptno1=[$1], deptno0=[$2])
                    LogicalProject(EXPR$0=[1], deptno1=[$5], deptno0=[$4])
                      LogicalFilter(condition=[<=($0, $5)])
                        LogicalJoin(condition=[true], joinType=[inner])
                          LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3], deptno0=[$4])
                            LogicalFilter(condition=[>=($0, $4)])
                              LogicalJoin(condition=[true], joinType=[inner])
                                EnumerableTableScan(table=[[hr, depts]])
                                LogicalAggregate(group=[{0}])
                                  LogicalProject(deptno=[$1])
                                    EnumerableTableScan(table=[[hr, emps]])
                          LogicalAggregate(group=[{0}])
                            LogicalProject(deptno=[$1])
                              EnumerableTableScan(table=[[hr, emps]])
      

      We would like to push the filter with asterisk below project in order to use FILTER_ON_JOIN rule to convert the left-join to inner-join.

      However, after we added FilterProjectTransposeRule. We got the plan:

      LogicalProject(deptno=[$1])
        LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4], $f0=[$6])
          LogicalFilter(condition=[IS NOT NULL($6)])
            LogicalJoin(condition=[=($1, $5)], joinType=[left])
              EnumerableTableScan(table=[[hr, emps]])
              LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
                LogicalProject(deptno0=[$1], $f0=[$0])
                  LogicalProject($f0=[true], deptno0=[$1])
                    LogicalProject(EXPR$0=[1], deptno0=[$4])
                      LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3], deptno0=[$4])
                        *LogicalFilter(condition=[<=($0, $cor1.deptno)])
                          LogicalFilter(condition=[>=($0, $4)])
                            LogicalJoin(condition=[true], joinType=[inner])
                              EnumerableTableScan(table=[[hr, depts]])
                              LogicalAggregate(group=[{0}])
                                LogicalProject(deptno=[$1])
                                  EnumerableTableScan(table=[[hr, emps]])
      

      The filter with asterisk contains the correlating variable, which will lead to an exception (stack trace given in the attachment).

        Attachments

        1. log.txt
          6 kB
          Sean Hsuan-Yi Chu

          Issue Links

            Activity

              People

              • Assignee:
                seanhychu Sean Hsuan-Yi Chu
                Reporter:
                seanhychu Sean Hsuan-Yi Chu
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: