1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test HAVING clause", "[having]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
14
15 // HAVING with condition on group
16 result = con.Query("SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING "
17 "b=21 ORDER BY b;");
18 REQUIRE(CHECK_COLUMN(result, 0, {21}));
19 REQUIRE(CHECK_COLUMN(result, 1, {12}));
20
21 // HAVING with condition on sum
22 result = con.Query("SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a) < 20 ORDER BY b;");
23 REQUIRE(CHECK_COLUMN(result, 0, {21}));
24 REQUIRE(CHECK_COLUMN(result, 1, {12}));
25
26 // HAVING with condition on ALIAS
27 // CONTROVERSIAL: this DOES work in SQLite, but not in PostgreSQL
28 REQUIRE_FAIL(con.Query("SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING sum < 20 ORDER BY b;"));
29
30 // HAVING without alias
31 result = con.Query("SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING "
32 "SUM(a) < 20 ORDER BY b;");
33 REQUIRE(CHECK_COLUMN(result, 0, {21}));
34 REQUIRE(CHECK_COLUMN(result, 1, {12}));
35 REQUIRE(result->types.size() == 2);
36
37 // HAVING on column not in aggregate
38 result = con.Query("SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING "
39 "COUNT(*) = 1 ORDER BY b;");
40 REQUIRE(CHECK_COLUMN(result, 0, {21}));
41 REQUIRE(CHECK_COLUMN(result, 1, {12}));
42 REQUIRE(result->types.size() == 2);
43
44 // expression in having
45 result = con.Query("SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a)+10>28;");
46 REQUIRE(CHECK_COLUMN(result, 0, {22}));
47 REQUIRE(CHECK_COLUMN(result, 1, {24}));
48 REQUIRE(result->types.size() == 2);
49
50 // uncorrelated subquery in having
51 result = con.Query("SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a)>(SELECT SUM(t.a)*0.5 FROM test t);");
52 REQUIRE(CHECK_COLUMN(result, 0, {22}));
53 REQUIRE(CHECK_COLUMN(result, 1, {24}));
54 REQUIRE(result->types.size() == 2);
55
56 // correlated subquery in having
57 result = con.Query("SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)=(SELECT SUM(a) FROM test t WHERE "
58 "test.b=t.b) ORDER BY test.b;");
59 REQUIRE(CHECK_COLUMN(result, 0, {21, 22}));
60 REQUIRE(CHECK_COLUMN(result, 1, {12, 24}));
61 REQUIRE(result->types.size() == 2);
62
63 // use outer aggregation in inner subquery
64 result = con.Query("SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)*2=(SELECT SUM(a)+SUM(t.a) FROM "
65 "test t WHERE test.b=t.b) ORDER BY test.b");
66 REQUIRE(CHECK_COLUMN(result, 0, {21, 22}));
67 REQUIRE(CHECK_COLUMN(result, 1, {12, 24}));
68 REQUIRE(result->types.size() == 2);
69
70 // use outer aggregation that hasn't been used yet in subquery
71 result = con.Query("SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)*2+2=(SELECT "
72 "SUM(a)+SUM(t.a)+COUNT(t.a) FROM test t WHERE test.b=t.b) ORDER BY test.b");
73 REQUIRE(CHECK_COLUMN(result, 0, {22}));
74 REQUIRE(CHECK_COLUMN(result, 1, {24}));
75 REQUIRE(result->types.size() == 2);
76
77 // ORDER BY subquery
78 result = con.Query(
79 "SELECT test.b, SUM(a) FROM test GROUP BY test.b ORDER BY (SELECT SUM(a) FROM test t WHERE test.b=t.b) DESC;");
80 REQUIRE(CHECK_COLUMN(result, 0, {22, 21}));
81 REQUIRE(CHECK_COLUMN(result, 1, {24, 12}));
82 REQUIRE(result->types.size() == 2);
83}
84
85TEST_CASE("Test HAVING clause without GROUP BY", "[having]") {
86 unique_ptr<QueryResult> result;
87 DuckDB db(nullptr);
88 Connection con(db);
89
90 // CONTROVERSIAL: HAVING without GROUP BY works in PostgreSQL, but not in SQLite
91 // scalar HAVING queries
92 // constants only
93 result = con.Query("SELECT 42 HAVING 42 > 20");
94 REQUIRE(CHECK_COLUMN(result, 0, {42}));
95 result = con.Query("SELECT 42 HAVING 42 > 80");
96 REQUIRE(CHECK_COLUMN(result, 0, {}));
97 // aggregates
98 result = con.Query("SELECT SUM(42) HAVING AVG(42) > MIN(20)");
99 REQUIRE(CHECK_COLUMN(result, 0, {42}));
100 result = con.Query("SELECT SUM(42) HAVING SUM(42) > SUM(80)");
101 REQUIRE(CHECK_COLUMN(result, 0, {}));
102 result = con.Query("SELECT SUM(42)+COUNT(*)+COUNT(1), 3 HAVING SUM(42)+MAX(20)+AVG(30) > SUM(120)-MIN(100)");
103 REQUIRE(CHECK_COLUMN(result, 0, {44}));
104 REQUIRE(CHECK_COLUMN(result, 1, {3}));
105 // subqueries
106 result = con.Query("SELECT SUM(42) HAVING (SELECT SUM(42)) > SUM(80)");
107 REQUIRE(CHECK_COLUMN(result, 0, {}));
108
109 con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
110 con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)");
111
112 // HAVING with column references does not work
113 // HAVING clause can only contain aggregates
114 REQUIRE_FAIL(con.Query("SELECT a FROM test WHERE a=13 HAVING a > 11"));
115 // HAVING clause also turns the rest of the query into an aggregate
116 // thus column references in SELECT clause also produce errors
117 REQUIRE_FAIL(con.Query("SELECT a FROM test WHERE a=13 HAVING SUM(a) > 11"));
118 // once we produce a sum this works though
119 result = con.Query("SELECT SUM(a) FROM test WHERE a=13 HAVING SUM(a) > 11");
120 REQUIRE(CHECK_COLUMN(result, 0, {13}));
121 result = con.Query("SELECT SUM(a) FROM test WHERE a=13 HAVING SUM(a) > 20");
122 REQUIRE(CHECK_COLUMN(result, 0, {}));
123
124 // HAVING with single-node aggregation does work, even without GROUP BY
125 result = con.Query("SELECT SUM(a) FROM test HAVING SUM(a)>10;");
126 REQUIRE(CHECK_COLUMN(result, 0, {36}));
127 result = con.Query("SELECT SUM(a) FROM test HAVING SUM(a)<10;");
128 REQUIRE(CHECK_COLUMN(result, 0, {}));
129 result = con.Query("SELECT SUM(a) FROM test HAVING COUNT(*)>1;");
130 REQUIRE(CHECK_COLUMN(result, 0, {36}));
131 result = con.Query("SELECT SUM(a) FROM test HAVING COUNT(*)>10;");
132 REQUIRE(CHECK_COLUMN(result, 0, {}));
133}
134