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

Add support for Enumerable Intersect/Minus all

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.22.0
    • Component/s: None

      Description

      When running sql with calcite driver

      final String sql = "select * from\n"
              + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))\n"
              + "except all\n"
              + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))\n";
      

      got

      java.sql.SQLException: Error while executing SQL "explain plan for select * from
      (select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))
      except all
      (select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))
      ": There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
      Missing conversion is LogicalMinus[convention: NONE -> ENUMERABLE]
      There is 1 empty subset: rel#27:Subset#4.ENUMERABLE.[], the relevant part of the original plan is as follows
      22:LogicalMinus(all=[true])
        1:LogicalValues(subset=[rel#16:Subset#0.NONE.[]], tuples=[[{ 1, 'a' }, { 2, 'b' }, { 2, 'b' }, { 3, 'c' }]])
        5:LogicalValues(subset=[rel#19:Subset#2.NONE.[]], tuples=[[{ 1, 'a' }, { 2, 'c' }, { 4, 'x' }]])
      
      Root: rel#27:Subset#4.ENUMERABLE.[]
      Original rel:
      LogicalMinus(all=[true]): rowcount = 3.0, cumulative cost = {17.0 rows, 19.0 cpu, 0.0 io}, id = 14
        LogicalProject(X=[$0], Y=[$1]): rowcount = 4.0, cumulative cost = {8.0 rows, 9.0 cpu, 0.0 io}, id = 9
          LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 2, 'b' }, { 3, 'c' }]]): rowcount = 4.0, cumulative cost = {4.0 rows, 1.0 cpu, 0.0 io}, id = 1
        LogicalProject(X=[$0], Y=[$1]): rowcount = 3.0, cumulative cost = {6.0 rows, 7.0 cpu, 0.0 io}, id = 12
          LogicalValues(tuples=[[{ 1, 'a' }, { 2, 'c' }, { 4, 'x' }]]): rowcount = 3.0, cumulative cost = {3.0 rows, 1.0 cpu, 0.0 io}, id = 5
      

      Add these cases in JdbcTest to reproduce

      @Test public void testExceptAll() {
          final String sql = "select * from\n"
              + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))\n"
              + "except all\n"
              + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))\n";
          CalciteAssert.hr()
              .query(sql)
              .explainContains(""
                  + "PLAN=EnumerableMinus(all=[true])")
              .returnsUnordered(
                  "X=2, Y=b",
                  "X=2, Y=b",
                  "X=3, Y=c");
        }
      
        @Test public void testIntersectAll() {
          final String sql = "select * from\n"
              + "(select x, y from (values (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y))\n"
              + "intersect all\n"
              + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))\n";
          CalciteAssert.hr()
              .query(sql)
              .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
                  planner.removeRule(IntersectToDistinctRule.INSTANCE))
              .explainContains(""
                  + "PLAN=EnumerableIntersect(all=[true])")
              .returnsUnordered(
                  "X=1; Y=a",
                  "X=1; Y=a");
        }
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                yanlin-Lynn Wang Yanlin
                Reporter:
                yanlin-Lynn Wang Yanlin
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 50m
                  1h 50m