-- Automatically generated by SQLQueryTestSuite -- Number of queries: 26 -- !query 0 CREATE OR REPLACE TEMPORARY VIEW testData AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 1), (2, 2), (3, 1), (3, 2) AS testData(a, b) -- !query 0 schema struct<> -- !query 0 output -- !query 1 SELECT a + b, b, SUM(a - b) FROM testData GROUP BY a + b, b WITH CUBE -- !query 1 schema struct<(a + b):int,b:int,sum((a - b)):bigint> -- !query 1 output 2 1 0 2 NULL 0 3 1 1 3 2 -1 3 NULL 0 4 1 2 4 2 0 4 NULL 2 5 2 1 5 NULL 1 NULL 1 3 NULL 2 0 NULL NULL 3 -- !query 2 SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH CUBE -- !query 2 schema struct -- !query 2 output 1 1 1 1 2 2 1 NULL 3 2 1 1 2 2 2 2 NULL 3 3 1 1 3 2 2 3 NULL 3 NULL 1 3 NULL 2 6 NULL NULL 9 -- !query 3 SELECT a + b, b, SUM(a - b) FROM testData GROUP BY a + b, b WITH ROLLUP -- !query 3 schema struct<(a + b):int,b:int,sum((a - b)):bigint> -- !query 3 output 2 1 0 2 NULL 0 3 1 1 3 2 -1 3 NULL 0 4 1 2 4 2 0 4 NULL 2 5 2 1 5 NULL 1 NULL NULL 3 -- !query 4 SELECT a, b, SUM(b) FROM testData GROUP BY a, b WITH ROLLUP -- !query 4 schema struct -- !query 4 output 1 1 1 1 2 2 1 NULL 3 2 1 1 2 2 2 2 NULL 3 3 1 1 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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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 -- !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;