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

Correlated variables in a select list are not deduplicated

    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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5127 Support correlation variables in Project

          • Major - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. IGNITE-16040 Calcite. Unable to plan query with several correlated sub-queries in select list

          • Major - Major loss of function.
          • Resolved
          relates to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-4718 Erroneous correlate variables in case of using collect sub-queries into ARRAY, MAP and MULTISET.

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-4673 If arguments to a table function are correlation variables, SqlToRelConverter should eliminate duplicate variables

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #2825

          Web Link PR 2825

          Activity

            People

              korlov Konstantin Orlov
              korlov Konstantin Orlov
              Votes:
              0 Vote for this issue
              Watchers:
              6 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