[ https://issues.apache.org/jira/browse/CALCITE-1495?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vineet Garg updated CALCITE-1495: --------------------------------- Description: For IN and EXISTS subqueries Calcite currently generates plan consisting of GROUP BY on inner table followed by INNER JOIN with outer table. e.g. for following query: {noformat} select sal from emp where empno IN (select deptno from dept) {noformat} Generated plan is: {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=[=($0, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {noformat} Such cases could be converted using this rule to use SEMI-JOIN to make it more efficient was: For IN and EXISTS subqueries Calcite currently generates plan consisting of GROUP BY on inner table followed by INNER JOIN with outer table. e.g. for following query: {noformat} :select sal from emp where empno IN (select deptno from dept) {noformat} Generated plan is: {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=[=($0, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) {noformat} Such cases could be converted using this rule to use SEMI-JOIN to make it more efficient > Add a rule to convert INNER JOIN preceded by GROUP BY to appropriate SEMI-JOIN > ------------------------------------------------------------------------------ > > Key: CALCITE-1495 > URL: https://issues.apache.org/jira/browse/CALCITE-1495 > Project: Calcite > Issue Type: New Feature > Components: core > Reporter: Vineet Garg > Assignee: Julian Hyde > > For IN and EXISTS subqueries Calcite currently generates plan consisting of > GROUP BY on inner table followed by INNER JOIN with outer table. > e.g. for following query: > {noformat} select sal from emp where empno IN (select deptno from dept) > {noformat} > Generated plan is: > {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=[=($0, $9)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalAggregate(group=[{0}]) > LogicalProject(DEPTNO=[$0]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > {noformat} > Such cases could be converted using this rule to use SEMI-JOIN to make it > more efficient -- This message was sent by Atlassian JIRA (v6.3.4#6332)