sql-tests/inputs/having.sql

0001 create temporary view hav as select * from values
0002   ("one", 1),
0003   ("two", 2),
0004   ("three", 3),
0005   ("one", 5)
0006   as hav(k, v);
0007
0008 -- having clause
0009 SELECT k, sum(v) FROM hav GROUP BY k HAVING sum(v) > 2;
0010
0011 -- having condition contains grouping column
0012 SELECT count(k) FROM hav GROUP BY v + 1 HAVING v + 1 = 2;
0013
0014 -- SPARK-11032: resolve having correctly
0015 SELECT MIN(t.v) FROM (SELECT * FROM hav WHERE v > 0) t HAVING(COUNT(1) > 0);
0016
0017 -- SPARK-20329: make sure we handle timezones correctly
0018 SELECT a + b FROM VALUES (1L, 2), (3L, 4) AS T(a, b) GROUP BY a + b HAVING a + b > 1;
0019
0020 -- SPARK-31519: Cast in having aggregate expressions returns the wrong result
0021 SELECT SUM(a) AS b, CAST('2020-01-01' AS DATE) AS fake FROM VALUES (1, 10), (2, 20) AS T(a, b) GROUP BY b HAVING b > 10;
0022
0023 -- SPARK-31663: Grouping sets with having clause returns the wrong result
0024 SELECT SUM(a) AS b FROM VALUES (1, 10), (2, 20) AS T(a, b) GROUP BY GROUPING SETS ((b), (a, b)) HAVING b > 10;
0025 SELECT SUM(a) AS b FROM VALUES (1, 10), (2, 20) AS T(a, b) GROUP BY CUBE(a, b) HAVING b > 10;
0026 SELECT SUM(a) AS b FROM VALUES (1, 10), (2, 20) AS T(a, b) GROUP BY ROLLUP(a, b) HAVING b > 10;