Repository: spark Updated Branches: refs/heads/master dcdda1978 -> 5b7d403c1
[SPARK-18094][SQL][TESTS] Move group analytics test cases from `SQLQuerySuite` into a query file test. ## What changes were proposed in this pull request? Currently we have several test cases for group analytics(ROLLUP/CUBE/GROUPING SETS) in `SQLQuerySuite`, should better move them into a query file test. The following test cases are moved to `group-analytics.sql`: ``` test("rollup") test("grouping sets when aggregate functions containing groupBy columns") test("cube") test("grouping sets") test("grouping and grouping_id") test("grouping and grouping_id in having") test("grouping and grouping_id in sort") ``` This is followup work of #15582 ## How was this patch tested? Modified query file `group-analytics.sql`, which will be tested by `SQLQueryTestSuite`. Author: jiangxingbo <jiangxb1...@gmail.com> Closes #15624 from jiangxb1987/group-analytics-test. Project: http://git-wip-us.apache.org/repos/asf/spark/repo Commit: http://git-wip-us.apache.org/repos/asf/spark/commit/5b7d403c Tree: http://git-wip-us.apache.org/repos/asf/spark/tree/5b7d403c Diff: http://git-wip-us.apache.org/repos/asf/spark/diff/5b7d403c Branch: refs/heads/master Commit: 5b7d403c1819c32a6a5b87d470f8de1a8ad7a987 Parents: dcdda19 Author: jiangxingbo <jiangxb1...@gmail.com> Authored: Wed Oct 26 23:51:16 2016 +0200 Committer: Reynold Xin <r...@databricks.com> Committed: Wed Oct 26 23:51:16 2016 +0200 ---------------------------------------------------------------------- .../sql-tests/inputs/group-analytics.sql | 46 +++- .../sql-tests/results/group-analytics.sql.out | 247 ++++++++++++++++++- .../org/apache/spark/sql/SQLQuerySuite.scala | 189 -------------- 3 files changed, 290 insertions(+), 192 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/spark/blob/5b7d403c/sql/core/src/test/resources/sql-tests/inputs/group-analytics.sql ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/inputs/group-analytics.sql b/sql/core/src/test/resources/sql-tests/inputs/group-analytics.sql index 2f78349..f813538 100644 --- a/sql/core/src/test/resources/sql-tests/inputs/group-analytics.sql +++ b/sql/core/src/test/resources/sql-tests/inputs/group-analytics.sql @@ -10,4 +10,48 @@ SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH CUBE; -- ROLLUP on overlapping columns SELECT a + b, b, SUM(a - b) FROM testData GROUP BY a + b, b WITH ROLLUP; -SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH ROLLUP; \ No newline at end of file +SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH ROLLUP; + +CREATE OR REPLACE TEMPORARY VIEW courseSales AS SELECT * FROM VALUES +("dotNET", 2012, 10000), ("Java", 2012, 20000), ("dotNET", 2012, 5000), ("dotNET", 2013, 48000), ("Java", 2013, 30000) +AS courseSales(course, year, earnings); + +-- ROLLUP +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY ROLLUP(course, year) ORDER BY course, year; + +-- CUBE +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY CUBE(course, year) ORDER BY course, year; + +-- GROUPING SETS +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course, year); +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course); +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(year); + +-- GROUPING SETS with aggregate functions containing groupBy columns +SELECT course, SUM(earnings) AS sum FROM courseSales +GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum; +SELECT course, SUM(earnings) AS sum, GROUPING_ID(course, earnings) FROM courseSales +GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum; + +-- GROUPING/GROUPING_ID +SELECT course, year, GROUPING(course), GROUPING(year), GROUPING_ID(course, year) FROM courseSales +GROUP BY CUBE(course, year); +SELECT course, year, GROUPING(course) FROM courseSales GROUP BY course, year; +SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY course, year; +SELECT course, year, grouping__id FROM courseSales GROUP BY CUBE(course, year); + +-- GROUPING/GROUPING_ID in having clause +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) +HAVING GROUPING(year) = 1 AND GROUPING_ID(course, year) > 0; +SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING(course) > 0; +SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING_ID(course) > 0; +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) HAVING grouping__id > 0; + +-- GROUPING/GROUPING_ID in orderBy clause +SELECT course, year, GROUPING(course), GROUPING(year) FROM courseSales GROUP BY CUBE(course, year) +ORDER BY GROUPING(course), GROUPING(year), course, year; +SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY CUBE(course, year) +ORDER BY GROUPING(course), GROUPING(year), course, year; +SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING(course); +SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING_ID(course); +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) ORDER BY grouping__id; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/spark/blob/5b7d403c/sql/core/src/test/resources/sql-tests/results/group-analytics.sql.out ---------------------------------------------------------------------- diff --git a/sql/core/src/test/resources/sql-tests/results/group-analytics.sql.out b/sql/core/src/test/resources/sql-tests/results/group-analytics.sql.out index 8ea7de8..825e8f5 100644 --- a/sql/core/src/test/resources/sql-tests/results/group-analytics.sql.out +++ b/sql/core/src/test/resources/sql-tests/results/group-analytics.sql.out @@ -1,5 +1,5 @@ -- Automatically generated by SQLQueryTestSuite --- Number of queries: 5 +-- Number of queries: 26 -- !query 0 @@ -32,7 +32,6 @@ NULL 2 0 NULL NULL 3 - -- !query 2 SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH CUBE -- !query 2 schema @@ -85,3 +84,247 @@ struct<a:int,b:int,sum(b):bigint> 3 2 2 3 NULL 3 NULL NULL 9 + + +-- !query 5 +CREATE OR REPLACE TEMPORARY VIEW courseSales AS SELECT * FROM VALUES +("dotNET", 2012, 10000), ("Java", 2012, 20000), ("dotNET", 2012, 5000), ("dotNET", 2013, 48000), ("Java", 2013, 30000) +AS courseSales(course, year, earnings) +-- !query 5 schema +struct<> +-- !query 5 output + + + +-- !query 6 +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY ROLLUP(course, year) ORDER BY course, year +-- !query 6 schema +struct<course:string,year:int,sum(earnings):bigint> +-- !query 6 output +NULL NULL 113000 +Java NULL 50000 +Java 2012 20000 +Java 2013 30000 +dotNET NULL 63000 +dotNET 2012 15000 +dotNET 2013 48000 + + +-- !query 7 +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY CUBE(course, year) ORDER BY course, year +-- !query 7 schema +struct<course:string,year:int,sum(earnings):bigint> +-- !query 7 output +NULL NULL 113000 +NULL 2012 35000 +NULL 2013 78000 +Java NULL 50000 +Java 2012 20000 +Java 2013 30000 +dotNET NULL 63000 +dotNET 2012 15000 +dotNET 2013 48000 + + +-- !query 8 +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course, year) +-- !query 8 schema +struct<course:string,year:int,sum(earnings):bigint> +-- !query 8 output +Java NULL 50000 +NULL 2012 35000 +NULL 2013 78000 +dotNET NULL 63000 + + +-- !query 9 +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(course) +-- !query 9 schema +struct<course:string,year:int,sum(earnings):bigint> +-- !query 9 output +Java NULL 50000 +dotNET NULL 63000 + + +-- !query 10 +SELECT course, year, SUM(earnings) FROM courseSales GROUP BY course, year GROUPING SETS(year) +-- !query 10 schema +struct<course:string,year:int,sum(earnings):bigint> +-- !query 10 output +NULL 2012 35000 +NULL 2013 78000 + + +-- !query 11 +SELECT course, SUM(earnings) AS sum FROM courseSales +GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum +-- !query 11 schema +struct<course:string,sum:bigint> +-- !query 11 output +NULL 113000 +Java 20000 +Java 30000 +Java 50000 +dotNET 5000 +dotNET 10000 +dotNET 48000 +dotNET 63000 + + +-- !query 12 +SELECT course, SUM(earnings) AS sum, GROUPING_ID(course, earnings) FROM courseSales +GROUP BY course, earnings GROUPING SETS((), (course), (course, earnings)) ORDER BY course, sum +-- !query 12 schema +struct<course:string,sum:bigint,grouping_id(course, earnings):int> +-- !query 12 output +NULL 113000 3 +Java 20000 0 +Java 30000 0 +Java 50000 1 +dotNET 5000 0 +dotNET 10000 0 +dotNET 48000 0 +dotNET 63000 1 + + +-- !query 13 +SELECT course, year, GROUPING(course), GROUPING(year), GROUPING_ID(course, year) FROM courseSales +GROUP BY CUBE(course, year) +-- !query 13 schema +struct<course:string,year:int,grouping(course):tinyint,grouping(year):tinyint,grouping_id(course, year):int> +-- !query 13 output +Java 2012 0 0 0 +Java 2013 0 0 0 +Java NULL 0 1 1 +NULL 2012 1 0 2 +NULL 2013 1 0 2 +NULL NULL 1 1 3 +dotNET 2012 0 0 0 +dotNET 2013 0 0 0 +dotNET NULL 0 1 1 + + +-- !query 14 +SELECT course, year, GROUPING(course) FROM courseSales GROUP BY course, year +-- !query 14 schema +struct<> +-- !query 14 output +org.apache.spark.sql.AnalysisException +grouping() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 15 +SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY course, year +-- !query 15 schema +struct<> +-- !query 15 output +org.apache.spark.sql.AnalysisException +grouping_id() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 16 +SELECT course, year, grouping__id FROM courseSales GROUP BY CUBE(course, year) +-- !query 16 schema +struct<> +-- !query 16 output +org.apache.spark.sql.AnalysisException +grouping__id is deprecated; use grouping_id() instead; + + +-- !query 17 +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) +HAVING GROUPING(year) = 1 AND GROUPING_ID(course, year) > 0 +-- !query 17 schema +struct<course:string,year:int> +-- !query 17 output +Java NULL +NULL NULL +dotNET NULL + + +-- !query 18 +SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING(course) > 0 +-- !query 18 schema +struct<> +-- !query 18 output +org.apache.spark.sql.AnalysisException +grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 19 +SELECT course, year FROM courseSales GROUP BY course, year HAVING GROUPING_ID(course) > 0 +-- !query 19 schema +struct<> +-- !query 19 output +org.apache.spark.sql.AnalysisException +grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 20 +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) HAVING grouping__id > 0 +-- !query 20 schema +struct<> +-- !query 20 output +org.apache.spark.sql.AnalysisException +grouping__id is deprecated; use grouping_id() instead; + + +-- !query 21 +SELECT course, year, GROUPING(course), GROUPING(year) FROM courseSales GROUP BY CUBE(course, year) +ORDER BY GROUPING(course), GROUPING(year), course, year +-- !query 21 schema +struct<course:string,year:int,grouping(course):tinyint,grouping(year):tinyint> +-- !query 21 output +Java 2012 0 0 +Java 2013 0 0 +dotNET 2012 0 0 +dotNET 2013 0 0 +Java NULL 0 1 +dotNET NULL 0 1 +NULL 2012 1 0 +NULL 2013 1 0 +NULL NULL 1 1 + + +-- !query 22 +SELECT course, year, GROUPING_ID(course, year) FROM courseSales GROUP BY CUBE(course, year) +ORDER BY GROUPING(course), GROUPING(year), course, year +-- !query 22 schema +struct<course:string,year:int,grouping_id(course, year):int> +-- !query 22 output +Java 2012 0 +Java 2013 0 +dotNET 2012 0 +dotNET 2013 0 +Java NULL 1 +dotNET NULL 1 +NULL 2012 2 +NULL 2013 2 +NULL NULL 3 + + +-- !query 23 +SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING(course) +-- !query 23 schema +struct<> +-- !query 23 output +org.apache.spark.sql.AnalysisException +grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 24 +SELECT course, year FROM courseSales GROUP BY course, year ORDER BY GROUPING_ID(course) +-- !query 24 schema +struct<> +-- !query 24 output +org.apache.spark.sql.AnalysisException +grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup; + + +-- !query 25 +SELECT course, year FROM courseSales GROUP BY CUBE(course, year) ORDER BY grouping__id +-- !query 25 schema +struct<> +-- !query 25 output +org.apache.spark.sql.AnalysisException +grouping__id is deprecated; use grouping_id() instead; http://git-wip-us.apache.org/repos/asf/spark/blob/5b7d403c/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala ---------------------------------------------------------------------- diff --git a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala index bd4c253..1a43d0b 100644 --- a/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala +++ b/sql/core/src/test/scala/org/apache/spark/sql/SQLQuerySuite.scala @@ -2005,195 +2005,6 @@ class SQLQuerySuite extends QueryTest with SharedSQLContext { Row(false) :: Row(true) :: Nil) } - test("rollup") { - checkAnswer( - sql("select course, year, sum(earnings) from courseSales group by rollup(course, year)" + - " order by course, year"), - Row(null, null, 113000.0) :: - Row("Java", null, 50000.0) :: - Row("Java", 2012, 20000.0) :: - Row("Java", 2013, 30000.0) :: - Row("dotNET", null, 63000.0) :: - Row("dotNET", 2012, 15000.0) :: - Row("dotNET", 2013, 48000.0) :: Nil - ) - } - - test("grouping sets when aggregate functions containing groupBy columns") { - checkAnswer( - sql("select course, sum(earnings) as sum from courseSales group by course, earnings " + - "grouping sets((), (course), (course, earnings)) " + - "order by course, sum"), - Row(null, 113000.0) :: - Row("Java", 20000.0) :: - Row("Java", 30000.0) :: - Row("Java", 50000.0) :: - Row("dotNET", 5000.0) :: - Row("dotNET", 10000.0) :: - Row("dotNET", 48000.0) :: - Row("dotNET", 63000.0) :: Nil - ) - - checkAnswer( - sql("select course, sum(earnings) as sum, grouping_id(course, earnings) from courseSales " + - "group by course, earnings grouping sets((), (course), (course, earnings)) " + - "order by course, sum"), - Row(null, 113000.0, 3) :: - Row("Java", 20000.0, 0) :: - Row("Java", 30000.0, 0) :: - Row("Java", 50000.0, 1) :: - Row("dotNET", 5000.0, 0) :: - Row("dotNET", 10000.0, 0) :: - Row("dotNET", 48000.0, 0) :: - Row("dotNET", 63000.0, 1) :: Nil - ) - } - - test("cube") { - checkAnswer( - sql("select course, year, sum(earnings) from courseSales group by cube(course, year)"), - Row("Java", 2012, 20000.0) :: - Row("Java", 2013, 30000.0) :: - Row("Java", null, 50000.0) :: - Row("dotNET", 2012, 15000.0) :: - Row("dotNET", 2013, 48000.0) :: - Row("dotNET", null, 63000.0) :: - Row(null, 2012, 35000.0) :: - Row(null, 2013, 78000.0) :: - Row(null, null, 113000.0) :: Nil - ) - } - - test("grouping sets") { - checkAnswer( - sql("select course, year, sum(earnings) from courseSales group by course, year " + - "grouping sets(course, year)"), - Row("Java", null, 50000.0) :: - Row("dotNET", null, 63000.0) :: - Row(null, 2012, 35000.0) :: - Row(null, 2013, 78000.0) :: Nil - ) - - checkAnswer( - sql("select course, year, sum(earnings) from courseSales group by course, year " + - "grouping sets(course)"), - Row("Java", null, 50000.0) :: - Row("dotNET", null, 63000.0) :: Nil - ) - - checkAnswer( - sql("select course, year, sum(earnings) from courseSales group by course, year " + - "grouping sets(year)"), - Row(null, 2012, 35000.0) :: - Row(null, 2013, 78000.0) :: Nil - ) - } - - test("grouping and grouping_id") { - checkAnswer( - sql("select course, year, grouping(course), grouping(year), grouping_id(course, year)" + - " from courseSales group by cube(course, year)"), - Row("Java", 2012, 0, 0, 0) :: - Row("Java", 2013, 0, 0, 0) :: - Row("Java", null, 0, 1, 1) :: - Row("dotNET", 2012, 0, 0, 0) :: - Row("dotNET", 2013, 0, 0, 0) :: - Row("dotNET", null, 0, 1, 1) :: - Row(null, 2012, 1, 0, 2) :: - Row(null, 2013, 1, 0, 2) :: - Row(null, null, 1, 1, 3) :: Nil - ) - - var error = intercept[AnalysisException] { - sql("select course, year, grouping(course) from courseSales group by course, year") - } - assert(error.getMessage contains "grouping() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year, grouping_id(course, year) from courseSales group by course, year") - } - assert(error.getMessage contains "grouping_id() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year, grouping__id from courseSales group by cube(course, year)") - } - assert(error.getMessage contains "grouping__id is deprecated; use grouping_id() instead") - } - - test("grouping and grouping_id in having") { - checkAnswer( - sql("select course, year from courseSales group by cube(course, year)" + - " having grouping(year) = 1 and grouping_id(course, year) > 0"), - Row("Java", null) :: - Row("dotNET", null) :: - Row(null, null) :: Nil - ) - - var error = intercept[AnalysisException] { - sql("select course, year from courseSales group by course, year" + - " having grouping(course) > 0") - } - assert(error.getMessage contains - "grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year from courseSales group by course, year" + - " having grouping_id(course, year) > 0") - } - assert(error.getMessage contains - "grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year from courseSales group by cube(course, year)" + - " having grouping__id > 0") - } - assert(error.getMessage contains "grouping__id is deprecated; use grouping_id() instead") - } - - test("grouping and grouping_id in sort") { - checkAnswer( - sql("select course, year, grouping(course), grouping(year) from courseSales" + - " group by cube(course, year) order by grouping_id(course, year), course, year"), - Row("Java", 2012, 0, 0) :: - Row("Java", 2013, 0, 0) :: - Row("dotNET", 2012, 0, 0) :: - Row("dotNET", 2013, 0, 0) :: - Row("Java", null, 0, 1) :: - Row("dotNET", null, 0, 1) :: - Row(null, 2012, 1, 0) :: - Row(null, 2013, 1, 0) :: - Row(null, null, 1, 1) :: Nil - ) - - checkAnswer( - sql("select course, year, grouping_id(course, year) from courseSales" + - " group by cube(course, year) order by grouping(course), grouping(year), course, year"), - Row("Java", 2012, 0) :: - Row("Java", 2013, 0) :: - Row("dotNET", 2012, 0) :: - Row("dotNET", 2013, 0) :: - Row("Java", null, 1) :: - Row("dotNET", null, 1) :: - Row(null, 2012, 2) :: - Row(null, 2013, 2) :: - Row(null, null, 3) :: Nil - ) - - var error = intercept[AnalysisException] { - sql("select course, year from courseSales group by course, year" + - " order by grouping(course)") - } - assert(error.getMessage contains - "grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year from courseSales group by course, year" + - " order by grouping_id(course, year)") - } - assert(error.getMessage contains - "grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup") - error = intercept[AnalysisException] { - sql("select course, year from courseSales group by cube(course, year)" + - " order by grouping__id") - } - assert(error.getMessage contains "grouping__id is deprecated; use grouping_id() instead") - } - test("filter on a grouping column that is not presented in SELECT") { checkAnswer( sql("select count(1) from (select 1 as a) t group by a having a > 0"), --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@spark.apache.org For additional commands, e-mail: commits-h...@spark.apache.org