[CALCITE-2936] Simplify EXISTS or NOT EXISTS sub-query that has "GROUP BY ()"

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Haisheng Yuan
• Created: 20/Mar/19 06:06
• Updated: 24/Jun/19 15:02
• Resolved: 15/May/19 04:03

Description

An EXISTS or NOT EXISTS sub-query whose inner child is an aggregate with no grouping columns should be simplified to a Boolean constant.

Example:

exists(select sum(i) from X) --> true
not exists(select sum(i) from X) --> false

Repro:

@Test public void testExistentialSubquery() {
    final String sql = "SELECT e1.empno\n"
        + "FROM emp e1 where exists\n"
        + "(select avg(sal) from emp e2 where e1.empno = e2.empno )";
    sql(sql).decorrelate(true).ok();
  }

We got plan:

LogicalProject(EMPNO=[$0])
  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN])
    LogicalJoin(condition=[=($0, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
        LogicalProject(EMPNO=[$0], $f0=[true])
          LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
            LogicalProject(EMPNO=[$0], SAL=[$5])
              LogicalTableScan(table=[[CATALOG, SALES, EMP]])

The preferred plan should be:

LogicalProject(EMPNO=[$0])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• links to: GitHub Pull Request #1121