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