yanlin-Lynn commented on a change in pull request #1502: [CALCITE-3407] Add
support for interpretering minus/intersect relational set operators
URL: https://github.com/apache/calcite/pull/1502#discussion_r335265758
##########
File path: core/src/test/java/org/apache/calcite/test/InterpreterTest.java
##########
@@ -301,6 +301,144 @@ private static void assertRows(Interpreter interpreter,
final Interpreter interpreter = new Interpreter(dataContext, convert);
assertRows(interpreter, "[0]", "[10]", "[20]", "[30]");
}
+
+ @Test public void testInterpretUnionWithNullValue() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1))),\n"
+ + "(cast(NULL as int), cast(NULL as varchar(1)))) as t(x, y))\n"
+ + "union\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1)))) as t2(x, y))\n";
+ SqlNode validate = planner.validate(planner.parse(sql));
+ RelNode convert = planner.rel(validate).rel;
+ final Interpreter interpreter = new Interpreter(dataContext, convert);
+ assertRows(interpreter, "[null, null]");
+ }
+
+ @Test public void testInterpretUnionAllWithNullValue() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1))),\n"
+ + "(cast(NULL as int), cast(NULL as varchar(1)))) as t(x, y))\n"
+ + "union all\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1)))) as t2(x, y))\n";
+ SqlNode validate = planner.validate(planner.parse(sql));
+ RelNode convert = planner.rel(validate).rel;
+ final Interpreter interpreter = new Interpreter(dataContext, convert);
+ assertRows(interpreter, "[null, null]", "[null, null]", "[null, null]");
+ }
+
+ @Test public void testInterpretIntersect() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c'))
as t(x, y))\n"
+ + "intersect\n"
+ + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
+ SqlNode validate = planner.validate(planner.parse(sql));
+ RelNode convert = planner.rel(validate).rel;
+ final Interpreter interpreter = new Interpreter(dataContext, convert);
+ assertRows(interpreter, "[1, a]");
+ }
+
+ @Test public void testInterpretIntersectAll() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (1, 'a'), (1, 'a'), (2, 'b'), (3, 'c'))
as t(x, y))\n"
+ + "intersect all\n"
+ + "(select x, y from (values (1, 'a'), (2, 'c'), (4, 'x')) as t2(x,
y))\n";
+ 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, a]");
+ }
+
+ @Test public void testInterpretIntersectWithNullValue() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1))),\n"
+ + " (cast(NULL as int), cast(NULL as varchar(1)))) as t(x, y))\n"
+ + "intersect\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1)))) as t2(x, y))\n";
+ SqlNode validate = planner.validate(planner.parse(sql));
+ RelNode convert = planner.rel(validate).rel;
+ final Interpreter interpreter = new Interpreter(dataContext, convert);
+ assertRows(interpreter, "[null, null]");
+ }
+
+ @Test public void testInterpretIntersectAllWithNullValue() throws Exception {
+ final String sql = "select * from\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1))),\n"
+ + " (cast(NULL as int), cast(NULL as varchar(1)))) as t(x, y))\n"
+ + "intersect all\n"
+ + "(select x, y from (values (cast(NULL as int), cast(NULL as
varchar(1)))) as t2(x, y))\n";
+ SqlNode validate = planner.validate(planner.parse(sql));
+ RelNode convert = planner.rel(validate).rel;
+ final Interpreter interpreter = new Interpreter(dataContext, convert);
+ assertRows(interpreter, "[null, null]", "[null, null]");
Review comment:
I may hava a misunderstanding here. just to make sure
1. ( (null, null), (null, null), (null, null)) intersect all ((null, null),
(null, null)), the result is ((null, null), (null, null))
2. ( (null, null), (null, null), (null, null)) intersect all ((null, null)),
the result is ((null, null))
so, (M intersect all N ) = ( N intersect all M ), am I right about this?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
With regards,
Apache Git Services
