[CALCITE-2183] Implement RelSubset.copy (Alessandro Solimando)

Before this fix, during the trim fields phase of query preparation
RelSubset.copy would throw UnsupportedOperationException. This would
only occur if the root was a registered RelSubset, which only occurred
if the root had previously been flattened, which only occurred if Spark
was enabled.


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/d3293319
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/d3293319
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/d3293319

Branch: refs/heads/master
Commit: d329331932628fb446fc8404639cb67190cab1cc
Parents: 47c49c9
Author: Alessandro Solimando <18898964+asolima...@users.noreply.github.com>
Authored: Wed Feb 7 23:29:03 2018 +0100
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Feb 22 18:18:37 2018 -0800

----------------------------------------------------------------------
 .../apache/calcite/plan/volcano/RelSubset.java  |   7 +
 core/src/test/resources/sql/misc.iq             |   6 +
 .../apache/calcite/test/SparkAdapterTest.java   | 753 ++++++++++++++++++-
 3 files changed, 746 insertions(+), 20 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/d3293319/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java 
b/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java
index e556063..60f4a07 100644
--- a/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java
+++ b/core/src/main/java/org/apache/calcite/plan/volcano/RelSubset.java
@@ -145,6 +145,13 @@ public class RelSubset extends AbstractRelNode {
   }
 
   public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) {
+    if (inputs.isEmpty()) {
+      final RelTraitSet traitSet1 = traitSet.simplify();
+      if (traitSet1.equals(this.traitSet)) {
+        return this;
+      }
+      return set.getOrCreateSubset(getCluster(), traitSet1);
+    }
     throw new UnsupportedOperationException();
   }
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/d3293319/core/src/test/resources/sql/misc.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.iq 
b/core/src/test/resources/sql/misc.iq
index 65ec6b5..4e00ee7 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2127,4 +2127,10 @@ FROM (VALUES (1, 'X'),(2, 'Y'),(3, 'X'),(4, 'X')) AS 
T(A, B);
 
 !ok
 
+# [CALCITE-2183] Implement RelSubset.copy
+select *
+from (values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)
+where false;
+!ok
+
 # End misc.iq

http://git-wip-us.apache.org/repos/asf/calcite/blob/d3293319/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
----------------------------------------------------------------------
diff --git a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java 
b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
index db08a7b..88b4c4d 100644
--- a/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
+++ b/spark/src/test/java/org/apache/calcite/test/SparkAdapterTest.java
@@ -19,46 +19,759 @@ package org.apache.calcite.test;
 import org.apache.calcite.adapter.spark.SparkRel;
 import org.apache.calcite.util.Util;
 
+import org.junit.Ignore;
 import org.junit.Test;
 
-import java.sql.SQLException;
-
 /**
  * Tests for using Calcite with Spark as an internal engine, as implemented by
  * the {@link org.apache.calcite.adapter.spark} package.
  */
 public class SparkAdapterTest {
+
+  private static final String commonValuesExpr0 = "(values (1, 'a'), (2, 
'b'))";
+
+  private static final String commonValuesExpr1 = 
+      "(values (1, 'a'), (2, 'b')) as t(x, y)";
+
+  private static final String commonValuesExpr2 =
+      "(values (1, 'a'), (2, 'b'), (1, 'b'), (2, 'c'), (2, 'c')) as t(x, y)";
+
+  private static final String commonValuesExpr3 =
+      "(values (1, 'a'), (2, 'b')) as v(w, z)";
+
+  private static final String commonValuesExpr4 =
+      "(values (1, 'a'), (2, 'b'), (3, 'b'), (4, 'c'), (2, 'c')) as t(x, y)";
+
   /**
    * Tests a VALUES query evaluated using Spark.
    * There are no data sources.
    */
-  @Test public void testValues() throws SQLException {
+  @Test public void testValues() {
     // Insert a spurious reference to a class in Calcite's Spark adapter.
     // Otherwise this test doesn't depend on the Spark module at all, and
     // Javadoc gets confused.
     Util.discard(SparkRel.class);
 
-    CalciteAssert.that()
-        .with(CalciteAssert.Config.SPARK)
-        .query("select *\n"
-            + "from (values (1, 'a'), (2, 'b'))")
-        .returns("EXPR$0=1; EXPR$1=a\n"
-            + "EXPR$0=2; EXPR$1=b\n")
-        .explainContains("PLAN="
-            + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])");
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr0;
+
+    final String plan = "PLAN="
+        + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])";
+
+    final String expectedResult = "EXPR$0=1; EXPR$1=a\n"
+        + "EXPR$0=2; EXPR$1=b";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
   }
 
   /** Tests values followed by filter, evaluated by Spark. */
