You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "James Starr (Jira)" <ji...@apache.org> on 2021/07/07 23:45:00 UTC
[jira] [Created] (CALCITE-4686) SubQueryRemoveRule.matchJoin should
correctly rewrite all sub-queries
James Starr created CALCITE-4686:
------------------------------------
Summary: SubQueryRemoveRule.matchJoin should correctly rewrite all sub-queries
Key: CALCITE-4686
URL: https://issues.apache.org/jira/browse/CALCITE-4686
Project: Calcite
Issue Type: Improvement
Reporter: James Starr
SubQueryRemoveRule.matchJoin only rewrites the first subquery in an ON condition. It should rewrite all of them down right side of the join. Furthermore, the filter generated is not shifted correctly for the scope that it is being applied to.
RelOptRulesTest.testExpandJoinIn, which currently disabled, throw an exception when run because filter is shifted to be applied to in the context of the right side but it applied on top of the join. Which can be observed by commenting out the litmus check.
{code:java}
@Test void testExpandJoinIn() {
final String sql = "select empno\n"
+ "from sales.emp left join sales.dept\n"
+ "on emp.deptno in (select deptno from sales.emp where empno < 20)";
checkSubQuery(sql).check();
}
{code}
{noformat}
RexInputRef index 7 out of range 0..2
java.lang.AssertionError: RexInputRef index 7 out of range 0..2
at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:125)
at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:61)
at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:114)
at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:144)
at org.apache.calcite.rex.RexChecker.visitCall(RexChecker.java:61)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
at org.apache.calcite.rel.core.Join.isValid(Join.java:176)
at org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:4261)
at org.apache.calcite.rel.BiRel.childrenAccept(BiRel.java:46)
at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:46)
at org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:4264)
at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:46)
at org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:4264)
at org.apache.calcite.rel.SingleRel.childrenAccept(SingleRel.java:72)
at org.apache.calcite.rel.RelVisitor.visit(RelVisitor.java:46)
at org.apache.calcite.test.SqlToRelConverterTest$RelValidityChecker.visit(SqlToRelConverterTest.java:4264)
at org.apache.calcite.rel.RelVisitor.go(RelVisitor.java:63)
at org.apache.calcite.test.SqlToRelTestBase.assertValid(SqlToRelTestBase.java:154)
at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:163)
at org.apache.calcite.test.RelOptTestBase.checkPlanning(RelOptTestBase.java:109)
at org.apache.calcite.test.RelOptTestBase.access$100(RelOptTestBase.java:66)
at org.apache.calcite.test.RelOptTestBase$Sql.check(RelOptTestBase.java:340)
at org.apache.calcite.test.RelOptTestBase$Sql.check(RelOptTestBase.java:316)
at org.apache.calcite.test.RelOptRulesTest.testExpandJoinIn(RelOptRulesTest.java:5702)
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:675)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
at org.junit.jupiter.engine.extension.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:139)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:81)
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:104)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
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$6(TestMethodTestDescriptor.java:202)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:115)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:115)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
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)
{noformat}
Current plan
{noformat}
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
LogicalJoin(condition=[true], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalJoin(condition=[=($7, $11)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
Correct Plan
{noformat}
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{7}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalJoin(condition=[=($cor0.DEPTNO, $2)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[<($0, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
Notice the first Join is changed to a correlate and the second join has a correlated variable in the join.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)