[ https://issues.apache.org/jira/browse/CALCITE-2329?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde resolved CALCITE-2329. ---------------------------------- Resolution: Fixed Fix Version/s: 1.17.0 Fixed in [1ae6a526|http://git-wip-us.apache.org/repos/asf/calcite/commit/1ae6a526]. > 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 > Fix For: 1.17.0 > > > 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)