You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by jn...@apache.org on 2015/09/11 07:20:13 UTC
[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 <hs...@usc.edu>
Authored: Fri Aug 21 17:11:49 2015 -0700
Committer: Jinfeng Ni <jn...@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()