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)
