gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/window4.test (about) 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