-  @Test public void testValuesFilter() throws SQLException {
-    CalciteAssert.that()
+  @Test public void testValuesFilter() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x < 2";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[<($t0, 
$t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n";
+
+    final String expectedResult = "X=1; Y=a";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  private CalciteAssert.AssertQuery sql(String sql) {
+    return CalciteAssert.that()
         .with(CalciteAssert.Config.SPARK)
-        .query("select *\n"
-            + "from (values (1, 'a'), (2, 'b')) as t(x, y)\n"
-            + "where x < 2")
-        .returns("X=1; Y=a\n")
-        .explainContains("PLAN="
-            + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[<($t0, 
$t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
-            + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n");
+        .query(sql);
+  }
+
+  @Test public void testSelectDistinct() {
+    final String sql = "select distinct *\n"
+        + "from " + commonValuesExpr2;
+
+    final String plan = "PLAN="
+        + "EnumerableAggregate(group=[{0, 1}])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  /** Tests about grouping and aggregation functions. */
+
+  @Test public void testGroupBy() {
+    final String sql = "select sum(x) as SUM_X, min(y) as MIN_Y, max(y) as 
MAX_Y, "
+        + "count(*) as CNT_Y, count(distinct y) as CNT_DIST_Y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "group by x";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):INTEGER NOT 
NULL], expr#7=[CAST($t2):CHAR(1) CHARACTER SET \"ISO-8859-1\" COLLATE 
\"ISO-8859-1$en_US$primary\" NOT NULL], expr#8=[CAST($t3):CHAR(1) CHARACTER SET 
\"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL], 
expr#9=[CAST($t4):BIGINT NOT NULL], SUM_X=[$t6], MIN_Y=[$t7], MAX_Y=[$t8], 
CNT_Y=[$t9], CNT_DIST_Y=[$t5])\n"
+        + "  EnumerableAggregate(group=[{0}], SUM_X=[MIN($2) FILTER $7], 
MIN_Y=[MIN($3) FILTER $7], MAX_Y=[MIN($4) FILTER $7], CNT_Y=[MIN($5) FILTER 
$7], CNT_DIST_Y=[COUNT($1) FILTER $6])\n"
+        + "    EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6, 
$t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8], 
$g_1=[$t10])\n"
+        + "      EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], 
SUM_X=[$SUM0($0)], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], 
$g=[GROUPING($0, $1)])\n"
+        + "        EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' 
}, { 2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "SUM_X=2; MIN_Y=a; MAX_Y=b; CNT_Y=2; 
CNT_DIST_Y=2\n"
+        + "SUM_X=6; MIN_Y=b; MAX_Y=c; CNT_Y=3; CNT_DIST_Y=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testAggFuncNoGroupBy() {
+    final String sql = "select sum(x) as SUM_X, min(y) as MIN_Y, max(y) as 
MAX_Y, "
+        + "count(*) as CNT_Y, count(distinct y) as CNT_DIST_Y\n"
+        + "from " + commonValuesExpr2;
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t3):BIGINT NOT 
NULL], proj#0..2=[{exprs}], CNT_Y=[$t5], CNT_DIST_Y=[$t4])\n"
+        + "  EnumerableAggregate(group=[{}], SUM_X=[MIN($1) FILTER $6], 
MIN_Y=[MIN($2) FILTER $6], MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER 
$6], CNT_DIST_Y=[COUNT($0) FILTER $5])\n"
+        + "    EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5, 
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7], 
$g_1=[$t9])\n"
+        + "      EnumerableAggregate(group=[{1}], groups=[[{1}, {}]], 
SUM_X=[$SUM0($0)], MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], 
$g=[GROUPING($1)])\n"
+        + "        EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' 
}, { 2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "SUM_X=8; MIN_Y=a; MAX_Y=c; CNT_Y=5; 
CNT_DIST_Y=3";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testGroupByOrderByAsc() {
+    final String sql = "select x, count(*) as CNT_Y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "group by x\n"
+        + "order by x asc";
+
+    final String plan = "";
+
+    final String expectedResult = "X=1; CNT_Y=2\n"
+        + "X=2; CNT_Y=3\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testGroupByMinMaxCountCountDistinctOrderByAsc() {
+    final String sql = "select x, min(y) as MIN_Y, max(y) as MAX_Y, count(*) 
as CNT_Y, "
+        + "count(distinct y) as CNT_DIST_Y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "group by x\n"
+        + "order by x asc";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1) 
CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL], 
expr#6=[CAST($t2):CHAR(1) CHARACTER SET \"ISO-8859-1\" COLLATE 
\"ISO-8859-1$en_US$primary\" NOT NULL], expr#7=[CAST($t3):BIGINT NOT NULL], 
X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
+        + "  EnumerableSort(sort0=[$0], dir0=[ASC])\n"
+        + "    EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6], 
MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1) 
FILTER $5])\n"
+        + "      EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], 
expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], 
$g_0=[$t7], $g_1=[$t9])\n"
+        + "        EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], 
MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
+        + "          EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 
'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; MIN_Y=a; MAX_Y=b; CNT_Y=2; 
CNT_DIST_Y=2\n"
+        + "X=2; MIN_Y=b; MAX_Y=c; CNT_Y=3; CNT_DIST_Y=2\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testGroupByMiMaxCountCountDistinctOrderByDesc() {
+    final String sql = "select x, min(y) as MIN_Y, max(y) as MAX_Y, count(*) 
as CNT_Y, "
+        + "count(distinct y) as CNT_DIST_Y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "group by x\n"
+        + "order by x desc";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):CHAR(1) 
CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\" NOT NULL], 
expr#6=[CAST($t2):CHAR(1) CHARACTER SET \"ISO-8859-1\" COLLATE 
\"ISO-8859-1$en_US$primary\" NOT NULL], expr#7=[CAST($t3):BIGINT NOT NULL], 
X=[$t0], MIN_Y=[$t5], MAX_Y=[$t6], CNT_Y=[$t7], CNT_DIST_Y=[$t4])\n"
+        + "  EnumerableSort(sort0=[$0], dir0=[DESC])\n"
+        + "    EnumerableAggregate(group=[{0}], MIN_Y=[MIN($2) FILTER $6], 
MAX_Y=[MIN($3) FILTER $6], CNT_Y=[MIN($4) FILTER $6], CNT_DIST_Y=[COUNT($1) 
FILTER $5])\n"
+        + "      EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], 
expr#7=[=($t5, $t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], 
$g_0=[$t7], $g_1=[$t9])\n"
+        + "        EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]], 
MIN_Y=[MIN($1)], MAX_Y=[MAX($1)], CNT_Y=[COUNT()], $g=[GROUPING($0, $1)])\n"
+        + "          EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 
'b' }, { 2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=2; MIN_Y=b; MAX_Y=c; CNT_Y=3; 
CNT_DIST_Y=2\n"
+        + "X=1; MIN_Y=a; MAX_Y=b; CNT_Y=2; CNT_DIST_Y=2\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testGroupByHaving() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "group by x\n"
+        + "having count(*) > 2";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[2], expr#3=[>($t1, 
$t2)], X=[$t0], $condition=[$t3])\n"
+        + "  EnumerableAggregate(group=[{0}], agg#0=[COUNT()])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests about set operators (union, union all, intersect).
+
+  @Test public void testUnionAll() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + " union all\n"
+        + "select *\n"
+        + "from " + commonValuesExpr2;
+
+    final String plan = "PLAN="
+        + "EnumerableUnion(all=[true])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testUnion() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + " union\n"
+        + "select *\n"
+        + "from " + commonValuesExpr2;
+
+    final String plan = "PLAN="
+        + "EnumerableUnion(all=[false])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testIntersect() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + " intersect\n"
+        + "select *\n"
+        + "from " + commonValuesExpr2;
+
+    final String plan = "PLAN="
+        + "EnumerableIntersect(all=[false])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=2; Y=b";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests about sorting
+
+  @Test public void testSortXAscProjectY() {
+    final String sql = "select y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by x asc";
+
+    final String plan = "PLAN="
+        + "EnumerableSort(sort0=[$1], dir0=[ASC])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Y=a\n"
+        + "Y=b\n"
+        + "Y=b\n"
+        + "Y=c\n"
+        + "Y=c\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testSortXDescYDescProjectY() {
+    final String sql = "select y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by x desc, y desc";
+
+    final String plan = "PLAN="
+        + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[DESC])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Y=c\n"
+        + "Y=c\n"
+        + "Y=b\n"
+        + "Y=b\n"
+        + "Y=a\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testSortXDescYAscProjectY() {
+    final String sql = "select y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by x desc, y";
+
+    final String plan = "PLAN="
+        + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[ASC])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Y=b\n"
+        + "Y=c\n"
+        + "Y=c\n"
+        + "Y=a\n"
+        + "Y=b\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testSortXAscYDescProjectY() {
+    final String sql = "select y\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by x, y desc";
+
+    final String plan = "PLAN="
+        + "EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[DESC])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], Y=[$t1], X=[$t0])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Y=b\n"
+        + "Y=a\n"
+        + "Y=c\n"
+        + "Y=c\n"
+        + "Y=b\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests involving joins.
+
+  @Test public void testJoinProject() {
+    final String sql = "select t.y, v.z\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "  join " + commonValuesExpr3 + " on t.x = v.w";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..3=[{inputs}], Y=[$t3], Z=[$t1])\n"
+        + "  EnumerableJoin(condition=[=($0, $2)], joinType=[inner])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Y=a; Z=a\n"
+        + "Y=b; Z=a\n"
+        + "Y=b; Z=b\n"
+        + "Y=c; Z=b\n"
+        + "Y=c; Z=b";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testJoinProjectAliasProject() {
+    final String sql = "select r.z\n"
+        + "from (\n"
+        + "  select *\n"
+        + "  from " + commonValuesExpr2 + "\n"
+        + "    join " + commonValuesExpr3 + " on t.x = v.w) as r";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..3=[{inputs}], Z=[$t1])\n"
+        + "  EnumerableJoin(condition=[=($0, $2)], joinType=[inner])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "Z=a\n"
+        + "Z=a\n"
+        + "Z=b\n"
+        + "Z=b\n"
+        + "Z=b";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests involving limit/offset.
+
+  @Test public void testLimit() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x = 1\n"
+        + "limit 1";
+
+    final String plan = "PLAN="
+        + "EnumerableLimit(fetch=[1])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[=($t0, 
$t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }";
+
+    final String expectedResult = "X=1; Y=a";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testOrderByLimit() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by y\n"
+        + "limit 1";
+
+    final String plan = "PLAN="
+        + "EnumerableLimit(fetch=[1])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=a\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testOrderByOffset() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "order by y\n"
+        + "offset 2";
+
+    final String plan = "PLAN="
+        + "EnumerableLimit(offset=[2])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c\n";
+
+    sql(sql).returns(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests involving "complex" filters in WHERE clause
+
+  @Test public void testFilterBetween() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr4 + "\n"
+        + "where x between 3 and 4";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[3], expr#3=[>=($t0, 
$t2)], expr#4=[4], expr#5=[<=($t0, $t4)], expr#6=[AND($t3, $t5)], 
proj#0..1=[{exprs}], $condition=[$t6])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 3, 'b' }, { 
4, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=3; Y=b\n"
+        + "X=4; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterIsIn() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr4 + "\n"
+        + "where x in (3, 4)";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[3], expr#3=[=($t0, 
$t2)], expr#4=[4], expr#5=[=($t0, $t4)], expr#6=[OR($t3, $t5)], 
proj#0..1=[{exprs}], $condition=[$t6])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 3, 'b' }, { 
4, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=3; Y=b\n"
+        + "X=4; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterTrue() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where true";
+
+    final String plan = "PLAN="
+        + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 
'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterFalse() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where false";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[false], 
proj#0..1=[{exprs}], $condition=[$t2])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterOr() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x = 1 or x = 2";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[=($t0, 
$t2)], expr#4=[2], expr#5=[=($t0, $t4)], expr#6=[OR($t3, $t5)], 
proj#0..1=[{exprs}], $condition=[$t6])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterIsNotNull() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x is not null";
+
+    final String plan = "PLAN="
+        + "EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 2, 
'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=1; Y=a\n"
+        + "X=1; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testFilterIsNull() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x is null";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[false], 
proj#0..1=[{exprs}], $condition=[$t2])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests on more complex queries as union operands.
+
+  @Test public void testUnionWithFilters() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x > 1\n"
+        + " union all\n"
+        + "select *\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x > 1";
+
+    final String plan = "PLAN="
+        + "EnumerableUnion(all=[true])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, 
$t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, 
$t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n";
+
+    final String expectedResult = "X=2; Y=b\n"
+        + "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=2; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testUnionWithFiltersProject() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x > 1\n"
+        + " union\n"
+        + "select x\n"
+        + "from " + commonValuesExpr2 + "\n"
+        + "where x > 1";
+
+    final String plan = "PLAN="
+        + "EnumerableUnion(all=[false])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, 
$t2)], X=[$t0], $condition=[$t3])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[>($t0, 
$t2)], X=[$t0], $condition=[$t3])\n"
+        + "    EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }, { 1, 'b' }, { 
2, 'c' }, { 2, 'c' }]])\n\n";
+
+    final String expectedResult = "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  // Tests involving arithmetic operators.
+
+  @Test public void testArithmeticPlus() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x + 1 > 1";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[+($t0, 
$t2)], expr#4=[>($t3, $t2)], X=[$t0], $condition=[$t4])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
+
+    final String expectedResult = "X=1\n"
+        + "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testArithmeticMinus() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x - 1 > 0";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[1], expr#3=[-($t0, 
$t2)], expr#4=[0], expr#5=[>($t3, $t4)], X=[$t0], $condition=[$t5])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
+
+    final String expectedResult = "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testArithmeticMul() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x * x > 1";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[*($t0, $t0)], 
expr#3=[1], expr#4=[>($t2, $t3)], X=[$t0], $condition=[$t4])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
+
+    final String expectedResult = "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Test public void testArithmeticDiv() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x / x = 1";
+
+    final String plan = "PLAN="
+        + "EnumerableCalc(expr#0..1=[{inputs}], expr#2=[/($t0, $t0)], 
expr#3=[1], expr#4=[=($t2, $t3)], X=[$t0], $condition=[$t4])\n"
+        + "  EnumerableValues(tuples=[[{ 1, 'a' }, { 2, 'b' }]])\n\n";
+
+    final String expectedResult = "X=1\n"
+        + "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  /** Tests involving subqueries (both correlated and non correlated). */
+  @Ignore("[CALCITE-2184] java.lang.ClassCastException: RexSubQuery cannot be 
cast to RexLocalRef")
+  @Test public void testFilterExists() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr4 + "\n"
+        + "where exists (\n"
+        + "  select *\n"
+        + "  from " + commonValuesExpr3 + "\n"
+        + "  where w < x\n"
+        + ")";
+
+    final String plan = "PLAN=todo\n\n";
+
+    final String expectedResult = "X=2; Y=b\n"
+        + "X=2; Y=c\n"
+        + "X=3; Y=b\n"
+        + "X=4; Y=c";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Ignore("[CALCITE-2184] java.lang.ClassCastException: RexSubQuery cannot be 
cast to RexLocalRef")
+  @Test public void testFilterNotExists() {
+    final String sql = "select *\n"
+        + "from " + commonValuesExpr4 + "\n"
+        + "where not exists (\n"
+        + "  select *\n"
+        + "  from " + commonValuesExpr3 + "\n"
+        + "  where w > x\n"
+        + ")";
+
+    final String plan = "PLAN=todo\n\n";
+
+    final String expectedResult = "X=1; Y=a";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Ignore("[CALCITE-2184] java.lang.ClassCastException: RexSubQuery cannot be 
cast to RexLocalRef")
+  @Test public void testSubqueryAny() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x <= any (\n"
+        + "  select x\n"
+        + "  from " + commonValuesExpr2 + "\n"
+        + ")";
+
+    final String plan = "PLAN=todo\n\n";
+
+    final String expectedResult = "X=1\n"
+        + "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
+  }
+
+  @Ignore("[CALCITE-2184] java.lang.ClassCastException: RexSubQuery cannot be 
cast to RexLocalRef")
+  @Test public void testSubqueryAll() {
+    final String sql = "select x\n"
+        + "from " + commonValuesExpr1 + "\n"
+        + "where x <= all (\n"
+        + "  select x\n"
+        + "  from " + commonValuesExpr2 + "\n"
+        + ")";
+
+    final String plan = "PLAN=todo\n\n";
+
+    final String expectedResult = "X=2";
+
+    sql(sql).returnsUnordered(expectedResult)
+        .explainContains(plan);
   }
 }
 

Reply via email to