1#include "catch.hpp"
2#include "test_helpers.hpp"
3#include "duckdb/main/appender.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test BIT_AND operator", "[aggregate]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db);
12
13 // test on scalar values
14 result = con.Query("SELECT BIT_AND(3), BIT_AND(NULL)");
15 REQUIRE(CHECK_COLUMN(result, 0, {3}));
16 REQUIRE(CHECK_COLUMN(result, 1, {-1}));
17
18 // test on a sequence
19 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
20 result = con.Query("SELECT BIT_AND(nextval('seq'))");
21 REQUIRE(CHECK_COLUMN(result, 0, {1}));
22 result = con.Query("SELECT BIT_AND(nextval('seq'))");
23 REQUIRE(CHECK_COLUMN(result, 0, {2}));
24
25 // test on a set of integers
26 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
27 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)"));
28 result = con.Query("SELECT BIT_AND(i), BIT_AND(1), BIT_AND(DISTINCT i), BIT_AND(NULL) FROM integers");
29 REQUIRE(CHECK_COLUMN(result, 0, {3}));
30 REQUIRE(CHECK_COLUMN(result, 1, {1}));
31 REQUIRE(CHECK_COLUMN(result, 2, {3}));
32 REQUIRE(CHECK_COLUMN(result, 3, {-1}));
33
34 // test on an empty set
35 result = con.Query("SELECT BIT_AND(i) FROM integers WHERE i > 100");
36 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
37
38 // test incorrect usage
39 REQUIRE_FAIL(con.Query("SELECT BIT_AND()"));
40 REQUIRE_FAIL(con.Query("SELECT BIT_AND(1, 2, 3)"));
41 REQUIRE_FAIL(con.Query("SELECT BIT_AND(BIT_AND(1))"));
42}
43
44TEST_CASE("Test BIT_OR operator", "[aggregate]") {
45 unique_ptr<QueryResult> result;
46 DuckDB db(nullptr);
47 Connection con(db);
48
49 // test on scalar values
50 result = con.Query("SELECT BIT_OR(3), BIT_OR(NULL)");
51 REQUIRE(CHECK_COLUMN(result, 0, {3}));
52 REQUIRE(CHECK_COLUMN(result, 1, {0}));
53
54 // test on a sequence
55 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
56 result = con.Query("SELECT BIT_OR(nextval('seq'))");
57 REQUIRE(CHECK_COLUMN(result, 0, {1}));
58 result = con.Query("SELECT BIT_OR(nextval('seq'))");
59 REQUIRE(CHECK_COLUMN(result, 0, {2}));
60
61 // test on a set of integers
62 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
63 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)"));
64 result = con.Query("SELECT BIT_OR(i), BIT_OR(1), BIT_OR(DISTINCT i), BIT_OR(NULL) FROM integers");
65 REQUIRE(CHECK_COLUMN(result, 0, {31}));
66 REQUIRE(CHECK_COLUMN(result, 1, {1}));
67 REQUIRE(CHECK_COLUMN(result, 2, {31}));
68 REQUIRE(CHECK_COLUMN(result, 3, {0}));
69
70 // test on an empty set
71 result = con.Query("SELECT BIT_OR(i) FROM integers WHERE i > 100");
72 REQUIRE(CHECK_COLUMN(result, 0, {0}));
73
74 // test incorrect usage
75 REQUIRE_FAIL(con.Query("SELECT BIT_OR()"));
76 REQUIRE_FAIL(con.Query("SELECT BIT_OR(1, 2, 3)"));
77 REQUIRE_FAIL(con.Query("SELECT BIT_OR(BIT_AND(1))"));
78}
79
80TEST_CASE("Test BIT_XOR operator", "[aggregate]") {
81 unique_ptr<QueryResult> result;
82 DuckDB db(nullptr);
83 Connection con(db);
84
85 // test on scalar values
86 result = con.Query("SELECT BIT_XOR(3), BIT_XOR(NULL)");
87 REQUIRE(CHECK_COLUMN(result, 0, {3}));
88 REQUIRE(CHECK_COLUMN(result, 1, {0}));
89
90 // test on a sequence
91 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
92 result = con.Query("SELECT BIT_XOR(nextval('seq'))");
93 REQUIRE(CHECK_COLUMN(result, 0, {1}));
94 result = con.Query("SELECT BIT_XOR(nextval('seq'))");
95 REQUIRE(CHECK_COLUMN(result, 0, {2}));
96
97 // test on a set of integers
98 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
99 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)"));
100 result = con.Query("SELECT BIT_XOR(i), BIT_XOR(1), BIT_XOR(DISTINCT i), BIT_XOR(NULL) FROM integers");
101 REQUIRE(CHECK_COLUMN(result, 0, {24}));
102 REQUIRE(CHECK_COLUMN(result, 1, {0}));
103 REQUIRE(CHECK_COLUMN(result, 2, {20}));
104 REQUIRE(CHECK_COLUMN(result, 3, {0}));
105
106 // test on an empty set
107 result = con.Query("SELECT BIT_XOR(i) FROM integers WHERE i > 100");
108 REQUIRE(CHECK_COLUMN(result, 0, {0}));
109
110 // test incorrect usage
111 REQUIRE_FAIL(con.Query("SELECT BIT_XOR()"));
112 REQUIRE_FAIL(con.Query("SELECT BIT_XOR(1, 2, 3)"));
113 REQUIRE_FAIL(con.Query("SELECT BIT_XOR(BIT_XOR(1))"));
114}
115
116TEST_CASE("Test COUNT operator", "[aggregate]") {
117 unique_ptr<QueryResult> result;
118 DuckDB db(nullptr);
119 Connection con(db);
120
121 // test counts on scalar values
122 result = con.Query("SELECT COUNT(*), COUNT(1), COUNT(100), COUNT(NULL), COUNT(DISTINCT 1)");
123 REQUIRE(CHECK_COLUMN(result, 0, {1}));
124 REQUIRE(CHECK_COLUMN(result, 1, {1}));
125 REQUIRE(CHECK_COLUMN(result, 2, {1}));
126 REQUIRE(CHECK_COLUMN(result, 3, {0}));
127 REQUIRE(CHECK_COLUMN(result, 4, {1}));
128
129 // test counts on a set of values
130 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
131 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (NULL)"));
132 result = con.Query("SELECT COUNT(*), COUNT(1), COUNT(i), COUNT(COALESCE(i, 1)), COUNT(DISTINCT i), COUNT(DISTINCT "
133 "1) FROM integers");
134 REQUIRE(CHECK_COLUMN(result, 0, {3}));
135 REQUIRE(CHECK_COLUMN(result, 1, {3}));
136 REQUIRE(CHECK_COLUMN(result, 2, {2}));
137 REQUIRE(CHECK_COLUMN(result, 3, {3}));
138 REQUIRE(CHECK_COLUMN(result, 4, {2}));
139 REQUIRE(CHECK_COLUMN(result, 5, {1}));
140
141 // ORDERED aggregates are not supported
142 REQUIRE_FAIL(con.Query("SELECT COUNT(1 ORDER BY 1)"));
143 // FILTER clause not supported
144 REQUIRE_FAIL(con.Query("SELECT COUNT(1) FILTER (WHERE false)"));
145 // cannot do DISTINCT *
146 REQUIRE_FAIL(con.Query("SELECT COUNT(DISTINCT *) FROM integers"));
147}
148
149TEST_CASE("Test aggregates with scalar inputs", "[aggregate]") {
150 unique_ptr<QueryResult> result;
151 DuckDB db(nullptr);
152 Connection con(db);
153
154 // test aggregate on scalar values
155 result = con.Query("SELECT COUNT(1), MIN(1), FIRST(1), MAX(1), SUM(1), STRING_AGG('hello', ',')");
156 REQUIRE(CHECK_COLUMN(result, 0, {1}));
157 REQUIRE(CHECK_COLUMN(result, 1, {1}));
158 REQUIRE(CHECK_COLUMN(result, 2, {1}));
159 REQUIRE(CHECK_COLUMN(result, 3, {1}));
160 REQUIRE(CHECK_COLUMN(result, 4, {1}));
161 REQUIRE(CHECK_COLUMN(result, 5, {"hello"}));
162
163 // test aggregate on scalar NULLs
164 result = con.Query("SELECT COUNT(NULL), MIN(NULL), FIRST(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL)");
165 REQUIRE(CHECK_COLUMN(result, 0, {0}));
166 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
167 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
168 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
169 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
170 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
171
172 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
173 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (NULL)"));
174
175 // test aggregates on a set of values with scalar inputs
176 result = con.Query("SELECT COUNT(1), MIN(1), FIRST(1), MAX(1), SUM(1), STRING_AGG('hello', ',') FROM integers");
177 REQUIRE(CHECK_COLUMN(result, 0, {3}));
178 REQUIRE(CHECK_COLUMN(result, 1, {1}));
179 REQUIRE(CHECK_COLUMN(result, 2, {1}));
180 REQUIRE(CHECK_COLUMN(result, 3, {1}));
181 REQUIRE(CHECK_COLUMN(result, 4, {3}));
182 REQUIRE(CHECK_COLUMN(result, 5, {"hello,hello,hello"}));
183
184 // test aggregates on a set of values with scalar NULL values as inputs
185 result = con.Query(
186 "SELECT COUNT(NULL), MIN(NULL), FIRST(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL) FROM integers");
187 REQUIRE(CHECK_COLUMN(result, 0, {0}));
188 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
189 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
190 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
191 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
192 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
193}
194
195TEST_CASE("Test COVAR operators", "[aggregate]") {
196 unique_ptr<QueryResult> result;
197 DuckDB db(nullptr);
198 Connection con(db);
199
200 // test incorrect usage of COVAR_POP function
201 REQUIRE_FAIL(con.Query("SELECT COVAR_POP()"));
202 REQUIRE_FAIL(con.Query("SELECT COVAR_POP(1, 2, 3)"));
203 REQUIRE_FAIL(con.Query("SELECT COVAR_POP(COVAR_POP(1))"));
204
205 // test incorrect usage of COVAR_SAMP function
206 REQUIRE_FAIL(con.Query("SELECT COVAR_SAMP()"));
207 REQUIRE_FAIL(con.Query("SELECT COVAR_SAMP(1, 2, 3)"));
208 REQUIRE_FAIL(con.Query("SELECT COVAR_SAMP(COVAR_SAMP(1))"));
209
210 // test population covariance on scalar values
211 result = con.Query("SELECT COVAR_POP(3,3), COVAR_POP(NULL,3), COVAR_POP(3,NULL), COVAR_POP(NULL,NULL)");
212 REQUIRE(CHECK_COLUMN(result, 0, {0}));
213 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
214 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
215 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
216
217 // test sample covariance on scalar values
218 result = con.Query("SELECT COVAR_SAMP(3,3), COVAR_SAMP(NULL,3), COVAR_SAMP(3,NULL), COVAR_SAMP(NULL,NULL)");
219 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
220 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
221 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
222 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
223
224 // test population covariance on a sequence
225 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seqx;"));
226 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seqy;"));
227 result = con.Query("SELECT COVAR_POP(nextval('seqx'),nextval('seqy'))");
228 REQUIRE(CHECK_COLUMN(result, 0, {0}));
229 result = con.Query("SELECT COVAR_POP(nextval('seqx'),nextval('seqy'))");
230 REQUIRE(CHECK_COLUMN(result, 0, {0}));
231
232 // test population covariance on a set of values
233 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(x INTEGER, y INTEGER);"));
234 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (10,NULL), (10,11), (20,22), (25,NULL), (30,35)"));
235
236 result = con.Query(
237 "SELECT COVAR_POP(x,y), COVAR_POP(x,1), COVAR_POP(1,y), COVAR_POP(x,NULL), COVAR_POP(NULL,y) FROM integers");
238 REQUIRE(CHECK_COLUMN(result, 0, {80.0}));
239 REQUIRE(CHECK_COLUMN(result, 1, {0.0}));
240 REQUIRE(CHECK_COLUMN(result, 2, {0.0}));
241 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
242 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
243
244 result = con.Query("SELECT COVAR_SAMP(x,y), COVAR_SAMP(x,1), COVAR_SAMP(1,y), COVAR_SAMP(x,NULL), "
245 "COVAR_SAMP(NULL,y) FROM integers");
246 REQUIRE(CHECK_COLUMN(result, 0, {120.0}));
247 REQUIRE(CHECK_COLUMN(result, 1, {0.0}));
248 REQUIRE(CHECK_COLUMN(result, 2, {0.0}));
249 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
250 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
251
252 // test covar on empty set
253 result = con.Query("SELECT COVAR_POP(x,y), COVAR_SAMP(x,y) FROM integers WHERE x > 100");
254 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
255 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
256
257 // test covar with only null inputs
258 result = con.Query("SELECT COVAR_POP(NULL, NULL), COVAR_SAMP(NULL, NULL) FROM integers");
259 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
260 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
261}
262
263TEST_CASE("Test STRING_AGG operator", "[aggregate]") {
264 unique_ptr<QueryResult> result;
265 DuckDB db(nullptr);
266 Connection con(db);
267
268 // test incorrect usage of STRING_AGG function
269 REQUIRE_FAIL(con.Query("SELECT STRING_AGG()"));
270 REQUIRE_FAIL(con.Query("SELECT STRING_AGG(1)"));
271 REQUIRE_FAIL(con.Query("SELECT STRING_AGG(1, 2, 3)"));
272 REQUIRE_FAIL(con.Query("SELECT STRING_AGG(STRING_AGG('a',','))"));
273
274 // test string aggregation on scalar values
275 result = con.Query("SELECT STRING_AGG('a',',')");
276 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
277
278 // test string aggregation on scalar values
279 result = con.Query("SELECT STRING_AGG('a',','), STRING_AGG(NULL,','), STRING_AGG('a',NULL), STRING_AGG(NULL,NULL)");
280 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
281 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
282 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
283 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
284
285 // test string aggregation on a set of values
286 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(g INTEGER, x VARCHAR, y VARCHAR);"));
287 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES (1,'a','/'), (1,'b','-'), "
288 "(2,'i','/'), (2,NULL,'-'), (2,'j','+'), "
289 "(3,'p','/'), "
290 "(4,'x','/'), (4,'y','-'), (4,'z','+')"));
291
292 result = con.Query("SELECT STRING_AGG(x,','), STRING_AGG(x,y) FROM strings");
293 REQUIRE(CHECK_COLUMN(result, 0, {"a,b,i,j,p,x,y,z"}));
294 REQUIRE(CHECK_COLUMN(result, 1, {"a-b/i+j/p/x-y+z"}));
295
296 result = con.Query("SELECT g, STRING_AGG(x,','), STRING_AGG(x,y) FROM strings GROUP BY g ORDER BY g");
297 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4}));
298 REQUIRE(CHECK_COLUMN(result, 1, {"a,b", "i,j", "p", "x,y,z"}));
299 REQUIRE(CHECK_COLUMN(result, 2, {"a-b", "i+j", "p", "x-y+z"}));
300
301 // test agg on empty set
302 result = con.Query("SELECT STRING_AGG(x,','), STRING_AGG(x,y) FROM strings WHERE g > 100");
303 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
304 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
305
306 // numerics are auto cast to strings
307 result = con.Query("SELECT STRING_AGG(1, 2)");
308 REQUIRE(CHECK_COLUMN(result, 0, {"1"}));
309}
310
311TEST_CASE("Test distinct STRING_AGG operator", "[aggregate]") {
312 unique_ptr<QueryResult> result;
313 DuckDB db(nullptr);
314 Connection con(db);
315
316 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(s VARCHAR);"));
317 REQUIRE_NO_FAIL(con.Query("INSERT INTO strings VALUES ('a'), ('b'), ('a');"));
318
319 result = con.Query("SELECT STRING_AGG(s,','), STRING_AGG(DISTINCT s, ',') FROM strings");
320 REQUIRE(CHECK_COLUMN(result, 0, {"a,b,a"}));
321 REQUIRE(CHECK_COLUMN(result, 1, {"a,b"}));
322}
323
324TEST_CASE("Test STRING_AGG operator with many groups", "[aggregate][.]") {
325 unique_ptr<QueryResult> result;
326 DuckDB db(nullptr);
327 Connection con(db);
328
329 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
330 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(g INTEGER, x VARCHAR);"));
331 vector<Value> expected_g, expected_h;
332 string expected_large_value;
333 Appender appender(con, "strings");
334 for (idx_t i = 0; i < 10000; i++) {
335 appender.AppendRow((int)i, "hello");
336 expected_g.push_back(Value::INTEGER(i));
337 expected_h.push_back(Value("hello"));
338 expected_large_value += (i > 0 ? "," : "") + string("hello");
339 }
340 appender.Close();
341 REQUIRE_NO_FAIL(con.Query("COMMIT;"));
342
343 // many small groups
344 result = con.Query("SELECT g, STRING_AGG(x, ',') FROM strings GROUP BY g ORDER BY g");
345 REQUIRE(CHECK_COLUMN(result, 0, expected_g));
346 REQUIRE(CHECK_COLUMN(result, 1, expected_h));
347
348 // one begin group
349 result = con.Query("SELECT 1, STRING_AGG(x, ',') FROM strings GROUP BY 1 ORDER BY 1");
350 REQUIRE(CHECK_COLUMN(result, 0, {1}));
351 REQUIRE(CHECK_COLUMN(result, 1, {Value(expected_large_value)}));
352
353 // now test exception in the middle of an aggregate
354 REQUIRE_FAIL(con.Query("SELECT STRING_AGG(k, ','), SUM(CAST(k AS BIGINT)) FROM (SELECT CAST(g AS VARCHAR) FROM "
355 "strings UNION ALL SELECT CAST(x AS VARCHAR) FROM strings) tbl1(k)"));
356}
357
358TEST_CASE("STRING_AGG big", "[aggregate]") {
359 unique_ptr<QueryResult> result;
360 DuckDB db(nullptr);
361 Connection con(db);
362
363 // test string aggregation on a set of values
364 REQUIRE_NO_FAIL(con.Query("CREATE TABLE strings(g VARCHAR, x VARCHAR);"));
365
366 std::stringstream query_string;
367 query_string << "INSERT INTO strings VALUES ";
368 for (int c = 0; c < 100; ++c) {
369 for (int e = 0; e < 100; ++e) {
370 query_string << "(";
371
372 query_string << c;
373
374 query_string << ",";
375
376 query_string << "'";
377 query_string << c * 10 + e;
378 query_string << "'";
379
380 query_string << "),";
381 }
382 }
383 std::string query_string_str = query_string.str();
384 query_string_str.pop_back();
385
386 REQUIRE_NO_FAIL(con.Query(query_string_str));
387
388 result = con.Query("SELECT g, STRING_AGG(x,',') FROM strings GROUP BY g");
389 REQUIRE_NO_FAIL(std::move(result));
390}
391
392TEST_CASE("Test AVG operator", "[aggregate]") {
393 unique_ptr<QueryResult> result;
394 DuckDB db(nullptr);
395 Connection con(db);
396
397 // test average on a scalar value
398 result = con.Query("SELECT AVG(3), AVG(NULL)");
399 REQUIRE(CHECK_COLUMN(result, 0, {3}));
400 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
401
402 // test average on a sequence
403 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
404 result = con.Query("SELECT AVG(nextval('seq'))");
405 REQUIRE(CHECK_COLUMN(result, 0, {1}));
406 result = con.Query("SELECT AVG(nextval('seq'))");
407 REQUIRE(CHECK_COLUMN(result, 0, {2}));
408
409 // test average on a set of values
410 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
411 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
412 result = con.Query("SELECT AVG(i), AVG(1), AVG(DISTINCT i), AVG(NULL) FROM integers");
413 REQUIRE(CHECK_COLUMN(result, 0, {2}));
414 REQUIRE(CHECK_COLUMN(result, 1, {1}));
415 REQUIRE(CHECK_COLUMN(result, 2, {2}));
416 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
417
418 // test average on empty set
419 result = con.Query("SELECT AVG(i) FROM integers WHERE i > 100");
420 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
421
422 // test incorrect usage of AVG function
423 REQUIRE_FAIL(con.Query("SELECT AVG()"));
424 REQUIRE_FAIL(con.Query("SELECT AVG(1, 2, 3)"));
425 REQUIRE_FAIL(con.Query("SELECT AVG(AVG(1))"));
426}
427
428TEST_CASE("Test implicit aggregate operators", "[aggregate]") {
429 unique_ptr<QueryResult> result;
430 DuckDB db(nullptr);
431 Connection con(db);
432
433 // test implicit aggregates on empty set
434 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER);"));
435 result = con.Query("SELECT COUNT(*), COUNT(i), STDDEV_SAMP(i), SUM(i), SUM(DISTINCT i), FIRST(i), MAX(i), MIN(i) "
436 "FROM integers WHERE i > 100");
437 REQUIRE(CHECK_COLUMN(result, 0, {0}));
438 REQUIRE(CHECK_COLUMN(result, 1, {0}));
439 REQUIRE(CHECK_COLUMN(result, 2, {Value()}));
440 REQUIRE(CHECK_COLUMN(result, 3, {Value()}));
441 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
442 REQUIRE(CHECK_COLUMN(result, 5, {Value()}));
443 REQUIRE(CHECK_COLUMN(result, 6, {Value()}));
444 REQUIRE(CHECK_COLUMN(result, 7, {Value()}));
445}
446
447TEST_CASE("Test built in aggregate operator usage", "[aggregate]") {
448 unique_ptr<QueryResult> result;
449 DuckDB db(nullptr);
450 Connection con(db);
451
452 // test incorrect usage of the COUNT aggregate
453 REQUIRE_FAIL(con.Query("SELECT COUNT(1, 2, 3)"));
454 REQUIRE_FAIL(con.Query("SELECT COUNT(COUNT(1))"));
455
456 // test incorrect usage of STDDEV_SAMP aggregate
457 REQUIRE_FAIL(con.Query("SELECT STDDEV_SAMP()"));
458 REQUIRE_FAIL(con.Query("SELECT STDDEV_SAMP(1, 2, 3)"));
459 REQUIRE_FAIL(con.Query("SELECT STDDEV_SAMP(STDDEV_SAMP(1))"));
460
461 // test incorrect usage of SUM aggregate
462 REQUIRE_FAIL(con.Query("SELECT SUM()"));
463 REQUIRE_FAIL(con.Query("SELECT SUM(1, 2, 3)"));
464 REQUIRE_FAIL(con.Query("SELECT SUM(SUM(1))"));
465
466 // test incorrect usage of FIRST aggregate
467 REQUIRE_FAIL(con.Query("SELECT FIRST()"));
468 REQUIRE_FAIL(con.Query("SELECT FIRST(1, 2, 3)"));
469 REQUIRE_FAIL(con.Query("SELECT FIRST(FIRST(1))"));
470
471 // test incorrect usage of MAX aggregate
472 REQUIRE_FAIL(con.Query("SELECT MAX()"));
473 REQUIRE_FAIL(con.Query("SELECT MAX(1, 2, 3)"));
474 REQUIRE_FAIL(con.Query("SELECT MAX(MAX(1))"));
475
476 // test incorrect usage of MIN aggregate
477 REQUIRE_FAIL(con.Query("SELECT MIN()"));
478 REQUIRE_FAIL(con.Query("SELECT MIN(1, 2, 3)"));
479 REQUIRE_FAIL(con.Query("SELECT MIN(MIN(1))"));
480}
481
482TEST_CASE("Test GROUP BY on expression", "[aggregate]") {
483 unique_ptr<QueryResult> result;
484 DuckDB db(nullptr);
485 Connection con(db);
486
487 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integer(i INTEGER, j INTEGER);"));
488 REQUIRE_NO_FAIL(con.Query("INSERT INTO integer VALUES (3, 4), (3, 5), (3, 7);"));
489 // group by on expression
490 result = con.Query("SELECT j * 2 FROM integer GROUP BY j * 2 ORDER BY j * 2;");
491 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
492 // verify that adding or removing the table name does not impact the validity of the query
493 result = con.Query("SELECT integer.j * 2 FROM integer GROUP BY j * 2 ORDER BY j * 2;");
494 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
495 result = con.Query("SELECT j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY j * 2;");
496 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
497 result = con.Query("SELECT j * 2 FROM integer GROUP BY j * 2 ORDER BY integer.j * 2;");
498 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
499 result = con.Query("SELECT integer.j * 2 FROM integer GROUP BY j * 2 ORDER BY integer.j * 2;");
500 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
501 result = con.Query("SELECT j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY integer.j * 2;");
502 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
503 result = con.Query("SELECT integer.j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY j * 2;");
504 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
505 result = con.Query("SELECT integer.j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY integer.j * 2;");
506 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
507 result = con.Query("SELECT j * 2 AS i FROM integer GROUP BY j * 2 ORDER BY i;");
508 REQUIRE(CHECK_COLUMN(result, 0, {8, 10, 14}));
509}
510
511TEST_CASE("Test GROUP BY with many groups", "[aggregate][.]") {
512 unique_ptr<QueryResult> result;
513 DuckDB db(nullptr);
514 Connection con(db);
515
516 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER);"));
517 Appender appender(con, "integers");
518 for (idx_t i = 0; i < 10000; i++) {
519 appender.AppendRow((int)i, (int)1);
520 appender.AppendRow((int)i, (int)2);
521 }
522 appender.Close();
523 result = con.Query("SELECT SUM(i), SUM(sums) FROM (SELECT i, SUM(j) AS sums FROM integers GROUP BY i) tbl1");
524 REQUIRE(CHECK_COLUMN(result, 0, {49995000}));
525 REQUIRE(CHECK_COLUMN(result, 1, {30000}));
526}
527
528TEST_CASE("Test FIRST with non-inlined strings", "[aggregate]") {
529 unique_ptr<QueryResult> result;
530 DuckDB db(nullptr);
531 Connection con(db);
532
533 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl(a INTEGER, b VARCHAR)"));
534 REQUIRE_NO_FAIL(
535 con.Query("INSERT INTO tbl VALUES (1, NULL), (2, 'thisisalongstring'), (3, 'thisisalsoalongstring')"));
536
537 // non-grouped aggregate
538 result = con.Query("SELECT FIRST(b) FROM tbl WHERE a=2");
539 REQUIRE(CHECK_COLUMN(result, 0, {"thisisalongstring"}));
540 result = con.Query("SELECT FIRST(b) FROM tbl WHERE a=1");
541 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
542
543 // grouped aggregate
544 result = con.Query("SELECT a, FIRST(b) FROM tbl GROUP BY a ORDER BY a");
545 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
546 REQUIRE(CHECK_COLUMN(result, 1, {Value(), "thisisalongstring", "thisisalsoalongstring"}));
547}
548