[CALCITE-388] Handle semi-joins in field trimmer

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.9.1-incubating
• Assignee: Julian Hyde
• Reporter: Harish Butani
• Created: 26/Aug/14 20:36
• Updated: 18/Oct/14 22:36
• Resolved: 26/Aug/14 22:17

Description

For e.g. for the following query:

select s.deptno from (select * from dept where exists (
  select * from emp
  where emp.deptno = dept.deptno
  and emp.sal > 100)) s join customer.account on s.deptno = account.acctno

Projection of DeptNo should get pushed below SemiJoin

ProjectRel(DEPTNO=[$0])
  JoinRel(condition=[=($0, $1)], joinType=[inner])
    SemiJoinRel(condition=[=($0, $1)], joinType=[inner])
      ProjectRel(DEPTNO=[$0])
        TableAccessRel(table=[[CATALOG, SALES, DEPT]])
      ProjectRel($f01=[$2])
        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]])
    ProjectRel(ACCTNO=[$0])
      TableAccessRel(table=[[CATALOG, CUSTOMER, ACCOUNT]])