1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test scalar aggregates with many different types", "[aggregate]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 // count
13 result = con.Query("SELECT COUNT(), COUNT(1), COUNT(*), COUNT(NULL), COUNT('hello'), COUNT(DATE '1992-02-02')");
14 REQUIRE(CHECK_COLUMN(result, 0, {1}));
15 REQUIRE(CHECK_COLUMN(result, 1, {1}));
16 REQUIRE(CHECK_COLUMN(result, 2, {1}));
17 REQUIRE(CHECK_COLUMN(result, 3, {0}));
18 REQUIRE(CHECK_COLUMN(result, 4, {1}));
19 REQUIRE(CHECK_COLUMN(result, 5, {1}));
20
21 REQUIRE_FAIL(con.Query("SELECT COUNT(1, 2)"));
22 // sum
23 result = con.Query("SELECT SUM(1), SUM(NULL), SUM(33.3)");
24 REQUIRE(CHECK_COLUMN(result, 0, {1}));
25 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
26 REQUIRE(CHECK_COLUMN(result, 2, {33.3}));
27
28 REQUIRE_FAIL(con.Query("SELECT SUM(True)"));
29 REQUIRE_FAIL(con.Query("SELECT SUM('hello')"));
30 REQUIRE_FAIL(con.Query("SELECT SUM(DATE '1992-02-02')"));
31 REQUIRE_FAIL(con.Query("SELECT SUM()"));
32 REQUIRE_FAIL(con.Query("SELECT SUM(1, 2)"));
33 // min
34 result = con.Query("SELECT MIN(1), MIN(NULL), MIN(33.3), MIN('hello'), MIN(True), MIN(DATE '1992-02-02'), "
35 "MIN(TIMESTAMP '2008-01-01 00:00:01')");
36 REQUIRE(CHECK_COLUMN(result, 0, {1}));
37 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
38 REQUIRE(CHECK_COLUMN(result, 2, {33.3}));
39 REQUIRE(CHECK_COLUMN(result, 3, {"hello"}));
40 REQUIRE(CHECK_COLUMN(result, 4, {Value::BOOLEAN(true)}));
41 REQUIRE(CHECK_COLUMN(result, 5, {Value::DATE(1992, 2, 2)}));
42 REQUIRE(CHECK_COLUMN(result, 6, {Value::TIMESTAMP(2008, 1, 1, 0, 0, 1, 0)}));
43
44 REQUIRE_FAIL(con.Query("SELECT MIN()"));
45 REQUIRE_FAIL(con.Query("SELECT MIN(1, 2)"));
46 // max
47 result = con.Query("SELECT MAX(1), MAX(NULL), MAX(33.3), MAX('hello'), MAX(True), MAX(DATE '1992-02-02'), "
48 "MAX(TIMESTAMP '2008-01-01 00:00:01')");
49 REQUIRE(CHECK_COLUMN(result, 0, {1}));
50 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
51 REQUIRE(CHECK_COLUMN(result, 2, {33.3}));
52 REQUIRE(CHECK_COLUMN(result, 3, {"hello"}));
53 REQUIRE(CHECK_COLUMN(result, 4, {Value::BOOLEAN(true)}));
54 REQUIRE(CHECK_COLUMN(result, 5, {Value::DATE(1992, 2, 2)}));
55 REQUIRE(CHECK_COLUMN(result, 6, {Value::TIMESTAMP(2008, 1, 1, 0, 0, 1, 0)}));
56
57 REQUIRE_FAIL(con.Query("SELECT MAX()"));
58 REQUIRE_FAIL(con.Query("SELECT MAX(1, 2)"));
59 // first
60 result = con.Query("SELECT FIRST(1), FIRST(NULL), FIRST(33.3), FIRST('hello'), FIRST(True), FIRST(DATE "
61 "'1992-02-02'), FIRST(TIMESTAMP '2008-01-01 00:00:01')");
62 REQUIRE(CHECK_COLUMN(result, 0, {1}));
63 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
64 REQUIRE(CHECK_COLUMN(result, 2, {33.3}));
65 REQUIRE(CHECK_COLUMN(result, 3, {"hello"}));
66 REQUIRE(CHECK_COLUMN(result, 4, {Value::BOOLEAN(true)}));
67 REQUIRE(CHECK_COLUMN(result, 5, {Value::DATE(1992, 2, 2)}));
68 REQUIRE(CHECK_COLUMN(result, 6, {Value::TIMESTAMP(2008, 1, 1, 0, 0, 1, 0)}));
69
70 REQUIRE_FAIL(con.Query("SELECT FIRST()"));
71 REQUIRE_FAIL(con.Query("SELECT FIRST(1, 2)"));
72
73 // avg
74 result = con.Query("SELECT AVG(1), AVG(NULL), AVG(33.3)");
75 REQUIRE(CHECK_COLUMN(result, 0, {1}));
76 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
77 REQUIRE(CHECK_COLUMN(result, 2, {33.3}));
78
79 REQUIRE_FAIL(con.Query("SELECT AVG(True)"));
80 REQUIRE_FAIL(con.Query("SELECT AVG('hello')"));
81 REQUIRE_FAIL(con.Query("SELECT AVG(DATE '1992-02-02')"));
82 REQUIRE_FAIL(con.Query("SELECT AVG()"));
83 REQUIRE_FAIL(con.Query("SELECT AVG(1, 2)"));
84
85 // string agg
86 result = con.Query("SELECT STRING_AGG('hello', ' '), STRING_AGG('hello', NULL), STRING_AGG(NULL, ' '), "
87 "STRING_AGG(NULL, NULL), STRING_AGG('', '')");
88 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
89 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
90 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
91 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
92 REQUIRE(CHECK_COLUMN(result, 4, {""}));
93
94 REQUIRE_FAIL(con.Query("SELECT STRING_AGG()"));
95 REQUIRE_FAIL(con.Query("SELECT STRING_AGG('hello')"));
96 REQUIRE_FAIL(con.Query("SELECT STRING_AGG(1, 2, 3)"));
97}
98
99TEST_CASE("Test aggregates with many different types", "[aggregate]") {
100 unique_ptr<QueryResult> result;
101 DuckDB db(nullptr);
102 Connection con(db);
103
104 // strings
105 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s STRING, g INTEGER)"));
106 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('hello', 0), ('world', 1), (NULL, 0), ('r', 1)"));
107
108 // simple aggregates only
109 result = con.Query("SELECT COUNT(*), COUNT(s), MIN(s), MAX(s) FROM strings");
110 REQUIRE(CHECK_COLUMN(result, 0, {4}));
111 REQUIRE(CHECK_COLUMN(result, 1, {3}));
112 REQUIRE(CHECK_COLUMN(result, 2, {"hello"}));
113 REQUIRE(CHECK_COLUMN(result, 3, {"world"}));
114 // simple aggr with only NULL values
115 result = con.Query("SELECT COUNT(*), COUNT(s), MIN(s), MAX(s) FROM strings WHERE s IS NULL");
116 REQUIRE(CHECK_COLUMN(result, 0, {1}));
117 REQUIRE(CHECK_COLUMN(result, 1, {0}));
118 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
119 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
120 // add string_agg
121 result = con.Query(
122 "SELECT STRING_AGG(s, ' '), STRING_AGG(s, ''), STRING_AGG('', ''), STRING_AGG('hello', ' ') FROM strings");
123 REQUIRE(CHECK_COLUMN(result, 0, {"hello world r"}));
124 REQUIRE(CHECK_COLUMN(result, 1, {"helloworldr"}));
125 REQUIRE(CHECK_COLUMN(result, 2, {""}));
126 REQUIRE(CHECK_COLUMN(result, 3, {"hello hello hello hello"}));
127
128 // more complex agg (groups)
129 result = con.Query(
130 "SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ') FROM strings GROUP BY g ORDER BY g");
131 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
132 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
133 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
134 REQUIRE(CHECK_COLUMN(result, 3, {"hello", "r"}));
135 REQUIRE(CHECK_COLUMN(result, 4, {"hello", "world"}));
136 REQUIRE(CHECK_COLUMN(result, 5, {"hello", "world r"}));
137 // empty group
138 result = con.Query("SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ') FROM strings WHERE s IS NULL "
139 "OR s <> 'hello' GROUP BY g ORDER BY g");
140 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
141 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
142 REQUIRE(CHECK_COLUMN(result, 2, {0, 2}));
143 REQUIRE(CHECK_COLUMN(result, 3, {Value(), "r"}));
144 REQUIRE(CHECK_COLUMN(result, 4, {Value(), "world"}));
145 REQUIRE(CHECK_COLUMN(result, 5, {Value(), "world r"}));
146
147 // unsupported aggregates
148 REQUIRE_FAIL(con.Query("SELECT SUM(s) FROM strings GROUP BY g ORDER BY g"));
149 REQUIRE_FAIL(con.Query("SELECT AVG(s) FROM strings GROUP BY g ORDER BY g"));
150
151 // booleans
152 REQUIRE_NO_FAIL(con.Query("CREATE TABLE booleans(b BOOLEAN, g INTEGER)"));
153 REQUIRE_NO_FAIL(con.Query("INSERT INTO booleans VALUES (false, 0), (true, 1), (NULL, 0), (false, 1)"));
154
155 // simple agg (no grouping)
156 result = con.Query("SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans");
157 REQUIRE(CHECK_COLUMN(result, 0, {4}));
158 REQUIRE(CHECK_COLUMN(result, 1, {3}));
159 REQUIRE(CHECK_COLUMN(result, 2, {false}));
160 REQUIRE(CHECK_COLUMN(result, 3, {true}));
161 // simple agg with only null values
162 result = con.Query("SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans WHERE b IS NULL");
163 REQUIRE(CHECK_COLUMN(result, 0, {1}));
164 REQUIRE(CHECK_COLUMN(result, 1, {0}));
165 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
166 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
167 // more complex agg (groups)
168 result = con.Query("SELECT g, COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans GROUP BY g ORDER BY g");
169 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
170 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
171 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
172 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
173 REQUIRE(CHECK_COLUMN(result, 4, {false, true}));
174 // more complex agg with empty groups
175 result = con.Query(
176 "SELECT g, COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans WHERE b IS NULL OR b=true GROUP BY g ORDER BY g");
177 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
178 REQUIRE(CHECK_COLUMN(result, 1, {1, 1}));
179 REQUIRE(CHECK_COLUMN(result, 2, {0, 1}));
180 REQUIRE(CHECK_COLUMN(result, 3, {Value(), true}));
181 REQUIRE(CHECK_COLUMN(result, 4, {Value(), true}));
182
183 // unsupported aggregates
184 REQUIRE_FAIL(con.Query("SELECT SUM(b) FROM booleans GROUP BY g ORDER BY g"));
185 REQUIRE_FAIL(con.Query("SELECT AVG(b) FROM booleans GROUP BY g ORDER BY g"));
186
187 // integers
188 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, g INTEGER)"));
189 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (12, 0), (22, 1), (NULL, 0), (14, 1)"));
190
191 // simple agg (no grouping)
192 result = con.Query("SELECT COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers");
193 REQUIRE(CHECK_COLUMN(result, 0, {4}));
194 REQUIRE(CHECK_COLUMN(result, 1, {3}));
195 REQUIRE(CHECK_COLUMN(result, 2, {12}));
196 REQUIRE(CHECK_COLUMN(result, 3, {22}));
197 REQUIRE(CHECK_COLUMN(result, 4, {48}));
198 // simple agg with only null values
199 result = con.Query("SELECT COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM INTEGERS WHERE i IS NULL");
200 REQUIRE(CHECK_COLUMN(result, 0, {1}));
201 REQUIRE(CHECK_COLUMN(result, 1, {0}));
202 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
203 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
204 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
205 // more complex agg (groups)
206 result = con.Query("SELECT g, COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers GROUP BY g ORDER BY g");
207 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
208 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
209 REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
210 REQUIRE(CHECK_COLUMN(result, 3, {12, 14}));
211 REQUIRE(CHECK_COLUMN(result, 4, {12, 22}));
212 REQUIRE(CHECK_COLUMN(result, 5, {12, 36}));
213 // more complex agg with empty groups
214 result = con.Query("SELECT g, COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers WHERE i IS NULL OR i > 15 "
215 "GROUP BY g ORDER BY g");
216 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
217 REQUIRE(CHECK_COLUMN(result, 1, {1, 1}));
218 REQUIRE(CHECK_COLUMN(result, 2, {0, 1}));
219 REQUIRE(CHECK_COLUMN(result, 3, {Value(), 22}));
220 REQUIRE(CHECK_COLUMN(result, 4, {Value(), 22}));
221 REQUIRE(CHECK_COLUMN(result, 5, {Value(), 22}));
222}
223