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

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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    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

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment