[jira] [Created] (CALCITE-5627) The rule of 'CoreRules.FILTER_INTO_JOIN' executed error, if some condition is sub-query

["Xurenhe (Jira)" <ji...@apache.org>]

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)