Return-Path: X-Original-To: apmail-drill-commits-archive@www.apache.org Delivered-To: apmail-drill-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AB5E918C30 for ; Fri, 11 Sep 2015 05:20:11 +0000 (UTC) Received: (qmail 76922 invoked by uid 500); 11 Sep 2015 05:20:11 -0000 Delivered-To: apmail-drill-commits-archive@drill.apache.org Received: (qmail 76845 invoked by uid 500); 11 Sep 2015 05:20:11 -0000 Mailing-List: contact commits-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: commits@drill.apache.org Delivered-To: mailing list commits@drill.apache.org Received: (qmail 76818 invoked by uid 99); 11 Sep 2015 05:20:11 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 11 Sep 2015 05:20:11 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 6AEB8DFF32; Fri, 11 Sep 2015 05:20:11 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: jni@apache.org To: commits@drill.apache.org Date: Fri, 11 Sep 2015 05:20:13 -0000 Message-Id: <817e5a9c904e4904a0ed18409ca2e735@git.apache.org> In-Reply-To: <09a00c032d94469d84f3a61251bf3503@git.apache.org> References: <09a00c032d94469d84f3a61251bf3503@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: [3/6] drill git commit: DRILL-3683: Add baseline and expected plan for TestWindowFunctions suite 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 Authored: Fri Aug 21 17:11:49 2015 -0700 Committer: Jinfeng Ni 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()