[ 
https://issues.apache.org/jira/browse/CALCITE-3400?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Wang Yanlin updated CALCITE-3400:
---------------------------------
    Description: 
Currently,  
[JoinNode|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/interpreter/JoinNode.java#L49]
 can just run inner type join.

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


{code:java}
  @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]");
  }
{code}


We can add support for more join types for JoinNode.

  was:
Currently,  
[JoinNode|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/interpreter/JoinNode.java#L49]
 can just run inner type join.

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


{code:java}
Test public void testInterpretInnerJoin() 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"
        + "join\n"
        + "(select x, y from (values (1, 'd'), (2, 'c')) 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]");
  }

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

  @Ignore 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]");
  }
{code}


We can add support for more join types for JoinNode.


> Add support for interpretering left/right/semi/anti/full join
> -------------------------------------------------------------
>
>                 Key: CALCITE-3400
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3400
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Wang Yanlin
>            Priority: Major
>
> Currently,  
> [JoinNode|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/interpreter/JoinNode.java#L49]
>  can just run inner type join.
> Currently, add the test cases in *InterpreterTest*, and run, they will fail 
> or throw exception 
> {code:java}
>   @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]");
>   }
> {code}
> We can add support for more join types for JoinNode.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Reply via email to