Debian Codegraph @

test/window1.test

raw
1 # 2018 May 8
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
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.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
12 #
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix window1
17
18 ifcapable !windowfunc {
19 finish_test
20 return
21 }
22
23 do_execsql_test 1.0 {
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);
28 }
29
30 do_execsql_test 1.1 {
31 SELECT sum(b) OVER () FROM t1
32 } {18 18 18}
33
34 do_execsql_test 1.2 {
35 SELECT a, sum(b) OVER () FROM t1
36 } {1 18 5 18 9 18}
37
38 do_execsql_test 1.3 {
39 SELECT a, 4 + sum(b) OVER () FROM t1
40 } {1 22 5 22 9 22}
41
42 do_execsql_test 1.4 {
43 SELECT a + 4 + sum(b) OVER () FROM t1
44 } {23 27 31}
45
46 do_execsql_test 1.5 {
47 SELECT a, sum(b) OVER (PARTITION BY c) FROM t1
48 } {1 2 5 6 9 10}
49
50 foreach {tn sql} {
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"
63 } {
64 do_test 2.$tn { lindex [catchsql $sql] 0 } 0
65 }
66
67 foreach {tn sql} {
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 ()"
71 } {
72 do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}}
73 }
74
75 do_execsql_test 4.0 {
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);
84 }
85
86 do_execsql_test 4.1 {
87 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2;
88 } {
89 0 12 2 12 4 12 6 12 1 9 3 9 5 9
90 }
91
92 do_execsql_test 4.2 {
93 SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a;
94 } {
95 0 12 1 9 2 12 3 9 4 12 5 9 6 12
96 }
97
98 do_execsql_test 4.3 {
99 SELECT a, sum(a) OVER () FROM t2 ORDER BY a;
100 } {
101 0 21 1 21 2 21 3 21 4 21 5 21 6 21
102 }
103
104 do_execsql_test 4.4 {
105 SELECT a, sum(a) OVER (ORDER BY a) FROM t2;
106 } {
107 0 0 1 1 2 3 3 6 4 10 5 15 6 21
108 }
109
110 do_execsql_test 4.5 {
111 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a
112 } {
113 0 0 1 1 2 2 3 4 4 6 5 9 6 12
114 }
115
116 do_execsql_test 4.6 {
117 SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a
118 } {
119 0 0 1 1 2 2 3 3 4 5 5 7 6 9
120 }
121
122 do_execsql_test 4.7 {
123 SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a
124 } {
125 0 12 1 9 2 12 3 8 4 10 5 5 6 6
126 }
127
128 do_execsql_test 4.8 {
129 SELECT a,
130 sum(a) OVER (PARTITION BY b ORDER BY a DESC),
131 sum(a) OVER (PARTITION BY c ORDER BY a)
132 FROM t2 ORDER BY a
133 } {
134 0 12 0
135 1 9 1
136 2 12 2
137 3 8 3
138 4 10 5
139 5 5 7
140 6 6 9
141 }
142
143 do_execsql_test 4.9 {
144 SELECT a,
145 sum(a) OVER (ORDER BY a),
146 avg(a) OVER (ORDER BY a)
147 FROM t2 ORDER BY a
148 } {
149 0 0 0.0
150 1 1 0.5
151 2 3 1.0
152 3 6 1.5
153 4 10 2.0
154 5 15 2.5
155 6 21 3.0
156 }
157
158 do_execsql_test 4.10.1 {
159 SELECT a,
160 count() OVER (ORDER BY a DESC),
161 string_agg(a, '.') OVER (ORDER BY a DESC)
162 FROM t2 ORDER BY a DESC
163 } {
164 6 1 6
165 5 2 6.5
166 4 3 6.5.4
167 3 4 6.5.4.3
168 2 5 6.5.4.3.2
169 1 6 6.5.4.3.2.1
170 0 7 6.5.4.3.2.1.0
171 }
172
173 do_execsql_test 4.10.2 {
174 SELECT a,
175 count(*) OVER (ORDER BY a DESC),
176 group_concat(a, '.') OVER (ORDER BY a DESC)
177 FROM t2 ORDER BY a DESC
178 } {
179 6 1 6
180 5 2 6.5
181 4 3 6.5.4
182 3 4 6.5.4.3
183 2 5 6.5.4.3.2
184 1 6 6.5.4.3.2.1
185 0 7 6.5.4.3.2.1.0
186 }
187
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;
200 } {}
201
202 #-------------------------------------------------------------------------
203 reset_db
204 do_execsql_test 6.1 {
205 CREATE TABLE t1(x);
206 INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1);
207
208 CREATE TABLE t2(x);
209 INSERT INTO t2 VALUES('b'), ('a');
210
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}
213
214 do_execsql_test 6.2 {
215 SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1)
216 ORDER BY 1, 2;
217 } {
218 a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
219 b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
220 }
221
222 do_catchsql_test 6.3 {
223 SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1
224 WINDOW w AS (ORDER BY x)
225 } {1 {FILTER clause may only be used with aggregate window functions}}
226
227 #-------------------------------------------------------------------------
228 # Attempt to use a window function as an aggregate. And other errors.
229 #
230 reset_db
231 do_execsql_test 7.0 {
232 CREATE TABLE t1(x, y);
233 INSERT INTO t1 VALUES(1, 2);
234 INSERT INTO t1 VALUES(3, 4);
235 INSERT INTO t1 VALUES(5, 6);
236 INSERT INTO t1 VALUES(7, 8);
237 INSERT INTO t1 VALUES(9, 10);
238 }
239
240 do_catchsql_test 7.1.1 {
241 SELECT nth_value(x, 1) FROM t1;
242 } {1 {misuse of window function nth_value()}}
243 do_catchsql_test 7.1.2 {
244 SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y);
245 } {1 {misuse of window function nth_value()}}
246 do_catchsql_test 7.1.3 {
247 SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y);
248 } {1 {misuse of window function nth_value()}}
249 do_catchsql_test 7.1.4 {
250 SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y);
251 } {1 {misuse of window function nth_value()}}
252 do_catchsql_test 7.1.5 {
253 SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER ();
254 } {1 {no such column: x}}
255 do_catchsql_test 7.1.6 {
256 SELECT trim(x) OVER (ORDER BY y) FROM t1;
257 } {1 {trim() may not be used as a window function}}
258 do_catchsql_test 7.1.7 {
259 SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
260 } {1 {no such window: abc}}
261 do_catchsql_test 7.1.8 {
262 SELECT row_number(x) OVER () FROM t1
263 } {1 {wrong number of arguments to function row_number()}}
264
265 do_execsql_test 7.2 {
266 SELECT
267 lead(y) OVER win,
268 lead(y, 2) OVER win,
269 lead(y, 3, 'default') OVER win
270 FROM t1
271 WINDOW win AS (ORDER BY x)
272 } {
273 4 6 8 6 8 10 8 10 default 10 {} default {} {} default
274 }
275
276 do_execsql_test 7.3 {
277 SELECT row_number() OVER (ORDER BY x) FROM t1
278 } {1 2 3 4 5}
279
280 do_execsql_test 7.4 {
281 SELECT
282 row_number() OVER win,
283 lead(x) OVER win
284 FROM t1
285 WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
286 } {1 3 2 5 3 7 4 9 5 {}}
287
288 #-------------------------------------------------------------------------
289 # Attempt to use a window function in a view.
290 #
291 do_execsql_test 8.0 {
292 CREATE TABLE t3(a, b, c);
293
294 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
295 INSERT INTO t3 SELECT i, i, i FROM s;
296
297 CREATE VIEW v1 AS SELECT
298 sum(b) OVER (ORDER BY c),
299 min(b) OVER (ORDER BY c),
300 max(b) OVER (ORDER BY c)
301 FROM t3;
302
303 CREATE VIEW v2 AS SELECT
304 sum(b) OVER win,
305 min(b) OVER win,
306 max(b) OVER win
307 FROM t3
308 WINDOW win AS (ORDER BY c);
309 }
310
311 do_execsql_test 8.1.1 {
312 SELECT * FROM v1
313 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
314 do_execsql_test 8.1.2 {
315 SELECT * FROM v2
316 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
317
318 db close
319 sqlite3 db test.db
320 do_execsql_test 8.2.1 {
321 SELECT * FROM v1
322 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
323 do_execsql_test 8.2.2 {
324 SELECT * FROM v2
325 } {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
326
327 #-------------------------------------------------------------------------
328 # Attempt to use a window function in a trigger.
329 #
330 do_execsql_test 9.0 {
331 CREATE TABLE t4(x, y);
332 INSERT INTO t4 VALUES(1, 'g');
333 INSERT INTO t4 VALUES(2, 'i');
334 INSERT INTO t4 VALUES(3, 'l');
335 INSERT INTO t4 VALUES(4, 'g');
336 INSERT INTO t4 VALUES(5, 'a');
337
338 CREATE TABLE t5(x, y, m);
339 CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
340 DELETE FROM t5;
341 INSERT INTO t5
342 SELECT x, y, max(y) OVER xyz FROM t4
343 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
344 END;
345 }
346
347 do_execsql_test 9.1.1 {
348 SELECT x, y, max(y) OVER xyz FROM t4
349 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
350 } {1 g g 2 i i 3 l l 4 g i 5 a l}
351
352 do_execsql_test 9.1.2 {
353 INSERT INTO t4 VALUES(6, 'm');
354 SELECT x, y, max(y) OVER xyz FROM t4
355 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
356 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
357
358 do_execsql_test 9.1.3 {
359 SELECT * FROM t5 ORDER BY 1
360 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
361
362 do_execsql_test 9.2 {
363 WITH aaa(x, y, z) AS (
364 SELECT x, y, max(y) OVER xyz FROM t4
365 WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x)
366 )
367 SELECT * FROM aaa ORDER BY 1;
368 } {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
369
370 do_execsql_test 9.3 {
371 WITH aaa(x, y, z) AS (
372 SELECT x, y, max(y) OVER xyz FROM t4
373 WINDOW xyz AS (ORDER BY x)
374 )
375 SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
376 } {1 g g g 2 i i g 3 l l g 4 g l g 5 a l g 6 m m g}
377
378 do_catchsql_test 9.4 {
379 -- 2021-04-17 dbsqlfuzz d9cf66100064952b66951845dfab41de1c124611
380 DROP TABLE IF EXISTS t1;
381 CREATE TABLE t1(a,b,c,d);
382 DROP TABLE IF EXISTS t2;
383 CREATE TABLE t2(x,y);
384 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
385 INSERT INTO t2(x,y)
386 SELECT a, max(d) OVER w1 FROM t1
387 WINDOW w1 AS (PARTITION BY EXISTS(SELECT 1 FROM t1 WHERE c=?1) );
388 END;
389 } {1 {trigger cannot use variables}}
390
391 do_catchsql_test 9.4.2 {
392 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
393 INSERT INTO t1(a,b)
394 SELECT a, max(d) OVER w1 FROM t1
395 WINDOW w1 AS (
396 ORDER BY a ROWS BETWEEN ? PRECEDING AND UNBOUNDED FOLLOWING
397 );
398 END;
399 } {1 {trigger cannot use variables}}
400 do_catchsql_test 9.4.3 {
401 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
402 INSERT INTO t1(a,b)
403 SELECT a, max(d) OVER w1 FROM t1
404 WINDOW w1 AS (
405 ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND ? FOLLOWING
406 );
407 END;
408 } {1 {trigger cannot use variables}}
409
410 #-------------------------------------------------------------------------
411 #
412 do_execsql_test 10.0 {
413 CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
414 INSERT INTO sales VALUES
415 ('Alice', 'North', 34),
416 ('Frank', 'South', 22),
417 ('Charles', 'North', 45),
418 ('Darrell', 'South', 8),
419 ('Grant', 'South', 23),
420 ('Brad' , 'North', 22),
421 ('Elizabeth', 'South', 99),
422 ('Horace', 'East', 1);
423 }
424
425 # Best two salespeople from each region
426 #
427 do_execsql_test 10.1 {
428 SELECT emp, region, total FROM (
429 SELECT
430 emp, region, total,
431 row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
432 FROM sales
433 ) WHERE rank<=2 ORDER BY region, total DESC
434 } {
435 Horace East 1
436 Charles North 45
437 Alice North 34
438 Elizabeth South 99
439 Grant South 23
440 }
441
442 do_execsql_test 10.2 {
443 SELECT emp, region, sum(total) OVER win FROM sales
444 WINDOW win AS (PARTITION BY region ORDER BY total)
445 } {
446 Horace East 1
447 Brad North 22
448 Alice North 56
449 Charles North 101
450 Darrell South 8
451 Frank South 30
452 Grant South 53
453 Elizabeth South 152
454 }
455
456 do_execsql_test 10.3 {
457 SELECT emp, region, sum(total) OVER win FROM sales
458 WINDOW win AS (PARTITION BY region ORDER BY total)
459 LIMIT 5
460 } {
461 Horace East 1
462 Brad North 22
463 Alice North 56
464 Charles North 101
465 Darrell South 8
466 }
467
468 do_execsql_test 10.4 {
469 SELECT emp, region, sum(total) OVER win FROM sales
470 WINDOW win AS (PARTITION BY region ORDER BY total)
471 LIMIT 5 OFFSET 2
472 } {
473 Alice North 56
474 Charles North 101
475 Darrell South 8
476 Frank South 30
477 Grant South 53
478 }
479
480 do_execsql_test 10.5 {
481 SELECT emp, region, sum(total) OVER win FROM sales
482 WINDOW win AS (
483 PARTITION BY region ORDER BY total
484 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
485 )
486 } {
487 Horace East 1
488 Brad North 101
489 Alice North 79
490 Charles North 45
491 Darrell South 152
492 Frank South 144
493 Grant South 122
494 Elizabeth South 99
495 }
496
497 do_execsql_test 10.6 {
498 SELECT emp, region, sum(total) OVER win FROM sales
499 WINDOW win AS (
500 PARTITION BY region ORDER BY total
501 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
502 ) LIMIT 5 OFFSET 2
503 } {
504 Alice North 79
505 Charles North 45
506 Darrell South 152
507 Frank South 144
508 Grant South 122
509 }
510
511 do_execsql_test 10.7 {
512 SELECT emp, region, (
513 SELECT sum(total) OVER (
514 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
515 ) || outer.emp FROM sales
516 ) FROM sales AS outer;
517 } {
518 Alice North 254Alice
519 Frank South 254Frank
520 Charles North 254Charles
521 Darrell South 254Darrell
522 Grant South 254Grant
523 Brad North 254Brad
524 Elizabeth South 254Elizabeth
525 Horace East 254Horace
526 }
527
528 do_execsql_test 10.8 {
529 SELECT emp, region, (
530 SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
531 ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
532 ) FROM sales
533 ) FROM sales AS outer;
534 } {
535 Alice North 220
536 Frank South 232
537 Charles North 209
538 Darrell South 246
539 Grant South 231
540 Brad North 232
541 Elizabeth South 155
542 Horace East 253
543 }
544
545 #-------------------------------------------------------------------------
546 # Check that it is not possible to use a window function in a CREATE INDEX
547 # statement.
548 #
549 do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }
550
551 do_catchsql_test 11.1 {
552 CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
553 } {1 {misuse of window function sum()}}
554 do_catchsql_test 11.2 {
555 CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
556 } {1 {misuse of window function lead()}}
557
558 do_catchsql_test 11.3 {
559 CREATE INDEX t6i ON t6(sum(b) OVER ());
560 } {1 {misuse of window function sum()}}
561 do_catchsql_test 11.4 {
562 CREATE INDEX t6i ON t6(lead(b) OVER ());
563 } {1 {misuse of window function lead()}}
564
565 # 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3
566 # Endless loop on a query with window functions and a limit
567 #
568 do_execsql_test 12.100 {
569 DROP TABLE IF EXISTS t1;
570 CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR);
571 INSERT INTO t1 VALUES(1, 'A', 'one');
572 INSERT INTO t1 VALUES(2, 'B', 'two');
573 INSERT INTO t1 VALUES(3, 'C', 'three');
574 INSERT INTO t1 VALUES(4, 'D', 'one');
575 INSERT INTO t1 VALUES(5, 'E', 'two');
576 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
577 FROM t1 WHERE id>1
578 ORDER BY b LIMIT 1;
579 } {2 B two}
580 do_execsql_test 12.110 {
581 INSERT INTO t1 VALUES(6, 'F', 'three');
582 INSERT INTO t1 VALUES(7, 'G', 'one');
583 SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
584 FROM t1 WHERE id>1
585 ORDER BY b LIMIT 2;
586 } {2 B two 3 C three}
587
588 #-------------------------------------------------------------------------
589
590 do_execsql_test 13.1 {
591 DROP TABLE IF EXISTS t1;
592 CREATE TABLE t1(a int, b int);
593 INSERT INTO t1 VALUES(1,11);
594 INSERT INTO t1 VALUES(2,12);
595 }
596
597 do_execsql_test 13.2.1 {
598 SELECT a, rank() OVER(ORDER BY b) FROM t1;
599 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
600 } {
601 1 1 2 2 2 1 1 2
602 }
603 do_execsql_test 13.2.2 {
604 SELECT a, rank() OVER(ORDER BY b) FROM t1
605 UNION ALL
606 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
607 } {
608 1 1 2 2 2 1 1 2
609 }
610 do_execsql_test 13.3 {
611 SELECT a, rank() OVER(ORDER BY b) FROM t1
612 UNION
613 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
614 } {
615 1 1 1 2 2 1 2 2
616 }
617
618 do_execsql_test 13.4 {
619 SELECT a, rank() OVER(ORDER BY b) FROM t1
620 EXCEPT
621 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
622 } {
623 1 1 2 2
624 }
625
626 do_execsql_test 13.5 {
627 SELECT a, rank() OVER(ORDER BY b) FROM t1
628 INTERSECT
629 SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
630 } {}
631
632 # 2018-12-06
633 # https://sqlite.org/src/info/f09fcd17810f65f7
634 # Assertion fault when window functions are used.
635 #
636 # Root cause is the query flattener invoking sqlite3ExprDup() on
637 # expressions that contain subqueries with window functions. The
638 # sqlite3ExprDup() routine is not making correctly initializing
639 # Select.pWin field of the subqueries.
640 #
641 sqlite3 db :memory:
642 do_execsql_test 14.0 {
643 SELECT * FROM(
644 SELECT * FROM (SELECT 1 AS c) WHERE c IN (
645 SELECT (row_number() OVER()) FROM (VALUES (0))
646 )
647 );
648 } {1}
649 do_execsql_test 14.1 {
650 CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345);
651 CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1);
652 SELECT y, y+1, y+2 FROM (
653 SELECT c IN (
654 SELECT (row_number() OVER()) FROM t1
655 ) AS y FROM t2
656 );
657 } {1 2 3}
658
659 # 2018-12-31
660 # https://sqlite.org/src/info/d0866b26f83e9c55
661 # Window function in correlated subquery causes assertion fault
662 #
663 do_catchsql_test 15.0 {
664 WITH t(id, parent) AS (
665 SELECT CAST(1 AS INT), CAST(NULL AS INT)
666 UNION ALL
667 SELECT 2, NULL
668 UNION ALL
669 SELECT 3, 1
670 UNION ALL
671 SELECT 4, 1
672 UNION ALL
673 SELECT 5, 2
674 UNION ALL
675 SELECT 6, 2
676 ), q AS (
677 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
678 FROM t
679 WHERE parent IS NULL
680 UNION ALL
681 SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
682 FROM q
683 JOIN t
684 ON t.parent = q.id
685 )
686 SELECT *
687 FROM q;
688 } {1 {cannot use window functions in recursive queries}}
689 do_execsql_test 15.1 {
690 DROP TABLE IF EXISTS t1;
691 DROP TABLE IF EXISTS t2;
692 CREATE TABLE t1(x);
693 INSERT INTO t1 VALUES('a'), ('b'), ('c');
694 CREATE TABLE t2(a, b);
695 INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3);
696 SELECT x, (
697 SELECT sum(b)
698 OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING
699 AND UNBOUNDED FOLLOWING)
700 FROM t2 WHERE b<x
701 ) FROM t1;
702 } {a 3 b 3 c 3}
703
704 do_execsql_test 15.2 {
705 SELECT(
706 WITH c AS(
707 VALUES(1)
708 ) SELECT '' FROM c,c
709 ) x WHERE x+x;
710 } {}
711
712 #-------------------------------------------------------------------------
713
714 do_execsql_test 16.0 {
715 CREATE TABLE t7(a,b);
716 INSERT INTO t7(rowid, a, b) VALUES
717 (1, 1, 3),
718 (2, 10, 4),
719 (3, 100, 2);
720 }
721
722 do_execsql_test 16.1 {
723 SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7;
724 } {
725 2 10
726 1 101
727 3 101
728 }
729
730 do_execsql_test 16.2 {
731 SELECT rowid, sum(a) OVER w1 FROM t7
732 WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
733 } {
734 2 10
735 1 101
736 3 101
737 }
738
739 #-------------------------------------------------------------------------
740 do_execsql_test 17.0 {
741 CREATE TABLE t8(a);
742 INSERT INTO t8 VALUES(1), (2), (3);
743 }
744
745 do_execsql_test 17.1 {
746 SELECT +sum(0) OVER () ORDER BY +sum(0) OVER ();
747 } {0}
748
749 do_execsql_test 17.2 {
750 select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC;
751 } {6 6 6}
752
753 do_execsql_test 17.3 {
754 SELECT 10+sum(a) OVER (ORDER BY a)
755 FROM t8
756 ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
757 } {16 13 11}
758
759
760 #-------------------------------------------------------------------------
761 # Test error cases from chaining window definitions.
762 #
763 reset_db
764 do_execsql_test 18.0 {
765 DROP TABLE IF EXISTS t1;
766 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
767 INSERT INTO t1 VALUES(1, 'odd', 'one', 1);
768 INSERT INTO t1 VALUES(2, 'even', 'two', 2);
769 INSERT INTO t1 VALUES(3, 'odd', 'three', 3);
770 INSERT INTO t1 VALUES(4, 'even', 'four', 4);
771 INSERT INTO t1 VALUES(5, 'odd', 'five', 5);
772 INSERT INTO t1 VALUES(6, 'even', 'six', 6);
773 }
774
775 foreach {tn sql error} {
776 1 {
777 SELECT c, sum(d) OVER win2 FROM t1
778 WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
779 win2 AS (win1 ORDER BY b)
780 } {cannot override frame specification of window: win1}
781
782 2 {
783 SELECT c, sum(d) OVER win2 FROM t1
784 WINDOW win1 AS (),
785 win2 AS (win4 ORDER BY b)
786 } {no such window: win4}
787
788 3 {
789 SELECT c, sum(d) OVER win2 FROM t1
790 WINDOW win1 AS (),
791 win2 AS (win1 PARTITION BY d)
792 } {cannot override PARTITION clause of window: win1}
793
794 4 {
795 SELECT c, sum(d) OVER win2 FROM t1
796 WINDOW win1 AS (ORDER BY b),
797 win2 AS (win1 ORDER BY d)
798 } {cannot override ORDER BY clause of window: win1}
799 } {
800 do_catchsql_test 18.1.$tn $sql [list 1 $error]
801 }
802
803 foreach {tn sql error} {
804 1 {
805 SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1
806 WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
807 } {cannot override frame specification of window: win1}
808
809 2 {
810 SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1
811 WINDOW win1 AS ()
812 } {no such window: win4}
813
814 3 {
815 SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1
816 WINDOW win1 AS ()
817 } {cannot override PARTITION clause of window: win1}
818
819 4 {
820 SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1
821 WINDOW win1 AS (ORDER BY b)
822 } {cannot override ORDER BY clause of window: win1}
823 } {
824 do_catchsql_test 18.2.$tn $sql [list 1 $error]
825 }
826
827 do_execsql_test 18.3.1 {
828 SELECT string_agg(c, '.') OVER (PARTITION BY b ORDER BY c)
829 FROM t1
830 } {four four.six four.six.two five five.one five.one.three}
831
832 do_execsql_test 18.3.2 {
833 SELECT group_concat(c, '.') OVER (win1 ORDER BY c)
834 FROM t1
835 WINDOW win1 AS (PARTITION BY b)
836 } {four four.six four.six.two five five.one five.one.three}
837
838 do_execsql_test 18.3.3 {
839 SELECT string_agg(c, '.') OVER win2
840 FROM t1
841 WINDOW win1 AS (PARTITION BY b),
842 win2 AS (win1 ORDER BY c)
843 } {four four.six four.six.two five five.one five.one.three}
844
845 do_execsql_test 18.3.4 {
846 SELECT group_concat(c, '.') OVER (win2)
847 FROM t1
848 WINDOW win1 AS (PARTITION BY b),
849 win2 AS (win1 ORDER BY c)
850 } {four four.six four.six.two five five.one five.one.three}
851
852 do_execsql_test 18.3.5 {
853 SELECT string_agg(c, '.') OVER win5
854 FROM t1
855 WINDOW win1 AS (PARTITION BY b),
856 win2 AS (win1),
857 win3 AS (win2),
858 win4 AS (win3),
859 win5 AS (win4 ORDER BY c)
860 } {four four.six four.six.two five five.one five.one.three}
861
862 #-------------------------------------------------------------------------
863 # Test RANGE <expr> PRECEDING/FOLLOWING when there are string, blob
864 # and NULL values in the dataset.
865 #
866 reset_db
867 do_execsql_test 19.0 {
868 CREATE TABLE t1(a, b);
869 INSERT INTO t1 VALUES
870 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
871 ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
872 }
873 do_execsql_test 19.1 {
874 SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
875 } {1 1 2 3 3 6 4 10 5 15 a 21 b 28 c 36 d 45 e 55}
876
877 do_execsql_test 19.2.1 {
878 SELECT a, sum(b) OVER (
879 ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
880 ) FROM t1;
881 } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
882 do_execsql_test 19.2.2 {
883 SELECT a, sum(b) OVER (
884 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
885 ) FROM t1 ORDER BY a ASC;
886 } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
887
888 do_execsql_test 19.3.1 {
889 SELECT a, sum(b) OVER (
890 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
891 ) FROM t1;
892 } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
893 do_execsql_test 19.3.2 {
894 SELECT a, sum(b) OVER (
895 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
896 ) FROM t1 ORDER BY a ASC;
897 } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
898
899
900 reset_db
901 do_execsql_test 20.0 {
902 CREATE TABLE t1(a, b);
903 INSERT INTO t1 VALUES
904 (NULL, 100), (NULL, 100),
905 (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
906 ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
907 }
908 do_execsql_test 20.1 {
909 SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
910 } {
911 {} 200 {} 200 1 201 2 203 3 206 4 210 5 215
912 a 221 b 228 c 236 d 245 e 255
913 }
914
915 do_execsql_test 20.2.1 {
916 SELECT a, sum(b) OVER (
917 ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
918 ) FROM t1;
919 } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
920 do_execsql_test 20.2.2 {
921 SELECT a, sum(b) OVER (
922 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
923 ) FROM t1 ORDER BY a ASC;
924 } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
925
926 do_execsql_test 20.3.1 {
927 SELECT a, sum(b) OVER (
928 ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
929 ) FROM t1;
930 } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
931 do_execsql_test 20.3.2 {
932 SELECT a, sum(b) OVER (
933 ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
934 ) FROM t1 ORDER BY a ASC;
935 } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
936
937 #-------------------------------------------------------------------------
938 do_execsql_test 21.0 {
939 CREATE TABLE keyword_tab(
940 current, exclude, filter, following, groups, no, others, over,
941 partition, preceding, range, ties, unbounded, window
942 );
943 }
944 do_execsql_test 21.1 {
945 SELECT
946 current, exclude, filter, following, groups, no, others, over,
947 partition, preceding, range, ties, unbounded, window
948 FROM keyword_tab
949 }
950
951 #-------------------------------------------------------------------------
952 foreach {tn expr err} {
953 1 4.5 0
954 2 NULL 1
955 3 0.0 0
956 4 0.1 0
957 5 -0.1 1
958 6 '' 1
959 7 '2.0' 0
960 8 '2.0x' 1
961 9 x'1234' 1
962 10 '1.2' 0
963 } {
964 set res {0 1}
965 if {$err} {set res {1 {frame starting offset must be a non-negative number}} }
966 do_catchsql_test 22.$tn.1 "
967 WITH a(x, y) AS ( VALUES(1, 2) )
968 SELECT sum(x) OVER (
969 ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING
970 ) FROM a
971 " $res
972
973 set res {0 1}
974 if {$err} {set res {1 {frame ending offset must be a non-negative number}} }
975 do_catchsql_test 22.$tn.2 "
976 WITH a(x, y) AS ( VALUES(1, 2) )
977 SELECT sum(x) OVER (
978 ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
979 ) FROM a
980 " $res
981 }
982
983 #-------------------------------------------------------------------------
984 reset_db
985 do_execsql_test 23.0 {
986 CREATE TABLE t5(a, b, c);
987 CREATE INDEX t5ab ON t5(a, b);
988 }
989
990 proc do_ordercount_test {tn sql nOrderBy} {
991 set plan [execsql "EXPLAIN QUERY PLAN $sql"]
992 uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy]
993 }
994
995 do_ordercount_test 23.1 {
996 SELECT
997 sum(c) OVER (ORDER BY a, b),
998 sum(c) OVER (PARTITION BY a ORDER BY b)
999 FROM t5
1000 } 0
1001
1002 do_ordercount_test 23.2 {
1003 SELECT
1004 sum(c) OVER (ORDER BY b, a),
1005 sum(c) OVER (PARTITION BY b ORDER BY a)
1006 FROM t5
1007 } 1
1008
1009 do_ordercount_test 23.3 {
1010 SELECT
1011 sum(c) OVER (ORDER BY b, a),
1012 sum(c) OVER (ORDER BY c, b)
1013 FROM t5
1014 } 2
1015
1016 do_ordercount_test 23.4 {
1017 SELECT
1018 sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1019 sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1020 sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1021 FROM t5
1022 } 1
1023
1024 do_ordercount_test 23.5 {
1025 SELECT
1026 sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
1027 sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING),
1028 sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING)
1029 FROM t5
1030 } 1
1031
1032 do_ordercount_test 23.6 {
1033 SELECT
1034 sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
1035 sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING),
1036 sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING)
1037 FROM t5
1038 } 3
1039
1040 do_execsql_test 24.1 {
1041 SELECT sum(44) OVER ()
1042 } {44}
1043
1044 do_execsql_test 24.2 {
1045 SELECT lead(44) OVER ()
1046 } {{}}
1047
1048 #-------------------------------------------------------------------------
1049 #
1050 reset_db
1051 do_execsql_test 25.0 {
1052 CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY );
1053 CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY );
1054 CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY );
1055
1056 INSERT INTO t1 VALUES(1), (3), (5);
1057 INSERT INTO t2 VALUES (3), (5);
1058 INSERT INTO t3 VALUES(10), (11), (12);
1059 }
1060
1061 do_execsql_test 25.1 {
1062 SELECT t1.* FROM t1, t2 WHERE
1063 t1_id=t2_id AND t1_id IN (
1064 SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3
1065 )
1066 }
1067
1068 do_execsql_test 25.2 {
1069 SELECT t1.* FROM t1, t2 WHERE
1070 t1_id=t2_id AND t1_id IN (
1071 SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3
1072 )
1073 } {3}
1074
1075 #-------------------------------------------------------------------------
1076 reset_db
1077 do_execsql_test 26.0 {
1078 CREATE TABLE t1(x);
1079 CREATE TABLE t2(c);
1080 }
1081
1082 do_execsql_test 26.1 {
1083 SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2
1084 } {}
1085
1086 do_execsql_test 26.2 {
1087 INSERT INTO t1 VALUES(1), (2), (3), (4);
1088 INSERT INTO t2 VALUES(2), (6), (8), (4);
1089 SELECT c, c IN (
1090 SELECT row_number() OVER () FROM ( SELECT c FROM t1 )
1091 ) FROM t2
1092 } {2 1 6 0 8 0 4 1}
1093
1094 do_execsql_test 26.3 {
1095 DELETE FROM t1;
1096 DELETE FROM t2;
1097
1098 INSERT INTO t2 VALUES(1), (2), (3), (4);
1099 INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4);
1100
1101 SELECT c, c IN (
1102 SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c )
1103 ) FROM t2
1104 } {1 1 2 0 3 1 4 0}
1105
1106 #-------------------------------------------------------------------------
1107 reset_db
1108 do_execsql_test 27.0 {
1109 CREATE TABLE t1(x);
1110 INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5);
1111 }
1112 do_execsql_test 27.1 {
1113 SELECT min(x) FROM t1;
1114 } {1}
1115 do_execsql_test 27.2 {
1116 SELECT min(x) OVER win FROM t1
1117 WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
1118 } {1 1 1 2 3 4}
1119
1120 #-------------------------------------------------------------------------
1121
1122 reset_db
1123 do_execsql_test 28.1.1 {
1124 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1125 INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
1126 INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
1127 }
1128
1129 do_execsql_test 28.1.2 {
1130 SELECT string_agg(b,'') OVER w1 FROM t1
1131 WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
1132 } {
1133 {} {}
1134 }
1135
1136 do_execsql_test 28.2.1 {
1137 CREATE TABLE t2(a TEXT, b INTEGER);
1138 INSERT INTO t2 VALUES('A', NULL);
1139 INSERT INTO t2 VALUES('B', NULL);
1140 }
1141
1142 do_execsql_test 28.2.1 {
1143 DROP TABLE IF EXISTS t1;
1144 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1145 INSERT INTO t1 VALUES
1146 (10,'J', 'cc', NULL),
1147 (11,'K', 'cc', 'xyz'),
1148 (13,'M', 'cc', NULL);
1149 }
1150
1151 do_execsql_test 28.2.2 {
1152 SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
1153 WINDOW w1 AS
1154 (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
1155 ORDER BY c, d, a;
1156 } {
1157 10 J cc NULL JM |
1158 13 M cc NULL JM |
1159 11 K cc 'xyz' K |
1160 }
1161
1162 #-------------------------------------------------------------------------
1163 reset_db
1164
1165 do_execsql_test 29.1 {
1166 DROP TABLE IF EXISTS t1;
1167 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1168 INSERT INTO t1 VALUES
1169 (1, 'A', 'aa', 2.5),
1170 (2, 'B', 'bb', 3.75),
1171 (3, 'C', 'cc', 1.0),
1172 (4, 'D', 'cc', 8.25),
1173 (5, 'E', 'bb', 6.5),
1174 (6, 'F', 'aa', 6.5),
1175 (7, 'G', 'aa', 6.0),
1176 (8, 'H', 'bb', 9.0),
1177 (9, 'I', 'aa', 3.75),
1178 (10,'J', 'cc', NULL),
1179 (11,'K', 'cc', 'xyz'),
1180 (12,'L', 'cc', 'xyZ'),
1181 (13,'M', 'cc', NULL);
1182 }
1183
1184 do_execsql_test 29.2 {
1185 SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
1186 WINDOW w1 AS
1187 (PARTITION BY c ORDER BY d DESC
1188 RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
1189 ORDER BY c, d, a;
1190 } {
1191 1 A aa 2.5 FG |
1192 9 I aa 3.75 F |
1193 7 G aa 6 {} |
1194 6 F aa 6.5 {} |
1195 2 B bb 3.75 HE |
1196 5 E bb 6.5 H |
1197 8 H bb 9 {} |
1198 10 J cc NULL JM |
1199 13 M cc NULL JM |
1200 3 C cc 1 {} |
1201 4 D cc 8.25 {} |
1202 12 L cc 'xyZ' L |
1203 11 K cc 'xyz' K |
1204 }
1205
1206 # 2019-07-18
1207 # Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket
1208 # https://sqlite.org/src/info/1be72aab9) introduced a new problem
1209 # if the LHS of a BETWEEN operator is a WINDOW function. The problem
1210 # was found by (the recently enhanced) dbsqlfuzz.
1211 #
1212 do_execsql_test 30.0 {
1213 DROP TABLE IF EXISTS t1;
1214 CREATE TABLE t1(a, b, c);
1215 INSERT INTO t1 VALUES('BB','aa',399);
1216 SELECT
1217 count () OVER win1 NOT BETWEEN 'a' AND 'mmm',
1218 count () OVER win3
1219 FROM t1
1220 WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
1221 EXCLUDE CURRENT ROW),
1222 win2 AS (PARTITION BY b ORDER BY a),
1223 win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
1224 } {1 1}
1225
1226 #-------------------------------------------------------------------------
1227 reset_db
1228 do_execsql_test 31.1 {
1229 CREATE TABLE t1(a, b);
1230 CREATE TABLE t2(c, d);
1231 CREATE TABLE t3(e, f);
1232
1233 INSERT INTO t1 VALUES(1, 1);
1234 INSERT INTO t2 VALUES(1, 1);
1235 INSERT INTO t3 VALUES(1, 1);
1236 }
1237
1238 do_execsql_test 31.2 {
1239 SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
1240 SELECT * FROM t2
1241 );
1242 } {1}
1243
1244 do_execsql_test 31.3 {
1245 SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
1246 SELECT * FROM t2
1247 );
1248 } {1}
1249
1250 do_catchsql_test 31.3 {
1251 SELECT d IN (
1252 SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING)
1253 FROM t3
1254 )
1255 FROM (
1256 SELECT * FROM t2
1257 );
1258 } {1 {frame starting offset must be a non-negative integer}}
1259
1260 do_catchsql_test 31.3 {
1261 SELECT d IN (
1262 SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING)
1263 FROM t3
1264 )
1265 FROM (
1266 SELECT * FROM t2
1267 );
1268 } {1 {frame ending offset must be a non-negative integer}}
1269
1270 # 2019-11-16 chromium issue 1025467
1271 ifcapable altertable {
1272 db close
1273 sqlite3 db :memory:
1274 do_catchsql_test 32.10 {
1275 CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R;
1276 CREATE TABLE a0 AS SELECT 0;
1277 ALTER TABLE a0 RENAME TO S;
1278 } {1 {error in view a: 1st ORDER BY term does not match any column in the result set}}
1279 }
1280
1281 reset_db
1282 do_execsql_test 33.1 {
1283 CREATE TABLE t1(aa, bb);
1284 INSERT INTO t1 VALUES(1, 2);
1285 INSERT INTO t1 VALUES(5, 6);
1286 CREATE TABLE t2(x);
1287 INSERT INTO t2 VALUES(1);
1288 }
1289 do_execsql_test 33.2 {
1290 SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2
1291 ORDER BY 1;
1292 } {6 1}
1293
1294 reset_db
1295 do_execsql_test 34.1 {
1296 CREATE TABLE t1(a,b,c);
1297 }
1298 do_execsql_test 34.2 {
1299 SELECT avg(a) OVER (
1300 ORDER BY (SELECT sum(b) OVER ()
1301 FROM t1 ORDER BY (
1302 SELECT total(d) OVER (ORDER BY c)
1303 FROM (SELECT 1 AS d) ORDER BY 1
1304 )
1305 )
1306 )
1307 FROM t1;
1308 }
1309
1310 #-------------------------------------------------------------------------
1311 reset_db
1312 do_catchsql_test 35.0 {
1313 SELECT * WINDOW f AS () ORDER BY name COLLATE nocase;
1314 } {1 {no tables specified}}
1315
1316 do_catchsql_test 35.1 {
1317 VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase;
1318 } {1 {no tables specified}}
1319
1320 do_execsql_test 35.2 {
1321 CREATE TABLE t1(x);
1322 INSERT INTO t1 VALUES(1), (2), (3);
1323 VALUES(1) INTERSECT
1324 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1325 } {1}
1326
1327 do_execsql_test 35.3 {
1328 VALUES(8) EXCEPT
1329 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1330 } {8}
1331
1332 do_execsql_test 35.4 {
1333 VALUES(1) UNION
1334 SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1335 } {1 3 6}
1336
1337 # 2019-12-07 gramfuzz find
1338 #
1339 do_execsql_test 36.10 {
1340 VALUES(count(*)OVER());
1341 } {1}
1342 do_execsql_test 36.20 {
1343 VALUES(count(*)OVER()),(2);
1344 } {1 2}
1345 do_execsql_test 36.30 {
1346 VALUES(2),(count(*)OVER());
1347 } {2 1}
1348 do_execsql_test 36.40 {
1349 VALUES(2),(3),(count(*)OVER()),(4),(5);
1350 } {2 3 1 4 5}
1351
1352 # 2019-12-17 crash test case found by Yongheng and Rui
1353 # See check-in 1ca0bd982ab1183b
1354 #
1355 reset_db
1356 do_execsql_test 37.10 {
1357 CREATE TABLE t0(a UNIQUE, b PRIMARY KEY);
1358 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0;
1359 SELECT c FROM v0 WHERE c BETWEEN 10 AND 20;
1360 } {}
1361 do_execsql_test 37.20 {
1362 DROP VIEW v0;
1363 CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
1364 SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
1365 } {}
1366
1367 # 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate
1368 # in a join.
1369 #
1370 reset_db
1371 do_catchsql_test 38.10 {
1372 CREATE TABLE t0(c0);
1373 CREATE TABLE t1(c0, c1 UNIQUE);
1374 INSERT INTO t0(c0) VALUES(1);
1375 INSERT INTO t1(c0,c1) VALUES(2,3);
1376 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1));
1377 } {1 {misuse of aggregate: AVG()}}
1378 do_execsql_test 38.20 {
1379 SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
1380 } {1 1.0}
1381 do_catchsql_test 38.30 {
1382 SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
1383 } {1 {misuse of aggregate: AVG()}}
1384
1385 reset_db
1386 do_execsql_test 39.1 {
1387 CREATE TABLE t0(c0 UNIQUE);
1388 }
1389 do_execsql_test 39.2 {
1390 SELECT FIRST_VALUE(0) OVER();
1391 } {0}
1392 do_execsql_test 39.3 {
1393 SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0);
1394 }
1395 do_execsql_test 39.4 {
1396 SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0);
1397 }
1398
1399 ifcapable rtree {
1400 # 2019-12-25 ticket d87336c81c7d0873
1401 #
1402 reset_db
1403 do_catchsql_test 40.1 {
1404 CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2);
1405 SELECT * FROM t0
1406 WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0);
1407 } {0 {}}
1408 }
1409
1410 #-------------------------------------------------------------------------
1411 reset_db
1412 do_execsql_test 41.1 {
1413 CREATE TABLE t1(a, b, c);
1414 INSERT INTO t1 VALUES(NULL,'bb',355);
1415 INSERT INTO t1 VALUES('CC','aa',158);
1416 INSERT INTO t1 VALUES('GG','bb',929);
1417 INSERT INTO t1 VALUES('FF','Rb',574);
1418 }
1419
1420 do_execsql_test 41.2 {
1421 SELECT min(c) OVER (
1422 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1423 ) FROM t1
1424 } {355 158 574 929}
1425
1426 do_execsql_test 41.2 {
1427 SELECT min(c) OVER (
1428 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1429 ) << 100 FROM t1
1430 } {0 0 0 0}
1431
1432 do_execsql_test 41.3 {
1433 SELECT
1434 min(c) OVER win3 << first_value(c) OVER win3,
1435 min(c) OVER win3 << first_value(c) OVER win3
1436 FROM t1
1437 WINDOW win3 AS (
1438 PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1439 );
1440 } {0 0 0 0 0 0 0 0}
1441
1442 #-------------------------------------------------------------------------
1443 reset_db
1444 do_execsql_test 42.1 {
1445 CREATE TABLE t1(a, b, c);
1446 INSERT INTO t1 VALUES(1, 1, 1);
1447 INSERT INTO t1 VALUES(2, 2, 2);
1448 }
1449 do_execsql_test 42.2 {
1450 SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 )
1451 } {}
1452 do_execsql_test 42.3 {
1453 SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 )
1454 } {1 1 1 2 2 2}
1455
1456 do_execsql_test 42.3 {
1457 SELECT count(*), max(a) OVER () FROM t1 GROUP BY c;
1458 } {1 2 1 2}
1459
1460 do_execsql_test 42.4 {
1461 SELECT sum(a), max(b) OVER () FROM t1;
1462 } {3 1}
1463
1464 do_execsql_test 42.5 {
1465 CREATE TABLE t2(a, b);
1466 INSERT INTO t2 VALUES('a', 1);
1467 INSERT INTO t2 VALUES('a', 2);
1468 INSERT INTO t2 VALUES('a', 3);
1469 INSERT INTO t2 VALUES('b', 4);
1470 INSERT INTO t2 VALUES('b', 5);
1471 INSERT INTO t2 VALUES('b', 6);
1472 }
1473
1474 do_execsql_test 42.6 {
1475 SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a;
1476 } {a 6 6 b 15 21}
1477
1478 do_execsql_test 42.7 {
1479 SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2;
1480 } {21 21}
1481
1482 #-------------------------------------------------------------------------
1483 reset_db
1484 do_execsql_test 43.1.1 {
1485 CREATE TABLE t1(x INTEGER PRIMARY KEY);
1486 INSERT INTO t1 VALUES (10);
1487 }
1488 do_catchsql_test 43.1.2 {
1489 SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m);
1490 } {1 {misuse of aliased window function m}}
1491
1492 reset_db
1493 do_execsql_test 43.2.1 {
1494 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
1495 INSERT INTO t1(a, b) VALUES(1, 10); -- 10
1496 INSERT INTO t1(a, b) VALUES(2, 15); -- 25
1497 INSERT INTO t1(a, b) VALUES(3, -5); -- 20
1498 INSERT INTO t1(a, b) VALUES(4, -5); -- 15
1499 INSERT INTO t1(a, b) VALUES(5, 20); -- 35
1500 INSERT INTO t1(a, b) VALUES(6, -11); -- 24
1501 }
1502
1503 do_execsql_test 43.2.2 {
1504 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2
1505 } {
1506 1 10 4 15 3 20 6 24 2 25 5 35
1507 }
1508
1509 do_execsql_test 43.2.3 {
1510 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc
1511 } {
1512 1 10 4 15 3 20 6 24 2 25 5 35
1513 }
1514
1515 do_execsql_test 43.2.4 {
1516 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5
1517 } {
1518 1 10 4 15 3 20 6 24 2 25 5 35
1519 }
1520
1521 do_catchsql_test 43.2.5 {
1522 SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
1523 } {1 {misuse of aliased window function abc}}
1524
1525 do_catchsql_test 43.2.6 {
1526 SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
1527 } {1 {misuse of aliased window function abc}}
1528
1529 #-------------------------------------------------------------------------
1530 reset_db
1531 do_execsql_test 44.1 {
1532 CREATE TABLE t0(c0);
1533 }
1534
1535 do_catchsql_test 44.2.1 {
1536 SELECT ntile(0) OVER ();
1537 } {1 {argument of ntile must be a positive integer}}
1538 do_catchsql_test 44.2.2 {
1539 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0;
1540 } {1 {argument of ntile must be a positive integer}}
1541
1542 do_execsql_test 44.3.1 {
1543 SELECT ntile(1) OVER ();
1544 } {1}
1545 do_execsql_test 44.3.2 {
1546 SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
1547 } {0}
1548
1549 do_execsql_test 44.4.2 {
1550 INSERT INTO t0 VALUES(2), (1), (0);
1551 SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
1552 } {1}
1553
1554 #-------------------------------------------------------------------------
1555 reset_db
1556 do_execsql_test 45.1 {
1557 CREATE TABLE t0(x);
1558 CREATE TABLE t1(a);
1559 INSERT INTO t1 VALUES(1000);
1560 INSERT INTO t1 VALUES(1000);
1561 INSERT INTO t0 VALUES(10000);
1562 }
1563 do_execsql_test 45.2 {
1564 SELECT * FROM (
1565 SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0
1566 );
1567 } {2000 2000 10000}
1568
1569 #-------------------------------------------------------------------------
1570 reset_db
1571 do_execsql_test 46.1 {
1572 CREATE TABLE t1 (a);
1573 CREATE INDEX i1 ON t1(a);
1574
1575 INSERT INTO t1 VALUES (10);
1576 }
1577
1578 do_execsql_test 46.2 {
1579 SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1
1580 } 10
1581
1582 do_execsql_test 46.3 {
1583 SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a));
1584 } 10
1585
1586 do_execsql_test 46.4 {
1587 SELECT * FROM t1 NATURAL JOIN t1
1588 WHERE a=1
1589 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)
1590 } 10
1591
1592 #-------------------------------------------------------------------------
1593 reset_db
1594 do_execsql_test 47.0 {
1595 CREATE TABLE t1(
1596 a,
1597 e,
1598 f,
1599 g UNIQUE,
1600 h UNIQUE
1601 );
1602 }
1603
1604 do_execsql_test 47.1 {
1605 CREATE VIEW t2(k) AS
1606 SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f;
1607 }
1608
1609 do_catchsql_test 47.2 {
1610 SELECT 234 FROM t2
1611 WHERE k=1
1612 OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1));
1613 } {1 {misuse of window function sum()}}
1614
1615 #-------------------------------------------------------------------------
1616 reset_db
1617 do_execsql_test 48.0 {
1618 CREATE TABLE t1(a);
1619 INSERT INTO t1 VALUES(1);
1620 INSERT INTO t1 VALUES(2);
1621 INSERT INTO t1 VALUES(3);
1622 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
1623 FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1);
1624 } {12 12 12}
1625
1626 do_execsql_test 48.1 {
1627 SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
1628 FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1);
1629 } {2 2 2}
1630
1631 #-------------------------------------------------------------------------
1632 reset_db
1633 do_execsql_test 49.1 {
1634 CREATE TABLE t1 (a PRIMARY KEY);
1635 INSERT INTO t1 VALUES(1);
1636 }
1637
1638 do_execsql_test 49.2 {
1639 SELECT b AS c FROM (
1640 SELECT a AS b FROM (
1641 SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ())
1642 )
1643 WHERE b=1 OR b<10
1644 )
1645 WHERE c=1 OR c>=10;
1646 } {1}
1647
1648
1649 #-------------------------------------------------------------------------
1650 reset_db
1651 do_execsql_test 50.0 {
1652 CREATE TABLE t1 (a DOUBLE PRIMARY KEY);
1653 INSERT INTO t1 VALUES(10.0);
1654 }
1655
1656 do_execsql_test 50.1 {
1657 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1658 } {10.0}
1659
1660 do_execsql_test 50.2 {
1661 SELECT * FROM (
1662 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1663 )
1664 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1665 } {10.0}
1666
1667 do_execsql_test 50.3 {
1668 SELECT a FROM (
1669 SELECT * FROM (
1670 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1671 )
1672 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1673 )
1674 WHERE a=1 OR a=10.0
1675 } {10.0}
1676
1677 do_execsql_test 50.4 {
1678 SELECT a FROM (
1679 SELECT * FROM (
1680 SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1681 )
1682 WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1683 )
1684 WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a)
1685 } {10.0}
1686
1687 do_execsql_test 50.5 {
1688 SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM (SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM t1 NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)))OVER(ORDER BY a% 1 )) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND 10<=a)))OVER(ORDER BY a%5)) AND a<=10);
1689 } {10.0}
1690
1691 # 2020-04-03 ticket af4556bb5c285c08
1692 #
1693 reset_db
1694 do_catchsql_test 51.1 {
1695 CREATE TABLE a(b, c);
1696 SELECT c FROM a GROUP BY c
1697 HAVING(SELECT(sum(b) OVER(ORDER BY b),
1698 sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b)));
1699 } {1 {row value misused}}
1700
1701 #-------------------------------------------------------------------------
1702 reset_db
1703 do_execsql_test 52.1 {
1704 CREATE TABLE t1(a, b, c);
1705 INSERT INTO t1 VALUES('AA','bb',356);
1706 INSERT INTO t1 VALUES('CC','aa',158);
1707 INSERT INTO t1 VALUES('BB','aa',399);
1708 INSERT INTO t1 VALUES('FF','bb',938);
1709 }
1710
1711 do_execsql_test 52.2 {
1712 SELECT
1713 count() OVER win1,
1714 sum(c) OVER win2,
1715 first_value(c) OVER win2,
1716 count(a) OVER (ORDER BY b)
1717 FROM t1
1718 WINDOW
1719 win1 AS (ORDER BY a),
1720 win2 AS (PARTITION BY 6 ORDER BY a
1721 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1722 } {
1723 1 356 356 4
1724 2 399 399 2
1725 3 158 158 2
1726 4 938 938 4
1727 }
1728
1729 do_execsql_test 52.3 {
1730 SELECT
1731 count() OVER (),
1732 sum(c) OVER win2,
1733 first_value(c) OVER win2,
1734 count(a) OVER (ORDER BY b)
1735 FROM t1
1736 WINDOW
1737 win1 AS (ORDER BY a),
1738 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
1739 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1740 } {
1741 4 356 356 4
1742 4 399 399 2
1743 4 158 158 2
1744 4 938 938 4
1745 }
1746
1747 do_execsql_test 52.4 {
1748 SELECT
1749 count() OVER win1,
1750 sum(c) OVER win2,
1751 first_value(c) OVER win2,
1752 count(a) OVER (ORDER BY b)
1753 FROM t1
1754 WINDOW
1755 win1 AS (ORDER BY a),
1756 win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
1757 RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1758 } {
1759 1 356 356 4
1760 2 399 399 2
1761 3 158 158 2
1762 4 938 938 4
1763 }
1764
1765 # 2020-05-23
1766 # ticket 7a5279a25c57adf1
1767 #
1768 reset_db
1769 do_execsql_test 53.0 {
1770 CREATE TABLE a(c UNIQUE);
1771 INSERT INTO a VALUES(4),(0),(9),(-9);
1772 SELECT a.c
1773 FROM a
1774 JOIN a AS b ON a.c=4
1775 JOIN a AS e ON a.c=e.c
1776 WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c))
1777 FROM a AS d
1778 WHERE a.c);
1779 } {4 4 4 4}
1780
1781 #-------------------------------------------------------------------------
1782 reset_db
1783 do_execsql_test 54.1 {
1784 CREATE TABLE t1(a VARCHAR(20), b FLOAT);
1785 INSERT INTO t1 VALUES('1',10.0);
1786 }
1787
1788 do_catchsql_test 54.2 {
1789 SELECT * FROM (
1790 SELECT sum(b) OVER() AS c FROM t1
1791 UNION
1792 SELECT b AS c FROM t1
1793 ) WHERE c>10;
1794 } {0 {}}
1795
1796 do_execsql_test 54.3 {
1797 INSERT INTO t1 VALUES('2',5.0);
1798 INSERT INTO t1 VALUES('3',15.0);
1799 }
1800
1801 do_catchsql_test 54.4 {
1802 SELECT * FROM (
1803 SELECT sum(b) OVER() AS c FROM t1
1804 UNION
1805 SELECT b AS c FROM t1
1806 ) WHERE c>10;
1807 } {0 {15.0 30.0}}
1808
1809 # 2020-06-05 ticket c8d3b9f0a750a529
1810 reset_db
1811 do_execsql_test 55.1 {
1812 CREATE TABLE a(b);
1813 SELECT
1814 (SELECT b FROM a
1815 GROUP BY b
1816 HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b))
1817 )
1818 FROM a
1819 UNION
1820 SELECT 99
1821 ORDER BY 1;
1822 } {99}
1823
1824 #------------------------------------------------------------------------
1825 reset_db
1826 do_execsql_test 56.1 {
1827 CREATE TABLE t1(a, b INTEGER);
1828 CREATE TABLE t2(c, d);
1829 }
1830 do_catchsql_test 56.2 {
1831 SELECT avg(b) FROM t1
1832 UNION ALL
1833 SELECT min(c) OVER () FROM t2
1834 ORDER BY nosuchcolumn;
1835 } {1 {1st ORDER BY term does not match any column in the result set}}
1836
1837 reset_db
1838 do_execsql_test 57.1 {
1839 CREATE TABLE t4(a, b, c, d, e);
1840 }
1841
1842 do_catchsql_test 57.2 {
1843 SELECT b FROM t4
1844 UNION
1845 SELECT a FROM t4
1846 ORDER BY (
1847 SELECT sum(x) OVER() FROM (
1848 SELECT c AS x FROM t4
1849 UNION
1850 SELECT d FROM t4
1851 ORDER BY (SELECT e FROM t4)
1852 )
1853 );
1854 } {1 {1st ORDER BY term does not match any column in the result set}}
1855
1856 # 2020-06-06 various dbsqlfuzz finds and
1857 # ticket 0899cf62f597d7e7
1858 #
1859 reset_db
1860 do_execsql_test 57.1 {
1861 CREATE TABLE t1(a, b, c);
1862 INSERT INTO t1 VALUES(NULL,NULL,NULL);
1863 SELECT
1864 sum(a),
1865 min(b) OVER (),
1866 count(c) OVER (ORDER BY b)
1867 FROM t1;
1868 } {{} {} 0}
1869 do_execsql_test 57.2 {
1870 CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ;
1871 INSERT INTO v0 VALUES ( 10 ) ;
1872 SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2;
1873 } {10 {}}
1874 do_catchsql_test 57.3 {
1875 DROP TABLE t1;
1876 CREATE TABLE t1(a);
1877 INSERT INTO t1(a) VALUES(22);
1878 CREATE TABLE t3(y);
1879 INSERT INTO t3(y) VALUES(5),(11),(-9);
1880 SELECT (
1881 SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1)))
1882 )
1883 FROM t3;
1884 } {0 5}
1885
1886 # 2020-06-06 ticket 1f6f353b684fc708
1887 reset_db
1888 do_execsql_test 58.1 {
1889 CREATE TABLE a(a, b, c);
1890 INSERT INTO a VALUES(1, 2, 3);
1891 INSERT INTO a VALUES(4, 5, 6);
1892 SELECT sum(345+b) OVER (ORDER BY b),
1893 sum(avg(678)) OVER (ORDER BY c) FROM a;
1894 } {347 678.0}
1895
1896 # 2020-06-06 ticket e5504e987e419fb0
1897 do_catchsql_test 59.1 {
1898 DROP TABLE IF EXISTS t1;
1899 CREATE TABLE t1(x INTEGER PRIMARY KEY);
1900 INSERT INTO t1 VALUES (123);
1901 SELECT
1902 ntile( (SELECT sum(x)) ) OVER(ORDER BY x),
1903 min(x) OVER(ORDER BY x)
1904 FROM t1;
1905 } {1 {misuse of aggregate: sum()}}
1906
1907 # 2020-06-07 ticket f7d890858f361402
1908 do_execsql_test 60.1 {
1909 DROP TABLE IF EXISTS t1;
1910 CREATE TABLE t1 (x INTEGER PRIMARY KEY);
1911 INSERT INTO t1 VALUES (99);
1912 SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER());
1913 } {1}
1914
1915 # 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo
1916 # object might be referenced after the sqlite3Select() call that created
1917 # it returns. This proves the need to persist all AggInfo objects until
1918 # the Parse object is destroyed.
1919 #
1920 reset_db
1921 do_catchsql_test 61.1 {
1922 CREATE TABLE t1(a);
1923 INSERT INTO t1 VALUES(5),(NULL),('seventeen');
1924 SELECT (SELECT max(x)OVER(ORDER BY x) % min(x)OVER(ORDER BY CASE x WHEN 889 THEN x WHEN x THEN x END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST((SELECT (SELECT max(x)OVER(ORDER BY x) / min(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN -true THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x) & sum ( a )OVER(ORDER BY CASE x WHEN -8 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a AS )) FROM t1) AS x FROM t1)) AS t1 )) FROM t1) AS x FROM t1)) AS x )) FROM t1) AS x FROM t1)) AS real)) FROM t1) AS x FROM t1);
1925 } {0 {{} {} {}}}
1926
1927 foreach tn {1 2} {
1928 if {$tn==2} { optimization_control db query-flattener 0 }
1929 do_catchsql_test 61.2.$tn {
1930 SELECT
1931 (SELECT max(x)OVER(ORDER BY x) / min(x) OVER() )
1932 FROM (
1933 SELECT (SELECT sum(a) FROM t1 ) AS x FROM t1
1934 )
1935
1936 } {0 {1.0 1.0 1.0}}
1937 }
1938
1939 reset_db
1940 optimization_control db all 0
1941 do_execsql_test 61.3.0 {
1942 CREATE TABLE t1(a);
1943 CREATE TABLE t2(y);
1944 }
1945
1946 do_execsql_test 61.3.1 {
1947 SELECT (
1948 SELECT count(a) OVER ( ORDER BY (SELECT sum(y) FROM t2) )
1949 + total(a) OVER()
1950 )
1951 FROM t1
1952 } {}
1953 do_execsql_test 61.4.2 {
1954 SELECT (
1955 SELECT count(a) OVER ( ORDER BY sum(a) )
1956 + total(a) OVER()
1957 )
1958 FROM t1
1959 } {0.0}
1960
1961 do_catchsql_test 61.4.3 {
1962 SELECT
1963 sum(a) OVER ( ORDER BY a )
1964 FROM t1
1965 ORDER BY (SELECT sum(a) FROM t2)
1966 } {1 {misuse of aggregate: sum()}}
1967 do_execsql_test 61.4.4 {
1968 SELECT
1969 sum(a) OVER ( ORDER BY a )
1970 FROM t1
1971 ORDER BY (SELECT sum(y) FROM t2)
1972 }
1973
1974
1975 #-------------------------------------------------------------------------
1976 reset_db
1977 do_execsql_test 62.1 {
1978 CREATE TABLE t1(a VARCHAR(20), b FLOAT);
1979 INSERT INTO t1 VALUES('1',10.0);
1980 }
1981
1982 do_execsql_test 62.2 {
1983 SELECT * FROM (
1984 SELECT sum(b) OVER() AS c FROM t1
1985 UNION
1986 SELECT b AS c FROM t1
1987 ) WHERE c>10;
1988 }
1989
1990 do_execsql_test 62.3 {
1991 INSERT INTO t1 VALUES('2',5.0);
1992 INSERT INTO t1 VALUES('3',15.0);
1993 }
1994
1995 do_execsql_test 62.4 {
1996 SELECT * FROM (
1997 SELECT sum(b) OVER() AS c FROM t1
1998 UNION
1999 SELECT b AS c FROM t1
2000 ) WHERE c>10;
2001 } {15.0 30.0}
2002
2003 #-------------------------------------------------------------------------
2004 reset_db
2005 do_execsql_test 63.1 {
2006 CREATE TABLE t1(b, x);
2007 CREATE TABLE t2(c, d);
2008 CREATE TABLE t3(e, f);
2009 }
2010
2011 do_execsql_test 63.2 {
2012 SELECT max(b) OVER(
2013 ORDER BY SUM(
2014 (SELECT c FROM t2 UNION SELECT x ORDER BY c)
2015 )
2016 ) FROM t1;
2017 } {{}}
2018
2019 do_execsql_test 63.3 {
2020 SELECT sum(b) over(
2021 ORDER BY (
2022 SELECT max(b) OVER(
2023 ORDER BY sum(
2024 (SELECT x AS c UNION SELECT 1234 ORDER BY c)
2025 )
2026 ) AS e
2027 ORDER BY e
2028 )
2029 )
2030 FROM t1;
2031 } {{}}
2032
2033 #-------------------------------------------------------------------------
2034 reset_db
2035 do_execsql_test 64.1 {
2036 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
2037 INSERT INTO t1 VALUES(1, 'abcd');
2038 INSERT INTO t1 VALUES(2, 'BCDE');
2039 INSERT INTO t1 VALUES(3, 'cdef');
2040 INSERT INTO t1 VALUES(4, 'DEFG');
2041 }
2042
2043 do_execsql_test 64.2 {
2044 SELECT rowid, max(b COLLATE nocase)||''
2045 FROM t1
2046 GROUP BY rowid
2047 ORDER BY max(b COLLATE nocase)||'';
2048 } {1 abcd 2 BCDE 3 cdef 4 DEFG}
2049
2050 do_execsql_test 64.3 {
2051 SELECT count() OVER (), rowid, max(b COLLATE nocase)||''
2052 FROM t1
2053 GROUP BY rowid
2054 ORDER BY max(b COLLATE nocase)||'';
2055 } {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG}
2056
2057 do_execsql_test 64.4 {
2058 SELECT count() OVER (), rowid, max(b COLLATE nocase)
2059 FROM t1
2060 GROUP BY rowid
2061 ORDER BY max(b COLLATE nocase);
2062 } {4 1 abcd 4 2 BCDE 4 3 cdef 4 4 DEFG}
2063
2064 #-------------------------------------------------------------------------
2065 reset_db
2066 do_execsql_test 65.1 {
2067 CREATE TABLE t1(c1);
2068 INSERT INTO t1 VALUES('abcd');
2069 }
2070 do_execsql_test 65.2 {
2071 SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
2072 } {1}
2073
2074 do_execsql_test 65.3 {
2075 SELECT
2076 count() OVER (),
2077 group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
2078 } {1 1}
2079
2080 do_execsql_test 65.4 {
2081 SELECT COUNT() OVER () LIKE lead(102030) OVER(
2082 ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321)
2083 )
2084 FROM t1;
2085 } {{}}
2086
2087 #-------------------------------------------------------------------------
2088 reset_db
2089
2090 do_execsql_test 66.1 {
2091 CREATE TABLE t1(a INTEGER);
2092 INSERT INTO t1 VALUES(3578824042033200656);
2093 INSERT INTO t1 VALUES(3029012920382354029);
2094 }
2095
2096 foreach {tn spec} {
2097 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2098 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING"
2099 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING"
2100 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2101 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2102 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING"
2103 } {
2104 do_execsql_test 66.2.$tn "
2105 SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a
2106 " {
2107 3.02901292038235e+18 3.5788240420332e+18
2108 }
2109 }
2110
2111
2112 do_execsql_test 66.3 {
2113 CREATE TABLE t2(a INTEGER);
2114 INSERT INTO t2 VALUES(45);
2115 INSERT INTO t2 VALUES(30);
2116 }
2117
2118 foreach {tn spec res} {
2119 1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
2120 2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING" {0.0 0.0}
2121 3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING" {0.0 0.0}
2122 4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
2123 5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
2124 6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0}
2125 } {
2126 do_execsql_test 66.2.$tn "
2127 SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a
2128 " $res
2129 }
2130
2131
2132 #-------------------------------------------------------------------------
2133 reset_db
2134 do_execsql_test 67.0 {
2135 CREATE TABLE t1(a, b, c);
2136 CREATE TABLE t2(a, b, c);
2137 }
2138
2139 do_catchsql_test 67.1 {
2140 SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
2141 SELECT nth_value(a,2) OVER w1
2142 WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
2143 )
2144 } {1 {no such table: v1}}
2145
2146 do_catchsql_test 67.2 {
2147 SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
2148 SELECT nth_value(a,2) OVER w1
2149 WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM t2)) )
2150 )
2151 } {1 {1st ORDER BY term does not match any column in the result set}}
2152
2153 # 2021-05-07
2154 # Do not allow aggregate functions in the ORDER BY clause even if
2155 # there are window functions in the result set.
2156 # Forum: /forumpost/540fdfef77
2157 #
2158 reset_db
2159 do_catchsql_test 68.0 {
2160 CREATE TABLE t1(a,b);
2161 INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99);
2162 SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b);
2163 } {1 {misuse of aggregate: count()}}
2164
2165 # 2021-05-22
2166 # Forum https://sqlite.org/forum/forumpost/7e484e225c
2167 #
2168 reset_db
2169 do_catchsql_test 69.0 {
2170 CREATE TABLE t1(a,b);
2171 CREATE INDEX t1ba ON t1(b,a);
2172 SELECT * FROM t1 WHERE b = (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2173 } {1 {misuse of aggregate: sum()}}
2174 do_catchsql_test 69.1 {
2175 SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2176 } {1 {misuse of aggregate: sum()}}
2177 do_catchsql_test 69.2 {
2178 SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2179 } {1 {misuse of aggregate: sum()}}
2180
2181 # 2021-06-23
2182 # Forum https://sqlite.org/forum/forumpost/31e0432608
2183 #
2184 reset_db
2185 do_execsql_test 70.0 {
2186 CREATE TABLE t1(a);
2187 }
2188 do_execsql_test 70.1 {
2189 SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0;
2190 }
2191 do_execsql_test 70.2 {
2192 SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0;
2193 }
2194
2195 # 2021-11-07
2196 # Bug report from Wang Ke
2197 # https://sqlite.org/forum/forumpost/9ba4f60ff8
2198 reset_db
2199 do_catchsql_test 71.0 {
2200 CREATE TABLE t0(a);
2201 SELECT a FROM t0, (SELECT a AS b FROM t0)
2202 WHERE (a,1)=(SELECT 2,2 UNION SELECT sum(b),max(b) OVER(ORDER BY b) ORDER BY 2)
2203 AND b=4
2204 ORDER BY b;
2205 } {/1 {.*}/}
2206
2207 do_execsql_test 72.1 {
2208 CREATE TABLE dual(dummy); INSERT INTO dual VALUES('X');
2209 CREATE VIEW v1(x,y) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0) FROM dual;
2210 SELECT * FROM v1 WHERE true;
2211 } {1 0}
2212
2213 #-------------------------------------------------------------------------
2214 reset_db
2215
2216 do_execsql_test 72.0 {
2217 CREATE TABLE t0(c0);
2218 INSERT INTO t0(c0) VALUES (0);
2219 CREATE VIEW v0(c0) AS SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0;
2220 }
2221 do_execsql_test 72.1 {
2222 SELECT COUNT(*) FROM (
2223 SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0
2224 )
2225 WHERE ('1' IS NOT ('abcde' NOTNULL));
2226 } {1}
2227
2228 # 2023-03-28 https://sqlite.org/forum/forumpost/dc3b92cfa0 (Song Liu)
2229 #
2230 reset_db
2231 do_execsql_test 73.0 {
2232 CREATE TABLE t1(a INT);
2233 INSERT INTO t1(a) VALUES(1),(2),(4);
2234 CREATE VIEW t2(b,c) AS SELECT * FROM t1 JOIN t1 A ORDER BY sum(0) OVER(PARTITION BY 0);
2235 CREATE TRIGGER x1 INSTEAD OF UPDATE ON t2 BEGIN SELECT true; END;
2236 }
2237 do_execsql_test 73.1 {
2238 SELECT * FROM t2;
2239 } {1 1 1 2 1 4 2 1 2 2 2 4 4 1 4 2 4 4}
2240 do_execsql_test 73.2 {
2241 UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
2242 } {4 99 4 99 4 99}
2243 do_execsql_test 73.3 {
2244 SELECT *, nth_value(15,2) OVER() FROM t2, t1 WHERE b=4;
2245 } {
2246 4 1 1 15
2247 4 2 1 15
2248 4 4 1 15
2249 4 1 2 15
2250 4 2 2 15
2251 4 4 2 15
2252 4 1 4 15
2253 4 2 4 15
2254 4 4 4 15
2255 }
2256 do_execsql_test 73.4 {
2257 UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
2258 } {
2259 4 15
2260 4 15
2261 4 15
2262 4 15
2263 4 15
2264 4 15
2265 4 15
2266 4 15
2267 4 15
2268 }
2269 do_execsql_test 73.5 {
2270 DROP TRIGGER x1;
2271 }
2272 do_catchsql_test 73.6 {
2273 UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
2274 } {1 {cannot modify t2 because it is a view}}
2275 do_catchsql_test 73.7 {
2276 UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
2277 } {1 {cannot modify t2 because it is a view}}
2278
2279 # 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c
2280 #
2281 reset_db
2282 do_execsql_test 74.0 {
2283 CREATE TABLE t1 (a INT, b INT);
2284 CREATE TABLE t2 (c INT, d INT);
2285 CREATE INDEX idx ON t1(abs(a));
2286 INSERT INTO t1 VALUES(1,2),(3,4);
2287 INSERT INTO t2 VALUES(5,6),(7,8);
2288 }
2289 do_execsql_test 74.1 {
2290 SELECT (
2291 SELECT count( a ) FROM t2 LIMIT 1
2292 )
2293 FROM t1;
2294 } {2} ;# Verified using PG 14.2
2295 do_execsql_test 74.2 {
2296 SELECT (
2297 SELECT count( a+c ) FROM t2 LIMIT 1
2298 )
2299 FROM t1;
2300 } {2 2} ;# verified on PG 14.2. Crashes PG 9.6!
2301 do_execsql_test 74.3 {
2302 SELECT (
2303 SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) )
2304 FROM t2 GROUP BY c LIMIT 1
2305 )
2306 FROM t1;
2307 } {1 1} ;# verified on PG 14.2
2308 do_execsql_test 74.4 {
2309 /* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c
2310 CREATE TABLE v0 (c1);
2311 CREATE INDEX i ON v0 (c1, c1=1);
2312 SELECT 0 FROM v0 AS a1
2313 WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) ))))
2314 FROM v0
2315 GROUP BY hex(0))
2316 AND a1.c1=0;
2317 } {}
2318
2319 # 2023-04-11 https://sqlite.org/forum/forumpost/6c5678e3da
2320 # An ALWAYS() turns out to be sometimes false.
2321 #
2322 do_execsql_test 75.0 {
2323 DROP TABLE t1;
2324 CREATE TABLE t1(a INT, b INT);
2325 CREATE INDEX t1x ON t1(a+b);
2326 }
2327 do_catchsql_test 75.1 {
2328 SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
2329 FROM t1 AS a0 JOIN t1 AS a1
2330 GROUP BY a1.a;
2331 } {1 {misuse of aggregate: count()}}
2332
2333 # 2023-04-13 https://sqlite.org/forum/forumpost/0d48347967
2334 reset_db
2335 do_execsql_test 76.0 {
2336 CREATE TABLE t1(a INT, b INT);
2337 INSERT INTO t1(a,b) VALUES (111,222),(111,223),(118,229);
2338 CREATE INDEX t1a ON t1(a);
2339 CREATE TABLE t2(x INT);
2340 INSERT INTO t2 VALUES (333),(444),(555);
2341 }
2342 do_execsql_test 76.1 {
2343 SELECT c, (SELECT c + sum(1) OVER ()) AS "res"
2344 FROM t2 LEFT JOIN (SELECT +a AS c FROM t1) AS v1 ON true
2345 GROUP BY c
2346 ORDER by c;
2347 } {111 112 118 119}
2348 # ^^^^^^^^^^^^^^^^^-- results verified against PG 14.2
2349
2350 do_execsql_test 76.2 {
2351 CREATE TABLE t3(x);
2352 CREATE TABLE t4(y);
2353 INSERT INTO t3 VALUES(100), (200), (400);
2354 INSERT INTO t4 VALUES(100), (300), (400);
2355 }
2356 do_execsql_test 76.3 {
2357 SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y;
2358 } {100 {} 400}
2359 do_execsql_test 76.4 {
2360 SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x;
2361 } {100 {} 400}
2362 do_execsql_test 76.5 {
2363 SELECT (SELECT max(y)+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x;
2364 } {100 {} 400}
2365
2366 # 2023-05-23 https://sqlite.org/forum/forumpost/fbfe330a20
2367 #
2368 reset_db
2369 do_execsql_test 77.1 {
2370 CREATE TABLE t1(x INT);
2371 CREATE INDEX t1x ON t1(likely(x));
2372 INSERT INTO t1 VALUES(1),(2),(4),(8);
2373 }
2374 do_execsql_test 77.2 {
2375 SELECT max(~likely(x)) FILTER (WHERE true) FROM t1 INDEXED BY t1x GROUP BY x;
2376 } {-2 -3 -5 -9}
2377
2378 # 2024-05-23 https://sqlite.org/forum/forumpost/bf8f43aa522c2299
2379 #
2380 # A bug in group_concat() when used as a window function, reported
2381 # just hours after the 3.46.0 release, though first appearing
2382 # in 3.28.0.
2383 #
2384 # When used as a window function, a group_concat() was not
2385 # correctly distinguishing between NULL and empty-string for
2386 # its return value.
2387 #
2388 do_execsql_test 78.1 {
2389 SELECT quote(group_concat(x) OVER ()) FROM (SELECT '' AS x);
2390 } ''
2391 do_execsql_test 78.2 {
2392 SELECT quote(group_concat(x) OVER (
2393 ORDER BY y RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING
2394 )) FROM (SELECT 'abc' AS x, 1 AS y);
2395 } NULL
2396
2397 #-------------------------------------------------------------------------
2398 # Test that the following queries do not run for a very long time.
2399 #
2400 # https://sqlite.org/forum/forumpost/b1993c858f
2401 #
2402 do_execsql_test 79.0 {
2403 CREATE TABLE t0 (c0 INTEGER );
2404 INSERT INTO t0 VALUES(1);
2405 INSERT INTO t0 VALUES(2);
2406 INSERT INTO t0 VALUES(3);
2407 }
2408
2409 do_execsql_test 79.1 {
2410 SELECT COUNT(*)
2411 OVER ( ROWS BETWEEN 0 FOLLOWING AND 100 FOLLOWING)
2412 FROM t0;
2413 } {3 2 1}
2414
2415 do_execsql_test 79.2 {
2416 SELECT COUNT(*)
2417 OVER ( ORDER BY c0 RANGE BETWEEN 0 FOLLOWING AND 10_000_000_000 FOLLOWING )
2418 FROM t0;
2419 } {3 2 1}
2420
2421 do_execsql_test 79.3 {
2422 SELECT sum(c0), COUNT(*)
2423 OVER ( ORDER BY c0 RANGE BETWEEN 0 FOLLOWING AND 10_000_000_000 FOLLOWING )
2424 FROM t0;
2425 } {6 1}
2426
2427 finish_test