1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Most scalar window functions", "[window]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // test scalar window functions
14 result = con.Query("SELECT row_number() OVER ()");
15 REQUIRE(CHECK_COLUMN(result, 0, {1}));
16
17 result = con.Query("SELECT avg(42) OVER ()");
18 REQUIRE(CHECK_COLUMN(result, 0, {42}));
19
20 // nested window functions are not allowed
21 REQUIRE_FAIL(con.Query("SELECT avg(row_number() over ()) over ()"));
22 REQUIRE_FAIL(con.Query("SELECT avg(42) over (partition by row_number() over ())"));
23 REQUIRE_FAIL(con.Query("SELECT avg(42) over (order by row_number() over ())"));
24 // distinct aggregates not supported for window functions
25 REQUIRE_FAIL(con.Query("SELECT COUNT(DISTINCT 42) OVER ()"));
26 REQUIRE_FAIL(
27 con.Query("WITH t AS (SELECT col0 AS a, col1 AS b FROM (VALUES(1,2),(1,1),(1,2),(2,1),(2,1),(2,2),(2,3),(2,4)) "
28 "v) SELECT *, COUNT(b) OVER(PARTITION BY a), COUNT(DISTINCT b) OVER(PARTITION BY a) FROM t;"));
29}
30
31TEST_CASE("Most basic window function", "[window]") {
32 unique_ptr<QueryResult> result;
33 DuckDB db(nullptr);
34 Connection con(db);
35 con.EnableQueryVerification();
36
37 REQUIRE_NO_FAIL(con.Query("CREATE TABLE empsalary (depname varchar, empno bigint, salary int, enroll_date date)"));
38 REQUIRE_NO_FAIL(
39 con.Query("INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'), "
40 "('personnel', 5, 3500, '2007-12-10'), ('sales', 4, 4800, '2007-08-08'), ('personnel', 2, 3900, "
41 "'2006-12-23'), ('develop', 7, 4200, '2008-01-01'), ('develop', 9, 4500, '2008-01-01'), ('sales', 3, "
42 "4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15')"));
43
44 // basic example from postgres' window.sql
45 result = con.Query("SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname ORDER BY empno) FROM "
46 "empsalary ORDER BY depname, empno");
47 REQUIRE(CHECK_COLUMN(
48 result, 0,
49 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
50 REQUIRE(CHECK_COLUMN(result, 1, {7, 8, 9, 10, 11, 2, 5, 1, 3, 4}));
51 REQUIRE(CHECK_COLUMN(result, 2, {4200, 6000, 4500, 5200, 5200, 3900, 3500, 5000, 4800, 4800}));
52 REQUIRE(CHECK_COLUMN(result, 3, {4200, 10200, 14700, 19900, 25100, 3900, 7400, 5000, 9800, 14600}));
53
54 // sum
55 result = con.Query(
56 "SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary) ss FROM empsalary ORDER BY depname, ss");
57 REQUIRE(result->types.size() == 1);
58 REQUIRE(CHECK_COLUMN(result, 0, {4200, 8700, 19100, 19100, 25100, 3500, 7400, 9600, 9600, 14600}));
59
60 // row_number
61 result = con.Query(
62 "SELECT row_number() OVER (PARTITION BY depname ORDER BY salary) rn FROM empsalary ORDER BY depname, rn");
63 REQUIRE(result->types.size() == 1);
64 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 1, 2, 1, 2, 3}));
65
66 // first_value
67 result = con.Query("SELECT empno, first_value(empno) OVER (PARTITION BY depname ORDER BY empno) fv FROM empsalary "
68 "ORDER BY depname, fv");
69 REQUIRE(result->types.size() == 2);
70 REQUIRE(CHECK_COLUMN(result, 0, {11, 8, 7, 9, 10, 5, 2, 4, 3, 1}));
71 REQUIRE(CHECK_COLUMN(result, 1, {7, 7, 7, 7, 7, 2, 2, 1, 1, 1}));
72
73 // rank_dense
74 result = con.Query("SELECT depname, salary, dense_rank() OVER (PARTITION BY depname ORDER BY salary) FROM "
75 "empsalary order by depname, salary");
76 REQUIRE(result->types.size() == 3);
77 REQUIRE(CHECK_COLUMN(
78 result, 0,
79 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
80 REQUIRE(CHECK_COLUMN(result, 1, {4200, 4500, 5200, 5200, 6000, 3500, 3900, 4800, 4800, 5000}));
81 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 3, 4, 1, 2, 1, 1, 2}));
82
83 // rank
84 result = con.Query("SELECT depname, salary, rank() OVER (PARTITION BY depname ORDER BY salary) FROM "
85 "empsalary order by depname, salary");
86 REQUIRE(result->types.size() == 3);
87 REQUIRE(CHECK_COLUMN(
88 result, 0,
89 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
90 REQUIRE(CHECK_COLUMN(result, 1, {4200, 4500, 5200, 5200, 6000, 3500, 3900, 4800, 4800, 5000}));
91 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 3, 5, 1, 2, 1, 1, 3}));
92
93 // min/max/avg
94 result = con.Query("SELECT depname, min(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m1, max(salary) "
95 "OVER (PARTITION BY depname ORDER BY salary, empno) m2, AVG(salary) OVER (PARTITION BY depname "
96 "ORDER BY salary, empno) m3 FROM empsalary ORDER BY depname, empno");
97 REQUIRE(result->types.size() == 4);
98 REQUIRE(CHECK_COLUMN(
99 result, 0,
100 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
101 REQUIRE(CHECK_COLUMN(result, 1, {4200, 4200, 4200, 4200, 4200, 3500, 3500, 4800, 4800, 4800}));
102 REQUIRE(CHECK_COLUMN(result, 2, {4200, 6000, 4500, 5200, 5200, 3900, 3500, 5000, 4800, 4800}));
103 REQUIRE(CHECK_COLUMN(
104 result, 3,
105 {4200.0, 5020.0, 4350.0, 4633.33333333333, 4775.0, 3700.0, 3500.0, 4866.66666666667, 4800.0, 4800.0}));
106
107 // stddev_pop
108 result = con.Query("SELECT depname, "
109 "STDDEV_POP(salary) OVER (PARTITION BY depname ORDER BY salary, empno) s "
110 "FROM empsalary ORDER BY depname, empno");
111 REQUIRE(result->types.size() == 2);
112 REQUIRE(CHECK_COLUMN(
113 result, 0,
114 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
115 REQUIRE(CHECK_COLUMN(result, 1, {0.0, 627.375486, 150.0, 418.993503, 438.035387, 200, 0.0, 94.280904, 0.0, 0.0}));
116
117 // covar_pop
118 result = con.Query("SELECT depname, "
119 "COVAR_POP(salary, empno) OVER (PARTITION BY depname ORDER BY salary, empno) c "
120 "FROM empsalary ORDER BY depname, empno");
121 REQUIRE(result->types.size() == 2);
122 REQUIRE(CHECK_COLUMN(
123 result, 0,
124 {"develop", "develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales", "sales"}));
125 REQUIRE(CHECK_COLUMN(result, 1, {0.0, 240.0, 150.0, 477.777778, 606.250000, -300.0, 0.0, -111.111111, 0.0, 0.0}));
126}
127
128TEST_CASE("Illegal window function", "[window]") {
129 unique_ptr<QueryResult> result;
130 DuckDB db(nullptr);
131 Connection con(db);
132 con.EnableQueryVerification();
133
134 REQUIRE_NO_FAIL(con.Query("CREATE TABLE empsalary (depname varchar, empno bigint, salary int, enroll_date date)"));
135 REQUIRE_NO_FAIL(
136 con.Query("INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'), "
137 "('personnel', 5, 3500, '2007-12-10'), ('sales', 4, 4800, '2007-08-08'), ('personnel', 2, 3900, "
138 "'2006-12-23'), ('develop', 7, 4200, '2008-01-01'), ('develop', 9, 4500, '2008-01-01'), ('sales', 3, "
139 "4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15')"));
140
141 // GROUP BY window function is not allowed
142 REQUIRE_FAIL(con.Query("SELECT depname, min(salary) OVER (PARTITION BY depname ORDER BY salary, empno) m1 FROM "
143 "empsalary GROUP BY m1 ORDER BY depname, empno"));
144}
145
146TEST_CASE("More evil cases", "[window]") {
147 unique_ptr<QueryResult> result;
148 DuckDB db(nullptr);
149 Connection con(db);
150 con.EnableQueryVerification();
151
152 REQUIRE_NO_FAIL(con.Query("CREATE TABLE empsalary (depname varchar, empno bigint, salary int, enroll_date date)"));
153 REQUIRE_NO_FAIL(
154 con.Query("INSERT INTO empsalary VALUES ('develop', 10, 5200, '2007-08-01'), ('sales', 1, 5000, '2006-10-01'), "
155 "('personnel', 5, 3500, '2007-12-10'), ('sales', 4, 4800, '2007-08-08'), ('personnel', 2, 3900, "
156 "'2006-12-23'), ('develop', 7, 4200, '2008-01-01'), ('develop', 9, 4500, '2008-01-01'), ('sales', 3, "
157 "4800, '2007-08-01'), ('develop', 8, 6000, '2006-10-01'), ('develop', 11, 5200, '2007-08-15')"));
158
159 // aggr as input to window
160 result = con.Query("SELECT depname, sum(sum(salary)) over (partition by depname order by salary) FROM empsalary "
161 "group by depname, salary order by depname, salary");
162 REQUIRE(result->types.size() == 2);
163 REQUIRE(CHECK_COLUMN(result, 0,
164 {"develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales"}));
165 REQUIRE(CHECK_COLUMN(result, 1, {4200, 8700, 19100, 25100, 3500, 7400, 9600, 14600}));
166
167 // expr in window
168 result = con.Query("SELECT empno, sum(salary*2) OVER (PARTITION BY depname ORDER BY empno) FROM "
169 "empsalary ORDER BY depname, empno");
170 REQUIRE(result->types.size() == 2);
171 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9, 10, 11, 2, 5, 1, 3, 4}));
172 REQUIRE(CHECK_COLUMN(result, 1, {8400, 20400, 29400, 39800, 50200, 7800, 14800, 10000, 19600, 29200}));
173
174 // expr ontop of window
175 result = con.Query("SELECT empno, 2*sum(salary) OVER (PARTITION BY depname ORDER BY empno) FROM "
176 "empsalary ORDER BY depname, empno");
177 REQUIRE(result->types.size() == 2);
178 REQUIRE(CHECK_COLUMN(result, 0, {7, 8, 9, 10, 11, 2, 5, 1, 3, 4}));
179 REQUIRE(CHECK_COLUMN(result, 1, {8400, 20400, 29400, 39800, 50200, 7800, 14800, 10000, 19600, 29200}));
180
181 // tpcds-derived window
182 result =
183 con.Query("SELECT depname, sum(salary)*100.0000/sum(sum(salary)) OVER (PARTITION BY depname ORDER BY salary) "
184 "AS revenueratio FROM empsalary GROUP BY depname, salary ORDER BY depname, revenueratio");
185 REQUIRE(result->types.size() == 2);
186 REQUIRE(CHECK_COLUMN(result, 0,
187 {"develop", "develop", "develop", "develop", "personnel", "personnel", "sales", "sales"}));
188 REQUIRE(CHECK_COLUMN(result, 1,
189 {23.904382, 51.724138, 54.450262, 100.000000, 52.702703, 100.000000, 34.246575, 100.000000}));
190}
191
192TEST_CASE("Wisconsin-derived window test cases", "[window]") {
193 unique_ptr<QueryResult> result;
194 DuckDB db(nullptr);
195 Connection con(db);
196 con.EnableQueryVerification();
197
198 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tenk1 (unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty "
199 "int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd "
200 "int4, even int4, stringu1 varchar, stringu2 varchar, string4 varchar)"));
201 REQUIRE_NO_FAIL(
202 con.Query("insert into tenk1 values (8800,0,0,0,0,0,0,800,800,3800,8800,0,1,'MAAAAA','AAAAAA','AAAAxx'), "
203 "(1891,1,1,3,1,11,91,891,1891,1891,1891,182,183,'TUAAAA','BAAAAA','HHHHxx'), "
204 "(3420,2,0,0,0,0,20,420,1420,3420,3420,40,41,'OBAAAA','CAAAAA','OOOOxx'), "
205 "(9850,3,0,2,0,10,50,850,1850,4850,9850,100,101,'WOAAAA','DAAAAA','VVVVxx'), "
206 "(7164,4,0,0,4,4,64,164,1164,2164,7164,128,129,'OPAAAA','EAAAAA','AAAAxx'), "
207 "(8009,5,1,1,9,9,9,9,9,3009,8009,18,19,'BWAAAA','FAAAAA','HHHHxx'), "
208 "(5057,6,1,1,7,17,57,57,1057,57,5057,114,115,'NMAAAA','GAAAAA','OOOOxx'), "
209 "(6701,7,1,1,1,1,1,701,701,1701,6701,2,3,'TXAAAA','HAAAAA','VVVVxx'), "
210 "(4321,8,1,1,1,1,21,321,321,4321,4321,42,43,'FKAAAA','IAAAAA','AAAAxx'), "
211 "(3043,9,1,3,3,3,43,43,1043,3043,3043,86,87,'BNAAAA','JAAAAA','HHHHxx')"));
212
213 result = con.Query("SELECT COUNT(*) OVER () FROM tenk1");
214 REQUIRE(result->types.size() == 1);
215
216 // FIXME REQUIRE(CHECK_COLUMN(result, 0, {10, 10, 10, 10, 10, 10, 10, 10, 10, 10}));
217
218 result = con.Query("SELECT sum(four) OVER (PARTITION BY ten ORDER BY unique2) AS sum_1, ten, four FROM tenk1 WHERE "
219 "unique2 < 10 order by ten, unique2");
220
221 REQUIRE(result->types.size() == 3);
222 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 2, 3, 4, 5, 3, 0, 1, 1}));
223 REQUIRE(CHECK_COLUMN(result, 1, {0, 0, 0, 1, 1, 1, 3, 4, 7, 9}));
224 REQUIRE(CHECK_COLUMN(result, 2, {0, 0, 2, 3, 1, 1, 3, 0, 1, 1}));
225
226 result = con.Query("SELECT row_number() OVER (ORDER BY unique2) rn FROM tenk1 WHERE unique2 < 10 ORDER BY rn");
227 REQUIRE(result->types.size() == 1);
228 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}));
229
230 result = con.Query("SELECT rank() OVER (PARTITION BY four ORDER BY ten) AS rank_1, ten, four FROM tenk1 WHERE "
231 "unique2 < 10 ORDER BY four, ten");
232 REQUIRE(result->types.size() == 3);
233 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 3, 1, 1, 3, 4, 1, 1, 2}));
234 REQUIRE(CHECK_COLUMN(result, 1, {0, 0, 4, 1, 1, 7, 9, 0, 1, 3}));
235 REQUIRE(CHECK_COLUMN(result, 2, {0, 0, 0, 1, 1, 1, 1, 2, 3, 3}));
236
237 result = con.Query("SELECT dense_rank() OVER (PARTITION BY four ORDER BY ten) FROM tenk1 WHERE unique2 "
238 "< 10 ORDER BY four, ten");
239 REQUIRE(result->types.size() == 1);
240 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 1, 1, 2, 3, 1, 1, 2}));
241
242 result = con.Query("SELECT first_value(ten) OVER (PARTITION BY four ORDER BY ten) FROM tenk1 WHERE unique2 < 10 "
243 "order by four, ten");
244 REQUIRE(result->types.size() == 1);
245 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 1, 1, 1, 1, 0, 1, 1}));
246
247 // percent_rank
248 result = con.Query("SELECT cast(percent_rank() OVER (PARTITION BY four ORDER BY ten)*10 as INTEGER) FROM tenk1 "
249 "ORDER BY four, ten");
250 REQUIRE(result->types.size() == 1);
251 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 10, 0, 0, 6, 10, 0, 0, 10}));
252
253 // cume_dist
254 result = con.Query("SELECT cast(cume_dist() OVER (PARTITION BY four ORDER BY ten)*10 as integer) FROM tenk1 WHERE "
255 "unique2 < 10 order by four, ten");
256 REQUIRE(result->types.size() == 1);
257 REQUIRE(CHECK_COLUMN(result, 0, {6, 6, 10, 5, 5, 7, 10, 10, 5, 10}));
258
259 // ntile
260 result = con.Query("SELECT ntile(2) OVER (ORDER BY ten, four) nn FROM tenk1 ORDER BY ten, four, nn");
261 REQUIRE(result->types.size() == 1);
262 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 1, 2, 2, 2, 2, 2}));
263
264 result = con.Query("SELECT ntile(3) OVER (ORDER BY ten, four) nn FROM tenk1 ORDER BY ten, four, nn");
265 REQUIRE(result->types.size() == 1);
266 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 2, 2, 2, 3, 3, 3}));
267
268 result = con.Query("SELECT ntile(4) OVER (ORDER BY ten, four) nn FROM tenk1 ORDER BY ten, four, nn");
269 REQUIRE(result->types.size() == 1);
270 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 2, 2, 2, 3, 3, 4, 4}));
271
272 result = con.Query("SELECT ntile(5) OVER (ORDER BY ten, four) nn FROM tenk1 ORDER BY ten, four, nn");
273 REQUIRE(result->types.size() == 1);
274 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2, 3, 3, 4, 4, 5, 5}));
275
276 // lead/lag
277 result = con.Query("SELECT lag(ten) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
278 REQUIRE(result->types.size() == 1);
279 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 0, Value(), 1, 1, 7, Value(), Value(), 1}));
280
281 result = con.Query("SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
282 REQUIRE(result->types.size() == 1);
283 REQUIRE(CHECK_COLUMN(result, 0, {0, 4, Value(), 1, 7, 9, Value(), Value(), 3, Value()}));
284
285 result =
286 con.Query("SELECT lag(ten, four) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
287 REQUIRE(result->types.size() == 1);
288 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 4, Value(), 1, 1, 7, Value(), Value(), Value()}));
289
290 result = con.Query(
291 "SELECT lag(ten, four, 0) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
292 REQUIRE(result->types.size() == 1);
293 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 4, 0, 1, 1, 7, 0, 0, 0}));
294
295 result = con.Query("SELECT lead(ten) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
296 REQUIRE(result->types.size() == 1);
297 REQUIRE(CHECK_COLUMN(result, 0, {0, 4, Value(), 1, 7, 9, Value(), Value(), 3, Value()}));
298
299 result =
300 con.Query("SELECT lead(ten * 2, 1) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
301 REQUIRE(result->types.size() == 1);
302 REQUIRE(CHECK_COLUMN(result, 0, {0, 8, Value(), 2, 14, 18, Value(), Value(), 6, Value()}));
303
304 result = con.Query(
305 "SELECT lead(ten * 2, 1, -1) OVER (PARTITION BY four ORDER BY ten) lt FROM tenk1 order by four, ten, lt");
306 REQUIRE(result->types.size() == 1);
307 REQUIRE(CHECK_COLUMN(result, 0, {0, 8, -1, 2, 14, 18, -1, -1, 6, -1}));
308
309 // empty OVER clause
310 result =
311 con.Query("SELECT COUNT(*) OVER w c, SUM(four) OVER w s, cast(AVG(ten) OVER w * 10 as integer) a, RANK() over "
312 "w r, DENSE_RANK() over w dr, ROW_NUMBER() OVER w rn FROM tenk1 WINDOW w AS () ORDER BY rn");
313 REQUIRE(result->types.size() == 6);
314 REQUIRE(CHECK_COLUMN(result, 0, {10, 10, 10, 10, 10, 10, 10, 10, 10, 10}));
315 REQUIRE(CHECK_COLUMN(result, 1, {12, 12, 12, 12, 12, 12, 12, 12, 12, 12}));
316 REQUIRE(CHECK_COLUMN(result, 2, {26, 26, 26, 26, 26, 26, 26, 26, 26, 26}));
317 REQUIRE(CHECK_COLUMN(result, 3, {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
318 REQUIRE(CHECK_COLUMN(result, 4, {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
319 REQUIRE(CHECK_COLUMN(result, 5, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}));
320
321 // no ordering but still a frame spec (somewhat underdefined)
322 result =
323 con.Query("SELECT COUNT(*) OVER w c, SUM(four) OVER w s, cast(AVG(ten) OVER w * 10 as integer) a, RANK() over "
324 "w r, DENSE_RANK() over w dr, ROW_NUMBER() OVER w rn FROM tenk1 WINDOW w AS (rows between 1 "
325 "preceding and 1 following) ORDER BY rn");
326 REQUIRE(result->types.size() == 6);
327 REQUIRE(CHECK_COLUMN(result, 0, {2, 3, 3, 3, 3, 3, 3, 3, 3, 2}));
328 REQUIRE(CHECK_COLUMN(result, 1, {3, 3, 5, 2, 3, 2, 3, 3, 5, 4}));
329 REQUIRE(CHECK_COLUMN(result, 2, {5, 3, 3, 13, 43, 66, 56, 30, 16, 20}));
330 REQUIRE(CHECK_COLUMN(result, 3, {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
331 REQUIRE(CHECK_COLUMN(result, 4, {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}));
332 REQUIRE(CHECK_COLUMN(result, 5, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}));
333}
334
335TEST_CASE("Non-default window specs", "[window]") {
336 unique_ptr<QueryResult> result;
337 DuckDB db(nullptr);
338 Connection con(db);
339 con.EnableQueryVerification();
340
341 REQUIRE_NO_FAIL(con.Query("create table tenk1d(ten int4, four int4)"));
342 REQUIRE_NO_FAIL(con.Query("insert into tenk1d values (0,0), (1,1), (3,3), (2,2), (4,2), (9,1), (4,0), (7,3), "
343 "(0,2), (2,0), (5,1), (1,3), (3,1), (6,0), (8,0), (9,3), (8,2), (6,2), (7,1), (5,3)"));
344
345 // BASIC
346 result = con.Query("SELECT four, ten, sum(ten) over (partition by four order by ten) st, last_value(ten) over "
347 "(partition by four order by ten) lt FROM tenk1d ORDER BY four, ten");
348 REQUIRE(result->types.size() == 4);
349 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3}));
350 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 4, 6, 8, 1, 3, 5, 7, 9, 0, 2, 4, 6, 8, 1, 3, 5, 7, 9}));
351 REQUIRE(CHECK_COLUMN(result, 2, {0, 2, 6, 12, 20, 1, 4, 9, 16, 25, 0, 2, 6, 12, 20, 1, 4, 9, 16, 25}));
352 REQUIRE(CHECK_COLUMN(result, 3, {0, 2, 4, 6, 8, 1, 3, 5, 7, 9, 0, 2, 4, 6, 8, 1, 3, 5, 7, 9}));
353
354 // same but with explicit window def
355 result = con.Query("SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded "
356 "preceding and current row) st, last_value(ten) over (partition by four order by ten range "
357 "between unbounded preceding and current row) lt FROM tenk1d order by four, ten");
358 REQUIRE(result->types.size() == 4);
359 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3}));
360 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 4, 6, 8, 1, 3, 5, 7, 9, 0, 2, 4, 6, 8, 1, 3, 5, 7, 9}));
361 REQUIRE(CHECK_COLUMN(result, 2, {0, 2, 6, 12, 20, 1, 4, 9, 16, 25, 0, 2, 6, 12, 20, 1, 4, 9, 16, 25}));
362 REQUIRE(CHECK_COLUMN(result, 3, {0, 2, 4, 6, 8, 1, 3, 5, 7, 9, 0, 2, 4, 6, 8, 1, 3, 5, 7, 9}));
363
364 // unbounded following
365 result = con.Query("SELECT four, ten, sum(ten) over (partition by four order by ten range between unbounded "
366 "preceding and unbounded following) st, last_value(ten) over (partition by four order by ten "
367 "range between unbounded preceding and unbounded following) lt FROM tenk1d order by four, ten");
368 REQUIRE(result->types.size() == 4);
369 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3}));
370 REQUIRE(CHECK_COLUMN(result, 1, {0, 2, 4, 6, 8, 1, 3, 5, 7, 9, 0, 2, 4, 6, 8, 1, 3, 5, 7, 9}));
371 REQUIRE(CHECK_COLUMN(result, 2, {20, 20, 20, 20, 20, 25, 25, 25, 25, 25, 20, 20, 20, 20, 20, 25, 25, 25, 25, 25}));
372 REQUIRE(CHECK_COLUMN(result, 3, {8, 8, 8, 8, 8, 9, 9, 9, 9, 9, 8, 8, 8, 8, 8, 9, 9, 9, 9, 9}));
373
374 // unbounded following with expressions
375 result = con.Query("SELECT four, ten/4 as two, sum(ten/4) over (partition by four order by ten/4 range between "
376 "unbounded preceding and current row) st, last_value(ten/4) over (partition by four order by "
377 "ten/4 range between unbounded preceding and current row) lt FROM tenk1d order by four, ten/4");
378 REQUIRE(result->types.size() == 4);
379 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3}));
380 REQUIRE(CHECK_COLUMN(result, 1, {0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2}));
381 REQUIRE(CHECK_COLUMN(result, 2, {0, 0, 2, 2, 4, 0, 0, 2, 2, 4, 0, 0, 2, 2, 4, 0, 0, 2, 2, 4}));
382 REQUIRE(CHECK_COLUMN(result, 3, {0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2}));
383
384 // unbounded following with named windows
385 result = con.Query(
386 "SELECT four, ten/4 as two, sum(ten/4) OVER w st, last_value(ten/4) OVER w lt FROM tenk1d WINDOW w AS "
387 "(partition by four order by ten/4 range between unbounded preceding and current row) order by four, ten/4 ");
388 REQUIRE(result->types.size() == 4);
389 REQUIRE(CHECK_COLUMN(result, 0, {0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3}));
390 REQUIRE(CHECK_COLUMN(result, 1, {0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2}));
391 REQUIRE(CHECK_COLUMN(result, 2, {0, 0, 2, 2, 4, 0, 0, 2, 2, 4, 0, 0, 2, 2, 4, 0, 0, 2, 2, 4}));
392 REQUIRE(CHECK_COLUMN(result, 3, {0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2, 0, 0, 1, 1, 2}));
393}
394
395TEST_CASE("Expressions in boundaries", "[window]") {
396 unique_ptr<QueryResult> result;
397 DuckDB db(nullptr);
398 Connection con(db);
399 con.EnableQueryVerification();
400
401 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tenk1 ( unique1 int4, unique2 int4, two int4, four int4, ten int4, twenty "
402 "int4, hundred int4, thousand int4, twothousand int4, fivethous int4, tenthous int4, odd "
403 "int4, even int4, stringu1 string, stringu2 string, string4 string )"));
404 REQUIRE_NO_FAIL(
405 con.Query("insert into tenk1 values (4, 1621, 0, 0, 4, 4, 4, 4, 4, 4, 4, 8 ,9 ,'EAAAAA', 'JKCAAA', 'HHHHxx'), "
406 "(2, 2716, 0, 2, 2, 2, 2, 2, 2, 2, 2, 4 ,5 ,'CAAAAA', 'MAEAAA', 'AAAAxx'), (1, 2838, 1, 1, 1, 1, 1, "
407 "1, 1, 1, 1, 2 ,3 ,'BAAAAA', 'EFEAAA', 'OOOOxx'), (6, 2855, 0, 2, 6, 6, 6, 6, 6, 6, 6, 12 ,13 "
408 ",'GAAAAA', 'VFEAAA', 'VVVVxx'), (9, 4463, 1, 1, 9, 9, 9, 9, 9, 9, 9, 18 ,19 ,'JAAAAA', 'RPGAAA', "
409 "'VVVVxx'),(8, 5435, 0, 0, 8, 8, 8, 8, 8, 8, 8, 16 ,17 ,'IAAAAA', 'BBIAAA', 'VVVVxx'), (5, 5557, 1, "
410 "1, 5, 5, 5, 5, 5, 5, 5, 10 ,11,'FAAAAA', 'TFIAAA', 'HHHHxx'), (3, 5679, 1, 3, 3, 3, 3, 3, 3, 3, 3, "
411 "6 ,7 ,'DAAAAA', 'LKIAAA', 'VVVVxx'), (7, 8518, 1,3, 7, 7, 7, 7, 7, 7, 7, 14 ,15 ,'HAAAAA', "
412 "'QPMAAA', 'OOOOxx'), (0, 9998, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ,1 ,'AAAAAA','OUOAAA', 'OOOOxx')"));
413
414 result = con.Query("SELECT sum(unique1) over (order by unique1 rows between 2 preceding and 2 following) su FROM "
415 "tenk1 order by unique1");
416 REQUIRE(CHECK_COLUMN(result, 0, {3, 6, 10, 15, 20, 25, 30, 35, 30, 24}));
417 REQUIRE(result->types.size() == 1);
418
419 result = con.Query("SELECT sum(unique1) over (order by unique1 rows between 2 preceding and 1 preceding) su FROM "
420 "tenk1 order by unique1");
421 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 1, 3, 5, 7, 9, 11, 13, 15}));
422 REQUIRE(result->types.size() == 1);
423
424 result = con.Query("SELECT sum(unique1) over (order by unique1 rows between 1 following and 3 following) su FROM "
425 "tenk1 order by unique1");
426 REQUIRE(CHECK_COLUMN(result, 0, {6, 9, 12, 15, 18, 21, 24, 17, 9, Value()}));
427 REQUIRE(result->types.size() == 1);
428
429 result = con.Query("SELECT sum(unique1) over (order by unique1 rows between unbounded preceding and 1 following) "
430 "su FROM tenk1 order by unique1");
431 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 6, 10, 15, 21, 28, 36, 45, 45}));
432 REQUIRE(result->types.size() == 1);
433}
434
435TEST_CASE("TPC-DS inspired micro benchmarks", "[window]") {
436 unique_ptr<QueryResult> result;
437 DuckDB db(nullptr);
438 Connection con(db);
439 con.EnableQueryVerification();
440
441 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
442 REQUIRE_NO_FAIL(con.Query("CREATE TABLE item(i_category VARCHAR, i_brand VARCHAR, i_price INTEGER)"));
443 REQUIRE_NO_FAIL(con.Query("INSERT INTO item VALUES ('toys', 'fisher-price', 100)"));
444 result =
445 con.Query("SELECT i_category, i_brand, avg(sum(i_price)) OVER (PARTITION BY i_category), rank() OVER "
446 "(PARTITION BY i_category ORDER BY i_category, i_brand) rn FROM item GROUP BY i_category, i_brand;");
447 REQUIRE(CHECK_COLUMN(result, 0, {"toys"}));
448 REQUIRE(CHECK_COLUMN(result, 1, {"fisher-price"}));
449 REQUIRE(CHECK_COLUMN(result, 2, {100}));
450 REQUIRE(CHECK_COLUMN(result, 3, {1}));
451 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
452
453 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
454 REQUIRE_NO_FAIL(con.Query("CREATE TABLE item(i_category VARCHAR, i_brand VARCHAR, i_item_sk INTEGER);"));
455 REQUIRE_NO_FAIL(con.Query("CREATE TABLE store(s_store_name VARCHAR, s_company_name VARCHAR, s_store_sk INTEGER);"));
456 REQUIRE_NO_FAIL(con.Query("CREATE TABLE date_dim(d_year INTEGER, d_moy INTEGER, d_date_sk INTEGER);"));
457 REQUIRE_NO_FAIL(con.Query("CREATE TABLE store_sales(ss_sales_price DECIMAL, ss_item_sk INTEGER, ss_sold_date_sk "
458 "INTEGER, ss_store_sk INTEGER);"));
459 REQUIRE_NO_FAIL(con.Query("INSERT INTO item VALUES ('Music', 'exportischolar', 1);"));
460 REQUIRE_NO_FAIL(con.Query("INSERT INTO store VALUES ('ought', 'Unknown', 1);"));
461 REQUIRE_NO_FAIL(con.Query("INSERT INTO date_dim VALUES (1999, 1, 1);"));
462 REQUIRE_NO_FAIL(con.Query("INSERT INTO store_sales VALUES (2.8, 1, 1, 1);"));
463 result = con.Query(
464 "SELECT i_category, i_brand, s_store_name, s_company_name, d_year, d_moy, sum(ss_sales_price) sum_sales, "
465 "avg(sum(ss_sales_price)) OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name, d_year) "
466 "avg_monthly_sales, rank() OVER (PARTITION BY i_category, i_brand, s_store_name, s_company_name ORDER BY "
467 "d_year, d_moy) rn FROM item, store_sales, date_dim, store WHERE ss_item_sk = i_item_sk AND ss_sold_date_sk = "
468 "d_date_sk AND ss_store_sk = s_store_sk AND (d_year = 1999 OR (d_year = 1999-1 AND d_moy =12) OR (d_year = "
469 "1999+1 AND d_moy =1)) GROUP BY i_category, i_brand, s_store_name, s_company_name, d_year, d_moy;");
470 REQUIRE(CHECK_COLUMN(result, 0, {"Music"}));
471 REQUIRE(CHECK_COLUMN(result, 1, {"exportischolar"}));
472 REQUIRE(CHECK_COLUMN(result, 2, {"ought"}));
473 REQUIRE(CHECK_COLUMN(result, 3, {"Unknown"}));
474 REQUIRE(CHECK_COLUMN(result, 4, {1999}));
475 REQUIRE(CHECK_COLUMN(result, 5, {1}));
476 REQUIRE(CHECK_COLUMN(result, 6, {2.8}));
477 REQUIRE(CHECK_COLUMN(result, 7, {2.8}));
478 REQUIRE(CHECK_COLUMN(result, 8, {1}));
479 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
480}
481
482TEST_CASE("TPC-DS Q49 bug fix for multi-sort window functions", "[window]") {
483 unique_ptr<QueryResult> result;
484 DuckDB db(nullptr);
485 Connection con(db);
486 con.EnableQueryVerification();
487
488 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
489 REQUIRE_NO_FAIL(con.Query("create table wintest( item integer, return_ratio numeric, currency_ratio numeric)"));
490 REQUIRE_NO_FAIL(con.Query(
491 "insert into wintest values (7539 ,0.590000 , 0.590000), (3337 ,0.626506 , 0.626506), (15597 ,0.661972 , "
492 "0.661972), (2915 ,0.698630 , 0.698630), (11933 ,0.717172 , 0.717172), (483 ,0.800000 , 0.800000), (85 "
493 ",0.857143 , 0.857143), (97 ,0.903614 , 0.903614), (117 ,0.925000 , 0.925000), (5299 ,0.927083 , "
494 "0.927083), (10055 ,0.945652 , 0.945652), (4231 ,0.977778 , 0.977778), (5647 ,0.987805 , 0.987805), (8679 "
495 ",0.988764 , 0.988764), (10323 ,0.977778 , 1.111111), (3305 ,0.737500 , 1.293860)"));
496
497 result = con.Query("SELECT item, rank() OVER (ORDER BY return_ratio) AS return_rank, rank() OVER (ORDER BY "
498 "currency_ratio) AS currency_rank FROM wintest order by item");
499 REQUIRE(CHECK_COLUMN(
500 result, 0, {85, 97, 117, 483, 2915, 3305, 3337, 4231, 5299, 5647, 7539, 8679, 10055, 10323, 11933, 15597}));
501 REQUIRE(CHECK_COLUMN(result, 1, {8, 9, 10, 7, 4, 6, 2, 13, 11, 15, 1, 16, 12, 13, 5, 3}));
502 REQUIRE(CHECK_COLUMN(result, 2, {7, 8, 9, 6, 4, 16, 2, 12, 10, 13, 1, 14, 11, 15, 5, 3}));
503
504 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
505}
506
507TEST_CASE("Ensure dbplyr crash with ORDER BY under window stays fixed", "[window]") {
508 unique_ptr<QueryResult> result;
509 DuckDB db(nullptr);
510 Connection con(db);
511 con.EnableQueryVerification();
512
513 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dbplyr_052 (x INTEGER, g DOUBLE, w int)"));
514 REQUIRE_NO_FAIL(
515 con.Query("INSERT INTO dbplyr_052 VALUES (1,1, 42),(2,1, 42),(3,1, 42),(2,2, 42),(3,2, 42),(4,2, 42)"));
516
517 // this works fine because we order by the already-projected column in the innermost query
518 result =
519 con.Query("SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS "
520 "zzz67 FROM (SELECT x, g FROM dbplyr_052 ORDER BY x) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)");
521 REQUIRE(CHECK_COLUMN(result, 0, {3, 3, 4}));
522 REQUIRE(CHECK_COLUMN(result, 1, {1.0, 2.0, 2.0}));
523
524 // this breaks because we add a fake projection that is not pruned
525 result =
526 con.Query("SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS "
527 "zzz67 FROM (SELECT x, g FROM dbplyr_052 ORDER BY w) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)");
528 REQUIRE(CHECK_COLUMN(result, 0, {3, 3, 4}));
529 REQUIRE(CHECK_COLUMN(result, 1, {1.0, 2.0, 2.0}));
530
531 // this also breaks because we add a fake projection that is not pruned even if we already have that projection,
532 // just with a different table name
533 result =
534 con.Query("SELECT x, g FROM (SELECT x, g, SUM(x) OVER (PARTITION BY g ORDER BY x ROWS UNBOUNDED PRECEDING) AS "
535 "zzz67 FROM (SELECT * FROM dbplyr_052 ORDER BY x) dbplyr_053) dbplyr_054 WHERE (zzz67 > 3.0)");
536 REQUIRE(CHECK_COLUMN(result, 0, {3, 3, 4}));
537 REQUIRE(CHECK_COLUMN(result, 1, {1.0, 2.0, 2.0}));
538}
539
540TEST_CASE("Test errors in binding window functions", "[window]") {
541 unique_ptr<QueryResult> result;
542 DuckDB db(nullptr);
543 Connection con(db);
544 con.EnableQueryVerification();
545
546 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
547
548 // we use columns here that are not part of the table
549 REQUIRE_FAIL(con.Query("SELECT MIN(a) OVER (PARTITION BY i ORDER BY i) FROM integers"));
550 REQUIRE_FAIL(con.Query("SELECT MIN(i) OVER (PARTITION BY a ORDER BY i) FROM integers"));
551 REQUIRE_FAIL(con.Query("SELECT MIN(i) OVER (PARTITION BY i ORDER BY a) FROM integers"));
552 REQUIRE_FAIL(con.Query("SELECT MIN(i) OVER (PARTITION BY i, a ORDER BY i) FROM integers"));
553 REQUIRE_FAIL(con.Query("SELECT MIN(i) OVER (PARTITION BY i ORDER BY i, a) FROM integers"));
554
555 // now we only use the "proper" columns
556 result = con.Query("SELECT MIN(i) OVER (PARTITION BY i ORDER BY i) FROM integers");
557 REQUIRE(CHECK_COLUMN(result, 0, {}));
558}
559
560TEST_CASE("Test binding of named window functions in CTEs", "[window]") {
561 unique_ptr<QueryResult> result;
562 DuckDB db(nullptr);
563 Connection con(db);
564 con.EnableQueryVerification();
565
566 // named window clause
567 result = con.Query(
568 "select i, lag(i) over named_window from (values (1), (2), (3)) as t (i) window named_window as (order by i);");
569 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
570 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2}));
571
572 // named window clause in CTE
573 result = con.Query("with subquery as (select i, lag(i) over named_window from (values (1), (2), (3)) as t (i) "
574 "window named_window as (order by i)) select * from subquery;");
575 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
576 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2}));
577
578 // named window clause in subquery
579 result = con.Query("select * from (select i, lag(i) over named_window from (values (1), (2), (3)) as t (i) window "
580 "named_window as (order by i)) t1;");
581 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
582 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2}));
583
584 // named window clause in view
585 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS select i, lag(i) over named_window from (values (1), (2), (3)) as t "
586 "(i) window named_window as (order by i);"));
587
588 result = con.Query("select * from v1;");
589 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
590 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 1, 2}));
591
592 // same window clause name multiple times but in different subqueries
593 result = con.Query("SELECT * FROM (SELECT i, lag(i) OVER named_window FROM ( VALUES (1), (2), (3)) AS t (i) window "
594 "named_window AS ( ORDER BY i)) t1, (SELECT i, lag(i) OVER named_window FROM ( VALUES (1), (2), "
595 "(3)) AS t (i) window named_window AS ( ORDER BY i)) t2 ORDER BY 1, 2, 3, 4;");
596 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 2, 2, 2, 3, 3, 3}));
597 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 1, 1, 1, 2, 2, 2}));
598 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 1, 2, 3, 1, 2, 3}));
599 REQUIRE(CHECK_COLUMN(result, 3, {Value(), 1, 2, Value(), 1, 2, Value(), 1, 2}));
600
601 // we cannot use named window specifications of the main query inside CTEs
602 REQUIRE_FAIL(con.Query("WITH subquery AS (SELECT i, lag(i) OVER named_window FROM ( VALUES (1), (2), (3)) AS t "
603 "(i)) SELECT * FROM subquery window named_window AS ( ORDER BY i);"));
604 // duplicate window clause name
605 REQUIRE_FAIL(con.Query("select i, lag(i) over named_window from (values (1), (2), (3)) as t (i) window "
606 "named_window as (order by i), named_window as (order by j);"));
607}
608