You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Wang Yanlin (Jira)" <ji...@apache.org> on 2019/10/18 07:54:00 UTC
[jira] [Assigned] (CALCITE-3400) Implement
left/right/semi/anti/full join in Interpreter
[ https://issues.apache.org/jira/browse/CALCITE-3400?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wang Yanlin reassigned CALCITE-3400:
------------------------------------
Assignee: Wang Yanlin
> Implement left/right/semi/anti/full join in Interpreter
> -------------------------------------------------------
>
> Key: CALCITE-3400
> URL: https://issues.apache.org/jira/browse/CALCITE-3400
> Project: Calcite
> Issue Type: Improvement
> Reporter: Wang Yanlin
> Assignee: Wang Yanlin
> Priority: Major
> Labels: pull-request-available
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> 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)