[CALCITE-3408] Add support for Enumerable Intersect/Minus all

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Labels: pull-request-available
• Assignee: Wang Yanlin
• Reporter: Wang Yanlin
• Created: 14/Oct/19 09:31
• Updated: 06/Mar/20 04:36
• Resolved: 07/Nov/19 18:12

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");
  }

Issue Links

• links to: GitHub Pull Request #1503