Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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]])