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

Decorrelation fails if query has more than one EXISTS in WHERE clause

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: core
    • Labels:

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vgarg Vineet Garg
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: