[CALCITE-1726] Subquery in FILTER is left untransformed

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.19.0
• Labels: pull-request-available sub-query
• Assignee: Vineet Garg
• Reporter: Vineet Garg
• Created: 28/Mar/17 20:35
• Updated: 27/Feb/24 22:23
• Resolved: 12/Feb/19 20:15

Description

Reproducer

Query:

select * from emp 
  where empno IN (select (select max(sal) from emp) from dept)

Plan after SubqueryRemoveRule

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
    LogicalJoin(condition=[=($0, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}])
        LogicalProject(EXPR$0=[$SCALAR_QUERY({
LogicalAggregate(group=[{}], EXPR$0=[MAX($0)])
  LogicalProject(SAL=[$5])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})])
          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])

As you can notice scalar query in LogicalProject is left as it is

Issue Links

• links to: GitHub Pull Request #1035