You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2022/06/06 10:12:00 UTC
[jira] [Resolved] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[ https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Stamatis Zampetakis resolved CALCITE-4913.
------------------------------------------
Resolution: Fixed
Fixed in [https://github.com/apache/calcite/commit/f7aa27ec22e843c5e27022f99237175d159699bb.]
Many thanks for the PR [~korlov] . Apart from the code itself your help in documenting the current behavior around correlations and willingness to push this to the end is worth mentioning and much appreciated as well.
Let's discuss the remaining bugs/improvements under separate JIRAs (create new or use existing ones).
> Correlated variables in a select list are not deduplicated
> ----------------------------------------------------------
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.28.0
> Reporter: Konstantin Orlov
> Assignee: Konstantin Orlov
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 12.5h
> Remaining Estimate: 0h
>
> 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:
> {code:java}
> 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;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> 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]])
> {code}
> 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:
> {code:java}
> > 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
> {code}
--
This message was sent by Atlassian Jira
(v8.20.7#820007)