[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)
