[jira] [Commented] (OPTIQ-341) Redundant AggregateRel for IN subquery

["Aman Sinha (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPTIQ-341?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14138353#comment-14138353 ] 

Aman Sinha commented on OPTIQ-341:
----------------------------------

On the latest master branch, this query now gives a different plan using SemiJoinRel instead of AggregateRel.   This new plan looks correct to me. 

{code:sql}
EnumerableCalcRel(expr#0..1=[{inputs}], N_NATIONKEY=[$t0])
  EnumerableSemiJoinRel(condition=[=($1, $2)], joinType=[inner])
    EnumerableCalcRel(expr#0..3=[{inputs}], $f0=[$t0], $f4=[$t2])
      EnumerableTableAccessRel(table=[[TPCH, NATION]])
    EnumerableCalcRel(expr#0..2=[{inputs}], R_REGIONKEY=[$t0])
      EnumerableTableAccessRel(table=[[TPCH, REGION]])
{code}


> Redundant AggregateRel for IN subquery
> --------------------------------------
>
>                 Key: OPTIQ-341
>                 URL: https://issues.apache.org/jira/browse/OPTIQ-341
>             Project: Optiq
>          Issue Type: Bug
>            Reporter: Aman Sinha
>            Assignee: Julian Hyde
>
> The following query against TPCH creates 2 AggregateRels for the IN subquery....one for the Group-By and one for the DISTINCT on the same column.  Since Group-by is already doing the distinct, the second AggregateRel is redundant and hurts performance.  
> {code:sql}
> SELECT n_name FROM nation 
>   WHERE n_regionkey IN (SELECT r_regionkey FROM region 
>                                             GROUP BY r_regionkey);
> ProjectRel(n_name=[$2])
>   JoinRel(condition=[=($3, $4)], joinType=[inner])
>     ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$1])
>       EnumerableTableAccessRel(table=[[dfs, TpchSf1, nation]])
>     AggregateRel(group=[{0}])
>       AggregateRel(group=[{0}])
>         ProjectRel(r_regionkey=[$1])
>           EnumerableTableAccessRel(table=[[dfs, TpchSf1, region]])
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)