[ https://issues.apache.org/jira/browse/CALCITE-5627?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17707274#comment-17707274 ]
Xurenhe commented on CALCITE-5627: ---------------------------------- [~libenchao] Thanks for your quick reply. In some cases, keeping origin sub-query and pushing down is better way to optimize the query plan. The plan will be complex after executing the rule of removing sub-query and removing correlate. In this case, it's better way by pushing sub-query down as much as possible. > 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 > Priority: Major > > 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)