[CALCITE-1511] Decorrelation fails if query has more than one EXISTS in WHERE clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.11.0
• Component/s: core
• Labels: sub-query
• Assignee: Julian Hyde
• Reporter: Vineet Garg
• Created: 28/Nov/16 20:02
• Updated: 06/Jan/17 19:30
• Resolved: 27/Dec/16 07:50

Description

When planning a query with more than one EXISTS in the same WHERE clause, Calcite throws in the RelDecorrelator.allLessThan method.

Calcite hits an assertion for following query (planning is done by calling SubQueryRemoveRule followed by decorrelateQuery):

  select * from emp
where EXISTS (select * from emp e where emp.deptno = e.deptno)
AND NOT EXISTS (select * from emp ee where ee.job = emp.job AND ee.sal=34) 

Assertion

 Caused by: java.lang.AssertionError: out of range; value: 3, limit: 3 

This assertion is hit in RelDecorrelator's allLessThan which is called while registering newly de-correlated LogicalAggregate.

Plan just before SubQueryRemoveRule:

LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
  LogicalFilter(condition=[AND(EXISTS({
LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
}), NOT(EXISTS({
LogicalFilter(condition=[AND(=($2, $cor0.JOB), =($5, 34))])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})))], variablesSet=[[$cor0]])
    LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Plan just 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])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[$9])
      LogicalFilter(condition=[AND(OR(IS NULL($12), =($10, 0)), OR(>=($11, $10), =($10, 0)))])
        LogicalJoin(condition=[true], joinType=[left])
          LogicalJoin(condition=[true], joinType=[inner])
            LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{7}])
              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
              LogicalAggregate(group=[{0}])
                LogicalProject(i=[true])
                  LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
            LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0, $1, $2, $3, $4, $5, $6, $7, $8)])
              LogicalFilter(condition=[AND(=($2, $cor0.JOB), =($5, 34))])
                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
          LogicalAggregate(group=[{0}])
            LogicalProject(i=[true])
              LogicalFilter(condition=[AND(=($2, $cor0.JOB), =($5, 34))])
                LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Issue Links

• is depended upon by: CALCITE-1547 Release Calcite 1.11.0
• relates to: CALCITE-1537 Unnecessary project expression in multi-sub-query plan