drill-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j..@apache.org
Subject [3/6] drill git commit: DRILL-3683: Add baseline and expected plan for TestWindowFunctions suite
Date Fri, 11 Sep 2015 05:20:13 GMT
DRILL-3683: Add baseline and expected plan for TestWindowFunctions suite


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

Branch: refs/heads/master
Commit: 24bcca11629dbd6302161cc36cb312595b2a1790
Parents: 27e4bac
Author: Hsuan-Yi Chu <hsuanyi@usc.edu>
Authored: Fri Aug 21 17:11:49 2015 -0700
Committer: Jinfeng Ni <jni@apache.org>
Committed: Thu Sep 10 17:45:56 2015 -0700

----------------------------------------------------------------------
 .../apache/drill/exec/TestWindowFunctions.java  | 299 ++++++++++++++++++-
 1 file changed, 284 insertions(+), 15 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/24bcca11/exec/java-exec/src/test/java/org/apache/drill/exec/TestWindowFunctions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/TestWindowFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/TestWindowFunctions.java
index 0ce82f5..0efd55c 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/exec/TestWindowFunctions.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/TestWindowFunctions.java
@@ -38,19 +38,91 @@ public class TestWindowFunctions extends BaseTestQuery {
 
   @Test // DRILL-3196
   public void testSinglePartition() throws Exception {
-    final String query = "explain plan for select sum(a2) over(partition by a2), count(*)
over(partition by a2) \n" +
+    final String query = "select sum(n_nationKey) over(partition by n_nationKey) as col1,
count(*) over(partition by n_nationKey) as col2 \n" +
         "from cp.`tpch/nation.parquet`";
 
-    test(query);
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{0\\} order by \\[\\].*\\[SUM\\(\\$0\\),
COUNT\\(\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("col1", "col2")
+        .baselineValues(0l, 1l)
+        .baselineValues(1l, 1l)
+        .baselineValues(2l, 1l)
+        .baselineValues(3l, 1l)
+        .baselineValues(4l, 1l)
+        .baselineValues(5l, 1l)
+        .baselineValues(6l, 1l)
+        .baselineValues(7l, 1l)
+        .baselineValues(8l, 1l)
+        .baselineValues(9l, 1l)
+        .baselineValues(10l, 1l)
+        .baselineValues(11l, 1l)
+        .baselineValues(12l, 1l)
+        .baselineValues(13l, 1l)
+        .baselineValues(14l, 1l)
+        .baselineValues(15l, 1l)
+        .baselineValues(16l, 1l)
+        .baselineValues(17l, 1l)
+        .baselineValues(18l, 1l)
+        .baselineValues(19l, 1l)
+        .baselineValues(20l, 1l)
+        .baselineValues(21l, 1l)
+        .baselineValues(22l, 1l)
+        .baselineValues(23l, 1l)
+        .baselineValues(24l, 1l)
+        .build()
+        .run();
   }
 
   @Test // DRILL-3196
   public void testSinglePartitionDefinedInWindowList() throws Exception {
-    final String query = "explain plan for select sum(a2) over w \n" +
+    final String query = "select sum(n_nationKey) over w as col \n" +
         "from cp.`tpch/nation.parquet` \n" +
-        "window w as (partition by a2 order by a2)";
+        "window w as (partition by n_nationKey order by n_nationKey)";
+
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{0\\} order by \\[0\\].*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
 
-    test(query);
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("col")
+        .baselineValues(0l)
+        .baselineValues(1l)
+        .baselineValues(2l)
+        .baselineValues(3l)
+        .baselineValues(4l)
+        .baselineValues(5l)
+        .baselineValues(6l)
+        .baselineValues(7l)
+        .baselineValues(8l)
+        .baselineValues(9l)
+        .baselineValues(10l)
+        .baselineValues(11l)
+        .baselineValues(12l)
+        .baselineValues(13l)
+        .baselineValues(14l)
+        .baselineValues(15l)
+        .baselineValues(16l)
+        .baselineValues(17l)
+        .baselineValues(18l)
+        .baselineValues(19l)
+        .baselineValues(20l)
+        .baselineValues(21l)
+        .baselineValues(22l)
+        .baselineValues(23l)
+        .baselineValues(24l)
+        .build()
+        .run();
   }
 
   @Test(expected = UnsupportedFunctionException.class) // DRILL-3182
@@ -165,39 +237,170 @@ public class TestWindowFunctions extends BaseTestQuery {
 
   @Test // DRILL-3188
   public void testWindowFrameEquivalentToDefault() throws Exception {
-    final String query1 = "explain plan for select sum(n_nationKey) over(partition by n_nationKey
order by n_nationKey) \n" +
+    final String query1 = "select sum(n_nationKey) over(partition by n_nationKey order by
n_nationKey) as col\n" +
         "from cp.`tpch/nation.parquet` t \n" +
         "order by n_nationKey";
 
-    final String query2 = "explain plan for select sum(n_nationKey) over(partition by n_nationKey
order by n_nationKey \n" +
-        "range between unbounded preceding and current row) \n" +
+    final String query2 = "select sum(n_nationKey) over(partition by n_nationKey order by
n_nationKey \n" +
+        "range between unbounded preceding and current row) as col \n" +
         "from cp.`tpch/nation.parquet` t \n" +
         "order by n_nationKey";
 
-    final String query3 = "explain plan for select sum(n_nationKey) over(partition by n_nationKey
\n" +
-        "rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)" +
+    final String query3 = "select sum(n_nationKey) over(partition by n_nationKey \n" +
+        "rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as col \n" +
         "from cp.`tpch/nation.parquet` t \n" +
         "order by n_nationKey";
 
-    test(query1);
-    test(query2);
-    test(query3);
+    // Validate the plan
+    final String[] expectedPlan1 = {"Window.*partition \\{0\\} order by \\[0\\].*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns1 = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query1, expectedPlan1, excludedPatterns1);
+
+    testBuilder()
+        .sqlQuery(query1)
+        .unOrdered()
+        .baselineColumns("col")
+        .baselineValues(0l)
+        .baselineValues(1l)
+        .baselineValues(2l)
+        .baselineValues(3l)
+        .baselineValues(4l)
+        .baselineValues(5l)
+        .baselineValues(6l)
+        .baselineValues(7l)
+        .baselineValues(8l)
+        .baselineValues(9l)
+        .baselineValues(10l)
+        .baselineValues(11l)
+        .baselineValues(12l)
+        .baselineValues(13l)
+        .baselineValues(14l)
+        .baselineValues(15l)
+        .baselineValues(16l)
+        .baselineValues(17l)
+        .baselineValues(18l)
+        .baselineValues(19l)
+        .baselineValues(20l)
+        .baselineValues(21l)
+        .baselineValues(22l)
+        .baselineValues(23l)
+        .baselineValues(24l)
+        .build()
+        .run();
+
+    final String[] expectedPlan2 = {"Window.*partition \\{0\\} order by \\[0\\].*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns2 = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query2, expectedPlan2, excludedPatterns2);
+
+    testBuilder()
+        .sqlQuery(query2)
+        .unOrdered()
+        .baselineColumns("col")
+        .baselineValues(0l)
+        .baselineValues(1l)
+        .baselineValues(2l)
+        .baselineValues(3l)
+        .baselineValues(4l)
+        .baselineValues(5l)
+        .baselineValues(6l)
+        .baselineValues(7l)
+        .baselineValues(8l)
+        .baselineValues(9l)
+        .baselineValues(10l)
+        .baselineValues(11l)
+        .baselineValues(12l)
+        .baselineValues(13l)
+        .baselineValues(14l)
+        .baselineValues(15l)
+        .baselineValues(16l)
+        .baselineValues(17l)
+        .baselineValues(18l)
+        .baselineValues(19l)
+        .baselineValues(20l)
+        .baselineValues(21l)
+        .baselineValues(22l)
+        .baselineValues(23l)
+        .baselineValues(24l)
+        .build()
+        .run();
+
+    final String[] expectedPlan3 = {"Window.*partition \\{0\\}.*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns3 = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query3, expectedPlan3, excludedPatterns3);
+
+    testBuilder()
+        .sqlQuery(query3)
+        .unOrdered()
+        .baselineColumns("col")
+        .baselineValues(0l)
+        .baselineValues(1l)
+        .baselineValues(2l)
+        .baselineValues(3l)
+        .baselineValues(4l)
+        .baselineValues(5l)
+        .baselineValues(6l)
+        .baselineValues(7l)
+        .baselineValues(8l)
+        .baselineValues(9l)
+        .baselineValues(10l)
+        .baselineValues(11l)
+        .baselineValues(12l)
+        .baselineValues(13l)
+        .baselineValues(14l)
+        .baselineValues(15l)
+        .baselineValues(16l)
+        .baselineValues(17l)
+        .baselineValues(18l)
+        .baselineValues(19l)
+        .baselineValues(20l)
+        .baselineValues(21l)
+        .baselineValues(22l)
+        .baselineValues(23l)
+        .baselineValues(24l)
+        .build()
+        .run();
   }
 
   @Test // DRILL-3204
   public void testWindowWithJoin() throws Exception {
-    final String query = "select sum(t1.r_regionKey) over(partition by t1.r_regionKey)  \n"
+
+    final String query = "select sum(t1.r_regionKey) over(partition by t1.r_regionKey) as
col \n" +
         "from cp.`tpch/region.parquet` t1, cp.`tpch/nation.parquet` t2 \n" +
         "where t1.r_regionKey = t2.n_nationKey \n" +
         "group by t1.r_regionKey";
 
-    test(query);
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{0\\}.*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationKey`\\].*",
+        "Scan.*columns=\\[`n_nationKey`\\].*"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\].*"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("col")
+        .baselineValues(0l)
+        .baselineValues(1l)
+        .baselineValues(2l)
+        .baselineValues(3l)
+        .baselineValues(4l)
+        .build()
+        .run();
   }
 
   @Test // DRILL-3298
   public void testCountEmptyPartitionByWithExchange() throws Exception {
     String query = String.format("select count(*) over (order by o_orderpriority) as cnt
from dfs.`%s/multilevel/parquet` where o_custkey < 100", TEST_RES_PATH);
     try {
+      // Validate the plan
+      final String[] expectedPlan = {"Window.*partition \\{\\} order by \\[0\\].*COUNT\\(\\)",
+          "Scan.*columns=\\[`o_custkey`, `o_orderpriority`\\]"};
+      final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+      PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
       testBuilder()
         .sqlQuery(query)
         .ordered()
@@ -223,6 +426,12 @@ public class TestWindowFunctions extends BaseTestQuery {
         "from cp.`tpch/nation.parquet` " +
         "where n_nationkey = 1";
 
+    // Validate the plan
+    final String[] expectedPlan1 = {"Window.*partition \\{0\\} order by \\[\\].*SUM\\(\\$0\\),
COUNT\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationkey`\\]"};
+    final String[] excludedPatterns1 = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(avgQuery, expectedPlan1, excludedPatterns1);
+
     testBuilder()
         .sqlQuery(avgQuery)
         .unOrdered()
@@ -234,6 +443,12 @@ public class TestWindowFunctions extends BaseTestQuery {
         "from cp.`tpch/nation.parquet` " +
         "where n_nationkey = 1";
 
+    // Validate the plan
+    final String[] expectedPlan2 = {"Window.*partition \\{0\\} order by \\[\\].*SUM\\(\\$1\\),
SUM\\(\\$0\\), COUNT\\(\\$0\\)",
+        "Scan.*columns=\\[`n_nationkey`\\]"};
+    final String[] excludedPatterns2 = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(varianceQuery, expectedPlan2, excludedPatterns2);
+
     testBuilder()
         .sqlQuery(varianceQuery)
         .unOrdered()
@@ -247,6 +462,12 @@ public class TestWindowFunctions extends BaseTestQuery {
     final String query = "select sum(cast(col_int as int)) over (partition by col_varchar)
as col1 " +
         "from cp.`jsoninput/large_int.json` limit 1";
 
+    // Validate the plan
+    final String[] expectedPlan1 = {"Window.*partition \\{0\\} order by \\[\\].*SUM\\(\\$1\\)",
+        "Scan.*columns=\\[`col_varchar`, `col_int`\\]"};
+    final String[] excludedPatterns1 = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan1, excludedPatterns1);
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -257,6 +478,12 @@ public class TestWindowFunctions extends BaseTestQuery {
     final String avgQuery = "select avg(cast(col_int as int)) over (partition by col_varchar)
as col1 " +
         "from cp.`jsoninput/large_int.json` limit 1";
 
+    // Validate the plan
+    final String[] expectedPlan2 = {"Window.*partition \\{0\\} order by \\[\\].*SUM\\(\\$1\\),
COUNT\\(\\$1\\)",
+        "Scan.*columns=\\[`col_varchar`, `col_int`\\]"};
+    final String[] excludedPatterns2 = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(avgQuery, expectedPlan2, excludedPatterns2);
+
     testBuilder()
         .sqlQuery(avgQuery)
         .unOrdered()
@@ -272,6 +499,12 @@ public class TestWindowFunctions extends BaseTestQuery {
         "FROM dfs_test.`%s` \n" +
         "WINDOW w AS (PARTITION BY columns[1] ORDER BY columns[0] DESC)", root);
 
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{1\\} order by \\[0 DESC\\].*COUNT\\(\\)",
+        "Scan.*columns=\\[`columns`\\[0\\], `columns`\\[1\\]\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     // Validate the result
     testBuilder()
         .sqlQuery(query)
@@ -296,6 +529,12 @@ public class TestWindowFunctions extends BaseTestQuery {
     final String query = "select dense_rank() over (order by l_suppkey) as rank1 " +
         " from cp.`tpch/lineitem.parquet` group by l_partkey, l_suppkey order by 1 desc limit
1";
 
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{\\} order by \\[1\\].*DENSE_RANK\\(\\)",
+        "Scan.*columns=\\[`l_partkey`, `l_suppkey`\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -307,6 +546,13 @@ public class TestWindowFunctions extends BaseTestQuery {
   @Test // DRILL-3404
   public void testWindowSumAggIsNotNull() throws Exception {
     String query = String.format("select count(*) cnt from (select sum ( c1 ) over ( partition
by c2 order by c1 asc nulls first ) w_sum from dfs.`%s/window/table_with_nulls.parquet` )
sub_query where w_sum is not null", TEST_RES_PATH);
+
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{1\\} order by \\[0 ASC-nulls-first\\].*SUM\\(\\$0\\)",
+        "Scan.*columns=\\[`c1`, `c2`\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     testBuilder()
       .sqlQuery(query)
       .ordered()
@@ -322,6 +568,12 @@ public class TestWindowFunctions extends BaseTestQuery {
         "where position_id = 2 \n" +
         "window w as(partition by position_id order by employee_id)";
 
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{0\\} order by \\[1\\].*RANK\\(\\),
SUM\\(\\$2\\), SUM\\(\\$1\\), SUM\\(\\$3\\)",
+        "Scan.*columns=\\[`position_id`, `employee_id`\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     testBuilder()
         .sqlQuery(query)
         .ordered()
@@ -344,6 +596,13 @@ public class TestWindowFunctions extends BaseTestQuery {
         "count(*) over(partition by a1 order by c1) as count2 \n" +
         "from dfs_test.`%s`", root);
 
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{2\\} order by \\[1\\].*COUNT\\(\\)",
+        "Window.*partition \\{0\\} order by \\[1\\].*COUNT\\(\\), SUM\\(\\$2\\)",
+        "Scan.*columns=\\[`b1`, `c1`, `a1`\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -370,6 +629,13 @@ public class TestWindowFunctions extends BaseTestQuery {
         "sum(a1)  over(partition by b1 order by c1) as sum1 \n" +
         "from dfs_test.`%s`", root);
 
+    // Validate the plan
+    final String[] expectedPlan = {"Window.*partition \\{2\\} order by \\[1\\].*COUNT\\(\\)",
+        "Window.*partition \\{0\\} order by \\[1\\].*COUNT\\(\\), SUM\\(\\$2\\)",
+        "Scan.*columns=\\[`b1`, `c1`, `a1`\\]"};
+    final String[] excludedPatterns = {"Scan.*columns=\\[`\\*`\\]"};
+    PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, excludedPatterns);
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -400,6 +666,7 @@ public class TestWindowFunctions extends BaseTestQuery {
     final String[] expectedPlan = {"Window\\(window#0=\\[window\\(partition \\{\\}.*\n" +
         ".*UnionExchange"};
     PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, new String[]{});
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -433,6 +700,7 @@ public class TestWindowFunctions extends BaseTestQuery {
         ".*Window.*SUM\\(\\$2\\).*"
     };
     PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, new String[]{});
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -489,6 +757,7 @@ public class TestWindowFunctions extends BaseTestQuery {
     // Validate the plan
     final String[] expectedPlan = {"Scan.*columns=\\[`n_nationkey`\\].*"};
     PlanTestBase.testPlanMatchingPatterns(query, expectedPlan, new String[]{});
+
     testBuilder()
         .sqlQuery(query)
         .unOrdered()


Mime
View raw message