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

SubQueryRemoveRule.matchJoin should correctly rewrite all sub-queries

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: In Progress
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      SubQueryRemoveRule.matchJoin only rewrites the first subquery in an ON condition each call. It should rewrite all of them down right side of the join in single call. 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.

        @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();
  }

      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)

      Current plan

      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]])

      Correct Plan

      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]])

      Notice the first Join is changed to a correlate and the second join has a correlated variable in the join.

      Attachments

        Issue Links

          is blocked by

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-4791 RelBuilder.join incorrectly rewrites correlated conditions

          • Major - Major loss of function.
          • Open

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-4792 SqlToRel should populate corralateId for join with corralated query in ON condition

          • Major - Major loss of function.
          • Open
          split from

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-1045 Decorrelate sub-queries in Project and Join

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

          Web Link GitHub Pull Request #2385

          Activity

            People

              jamesstarr James Starr
              jamesstarr James Starr
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 3.5h
                  3.5h