[
https://issues.apache.org/jira/browse/CALCITE-375?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Brandon Chong updated CALCITE-375:
----------------------------------
Issue Type: Improvement (was: Bug)
> Support simpler decorrelation transformations for Exists and In
> ----------------------------------------------------------------
>
> Key: CALCITE-375
> URL: https://issues.apache.org/jira/browse/CALCITE-375
> Project: Calcite
> Issue Type: Improvement
> Reporter: Harish Butani
> Priority: Major
> 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
(v8.20.10#820010)
