3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix window1
18 ifcapable !windowfunc {
24 CREATE TABLE t1(a, b, c, d);
25 INSERT INTO t1 VALUES(1, 2, 3, 4);
26 INSERT INTO t1 VALUES(5, 6, 7, 8);
27 INSERT INTO t1 VALUES(9, 10, 11, 12);
31 SELECT sum(b) OVER () FROM t1
35 SELECT a, sum(b) OVER () FROM t1
39 SELECT a, 4 + sum(b) OVER () FROM t1
43 SELECT a + 4 + sum(b) OVER () FROM t1
47 SELECT a, sum(b) OVER (PARTITION BY c) FROM t1
51 1 "SELECT sum(b) OVER () FROM t1"
52 2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1"
53 3 "SELECT sum(b) OVER (ORDER BY c) FROM t1"
54 4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1"
55 5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1"
56 6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1"
57 7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1"
58 8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1"
59 9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING
60 AND CURRENT ROW) FROM t1"
61 10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING
62 AND UNBOUNDED FOLLOWING) FROM t1"
64 do_test 2.$tn { lindex [catchsql $sql] 0 } 0
68 1 "SELECT * FROM t1 WHERE sum(b) OVER ()"
69 2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()"
70 3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()"
72 do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}}
76 CREATE TABLE t2(a, b, c);
77 INSERT INTO t2 VALUES(0, 0, 0);
78 INSERT INTO t2 VALUES(1, 1, 1);
79 INSERT INTO t2 VALUES(2, 0, 2);
80 INSERT INTO t2 VALUES(3, 1, 0);
81 INSERT INTO t2 VALUES(4, 0, 1);
82 INSERT INTO t2 VALUES(5, 1, 2);
83 INSERT INTO t2 VALUES(6, 0, 0);
87 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2;
89 0 12 2 12 4 12 6 12 1 9 3 9 5 9
93 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a;
95 0 12 1 9 2 12 3 9 4 12 5 9 6 12
99 SELECT a, sum(a) OVER () FROM t2 ORDER BY a;
101 0 21 1 21 2 21 3 21 4 21 5 21 6 21
104 do_execsql_test 4.4 {
105 SELECT a, sum(a) OVER (ORDER BY a) FROM t2;
107 0 0 1 1 2 3 3 6 4 10 5 15 6 21
110 do_execsql_test 4.5 {
111 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a
113 0 0 1 1 2 2 3 4 4 6 5 9 6 12
116 do_execsql_test 4.6 {
117 SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a
119 0 0 1 1 2 2 3 3 4 5 5 7 6 9
122 do_execsql_test 4.7 {
123 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a
125 0 12 1 9 2 12 3 8 4 10 5 5 6 6
128 do_execsql_test 4.8 {
130 sum(a) OVER (PARTITION BY b ORDER BY a DESC),
131 sum(a) OVER (PARTITION BY c ORDER BY a)
143 do_execsql_test 4.9 {
145 sum(a) OVER (ORDER BY a),
146 avg(a) OVER (ORDER BY a)
158 do_execsql_test 4.10.1 {
160 count() OVER (ORDER BY a DESC),
161 group_concat(a, '.') OVER (ORDER BY a DESC)
162 FROM t2 ORDER BY a DESC
173 do_execsql_test 4.10.2 {
175 count(*) OVER (ORDER BY a DESC),
176 group_concat(a, '.') OVER (ORDER BY a DESC)
177 FROM t2 ORDER BY a DESC
188 do_catchsql_test 5.1 {
189 SELECT ntile(0) OVER (ORDER BY a) FROM t2;
190 } {1 {argument of ntile must be a positive integer}}
191 do_catchsql_test 5.2 {
192 SELECT ntile(-1) OVER (ORDER BY a) FROM t2;
193 } {1 {argument of ntile must be a positive integer}}
194 do_catchsql_test 5.3 {
195 SELECT ntile('zbc') OVER (ORDER BY a) FROM t2;
196 } {1 {argument of ntile must be a positive integer}}
197 do_execsql_test 5.4 {
198 CREATE TABLE t4(a, b);
199 SELECT ntile(1) OVER (ORDER BY a) FROM t4;
202 #-------------------------------------------------------------------------
204 do_execsql_test 6.1 {
206 INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1);
209 INSERT INTO t2 VALUES('b'), ('a');
211 SELECT x, count(*) OVER (ORDER BY x) FROM t1;
212 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7}
214 do_execsql_test 6.2 {
215 SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1);
217 b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
218 a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
221 do_catchsql_test 6.3 {
222 SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1
223 WINDOW w AS (ORDER BY x)
224 } {1 {FILTER clause may only be used with aggregate window functions}}
226 #-------------------------------------------------------------------------
227 # Attempt to use a window function as an aggregate. And other errors.
230 do_execsql_test 7.0 {
231 CREATE TABLE t1(x, y);
232 INSERT INTO t1 VALUES(1, 2);
233 INSERT INTO t1 VALUES(3, 4);
234 INSERT INTO t1 VALUES(5, 6);
235 INSERT INTO t1 VALUES(7, 8);
236 INSERT INTO t1 VALUES(9, 10);
239 do_catchsql_test 7.1.1 {
240 SELECT nth_value(x, 1) FROM t1;
241 } {1 {misuse of window function nth_value()}}
242 do_catchsql_test 7.1.2 {
243 SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y);
244 } {1 {misuse of window function nth_value()}}
245 do_catchsql_test 7.1.3 {
246 SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y);
247 } {1 {misuse of window function nth_value()}}
248 do_catchsql_test 7.1.4 {
249 SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y);
250 } {1 {misuse of window function nth_value()}}
251 do_catchsql_test 7.1.5 {
252 SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER ();
253 } {1 {no such column: x}}
254 do_catchsql_test 7.1.6 {
255 SELECT trim(x) OVER (ORDER BY y) FROM t1;
256 } {1 {trim() may not be used as a window function}}
257 do_catchsql_test 7.1.7 {
258 SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
259 } {1 {no such window: abc}}
261 do_execsql_test 7.2 {
265 lead(y, 3, 'default') OVER win
267 WINDOW win AS (ORDER BY x)
269 4 6 8 6 8 10 8 10 default 10 {} default {} {} default
272 do_execsql_test 7.3 {
273 SELECT row_number() OVER (ORDER BY x) FROM t1
276 do_execsql_test 7.4 {
278 row_number() OVER win,
281 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
282 } {1 3 2 5 3 7 4 9 5 {}}
284 #-------------------------------------------------------------------------
285 # Attempt to use a window function in a view.
287 do_execsql_test 8.0 {
288 CREATE TABLE t3(a, b, c);
290 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
291 INSERT INTO t3 SELECT i, i, i FROM s;
293 CREATE VIEW v1 AS SELECT
294 sum(b) OVER (ORDER BY c),
295 min(b) OVER (ORDER BY c),
296 max(b) OVER (ORDER BY c)
299 CREATE VIEW v2 AS SELECT
304 WINDOW win AS (ORDER BY c);
307 do_execsql_test 8.1.1 {
309 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
310 do_execsql_test 8.1.2 {
312 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
316 do_execsql_test 8.2.1 {
318 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
319 do_execsql_test 8.2.2 {
321 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
323 #-------------------------------------------------------------------------
324 # Attempt to use a window function in a trigger.
326 do_execsql_test 9.0 {
327 CREATE TABLE t4(x, y);
328 INSERT INTO t4 VALUES(1, 'g');
329 INSERT INTO t4 VALUES(2, 'i');
330 INSERT INTO t4 VALUES(3, 'l');
331 INSERT INTO t4 VALUES(4, 'g');
332 INSERT INTO t4 VALUES(5, 'a');
334 CREATE TABLE t5(x, y, m);
335 CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
338 SELECT x, y, max(y) OVER xyz FROM t4
339 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
343 do_execsql_test 9.1.1 {
344 SELECT x, y, max(y) OVER xyz FROM t4
345 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
346 } {1 g g 2 i i 3 l l 4 g i 5 a l}
348 do_execsql_test 9.1.2 {
349 INSERT INTO t4 VALUES(6, 'm');
350 SELECT x, y, max(y) OVER xyz FROM t4
351 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
352 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
354 do_execsql_test 9.1.3 {
355 SELECT * FROM t5 ORDER BY 1
356 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
358 do_execsql_test 9.2 {
359 WITH aaa(x, y, z) AS (
360 SELECT x, y, max(y) OVER xyz FROM t4
361 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x)
363 SELECT * FROM aaa ORDER BY 1;
364 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
366 do_execsql_test 9.3 {
367 WITH aaa(x, y, z) AS (
368 SELECT x, y, max(y) OVER xyz FROM t4
369 WINDOW xyz AS (ORDER BY x)
371 SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
372 } {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g}
374 #-------------------------------------------------------------------------
376 do_execsql_test 10.0 {
377 CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
378 INSERT INTO sales VALUES
379 ('Alice', 'North', 34),
380 ('Frank', 'South', 22),
381 ('Charles', 'North', 45),
382 ('Darrell', 'South', 8),
383 ('Grant', 'South', 23),
384 ('Brad' , 'North', 22),
385 ('Elizabeth', 'South', 99),
386 ('Horace', 'East', 1);
389 # Best two salespeople from each region
391 do_execsql_test 10.1 {
392 SELECT emp, region, total FROM (
395 row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
397 ) WHERE rank<=2 ORDER BY region, total DESC
406 do_execsql_test 10.2 {
407 SELECT emp, region, sum(total) OVER win FROM sales
408 WINDOW win AS (PARTITION BY region ORDER BY total)
420 do_execsql_test 10.3 {
421 SELECT emp, region, sum(total) OVER win FROM sales
422 WINDOW win AS (PARTITION BY region ORDER BY total)
432 do_execsql_test 10.4 {
433 SELECT emp, region, sum(total) OVER win FROM sales
434 WINDOW win AS (PARTITION BY region ORDER BY total)
444 do_execsql_test 10.5 {
445 SELECT emp, region, sum(total) OVER win FROM sales
447 PARTITION BY region ORDER BY total
448 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
461 do_execsql_test 10.6 {
462 SELECT emp, region, sum(total) OVER win FROM sales
464 PARTITION BY region ORDER BY total
465 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
475 do_execsql_test 10.7 {
476 SELECT emp, region, (
477 SELECT sum(total) OVER (
478 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
479 ) || outer.emp FROM sales
480 ) FROM sales AS outer;
484 Charles North 254Charles
485 Darrell South 254Darrell
488 Elizabeth South 254Elizabeth
489 Horace East 254Horace
492 do_execsql_test 10.8 {
493 SELECT emp, region, (
494 SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
495 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
497 ) FROM sales AS outer;
509 #-------------------------------------------------------------------------
510 # Check that it is not possible to use a window function in a CREATE INDEX
513 do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }
515 do_catchsql_test 11.1 {
516 CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
517 } {1 {misuse of window function sum()}}
518 do_catchsql_test 11.2 {
519 CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
520 } {1 {misuse of window function lead()}}
522 do_catchsql_test 11.3 {
523 CREATE INDEX t6i ON t6(sum(b) OVER ());
524 } {1 {misuse of window function sum()}}
525 do_catchsql_test 11.4 {
526 CREATE INDEX t6i ON t6(lead(b) OVER ());
527 } {1 {misuse of window function lead()}}