Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
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
Attachments
Issue Links
- causes
-
CALCITE-2617 FilterProjectTransposeRule should allow filter conditions with correlated variables to be pushed down
- Closed
- depends upon
-
CALCITE-714 When de-correlating, push join condition into subquery
- Closed