[jira] [Created] (OPTIQ-375) Support simpler decorrelation transformations for Exists and In

["Harish Butani (JIRA)" <ji...@apache.org>]

Harish Butani created OPTIQ-375:
-----------------------------------

             Summary: Support simpler decorrelation transformations for Exists and In 
                 Key: OPTIQ-375
                 URL: https://issues.apache.org/jira/browse/OPTIQ-375
             Project: Optiq
          Issue Type: Bug
            Reporter: Harish Butani
            Assignee: Julian Hyde


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.2#6252)