sql-tests/inputs/grouping_set.sql

0001 CREATE TEMPORARY VIEW grouping AS SELECT * FROM VALUES
0002   ("1", "2", "3", 1),
0003   ("4", "5", "6", 1),
0004   ("7", "8", "9", 1)
0005   as grouping(a, b, c, d);
0006
0007 -- SPARK-17849: grouping set throws NPE #1
0008 SELECT a, b, c, count(d) FROM grouping GROUP BY a, b, c GROUPING SETS (());
0009
0010 -- SPARK-17849: grouping set throws NPE #2
0011 SELECT a, b, c, count(d) FROM grouping GROUP BY a, b, c GROUPING SETS ((a));
0012
0013 -- SPARK-17849: grouping set throws NPE #3
0014 SELECT a, b, c, count(d) FROM grouping GROUP BY a, b, c GROUPING SETS ((c));
0015
0016 -- Group sets without explicit group by
0017 SELECT c1, sum(c2) FROM (VALUES ('x', 10, 0), ('y', 20, 0)) AS t (c1, c2, c3) GROUP BY GROUPING SETS (c1);
0018
0019 -- Group sets without group by and with grouping
0020 SELECT c1, sum(c2), grouping(c1) FROM (VALUES ('x', 10, 0), ('y', 20, 0)) AS t (c1, c2, c3) GROUP BY GROUPING SETS (c1);
0021
0022 -- Mutiple grouping within a grouping set
0023 SELECT c1, c2, Sum(c3), grouping__id
0024 FROM   (VALUES ('x', 'a', 10), ('y', 'b', 20) ) AS t (c1, c2, c3)
0025 GROUP  BY GROUPING SETS ( ( c1 ), ( c2 ) )
0026 HAVING GROUPING__ID > 1;
0027
0028 -- Group sets without explicit group by
0029 SELECT grouping(c1) FROM (VALUES ('x', 'a', 10), ('y', 'b', 20)) AS t (c1, c2, c3) GROUP BY GROUPING SETS (c1,c2);
0030
0031 -- Mutiple grouping within a grouping set
0032 SELECT -c1 AS c1 FROM (values (1,2), (3,2)) t(c1, c2) GROUP BY GROUPING SETS ((c1), (c1, c2));
0033
0034 -- complex expression in grouping sets
0035 SELECT a + b, b, sum(c) FROM (VALUES (1,1,1),(2,2,2)) AS t(a,b,c) GROUP BY GROUPING SETS ( (a + b), (b));
0036
0037 -- complex expression in grouping sets
0038 SELECT a + b, b, sum(c) FROM (VALUES (1,1,1),(2,2,2)) AS t(a,b,c) GROUP BY GROUPING SETS ( (a + b), (b + a), (b));
0039
0040 -- more query constructs with grouping sets
0041 SELECT c1 AS col1, c2 AS col2
0042 FROM   (VALUES (1, 2), (3, 2)) t(c1, c2)
0043 GROUP  BY GROUPING SETS ( ( c1 ), ( c1, c2 ) )
0044 HAVING col2 IS NOT NULL
0045 ORDER  BY -col1;
0046
0047 -- negative tests - must have at least one grouping expression
0048 SELECT a, b, c, count(d) FROM grouping GROUP BY WITH ROLLUP;
0049
0050 SELECT a, b, c, count(d) FROM grouping GROUP BY WITH CUBE;
0051
0052 SELECT c1 FROM (values (1,2), (3,2)) t(c1, c2) GROUP BY GROUPING SETS (());
0053
0054 -- duplicate entries in grouping sets
0055 SELECT k1, k2, avg(v) FROM (VALUES (1,1,1),(2,2,2)) AS t(k1,k2,v) GROUP BY GROUPING SETS ((k1),(k1,k2),(k2,k1));
0056
0057 SELECT grouping__id, k1, k2, avg(v) FROM (VALUES (1,1,1),(2,2,2)) AS t(k1,k2,v) GROUP BY GROUPING SETS ((k1),(k1,k2),(k2,k1));
0058
0059 SELECT grouping(k1), k1, k2, avg(v) FROM (VALUES (1,1,1),(2,2,2)) AS t(k1,k2,v) GROUP BY GROUPING SETS ((k1),(k1,k2),(k2,k1));