[
https://issues.apache.org/jira/browse/CALCITE-2329?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16504633#comment-16504633
]
Volodymyr Vysotskyi commented on CALCITE-2329:
----------------------------------------------
I reworked my PR, so now plan for IN with literals from the left side differs
from the plan for EXISTS, but it much simpler than before the changes.
Added .iq tests for different cases, including correlated and non-correlated
subqueries.
Could you please take a look again? For easier review, I made several commits:
the first one contains only refactoring changes, the second commit contains
changes connected with optimizing IN with literals.
> Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the
> left side more optimally
> -----------------------------------------------------------------------------------------------------
>
> Key: CALCITE-2329
> URL: https://issues.apache.org/jira/browse/CALCITE-2329
> Project: Calcite
> Issue Type: Improvement
> Reporter: Volodymyr Vysotskyi
> Assignee: Julian Hyde
> Priority: Major
>
> Currently, for queries like this:
> {code:sql}
> select sal from emp where 2 IN (select deptno from dept)
> {code}
> SubQueryRemoveRule rules expand query plan in such a way:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[=(2, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
> Since join condition is actually a filter condition, it will be pushed into
> the filter during further planning stages and join with the true condition is
> left.
> But these types of the queries may be rewritten in the same way as EXISTS
> queries:
> {code:sql}
> select sal from emp where EXISTS (select deptno from dept where deptno=2)
> {code}
> with the more optimal plan:
> {noformat}
> LogicalProject(SAL=[$5])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[=($0, 2)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
