Debian Codegraph @

test/window4.test

raw
1 # 2018 June 04
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 ####################################################
15 # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
16 ####################################################
17
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set testprefix window4
21
22 ifcapable !windowfunc { finish_test ; return }
23 do_execsql_test 1.0 {
24 DROP TABLE IF EXISTS t3;
25 CREATE TABLE t3(a TEXT PRIMARY KEY);
26 INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e');
27 INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j');
28 } {}
29
30 do_execsql_test 1.1 {
31 SELECT a, ntile(1) OVER (ORDER BY a) FROM t3
32 } {a 1 b 1 c 1 d 1 e 1 f 1 g 1 h 1 i 1 j 1}
33
34 do_execsql_test 1.2 {
35 SELECT a, ntile(2) OVER (ORDER BY a) FROM t3
36 } {a 1 b 1 c 1 d 1 e 1 f 2 g 2 h 2 i 2 j 2}
37
38 do_execsql_test 1.3 {
39 SELECT a, ntile(3) OVER (ORDER BY a) FROM t3
40 } {a 1 b 1 c 1 d 1 e 2 f 2 g 2 h 3 i 3 j 3}
41
42 do_execsql_test 1.4 {
43 SELECT a, ntile(4) OVER (ORDER BY a) FROM t3
44 } {a 1 b 1 c 1 d 2 e 2 f 2 g 3 h 3 i 4 j 4}
45
46 do_execsql_test 1.5 {
47 SELECT a, ntile(5) OVER (ORDER BY a) FROM t3
48 } {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 5}
49
50 do_execsql_test 1.6 {
51 SELECT a, ntile(6) OVER (ORDER BY a) FROM t3
52 } {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 6}
53
54 do_execsql_test 1.7 {
55 SELECT a, ntile(7) OVER (ORDER BY a) FROM t3
56 } {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 5 i 6 j 7}
57
58 do_execsql_test 1.8 {
59 SELECT a, ntile(8) OVER (ORDER BY a) FROM t3
60 } {a 1 b 1 c 2 d 2 e 3 f 4 g 5 h 6 i 7 j 8}
61
62 do_execsql_test 1.9 {
63 SELECT a, ntile(9) OVER (ORDER BY a) FROM t3
64 } {a 1 b 1 c 2 d 3 e 4 f 5 g 6 h 7 i 8 j 9}
65
66 do_execsql_test 1.10 {
67 SELECT a, ntile(10) OVER (ORDER BY a) FROM t3
68 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
69
70 do_execsql_test 1.11 {
71 SELECT a, ntile(11) OVER (ORDER BY a) FROM t3
72 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
73
74 do_execsql_test 1.12 {
75 SELECT a, ntile(12) OVER (ORDER BY a) FROM t3
76 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
77
78 do_execsql_test 1.13 {
79 SELECT a, ntile(13) OVER (ORDER BY a) FROM t3
80 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
81
82 do_execsql_test 1.14 {
83 SELECT a, ntile(14) OVER (ORDER BY a) FROM t3
84 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
85
86 do_execsql_test 1.15 {
87 SELECT a, ntile(15) OVER (ORDER BY a) FROM t3
88 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
89
90 do_execsql_test 1.16 {
91 SELECT a, ntile(16) OVER (ORDER BY a) FROM t3
92 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
93
94 do_execsql_test 1.17 {
95 SELECT a, ntile(17) OVER (ORDER BY a) FROM t3
96 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
97
98 do_execsql_test 1.18 {
99 SELECT a, ntile(18) OVER (ORDER BY a) FROM t3
100 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
101
102 do_execsql_test 1.19 {
103 SELECT a, ntile(19) OVER (ORDER BY a) FROM t3
104 } {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10}
105
106 do_execsql_test 2.0 {
107 DROP TABLE IF EXISTS t4;
108 CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER);
109 INSERT INTO t4 VALUES(1, 'A', 9);
110 INSERT INTO t4 VALUES(2, 'B', 3);
111 INSERT INTO t4 VALUES(3, 'C', 2);
112 INSERT INTO t4 VALUES(4, 'D', 10);
113 INSERT INTO t4 VALUES(5, 'E', 5);
114 INSERT INTO t4 VALUES(6, 'F', 1);
115 INSERT INTO t4 VALUES(7, 'G', 1);
116 INSERT INTO t4 VALUES(8, 'H', 2);
117 INSERT INTO t4 VALUES(9, 'I', 10);
118 INSERT INTO t4 VALUES(10, 'J', 4);
119 } {}
120
121 do_execsql_test 2.1 {
122 SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4
123 } {1 {} 2 {} 3 B 4 {} 5 E 6 A 7 A 8 B 9 {} 10 D}
124
125 do_execsql_test 2.2.1 {
126 SELECT a, lead(b) OVER (ORDER BY a) FROM t4
127 } {1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 {}}
128
129 do_execsql_test 2.2.2 {
130 SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4
131 } {1 C 2 D 3 E 4 F 5 G 6 H 7 I 8 J 9 {} 10 {}}
132
133 do_execsql_test 2.2.3 {
134 SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4
135 } {1 D 2 E 3 F 4 G 5 H 6 I 7 J 8 abc 9 abc 10 abc}
136
137 do_execsql_test 2.3.1 {
138 SELECT a, lag(b) OVER (ORDER BY a) FROM t4
139 } {1 {} 2 A 3 B 4 C 5 D 6 E 7 F 8 G 9 H 10 I}
140
141 do_execsql_test 2.3.2 {
142 SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
143 } {1 {} 2 {} 3 A 4 B 5 C 6 D 7 E 8 F 9 G 10 H}
144
145 do_execsql_test 2.3.3 {
146 SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
147 } {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G}
148
149 do_execsql_test 2.4.1 {
150 SELECT group_concat(b, '.') OVER (
151 ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
152 ) FROM t4
153 } {A.B.C.D.E.F.G.H.I.J B.C.D.E.F.G.H.I.J C.D.E.F.G.H.I.J D.E.F.G.H.I.J
154 E.F.G.H.I.J F.G.H.I.J G.H.I.J H.I.J I.J J}
155
156 do_execsql_test 3.0 {
157 DROP TABLE IF EXISTS t5;
158 CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
159 INSERT INTO t5 VALUES(1, 'A', 'one', 5);
160 INSERT INTO t5 VALUES(2, 'B', 'two', 4);
161 INSERT INTO t5 VALUES(3, 'A', 'three', 3);
162 INSERT INTO t5 VALUES(4, 'B', 'four', 2);
163 INSERT INTO t5 VALUES(5, 'A', 'five', 1);
164 } {}
165
166 do_execsql_test 3.1 {
167 SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5
168 } {1 {} 3 five 5 one 2 two 4 three}
169
170 do_execsql_test 3.2 {
171 SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5
172 } {1 {} 3 {} 5 one 2 {} 4 four}
173
174 do_execsql_test 3.3 {
175 SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
176 WINDOW abc AS (ORDER BY a),
177 def AS (ORDER BY a DESC)
178 ORDER BY a;
179 } {1 1 5 2 2 4 3 3 3 4 4 2 5 5 1}
180
181 do_execsql_test 3.4 {
182 SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5
183 WINDOW w AS (ORDER BY a)
184 } {1 {} 2 2 3 2 4 4 5 4}
185
186 do_execsql_test 3.5.1 {
187 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING)
188 FROM t5
189 } {1 {} 2 {} 3 {} 4 {} 5 {}}
190
191 do_execsql_test 3.5.2 {
192 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
193 FROM t5
194 } {1 {} 2 one 3 two 4 three 5 four}
195
196 do_execsql_test 3.5.3 {
197 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 PRECEDING AND 0 PRECEDING)
198 FROM t5
199 } {1 one 2 two 3 three 4 four 5 five}
200
201 do_execsql_test 3.6.1 {
202 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 1 FOLLOWING)
203 FROM t5
204 } {1 {} 2 {} 3 {} 4 {} 5 {}}
205
206 do_execsql_test 3.6.2 {
207 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
208 FROM t5
209 } {1 two 2 three 3 four 4 five 5 {}}
210
211 do_execsql_test 3.6.3 {
212 SELECT a, max(c) OVER (ORDER BY a ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING)
213 FROM t5
214 } {1 one 2 two 3 three 4 four 5 five}
215
216 #==========================================================================
217
218 do_execsql_test 4.0 {
219 DROP TABLE IF EXISTS ttt;
220 CREATE TABLE ttt(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
221 INSERT INTO ttt VALUES(1, 1, 1);
222 INSERT INTO ttt VALUES(2, 2, 2);
223 INSERT INTO ttt VALUES(3, 3, 3);
224
225 INSERT INTO ttt VALUES(4, 1, 2);
226 INSERT INTO ttt VALUES(5, 2, 3);
227 INSERT INTO ttt VALUES(6, 3, 4);
228
229 INSERT INTO ttt VALUES(7, 1, 3);
230 INSERT INTO ttt VALUES(8, 2, 4);
231 INSERT INTO ttt VALUES(9, 3, 5);
232 } {}
233
234 do_execsql_test 4.1 {
235 SELECT max(c), max(b) OVER (ORDER BY b) FROM ttt GROUP BY b;
236 } {3 1 4 2 5 3}
237
238 do_execsql_test 4.2 {
239 SELECT max(b) OVER (ORDER BY max(c)) FROM ttt GROUP BY b;
240 } {1 2 3}
241
242 do_execsql_test 4.3 {
243 SELECT abs(max(b) OVER (ORDER BY b)) FROM ttt GROUP BY b;
244 } {1 2 3}
245
246 do_execsql_test 4.4 {
247 SELECT sum(b) OVER (
248 ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
249 ) FROM ttt;
250 } {18 17 15 12 11 9 6 5 3}
251
252 do_execsql_test 4.5.1.1 {
253 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
254 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
255 FROM ttt ORDER BY a
256 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
257
258 do_execsql_test 4.5.1.2 {
259 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
260 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
261 FROM ttt ORDER BY a
262 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
263
264 do_execsql_test 4.5.2.1 {
265 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
266 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
267 FROM ttt ORDER BY a
268 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
269
270 do_execsql_test 4.5.2.2 {
271 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
272 sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
273 FROM ttt ORDER BY a
274 } {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
275
276 do_execsql_test 4.5.3.1 {
277 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
278 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
279 FROM ttt ORDER BY a
280 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
281
282 do_execsql_test 4.5.3.2 {
283 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
284 sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
285 FROM ttt ORDER BY a
286 } {1 1 2 3 3 6 3 8 5 11 7 15 6 18 9 22 12 27}
287
288 do_execsql_test 4.5.4.1 {
289 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
290 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
291 FROM ttt ORDER BY a
292 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
293
294 do_execsql_test 4.5.4.2 {
295 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
296 sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
297 FROM ttt ORDER BY a
298 } {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
299
300 do_execsql_test 4.5.5.1 {
301 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
302 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
303 FROM ttt ORDER BY a
304 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
305
306 do_execsql_test 4.5.5.2 {
307 SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
308 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
309 FROM ttt ORDER BY a
310 } {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
311
312 do_execsql_test 4.5.6.1 {
313 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
314 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
315 FROM ttt ORDER BY a
316 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
317
318 do_execsql_test 4.5.6.2 {
319 SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
320 sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
321 FROM ttt ORDER BY a
322 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
323
324 do_execsql_test 4.5.7.1 {
325 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
326 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
327 FROM ttt ORDER BY a
328 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
329
330 do_execsql_test 4.5.7.2 {
331 SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
332 sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
333 FROM ttt ORDER BY a
334 } {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27}
335
336 do_execsql_test 4.5.8.1 {
337 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
338 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
339 FROM ttt ORDER BY a
340 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
341
342 do_execsql_test 4.5.8.2 {
343 SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
344 sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
345 FROM ttt ORDER BY a
346 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
347
348 do_execsql_test 4.5.9.1 {
349 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
350 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
351 FROM ttt ORDER BY a
352 } {1 1 2 2 3 3 3 1 3 2 4 3 4 1 4 2 5 3}
353
354 do_execsql_test 4.5.9.2 {
355 SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
356 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
357 FROM ttt ORDER BY a
358 } {1 1 3 2 6 3 8 3 11 5 15 7 18 6 22 9 27 12}
359
360 do_execsql_test 4.5.10.1 {
361 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
362 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
363 FROM ttt ORDER BY a
364 } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5}
365
366 do_execsql_test 4.5.10.2 {
367 SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
368 sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
369 FROM ttt ORDER BY a
370 } {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5}
371
372 do_execsql_test 4.5.11.1 {
373 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
374 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
375 FROM ttt ORDER BY a
376 } {1 1 2 1 3 1 3 1 3 1 4 1 4 1 4 1 5 1}
377
378 do_execsql_test 4.5.11.2 {
379 SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
380 sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
381 FROM ttt ORDER BY a
382 } {1 1 3 3 6 6 8 8 11 11 15 15 18 18 22 22 27 27}
383
384 do_execsql_test 4.5.12.1 {
385 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
386 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
387 FROM ttt ORDER BY a
388 } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5}
389
390 do_execsql_test 4.5.12.2 {
391 SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
392 sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
393 FROM ttt ORDER BY a
394 } {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5}
395
396 do_execsql_test 4.5.13.1 {
397 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
398 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
399 FROM ttt ORDER BY a
400 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
401
402 do_execsql_test 4.5.13.2 {
403 SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
404 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
405 FROM ttt ORDER BY a
406 } {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
407
408 do_execsql_test 4.5.14.1 {
409 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
410 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
411 FROM ttt ORDER BY a
412 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
413
414 do_execsql_test 4.5.14.2 {
415 SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
416 sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
417 FROM ttt ORDER BY a
418 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
419
420 do_execsql_test 4.5.15.1 {
421 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
422 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
423 FROM ttt ORDER BY a
424 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1}
425
426 do_execsql_test 4.5.15.2 {
427 SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
428 sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
429 FROM ttt ORDER BY a
430 } {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27}
431
432 do_execsql_test 4.5.16.1 {
433 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
434 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
435 FROM ttt ORDER BY a
436 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
437
438 do_execsql_test 4.5.16.2 {
439 SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
440 sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
441 FROM ttt ORDER BY a
442 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
443
444 do_execsql_test 4.5.17.1 {
445 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
446 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
447 FROM ttt ORDER BY a
448 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
449
450 do_execsql_test 4.5.17.2 {
451 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
452 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
453 FROM ttt ORDER BY a
454 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
455
456 do_execsql_test 4.5.18.1 {
457 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
458 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
459 FROM ttt ORDER BY a
460 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
461
462 do_execsql_test 4.5.18.2 {
463 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
464 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
465 FROM ttt ORDER BY a
466 } {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
467
468 do_execsql_test 4.5.19.1 {
469 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
470 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
471 FROM ttt ORDER BY a
472 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
473
474 do_execsql_test 4.5.19.2 {
475 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
476 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
477 FROM ttt ORDER BY a
478 } {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5}
479
480 do_execsql_test 4.5.20.1 {
481 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
482 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
483 FROM ttt ORDER BY a
484 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
485
486 do_execsql_test 4.5.20.2 {
487 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
488 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
489 FROM ttt ORDER BY a
490 } {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
491
492 do_execsql_test 4.5.21.1 {
493 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
494 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
495 FROM ttt ORDER BY a
496 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
497
498 do_execsql_test 4.5.21.2 {
499 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
500 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
501 FROM ttt ORDER BY a
502 } {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
503
504 do_execsql_test 4.5.22.1 {
505 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
506 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
507 FROM ttt ORDER BY a
508 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
509
510 do_execsql_test 4.5.22.2 {
511 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
512 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
513 FROM ttt ORDER BY a
514 } {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
515
516 do_execsql_test 4.5.23.1 {
517 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
518 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
519 FROM ttt ORDER BY a
520 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
521
522 do_execsql_test 4.5.23.2 {
523 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
524 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
525 FROM ttt ORDER BY a
526 } {6 1 9 2 12 3 6 2 9 3 12 4 6 3 9 4 12 5}
527
528 do_execsql_test 4.5.24.1 {
529 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
530 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
531 FROM ttt ORDER BY a
532 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
533
534 do_execsql_test 4.5.24.2 {
535 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
536 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
537 FROM ttt ORDER BY a
538 } {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5}
539
540 do_execsql_test 4.5.25.1 {
541 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
542 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
543 FROM ttt ORDER BY a
544 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
545
546 do_execsql_test 4.5.25.2 {
547 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
548 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
549 FROM ttt ORDER BY a
550 } {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12}
551
552 do_execsql_test 4.5.26.1 {
553 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
554 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
555 FROM ttt ORDER BY a
556 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
557
558 do_execsql_test 4.5.26.2 {
559 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
560 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
561 FROM ttt ORDER BY a
562 } {1 6 2 9 3 12 2 6 3 9 4 12 3 6 4 9 5 12}
563
564 do_execsql_test 4.5.27.1 {
565 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
566 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
567 FROM ttt ORDER BY a
568 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
569
570 do_execsql_test 4.5.27.2 {
571 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
572 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
573 FROM ttt ORDER BY a
574 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
575
576 do_execsql_test 4.5.28.1 {
577 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
578 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
579 FROM ttt ORDER BY a
580 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
581
582 do_execsql_test 4.5.28.2 {
583 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW),
584 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
585 FROM ttt ORDER BY a
586 } {1 6 2 9 3 12 2 5 3 7 4 9 3 3 4 4 5 5}
587
588 do_execsql_test 4.5.29.1 {
589 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
590 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
591 FROM ttt ORDER BY a
592 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
593
594 do_execsql_test 4.5.29.2 {
595 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
596 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
597 FROM ttt ORDER BY a
598 } {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
599
600 do_execsql_test 4.5.30.1 {
601 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
602 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
603 FROM ttt ORDER BY a
604 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
605
606 do_execsql_test 4.5.30.2 {
607 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
608 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
609 FROM ttt ORDER BY a
610 } {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12}
611
612 do_execsql_test 4.5.31.1 {
613 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
614 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
615 FROM ttt ORDER BY a
616 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
617
618 do_execsql_test 4.5.31.2 {
619 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
620 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
621 FROM ttt ORDER BY a
622 } {6 1 9 2 12 3 5 2 7 3 9 4 3 3 4 4 5 5}
623
624 do_execsql_test 4.5.32.1 {
625 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
626 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
627 FROM ttt ORDER BY a
628 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
629
630 do_execsql_test 4.5.32.2 {
631 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
632 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
633 FROM ttt ORDER BY a
634 } {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5}
635
636 do_execsql_test 4.5.33.1 {
637 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
638 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
639 FROM ttt ORDER BY a
640 } {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3}
641
642 do_execsql_test 4.5.33.2 {
643 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
644 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
645 FROM ttt ORDER BY a
646 } {3 3 5 5 7 7 6 6 9 9 12 12 6 6 9 9 12 12}
647
648 do_execsql_test 4.5.34.1 {
649 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
650 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
651 FROM ttt ORDER BY a
652 } {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3}
653
654 do_execsql_test 4.5.34.2 {
655 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
656 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
657 FROM ttt ORDER BY a
658 } {3 6 5 9 7 12 6 6 9 9 12 12 6 6 9 9 12 12}
659
660 do_execsql_test 4.5.35.1 {
661 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
662 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
663 FROM ttt ORDER BY a
664 } {2 {} 3 {} 4 {} 3 1 4 2 5 3 3 2 4 3 5 4}
665
666 do_execsql_test 4.5.35.2 {
667 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
668 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
669 FROM ttt ORDER BY a
670 } {3 {} 5 {} 7 {} 6 1 9 2 12 3 6 2 9 3 12 4}
671
672 do_execsql_test 4.5.36.1 {
673 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
674 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
675 FROM ttt ORDER BY a
676 } {2 {} 3 {} 4 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}}
677
678 do_execsql_test 4.5.36.2 {
679 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
680 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
681 FROM ttt ORDER BY a
682 } {3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}}
683
684 do_execsql_test 4.5.37.1 {
685 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
686 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
687 FROM ttt ORDER BY a
688 } {2 2 3 3 4 4 3 3 4 4 5 5 3 {} 4 {} 5 {}}
689
690 do_execsql_test 4.5.37.2 {
691 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING),
692 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
693 FROM ttt ORDER BY a
694 } {3 5 5 7 7 9 6 3 9 4 12 5 6 {} 9 {} 12 {}}
695
696 do_execsql_test 4.5.38.1 {
697 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
698 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
699 FROM ttt ORDER BY a
700 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
701
702 do_execsql_test 4.5.38.2 {
703 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
704 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
705 FROM ttt ORDER BY a
706 } {6 3 9 5 12 7 6 6 9 9 12 12 6 6 9 9 12 12}
707
708 do_execsql_test 4.5.39.1 {
709 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
710 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
711 FROM ttt ORDER BY a
712 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
713
714 do_execsql_test 4.5.39.2 {
715 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
716 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
717 FROM ttt ORDER BY a
718 } {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
719
720 do_execsql_test 4.5.40.1 {
721 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
722 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
723 FROM ttt ORDER BY a
724 } {3 {} 4 {} 5 {} 3 1 4 2 5 3 3 2 4 3 5 4}
725
726 do_execsql_test 4.5.40.2 {
727 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
728 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
729 FROM ttt ORDER BY a
730 } {6 {} 9 {} 12 {} 6 1 9 2 12 3 6 2 9 3 12 4}
731
732 do_execsql_test 4.5.41.1 {
733 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
734 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
735 FROM ttt ORDER BY a
736 } {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}}
737
738 do_execsql_test 4.5.41.2 {
739 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
740 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
741 FROM ttt ORDER BY a
742 } {6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}}
743
744 do_execsql_test 4.5.42.1 {
745 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
746 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
747 FROM ttt ORDER BY a
748 } {3 2 4 3 5 4 3 3 4 4 5 5 3 {} 4 {} 5 {}}
749
750 do_execsql_test 4.5.42.2 {
751 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING),
752 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
753 FROM ttt ORDER BY a
754 } {6 5 9 7 12 9 6 3 9 4 12 5 6 {} 9 {} 12 {}}
755
756 do_execsql_test 4.5.43.1 {
757 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
758 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
759 FROM ttt ORDER BY a
760 } {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3}
761
762 do_execsql_test 4.5.43.2 {
763 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
764 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
765 FROM ttt ORDER BY a
766 } {{} 3 {} 5 {} 7 1 6 2 9 3 12 2 6 3 9 4 12}
767
768 do_execsql_test 4.5.44.1 {
769 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
770 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
771 FROM ttt ORDER BY a
772 } {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3}
773
774 do_execsql_test 4.5.44.2 {
775 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
776 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
777 FROM ttt ORDER BY a
778 } {{} 6 {} 9 {} 12 1 6 2 9 3 12 2 6 3 9 4 12}
779
780 do_execsql_test 4.5.45.1 {
781 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
782 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
783 FROM ttt ORDER BY a
784 } {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4}
785
786 do_execsql_test 4.5.45.2 {
787 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
788 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
789 FROM ttt ORDER BY a
790 } {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4}
791
792 do_execsql_test 4.5.46.1 {
793 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
794 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
795 FROM ttt ORDER BY a
796 } {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}}
797
798 do_execsql_test 4.5.46.2 {
799 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
800 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
801 FROM ttt ORDER BY a
802 } {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}}
803
804 do_execsql_test 4.5.47.1 {
805 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
806 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
807 FROM ttt ORDER BY a
808 } {{} 2 {} 3 {} 4 1 3 2 4 3 5 2 {} 3 {} 4 {}}
809
810 do_execsql_test 4.5.47.2 {
811 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
812 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
813 FROM ttt ORDER BY a
814 } {{} 5 {} 7 {} 9 1 3 2 4 3 5 2 {} 3 {} 4 {}}
815
816 do_execsql_test 4.5.48.1 {
817 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
818 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
819 FROM ttt ORDER BY a
820 } {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3}
821
822 do_execsql_test 4.5.48.2 {
823 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
824 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
825 FROM ttt ORDER BY a
826 } {{} 3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12}
827
828 do_execsql_test 4.5.49.1 {
829 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
830 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
831 FROM ttt ORDER BY a
832 } {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3}
833
834 do_execsql_test 4.5.49.2 {
835 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
836 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
837 FROM ttt ORDER BY a
838 } {{} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12}
839
840 do_execsql_test 4.5.50.1 {
841 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
842 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
843 FROM ttt ORDER BY a
844 } {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4}
845
846 do_execsql_test 4.5.50.2 {
847 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
848 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
849 FROM ttt ORDER BY a
850 } {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4}
851
852 do_execsql_test 4.5.51.1 {
853 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
854 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
855 FROM ttt ORDER BY a
856 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
857
858 do_execsql_test 4.5.51.2 {
859 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
860 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
861 FROM ttt ORDER BY a
862 } {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
863
864 do_execsql_test 4.5.52.1 {
865 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
866 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
867 FROM ttt ORDER BY a
868 } {{} 2 {} 3 {} 4 {} 3 {} 4 {} 5 {} {} {} {} {} {}}
869
870 do_execsql_test 4.5.52.2 {
871 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING),
872 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
873 FROM ttt ORDER BY a
874 } {{} 5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {}}
875
876 do_execsql_test 4.5.53.1 {
877 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
878 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
879 FROM ttt ORDER BY a
880 } {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3}
881
882 do_execsql_test 4.5.53.2 {
883 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
884 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING)
885 FROM ttt ORDER BY a
886 } {5 3 7 5 9 7 3 6 4 9 5 12 {} 6 {} 9 {} 12}
887
888 do_execsql_test 4.5.54.1 {
889 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
890 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
891 FROM ttt ORDER BY a
892 } {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3}
893
894 do_execsql_test 4.5.54.2 {
895 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
896 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
897 FROM ttt ORDER BY a
898 } {5 6 7 9 9 12 3 6 4 9 5 12 {} 6 {} 9 {} 12}
899
900 do_execsql_test 4.5.55.1 {
901 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
902 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
903 FROM ttt ORDER BY a
904 } {3 {} 4 {} 5 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4}
905
906 do_execsql_test 4.5.55.2 {
907 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
908 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
909 FROM ttt ORDER BY a
910 } {5 {} 7 {} 9 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4}
911
912 do_execsql_test 4.5.56.1 {
913 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
914 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
915 FROM ttt ORDER BY a
916 } {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}}
917
918 do_execsql_test 4.5.56.2 {
919 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
920 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING)
921 FROM ttt ORDER BY a
922 } {5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}}
923
924 do_execsql_test 4.5.57.1 {
925 SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
926 min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
927 FROM ttt ORDER BY a
928 } {3 2 4 3 5 4 3 3 4 4 5 5 {} {} {} {} {} {}}
929
930 do_execsql_test 4.5.57.2 {
931 SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING),
932 sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING)
933 FROM ttt ORDER BY a
934 } {5 5 7 7 9 9 3 3 4 4 5 5 {} {} {} {} {} {}}
935
936 do_execsql_test 4.5.58.1 {
937 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
938 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
939 FROM ttt ORDER BY a
940 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
941
942 do_execsql_test 4.5.58.2 {
943 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
944 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
945 FROM ttt ORDER BY a
946 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
947
948 do_execsql_test 4.5.59.1 {
949 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
950 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
951 FROM ttt ORDER BY a
952 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
953
954 do_execsql_test 4.5.59.2 {
955 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
956 sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
957 FROM ttt ORDER BY a
958 } {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
959
960 do_execsql_test 4.5.60.1 {
961 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
962 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
963 FROM ttt ORDER BY a
964 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
965
966 do_execsql_test 4.5.60.2 {
967 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
968 sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
969 FROM ttt ORDER BY a
970 } {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
971
972 do_execsql_test 4.5.61.1 {
973 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
974 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
975 FROM ttt ORDER BY a
976 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
977
978 do_execsql_test 4.5.61.2 {
979 SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
980 sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
981 FROM ttt ORDER BY a
982 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
983
984 do_execsql_test 4.5.62.1 {
985 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
986 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
987 FROM ttt ORDER BY a
988 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
989
990 do_execsql_test 4.5.62.2 {
991 SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
992 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
993 FROM ttt ORDER BY a
994 } {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
995
996 do_execsql_test 4.5.63.1 {
997 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
998 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
999 FROM ttt ORDER BY a
1000 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
1001
1002 do_execsql_test 4.5.63.2 {
1003 SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1004 sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1005 FROM ttt ORDER BY a
1006 } {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5}
1007
1008 do_execsql_test 4.5.64.1 {
1009 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1010 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1011 FROM ttt ORDER BY a
1012 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
1013
1014 do_execsql_test 4.5.64.2 {
1015 SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1016 sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1017 FROM ttt ORDER BY a
1018 } {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12}
1019
1020 do_execsql_test 4.5.65.1 {
1021 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1022 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1023 FROM ttt ORDER BY a
1024 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
1025
1026 do_execsql_test 4.5.65.2 {
1027 SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1028 sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1029 FROM ttt ORDER BY a
1030 } {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12}
1031
1032 do_execsql_test 4.5.66.1 {
1033 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1034 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1035 FROM ttt ORDER BY a
1036 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
1037
1038 do_execsql_test 4.5.66.2 {
1039 SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1040 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1041 FROM ttt ORDER BY a
1042 } {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
1043
1044 do_execsql_test 4.5.67.1 {
1045 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1046 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1047 FROM ttt ORDER BY a
1048 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5}
1049
1050 do_execsql_test 4.5.67.2 {
1051 SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1052 sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1053 FROM ttt ORDER BY a
1054 } {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5}
1055
1056 do_execsql_test 4.5.68.1 {
1057 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1058 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1059 FROM ttt ORDER BY a
1060 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
1061
1062 do_execsql_test 4.5.68.2 {
1063 SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1064 sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1065 FROM ttt ORDER BY a
1066 } {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12}
1067
1068 do_execsql_test 4.5.69.1 {
1069 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1070 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1071 FROM ttt ORDER BY a
1072 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3}
1073
1074 do_execsql_test 4.5.69.2 {
1075 SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1076 sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1077 FROM ttt ORDER BY a
1078 } {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12}
1079
1080 do_execsql_test 4.5.70.1 {
1081 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1082 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1083 FROM ttt ORDER BY a
1084 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
1085
1086 do_execsql_test 4.5.70.2 {
1087 SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1088 sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1089 FROM ttt ORDER BY a
1090 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
1091
1092 do_execsql_test 4.5.71.1 {
1093 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1094 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1095 FROM ttt ORDER BY a
1096 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5}
1097
1098 do_execsql_test 4.5.71.2 {
1099 SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1100 sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1101 FROM ttt ORDER BY a
1102 } {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5}
1103
1104 do_execsql_test 4.5.72.1 {
1105 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1106 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1107 FROM ttt ORDER BY a
1108 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
1109
1110 do_execsql_test 4.5.72.2 {
1111 SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1112 sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1113 FROM ttt ORDER BY a
1114 } {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12}
1115
1116 do_execsql_test 4.5.73.1 {
1117 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1118 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1119 FROM ttt ORDER BY a
1120 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3}
1121
1122 do_execsql_test 4.5.73.2 {
1123 SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1124 sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1125 FROM ttt ORDER BY a
1126 } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12}
1127
1128 #==========================================================================
1129
1130 do_execsql_test 7.0 {
1131 DROP TABLE IF EXISTS t1;
1132 CREATE TABLE t1(x INTEGER, y INTEGER);
1133 INSERT INTO t1 VALUES(1, 2);
1134 INSERT INTO t1 VALUES(3, 4);
1135 INSERT INTO t1 VALUES(5, 6);
1136 INSERT INTO t1 VALUES(7, 8);
1137 INSERT INTO t1 VALUES(9, 10);
1138 } {}
1139
1140 do_execsql_test 7.1 {
1141 SELECT lead(y) OVER win FROM t1
1142 WINDOW win AS (ORDER BY x)
1143 } {4 6 8 10 {}}
1144
1145 do_execsql_test 7.2 {
1146 SELECT lead(y, 2) OVER win FROM t1
1147 WINDOW win AS (ORDER BY x)
1148 } {6 8 10 {} {}}
1149
1150 do_execsql_test 7.3 {
1151 SELECT lead(y, 3, -1) OVER win FROM t1
1152 WINDOW win AS (ORDER BY x)
1153 } {8 10 -1 -1 -1}
1154
1155 do_execsql_test 7.4 {
1156 SELECT
1157 lead(y) OVER win, lead(y) OVER win
1158 FROM t1
1159 WINDOW win AS (ORDER BY x)
1160 } {4 4 6 6 8 8 10 10 {} {}}
1161
1162 do_execsql_test 7.5 {
1163 SELECT
1164 lead(y) OVER win,
1165 lead(y, 2) OVER win,
1166 lead(y, 3, -1) OVER win
1167 FROM t1
1168 WINDOW win AS (ORDER BY x)
1169 } {4 6 8 6 8 10 8 10 -1 10 {} -1 {} {} -1}
1170
1171 #==========================================================================
1172
1173 do_execsql_test 8.0 {
1174 DROP TABLE IF EXISTS t1;
1175 CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER);
1176 INSERT INTO t1 VALUES(1, 2, 3, 4);
1177 INSERT INTO t1 VALUES(5, 6, 7, 8);
1178 INSERT INTO t1 VALUES(9, 10, 11, 12);
1179 } {}
1180
1181 do_execsql_test 8.1 {
1182 SELECT row_number() OVER win,
1183 nth_value(d,2) OVER win,
1184 lead(d) OVER win
1185 FROM t1
1186 WINDOW win AS (ORDER BY a)
1187 } {1 {} 8 2 8 12 3 8 {}}
1188
1189 do_execsql_test 8.2 {
1190 SELECT row_number() OVER win,
1191 rank() OVER win,
1192 dense_rank() OVER win,
1193 ntile(2) OVER win,
1194 first_value(d) OVER win,
1195 last_value(d) OVER win,
1196 nth_value(d,2) OVER win,
1197 lead(d) OVER win,
1198 lag(d) OVER win,
1199 max(d) OVER win,
1200 min(d) OVER win
1201 FROM t1
1202 WINDOW win AS (ORDER BY a)
1203 } {1 1 1 1 4 4 {} 8 {} 4 4 2 2 2 1 4 8 8 12 4 8 4 3 3 3 2 4 12 8 {} 8 12 4}
1204
1205 #==========================================================================
1206
1207 do_execsql_test 9.0 {
1208 DROP TABLE IF EXISTS t2;
1209 CREATE TABLE t2(x INTEGER);
1210 INSERT INTO t2 VALUES(1), (1), (1), (4), (4), (6), (7);
1211 } {}
1212
1213 do_execsql_test 9.1 {
1214 SELECT rank() OVER () FROM t2
1215 } {1 1 1 1 1 1 1}
1216
1217 do_execsql_test 9.2 {
1218 SELECT dense_rank() OVER (PARTITION BY x) FROM t2
1219 } {1 1 1 1 1 1 1}
1220
1221
1222 do_test 9.3 {
1223 set myres {}
1224 foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
1225 lappend myres [format %.4f [set r]]
1226 }
1227 set res2 {1.0000 0.0000 1.0000 0.0000 1.0000 0.0000 4.0000 0.0000 4.0000 0.0000 6.0000 0.0000 7.0000 0.0000}
1228 set i 0
1229 foreach r [set myres] r2 [set res2] {
1230 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
1231 error "list element [set i] does not match: got=[set r] expected=[set r2]"
1232 }
1233 incr i
1234 }
1235 set {} {}
1236 } {}
1237
1238 do_execsql_test 9.4 {
1239 SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
1240 } {1 1 1 1 1 1 4 4 4 4 6 6 7 7}
1241
1242 do_execsql_test 9.5 {
1243 SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
1244 } {1 1 4 4 6 6 7 7}
1245
1246
1247 do_test 9.6 {
1248 set myres {}
1249 foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
1250 lappend myres [format %.4f [set r]]
1251 }
1252 set res2 {0.0000 0.0000 0.0000}
1253 set i 0
1254 foreach r [set myres] r2 [set res2] {
1255 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
1256 error "list element [set i] does not match: got=[set r] expected=[set r2]"
1257 }
1258 incr i
1259 }
1260 set {} {}
1261 } {}
1262
1263
1264 do_test 9.7 {
1265 set myres {}
1266 foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
1267 lappend myres [format %.4f [set r]]
1268 }
1269 set res2 {1.0000 1.0000 1.0000}
1270 set i 0
1271 foreach r [set myres] r2 [set res2] {
1272 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
1273 error "list element [set i] does not match: got=[set r] expected=[set r2]"
1274 }
1275 incr i
1276 }
1277 set {} {}
1278 } {}
1279
1280 do_execsql_test 10.0 {
1281 DROP TABLE IF EXISTS t7;
1282 CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
1283 INSERT INTO t7(id, a, b) VALUES
1284 (1, 1, 2), (2, 1, NULL), (3, 1, 4),
1285 (4, 3, NULL), (5, 3, 8), (6, 3, 1);
1286 } {}
1287
1288 do_execsql_test 10.1 {
1289 SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
1290 } {1 2 2 2 3 2 4 {} 5 8 6 1}
1291
1292 do_execsql_test 10.2 {
1293 SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
1294 } {1 {} 2 2 3 {} 4 {} 5 {} 6 8}
1295
1296 do_execsql_test 10.3 {
1297 SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
1298 } {1 {} 2 4 3 {} 4 8 5 1 6 {}}
1299
1300 do_execsql_test 11.0 {
1301 DROP VIEW IF EXISTS v8;
1302 DROP TABLE IF EXISTS t8;
1303 CREATE TABLE t8(t INT, total INT);
1304 INSERT INTO t8 VALUES(0,2);
1305 INSERT INTO t8 VALUES(5,1);
1306 INSERT INTO t8 VALUES(10,1);
1307 } {}
1308
1309 do_execsql_test 11.1 {
1310 SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
1311 } {0 1 2}
1312
1313 do_execsql_test 11.2 {
1314 CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
1315 } {}
1316
1317 do_execsql_test 11.3 {
1318 SELECT * FROM v8;
1319 } {0 1 2}
1320
1321 do_execsql_test 11.4 {
1322 SELECT * FROM (
1323 SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
1324 ) sub;
1325 } {0 1 2}
1326
1327 do_execsql_test 11.5 {
1328 SELECT sum( min(t) ) OVER () FROM t8 GROUP BY total;
1329 } {5 5}
1330
1331 do_execsql_test 11.5 {
1332 SELECT sum( max(t) ) OVER () FROM t8 GROUP BY total;
1333 } {10 10}
1334
1335 do_execsql_test 11.7 {
1336 SELECT sum( min(t) ) OVER () FROM t8;
1337 } {0}
1338
1339 do_execsql_test 11.8 {
1340 SELECT sum( max(t) ) OVER () FROM t8;
1341 } {10}
1342
1343 do_execsql_test 12.0 {
1344 DROP TABLE IF EXISTS t2;
1345 CREATE TABLE t2(a INTEGER);
1346 INSERT INTO t2 VALUES(1), (2), (3);
1347 } {}
1348
1349 do_execsql_test 12.1 {
1350 SELECT (SELECT min(a) OVER ()) FROM t2
1351 } {1 2 3}
1352
1353
1354 do_test 12.2 {
1355 set myres {}
1356 foreach r [db eval {SELECT (SELECT avg(a)) FROM t2 ORDER BY 1}] {
1357 lappend myres [format %.4f [set r]]
1358 }
1359 set res2 {2.0000}
1360 set i 0
1361 foreach r [set myres] r2 [set res2] {
1362 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
1363 error "list element [set i] does not match: got=[set r] expected=[set r2]"
1364 }
1365 incr i
1366 }
1367 set {} {}
1368 } {}
1369
1370
1371 do_test 12.3 {
1372 set myres {}
1373 foreach r [db eval {SELECT
1374 (SELECT avg(a) UNION SELECT min(a) OVER ())
1375 FROM t2 GROUP BY a
1376 ORDER BY 1}] {
1377 lappend myres [format %.4f [set r]]
1378 }
1379 set res2 {1.0000 2.0000 3.0000}
1380 set i 0
1381 foreach r [set myres] r2 [set res2] {
1382 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
1383 error "list element [set i] does not match: got=[set r] expected=[set r2]"
1384 }
1385 incr i
1386 }
1387 set {} {}
1388 } {}
1389
1390 finish_test