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