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

Correlated variables in a select list are not deduplicated

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      Currently, the deduplication of correlated variables ain't done for correlates in select list. Looks like the root cause here is same as for CALCITE-4673, but, unfortunately, suggested patch has addressed only the particular problem with a table function. It would be great to provide a more gereric fix.

      Besides, the project rel doesn't expose any correlated variables that should be set by this node.

      The problematic query is follow:

      select e.deptno,
             (select count(*) from emp where e.deptno > 0) as c1,
             (select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
        from emp e;
      

      Currently after conversion to relational nodes it looks like:

      LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
      LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
        LogicalProject($f0=[0])
          LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      })], EXPR$2=[$SCALAR_QUERY({
      LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
        LogicalProject($f0=[0])
          LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      })])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      

      All the correlates are set by the very top project node and could be represented by only one variable.

      Moreover, I've tried to add the query above to sub-query.iq and got the follow error:

      > java.sql.SQLException: Error while executing SQL "select e.deptno,
      >        (select count(*) from emp where e.deptno > 0) as c1,
      >        (select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
      >   from emp e": cm.mapCorToCorRel.get($cor1)
      > 	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
      > 	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
      > 	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
      > 	at org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
      > 	at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:325)
      > 	at net.hydromatic.quidem.Quidem.access$2800(Quidem.java:54)
      > 	at net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1748)
      > 	at net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:1079)
      > 	at net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1549)
      > 	at net.hydromatic.quidem.Quidem.execute(Quidem.java:216)
      > 	at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:173)
      > 	at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:224)
      > 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      > 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      > 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      > 	at java.lang.reflect.Method.invoke(Method.java:498)
      > 	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
      > 	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
      > 	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
      > 	at org.junit.jupiter.engine.extension.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
      > 	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
      > 	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
      > 	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestTemplateMethod(TimeoutExtension.java:92)
      > 	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
      > 	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
      > 	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
      > 	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
      > 	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
      > 	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
      > 	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
      > 	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
      > 	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
      > 	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
      > 	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
      > 	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
      > 	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
      > 	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
      > 	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
      > 	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
      > 	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
      > 	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
      > 	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
      > 	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
      > 	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
      > 	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:185)
      > 	at java.util.concurrent.RecursiveAction.exec(RecursiveAction.java:189)
      > 	at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
      > 	at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
      > 	at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
      > 	at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
      > Caused by: java.lang.NullPointerException: cm.mapCorToCorRel.get($cor1)
      > 	at java.util.Objects.requireNonNull(Objects.java:290)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.getCorRel(RelDecorrelator.java:934)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.createValueGenerator(RelDecorrelator.java:826)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateInputWithValueGenerator(RelDecorrelator.java:1034)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.maybeAddValueGenerator(RelDecorrelator.java:953)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1156)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1122)
      > 	at sun.reflect.GeneratedMethodAccessor17.invoke(Unknown Source)
      > 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      > 	at java.lang.reflect.Method.invoke(Method.java:498)
      > 	at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:711)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:515)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:498)
      > 	at sun.reflect.GeneratedMethodAccessor26.invoke(Unknown Source)
      > 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      > 	at java.lang.reflect.Method.invoke(Method.java:498)
      > 	at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:711)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1297)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:1276)
      > 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      > 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      > 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      > 	at java.lang.reflect.Method.invoke(Method.java:498)
      > 	at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:711)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:753)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateRel(RelDecorrelator.java:742)
      > 	at sun.reflect.GeneratedMethodAccessor14.invoke(Unknown Source)
      > 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      > 	at java.lang.reflect.Method.invoke(Method.java:498)
      > 	at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.getInvoke(RelDecorrelator.java:711)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelate(RelDecorrelator.java:306)
      > 	at org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:230)
      > 	at org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:361)
      > 	at org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:336)
      > 	at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:177)
      > 	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:312)
      > 	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:220)
      > 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:647)
      > 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:513)
      > 	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:483)
      > 	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:249)
      > 	at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:623)
      > 	at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:675)
      > 	at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
      > 	... 47 more
      

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            korlov Konstantin Orlov
            korlov Konstantin Orlov
            Votes:
            0 Vote for this issue
            Watchers:
            5 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 - 12h 40m
                12h 40m

                Slack

                  Issue deployment