[CALCITE-3407] Implement Minus and Intersect relational operators in the interpreter

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Wang Yanlin
• Created: 14/Oct/19 06:51
• Updated: 23/Jul/20 17:59
• Resolved: 17/Oct/19 08:16

Description

Currently, for SetOp, only `union` is supported by Interpreter
add the test cases in InterpreterTest, and run, they will fail by throwing exception

@Test public void testInterpretIntersect() throws Exception {
    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\n"
        + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))\n";
    SqlNode validate = planner.validate(planner.parse(sql));
    RelNode convert = planner.rel(validate).rel;
    final Interpreter interpreter = new Interpreter(dataContext, convert);
    assertRows(interpreter, "[1, a]");
  }

  @Test public void testInterpretIntersectAll() throws Exception {
    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";
    SqlNode validate = planner.validate(planner.parse(sql));
    RelNode convert = planner.rel(validate).rel;
    final Interpreter interpreter = new Interpreter(dataContext, convert);
    assertRows(interpreter, "[1, a]", "[1, a]");
  }

  @Test public void testInterpretMinus() throws Exception {
    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\n"
        + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x, y))\n";
    SqlNode validate = planner.validate(planner.parse(sql));
    RelNode convert = planner.rel(validate).rel;
    final Interpreter interpreter = new Interpreter(dataContext, convert);
    assertRows(interpreter, "[2, b]", "[3, c]");
  }

  @Test public void testInterpretMinusAll() throws Exception {
    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";
    SqlNode validate = planner.validate(planner.parse(sql));
    RelNode convert = planner.rel(validate).rel;
    final Interpreter interpreter = new Interpreter(dataContext, convert);
    assertRows(interpreter, "[2, b]", "[2, b]", "[3, c]");
  }

Issue Links

• breaks: CALCITE-4046 In interpreter, set operations with more than 2 inputs give wrong results
• links to: GitHub Pull Request #1502