[CALCITE-769] FilterProjectTransposeRule needs to avoid filter being pushed down if there is "correlating variable" in the condition

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.4.0-incubating
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Sean Hsuan-Yi Chu
• Created: 24/Jun/15 23:40
• Updated: 10/Oct/18 13:21
• Resolved: 24/Jul/15 16:39

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).

Issue Links

• causes: CALCITE-2617 FilterProjectTransposeRule should allow filter conditions with correlated variables to be pushed down
• depends upon: CALCITE-714 When de-correlating, push join condition into subquery