[GitHub] [calcite] hsyuan commented on a change in pull request #1502: [CALCITE-3407] Add support for interpretering minus/intersect relational set operators

[GitBox <gi...@apache.org>]

hsyuan 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_r334682964
 
 

 ##########
 File path: core/src/test/java/org/apache/calcite/test/InterpreterTest.java
 ##########
 @@ -301,6 +301,50 @@ private static void assertRows(Interpreter interpreter,
     final Interpreter interpreter = new Interpreter(dataContext, convert);
     assertRows(interpreter, "[0]", "[10]", "[20]", "[30]");
   }
+
+  @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 testInterpretMinus() throws Exception {
+    final String sql = "select * from\n"
+        + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))\n"
+        + "except\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, "[2, b]", "[3, c]");
+  }
+
+  @Test public void testInterpretMinusAll() throws Exception {
+    final String sql = "select * from\n"
+        + "(select x, y from (values (1, 'a'), (2, 'b'), (2, 'b'), (3, 'c')) as t(x, y))\n"
+        + "except 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, "[2, b]", "[2, b]", "[3, c]");
 
 Review comment:
   can you add an case for `(2, 'b'), (2, 'b') except (2, 'b')` and  `(2, 'b'), (2, 'b') except all (2, 'b')`?

----------------------------------------------------------------
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:
users@infra.apache.org


With regards,
Apache Git Services