Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2936

Simplify EXISTS or NOT EXISTS sub-query that has "GROUP BY ()"

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.20.0
    • Component/s: None

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                hyuan Haisheng Yuan
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 40m
                  1h 40m