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

        Issue Links

          Activity

          Hide
          vgarg Vineet Garg added a comment -

          allLessThan is a check which seems to assure that the field count of newly created RelNode is less than values of oldToNewOutputPos (this keeps track of mapping from old position to new position). In this case LogicalAggregate has count, hence LogicalProject underneath which projects all column but it outputs only three columns this test fails. Julian Hyde Could you shed some light on this as to what is the purpose of this check and does it make sense in this particular case ?

          Show
          vgarg Vineet Garg added a comment - allLessThan is a check which seems to assure that the field count of newly created RelNode is less than values of oldToNewOutputPos (this keeps track of mapping from old position to new position). In this case LogicalAggregate has count , hence LogicalProject underneath which projects all column but it outputs only three columns this test fails. Julian Hyde Could you shed some light on this as to what is the purpose of this check and does it make sense in this particular case ?
          Hide
          julianhyde Julian Hyde added a comment -

          I can't really answer without deep-diving into the code.

          Show
          julianhyde Julian Hyde added a comment - I can't really answer without deep-diving into the code.
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/1d2067bd .
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.11.0 (2017-01-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

            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:

                Development