[
https://issues.apache.org/jira/browse/CALCITE-5627?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17707244#comment-17707244
]
Benchao Li commented on CALCITE-5627:
-------------------------------------
I agree that the correlation id and sub-query is not easy with most
optimization rules. Now, we usually use {{SubQueryRemoveRule}} to rewrite
sub-queries into {{Join}} and {{Correlate}}, and do optimizations after that.
There is a discussion about improving the SubQuery[1], so far we haven't get
that in yet.
[1] https://lists.apache.org/thread/fvf35njbddcck9hwcqnnxowl49n4nkth
> 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)
