Debian Codegraph @

test/windowE.test

raw
1 # 2022 October 18
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 #
12
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix windowE
16
17 proc custom {a b} { return [string compare $a $b] }
18 db collate custom custom
19
20 do_execsql_test 1.0 {
21 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT COLLATE custom);
22 INSERT INTO t1 VALUES(1, 'one');
23 INSERT INTO t1 VALUES(2, 'two');
24 INSERT INTO t1 VALUES(3, 'three');
25 INSERT INTO t1 VALUES(4, 'four');
26 INSERT INTO t1 VALUES(5, 'five');
27 INSERT INTO t1 VALUES(6, 'six');
28 CREATE INDEX t1b ON t1(b);
29 }
30
31 do_execsql_test 1.1 {
32 SELECT * FROM t1
33 } {
34 1 one 2 two 3 three 4 four 5 five 6 six
35 }
36
37 do_execsql_test 1.2 {
38 SELECT group_concat(a,',') OVER win FROM t1
39 WINDOW win AS (
40 ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
41 )
42 } {
43 5 4 1 6 3 2
44 }
45
46 proc custom {a b} { return [string compare $b $a] }
47
48 do_execsql_test 1.3 {
49 SELECT group_concat(a,',') OVER win FROM t1
50 WINDOW win AS (
51 ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
52 )
53 } {
54 5 5,4 5,4,1 5,4,1,6 5,4,1,6,3 5,4,1,6,3,2
55 }
56
57 #-------------------------------------------------------------------------
58 reset_db
59 do_execsql_test 2.0 {
60 CREATE TABLE t1(x);
61 }
62
63 sqlite3_create_aggregate db
64
65 do_catchsql_test 2.1 {
66 SELECT min(x) OVER w1 FROM t1
67 WINDOW w1 AS (PARTITION BY x_count(x) OVER w1);
68 } {1 {x_count() may not be used as a window function}}
69
70 do_catchsql_test 2.2 {
71 SELECT min(x) FILTER (WHERE x_count(x) OVER w1) OVER w1 FROM t1
72 WINDOW w1 AS (PARTITION BY x OVER w1);
73 } {1 {near "OVER": syntax error}}
74
75 #-------------------------------------------------------------------------
76 reset_db
77 do_execsql_test 3.0 {
78 BEGIN TRANSACTION;
79 CREATE TABLE t2(c1 INT, c2 REAL);
80 INSERT INTO t2 VALUES
81 (447,0.0), (448,0.0), (449,0.0), (452,0.0), (453,0.0), (454,0.0), (455,0.0),
82 (456,0.0), (459,0.0), (460,0.0), (462,0.0), (463,0.0), (466,0.0), (467,0.0),
83 (468,0.0), (469,0.0), (470,0.0), (473,0.0), (474,0.0), (475,0.0), (476,0.0),
84 (477,0.0), (480,0.0), (481,0.0), (482,0.0), (483,0.0), (484,0.0), (487,0.0),
85 (488,0.0), (489,0.0), (490,0.0), (491,0.0), (494,0.0), (495,0.0), (496,0.0),
86 (497,0.0), (498,0.0), (501,0.0), (502,0.0), (503,0.0), (504,0.0), (505,0.0),
87 (508,0.0), (509,0.0), (510,0.0), (511,0.0), (512,0.0), (515,0.0), (516,0.0),
88 (517,0.0), (518,0.0), (519,0.0), (522,0.0), (523,0.0), (524,0.0), (525,0.0),
89 (526,0.0), (529,0.0), (530,0.0), (531,0.0), (532,0.0), (533,0.0), (536,0.0),
90 (537,1.0), (538,0.0), (539,0.0), (540,0.0), (543,0.0), (544,0.0);
91 COMMIT;
92 }
93
94 do_execsql_test 3.1 {
95 select c1, max(c2) over (order by c1 range 366.0 preceding) from t2;
96 } {
97 447 0.0 448 0.0 449 0.0 452 0.0 453 0.0 454 0.0 455 0.0 456 0.0 459 0.0
98 460 0.0 462 0.0 463 0.0 466 0.0 467 0.0 468 0.0 469 0.0 470 0.0 473 0.0
99 474 0.0 475 0.0 476 0.0 477 0.0 480 0.0 481 0.0 482 0.0 483 0.0 484 0.0
100 487 0.0 488 0.0 489 0.0 490 0.0 491 0.0 494 0.0 495 0.0 496 0.0 497 0.0
101 498 0.0 501 0.0 502 0.0 503 0.0 504 0.0 505 0.0 508 0.0 509 0.0 510 0.0
102 511 0.0 512 0.0 515 0.0 516 0.0 517 0.0 518 0.0 519 0.0 522 0.0 523 0.0
103 524 0.0 525 0.0 526 0.0 529 0.0 530 0.0 531 0.0 532 0.0 533 0.0 536 0.0
104 537 1.0 538 1.0 539 1.0 540 1.0 543 1.0 544 1.0
105 }
106
107 #-------------------------------------------------------------------------
108 reset_db
109 do_execsql_test 4.0 {
110 CREATE TABLE t1(a, b);
111 INSERT INTO t1 VALUES(1, 1);
112 INSERT INTO t1 VALUES(2, 9223372036854775807);
113 INSERT INTO t1 VALUES(3, 3);
114 INSERT INTO t1 VALUES(4, 4);
115 }
116
117 do_execsql_test 4.1 {
118 SELECT a,
119 total(b) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
120 FROM t1;
121 } {
122 1 9.22337203685478e+18 2 9.22337203685478e+18 3 7.0 4 4.0
123 }
124
125 do_execsql_test 4.2 {
126 SELECT a,
127 total(b) OVER (ORDER BY a ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
128 FROM t1;
129 } {
130 1 9.22337203685478e+18 2 9.22337203685478e+18 3 7.0 4 4.0
131 }
132
133 #-------------------------------------------------------------------------
134 reset_db
135 do_execsql_test 5.0 {
136 CREATE TABLE t(id, x);
137 INSERT INTO t VALUES(1, -1);
138 INSERT INTO t VALUES(2, 9223372036854775807);
139 INSERT INTO t VALUES(3, 1);
140 INSERT INTO t VALUES(4, 0.5);
141 }
142
143 do_execsql_test 5.1 {
144 SELECT id,
145 sum(id) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
146 FROM t t1
147 } {
148 1 6
149 2 9
150 3 7
151 4 4
152 }
153
154 breakpoint
155 do_execsql_test 5.2 {
156 SELECT id,
157 sum(x) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
158 FROM t t1
159 } {
160 1 9223372036854775807
161 2 9.22337203685478e+18
162 3 1.5
163 4 0.5
164 }
165
166 finish_test
167