Xurenhe created CALCITE-5627: -------------------------------- Summary: The rule of 'CoreRules.FILTER_INTO_JOIN' executed error, if some condition is sub-query Key: CALCITE-5627 URL: https://issues.apache.org/jira/browse/CALCITE-5627 Project: Calcite Issue Type: Bug Components: core Reporter: Xurenhe Assignee: Xurenhe
I want to push down some filter's condition into scan using the rule of {*}FILTER_INTO_JOIN{*}, but I found that some filter's condition is pushed error, if these conditions is sub-query. eg: {code:java} -- sql select d1.deptno, e.mgr from sales.emp e, sales.dept d1 where d1.deptno = e.deptno and d1.deptno > e.mgr and d1.deptno > 10 and exists(select * from sales.dept d2 where d2.deptno = d1.deptno and d2.name like 'a%') {code} origin plan: {code:java} LogicalProject(DEPTNO=[$9], MGR=[$3]) LogicalFilter(condition=[AND(=($9, $7), >($9, $3), >($9, 10), EXISTS({ LogicalFilter(condition=[AND(=($0, $cor0.DEPTNO0), LIKE($1, 'a%'))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) }))], variablesSet=[[$cor0]]) LogicalJoin(condition=[true], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} result plan: {code:java} LogicalProject(DEPTNO=[$9], MGR=[$3]) LogicalJoin(condition=[AND(=($9, $7), >($9, $3))], joinType=[inner]) LogicalFilter(condition=[EXISTS({ LogicalFilter(condition=[AND(=($0, $cor0.DEPTNO0), LIKE($1, 'a%'))]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) })]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalFilter(condition=[>($0, 10)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {code} I think better result: condition of sub-query is pushed down into right side, which is near to `LogicalTableScan(table=[[CATALOG, SALES, DEPT]])` So, the class of *org.apache.calcite.plan.RelOptUtil.InputFinder* should support to analyze type of {*}RexFieldAccess{*}. -- This message was sent by Atlassian Jira (v8.20.10#820010)