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

Implement Minus and Intersect relational operators in the interpreter

    XMLWordPrintableJSON

    Details

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

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                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 - 4h 20m
                  4h 20m