[CALCITE-375] Support simpler decorrelation transformations for Exists and In

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: sub-query
• Assignee: Unassigned
• Reporter: Harish Butani
• Created: 15/Aug/14 00:59
• Updated: 01/Aug/24 17:33

Description

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.

select * from dept where exists (
  select * from emp
  where emp.deptno = dept.deptno
  and emp.sal > 100

gets converted to:

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]])

But this should suffice:

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]])