[jira] [Updated] (CALCITE-3400) Add support for interpretering left/right/semi/anti/full join
[ https://issues.apache.org/jira/browse/CALCITE-3400?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3400: Labels: pull-request-available (was: ) > 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 > Labels: pull-request-available > > 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)
[jira] [Updated] (CALCITE-3400) Add support for interpretering left/right/semi/anti/full join
[ 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 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. 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. We can add support for more join types for JoinNode. > Add support for interpretering left/right/semi/anti/full join >
[jira] [Updated] (CALCITE-3400) Add support for interpretering left/right/semi/anti/full join
[ 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
[jira] [Updated] (CALCITE-3400) Add support for interpretering left/right/semi/anti/full join
[ https://issues.apache.org/jira/browse/CALCITE-3400?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Wang Yanlin updated CALCITE-3400: - Summary: Add support for interpretering left/right/semi/anti/full join (was: Add support for interpretering outer/semi/anti/full join) > 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. > We can add support for more join types for JoinNode. -- This message was sent by Atlassian Jira (v8.3.4#803005)