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

Implement left/right/semi/anti/full join in Interpreter

    XMLWordPrintableJSON

    Details

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

      Description

      Currently,  JoinNode can just run inner type join.

      Currently, add the test cases in InterpreterTest, and run, they will fail or throw exception

        @Test public void testInterpretLeftOutJoin() throws Exception {
          final String sql = "select * from\n"
              + "(select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)) t\n"
              + "left join\n"
              + "(select x, y from (values (1, 'd')) as t2(x, y)) t2\n"
              + "on t.x = t2.x";
          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, d]", "[2, b, null, null]", "[3, c, null, null]");
        }
      
        @Test public void testInterpretRightOutJoin() throws Exception {
          final String sql = "select * from\n"
              + "(select x, y from (values (1, 'd')) as t2(x, y)) t2\n"
              + "right join\n"
              + "(select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)) t\n"
              + "on t2.x = t.x";
          SqlNode validate = planner.validate(planner.parse(sql));
          RelNode convert = planner.rel(validate).rel;
          final Interpreter interpreter = new Interpreter(dataContext, convert);
          assertRows(interpreter, "[1, d, 1, a]", "[null, null, 2, b]", "[null, null, 3, c]");
        }
      
        @Test public void testInterpretSemanticSemiJoin() throws Exception {
          final String sql = "select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)\n"
              + "where x in\n"
              + "(select x from (values (1, 'd'), (3, 'g')) as t2(x, y))";
          SqlNode validate = planner.validate(planner.parse(sql));
          RelNode convert = planner.rel(validate).rel;
          final Interpreter interpreter = new Interpreter(dataContext, convert);
          assertRows(interpreter, "[1, a]", "[3, c]");
        }
      
        @Test public void testInterpretSemiJoin() throws Exception {
          final String sql = "select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)\n"
              + "where x in\n"
              + "(select x from (values (1, 'd'), (3, 'g')) as t2(x, y))";
          SqlNode validate = planner.validate(planner.parse(sql));
          RelNode convert = planner.rel(validate).rel;
          final HepProgram program = new HepProgramBuilder()
              .addRuleInstance(SemiJoinRule.PROJECT)
              .build();
          final HepPlanner hepPlanner = new HepPlanner(program);
          hepPlanner.setRoot(convert);
          final RelNode relNode = hepPlanner.findBestExp();
          final Interpreter interpreter = new Interpreter(dataContext, relNode);
          assertRows(interpreter, "[1, a]", "[3, c]");
        }
      
        @Test public void testInterpretAntiJoin() throws Exception {
          final String sql = "select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)\n"
              + "where x not in \n"
              + "(select x from (values (1, 'd')) as t2(x, y))";
          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 testInterpretFullJoin() throws Exception {
          final String sql = "select * from\n"
              + "(select x, y from (values (1, 'a'), (2, 'b'), (3, 'c')) as t(x, y)) t\n"
              + "full join\n"
              + "(select x, y from (values (1, 'd'), (2, 'c'), (4, 'x')) as t2(x, y)) t2\n"
              + "on t.x = t2.x";
          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, d]", "[2, b, 2, c]", "[3, c, null, null]", "[null, null, 4, x]");
        }
      

      We can add support for more join types for JoinNode.

        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 - 3h
                  3h