[ 
https://issues.apache.org/jira/browse/CALCITE-375?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Julian Hyde updated CALCITE-375:
--------------------------------
    Labels: sub-query  (was: )

> Support simpler decorrelation transformations for Exists and In 
> ----------------------------------------------------------------
>
>                 Key: CALCITE-375
>                 URL: https://issues.apache.org/jira/browse/CALCITE-375
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Harish Butani
>            Assignee: Julian Hyde
>              Labels: sub-query
>
> Decorrelation always generates a Value Expression for the driving(outer) 
> Relation Expression and then joins it with the inner Relation. 
> In the case there are equality correlation expressions it is possible to 
> generate a distinct list from the inner Query Block.
> For e.g.
> {code}
> select * from dept where exists (
>   select * from emp
>   where emp.deptno = dept.deptno
>   and emp.sal > 100
> {code}
> gets converted to:
> {code}
> ProjectRel(DEPTNO=[$0], NAME=[$1])
>   JoinRel(condition=[=($0, $2)], joinType=[inner])
>     TableAccessRel(table=[[CATALOG, SALES, DEPT]])
>     AggregateRel(group=[{0}], agg#0=[MIN($1)])
>       ProjectRel($f01=[$2], $f0=[true])
>         JoinRel(condition=[=($1, $2)], joinType=[inner])
>           FilterRel(condition=[>($0, 100)])
>             ProjectRel(SAL=[$5], DEPTNO=[$7])
>               TableAccessRel(table=[[CATALOG, SALES, EMP]])
>           AggregateRel(group=[{0}])
>             ProjectRel($f0=[$0])
>               ProjectRel(DEPTNO=[$0])
>                 TableAccessRel(table=[[CATALOG, SALES, DEPT]])
> {code}
> But this should suffice:
> {code}
> ProjectRel(DEPTNO=[$0], NAME=[$1])
>   JoinRel(condition=[=($0, $2)], joinType=[inner])
>     TableAccessRel(table=[[CATALOG, SALES, DEPT]])
>     AggregateRel(group=[{0}], agg#0=[MIN($1)])
>       ProjectRel($f01=[$0], $f0=[true])
>           FilterRel(condition=[>($0, 100)])
>             ProjectRel(SAL=[$5], DEPTNO=[$7])
>               TableAccessRel(table=[[CATALOG, SALES, EMP]])
> {code}



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

Reply via email to