[ https://issues.apache.org/jira/browse/CALCITE-1495?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15670991#comment-15670991 ]
Julian Hyde commented on CALCITE-1495: -------------------------------------- Yes, {{SemiJoinRule}} ensures that no columns from the RHS are used. One improvement: Currently it looks for an {{Aggregate}} as its input. It would be better if it looked for an input that is unique on the join key. > 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)