-- 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<a:int,b:int,sum(b):bigint>
-- !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<a:int,b:int,sum(b):bigint>
-- !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<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;
