[ https://issues.apache.org/jira/browse/CALCITE-4686?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Starr reassigned CALCITE-4686: ------------------------------------ Assignee: James Starr > 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 > Assignee: James Starr > Priority: Major > > 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)