Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with handling of statements containing two or more different windows. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
567e09ef2a8cd84a2481117e52595bed |
| User & Date: | dan 2018-06-14 20:52:08 |
Context
|
2018-06-15
| ||
| 16:10 | Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. (check-in: 3839fb18 user: dan tags: exp-window-functions) | |
|
2018-06-14
| ||
| 20:52 | Fix a problem with handling of statements containing two or more different windows. (check-in: 567e09ef user: dan tags: exp-window-functions) | |
| 19:06 | Fix problem with window functions min() and max() when used with a PARTITION clause and a frame starting point other than "UNBOUNDED PRECEDING". (check-in: 43eb1e75 user: dan tags: exp-window-functions) | |
Changes
Changes to src/window.c.
1933 1933 if( pNew ){ 1934 1934 pNew->pFilter = sqlite3ExprDup(db, p->pFilter, 0); 1935 1935 pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0); 1936 1936 pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0); 1937 1937 pNew->eType = p->eType; 1938 1938 pNew->eEnd = p->eEnd; 1939 1939 pNew->eStart = p->eStart; 1940 - pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0); 1941 - pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0); 1940 + pNew->pStart = sqlite3ExprDup(db, p->pStart, 0); 1941 + pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0); 1942 1942 pNew->pOwner = pOwner; 1943 1943 } 1944 1944 } 1945 1945 return pNew; 1946 1946 } 1947 1947 1948 1948 /*
Changes to test/pg_common.tcl.
70 70 # Same as [execsql_test], except coerce all results to floating point values 71 71 # with two decimal points. 72 72 # 73 73 proc execsql_float_test {tn sql} { 74 74 set F "%.2f" 75 75 set res [execsql $sql] 76 76 set res2 [list] 77 - foreach r $res { lappend res2 [format $F $r] } 77 + foreach r $res { 78 + if {$r != ""} { set r [format $F $r] } 79 + lappend res2 $r 80 + } 78 81 79 82 puts $::fd "do_test $tn {" 80 83 puts $::fd " set myres {}" 81 84 puts $::fd " foreach r \[db eval {[string trim $sql]}\] {" 82 85 puts $::fd " lappend myres \[format $F \[set r\]\]" 83 86 puts $::fd " }" 84 87 puts $::fd " set myres"
Changes to test/window4.tcl.
163 163 ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 164 164 ) FROM ttt; 165 165 } 166 166 167 167 set lPart [list "PARTITION BY b" "PARTITION BY b, a" "" "PARTITION BY a"] 168 168 set lOrder [list "ORDER BY a" "ORDER BY a DESC" "" "ORDER BY b, a"] 169 169 set lRange { 170 - "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 171 - "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 172 - "BETWEEN CURRENT ROW AND CURRENT ROW" 173 - "BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" 170 + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 171 + "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 172 + "RANGE BETWEEN CURRENT ROW AND CURRENT ROW" 173 + "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" 174 +} 175 + 176 +set lRows { 177 + "ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING" 178 + "ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING" 179 + "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" 180 + "ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING" 181 + "ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING" 174 182 } 175 183 176 184 set tn 1 177 185 set SQL { 178 - SELECT max(c) OVER ($p1 $o1 RANGE $r1), 179 - min(c) OVER ($p2 $o2 RANGE $r2) 186 + SELECT max(c) OVER ($p1 $o1 $r1), 187 + min(c) OVER ($p2 $o2 $r2) 180 188 FROM ttt ORDER BY a 181 189 } 182 190 set SQL2 { 183 - SELECT avg(c) OVER ($p1 $o1 RANGE $r1), 184 - avg(c) OVER ($p2 $o2 RANGE $r2) 191 + SELECT sum(c) OVER ($p1 $o1 $r1), 192 + sum(c) OVER ($p2 $o2 $r2) 185 193 FROM ttt ORDER BY a 186 194 } 187 195 188 196 set o1 [lindex $lOrder 0] 189 197 set o2 [lindex $lOrder 0] 190 198 set r1 [lindex $lRange 0] 191 199 set r2 [lindex $lRange 0] 192 200 foreach p1 $lPart { foreach p2 $lPart { 193 201 execsql_test 4.5.$tn.1 [subst $SQL] 194 - execsql_float_test 4.5.$tn.2 [subst $SQL2] 202 + execsql_test 4.5.$tn.2 [subst $SQL2] 195 203 incr tn 196 204 }} 197 205 198 206 set o1 [lindex $lOrder 0] 199 207 set o2 [lindex $lOrder 0] 200 208 set p1 [lindex $lPart 0] 201 209 set p2 [lindex $lPart 0] 202 210 foreach r1 $lRange { foreach r2 $lRange { 203 211 execsql_test 4.5.$tn.1 [subst $SQL] 204 - execsql_float_test 4.5.$tn.2 [subst $SQL2] 212 + execsql_test 4.5.$tn.2 [subst $SQL2] 213 + incr tn 214 +}} 215 +foreach r1 $lRows { foreach r2 $lRows { 216 + execsql_test 4.5.$tn.1 [subst $SQL] 217 + execsql_test 4.5.$tn.2 [subst $SQL2] 205 218 incr tn 206 219 }} 207 220 208 221 set r1 [lindex $lRange 0] 209 222 set r2 [lindex $lRange 0] 210 223 set p1 [lindex $lPart 0] 211 224 set p2 [lindex $lPart 0] 212 225 foreach o1 $lOrder { foreach o2 $lOrder { 213 226 execsql_test 4.5.$tn.1 [subst $SQL] 214 - execsql_float_test 4.5.$tn.2 [subst $SQL2] 227 + execsql_test 4.5.$tn.2 [subst $SQL2] 215 228 incr tn 216 229 }} 217 230 218 231 219 232 finish_test 220 233
Changes to test/window4.test.
249 249 250 250 do_execsql_test 4.5.1.1 { 251 251 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 252 252 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 253 253 FROM ttt ORDER BY a 254 254 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 255 255 256 -do_test 4.5.1.2 { 257 - set myres {} 258 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 259 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 260 - FROM ttt ORDER BY a}] { 261 - lappend myres [format %.2f [set r]] 262 - } 263 - set myres 264 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 256 +do_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} 265 261 266 262 do_execsql_test 4.5.2.1 { 267 263 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 268 264 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 269 265 FROM ttt ORDER BY a 270 266 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 271 267 272 -do_test 4.5.2.2 { 273 - set myres {} 274 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 275 - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 276 - FROM ttt ORDER BY a}] { 277 - lappend myres [format %.2f [set r]] 278 - } 279 - set myres 280 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 3.00 3.00 4.00 4.00 5.00} 268 +do_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} 281 273 282 274 do_execsql_test 4.5.3.1 { 283 275 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 284 276 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 285 277 FROM ttt ORDER BY a 286 278 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} 287 279 288 -do_test 4.5.3.2 { 289 - set myres {} 290 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 291 - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 292 - FROM ttt ORDER BY a}] { 293 - lappend myres [format %.2f [set r]] 294 - } 295 - set myres 296 -} {1.00 1.00 2.00 1.50 3.00 2.00 1.50 2.00 2.50 2.20 3.50 2.50 2.00 2.57 3.00 2.75 4.00 3.00} 280 +do_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} 297 285 298 286 do_execsql_test 4.5.4.1 { 299 287 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 300 288 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 301 289 FROM ttt ORDER BY a 302 290 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 303 291 304 -do_test 4.5.4.2 { 305 - set myres {} 306 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 307 - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 308 - FROM ttt ORDER BY a}] { 309 - lappend myres [format %.2f [set r]] 310 - } 311 - set myres 312 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 3.00 3.00 4.00 4.00 5.00} 292 +do_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} 313 297 314 298 do_execsql_test 4.5.5.1 { 315 299 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 316 300 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 317 301 FROM ttt ORDER BY a 318 302 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 319 303 320 -do_test 4.5.5.2 { 321 - set myres {} 322 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 323 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 324 - FROM ttt ORDER BY a}] { 325 - lappend myres [format %.2f [set r]] 326 - } 327 - set myres 328 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 304 +do_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} 329 309 330 310 do_execsql_test 4.5.6.1 { 331 311 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 332 312 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 333 313 FROM ttt ORDER BY a 334 314 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 335 315 336 -do_test 4.5.6.2 { 337 - set myres {} 338 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 339 - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 340 - FROM ttt ORDER BY a}] { 341 - lappend myres [format %.2f [set r]] 342 - } 343 - set myres 344 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 316 +do_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} 345 321 346 322 do_execsql_test 4.5.7.1 { 347 323 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 348 324 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 349 325 FROM ttt ORDER BY a 350 326 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} 351 327 352 -do_test 4.5.7.2 { 353 - set myres {} 354 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 355 - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 356 - FROM ttt ORDER BY a}] { 357 - lappend myres [format %.2f [set r]] 358 - } 359 - set myres 360 -} {1.00 1.00 2.00 1.50 3.00 2.00 2.00 2.00 3.00 2.20 4.00 2.50 3.00 2.57 4.00 2.75 5.00 3.00} 328 +do_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} 361 333 362 334 do_execsql_test 4.5.8.1 { 363 335 SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 364 336 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 365 337 FROM ttt ORDER BY a 366 338 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 367 339 368 -do_test 4.5.8.2 { 369 - set myres {} 370 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 371 - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 372 - FROM ttt ORDER BY a}] { 373 - lappend myres [format %.2f [set r]] 374 - } 375 - set myres 376 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 340 +do_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} 377 345 378 346 do_execsql_test 4.5.9.1 { 379 347 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 380 348 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 381 349 FROM ttt ORDER BY a 382 350 } {1 1 2 2 3 3 3 1 3 2 4 3 4 1 4 2 5 3} 383 351 384 -do_test 4.5.9.2 { 385 - set myres {} 386 - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 387 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 388 - FROM ttt ORDER BY a}] { 389 - lappend myres [format %.2f [set r]] 390 - } 391 - set myres 392 -} {1.00 1.00 1.50 2.00 2.00 3.00 2.00 1.50 2.20 2.50 2.50 3.50 2.57 2.00 2.75 3.00 3.00 4.00} 352 +do_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} 393 357 394 358 do_execsql_test 4.5.10.1 { 395 359 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 396 360 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 397 361 FROM ttt ORDER BY a 398 362 } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5} 399 363 400 -do_test 4.5.10.2 { 401 - set myres {} 402 - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 403 - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 404 - FROM ttt ORDER BY a}] { 405 - lappend myres [format %.2f [set r]] 406 - } 407 - set myres 408 -} {1.00 1.00 1.50 2.00 2.00 3.00 2.00 2.00 2.20 3.00 2.50 4.00 2.57 3.00 2.75 4.00 3.00 5.00} 364 +do_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} 409 369 410 370 do_execsql_test 4.5.11.1 { 411 371 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 412 372 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 413 373 FROM ttt ORDER BY a 414 374 } {1 1 2 1 3 1 3 1 3 1 4 1 4 1 4 1 5 1} 415 375 416 -do_test 4.5.11.2 { 417 - set myres {} 418 - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 419 - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 420 - FROM ttt ORDER BY a}] { 421 - lappend myres [format %.2f [set r]] 422 - } 423 - set myres 424 -} {1.00 1.00 1.50 1.50 2.00 2.00 2.00 2.00 2.20 2.20 2.50 2.50 2.57 2.57 2.75 2.75 3.00 3.00} 376 +do_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} 425 381 426 382 do_execsql_test 4.5.12.1 { 427 383 SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 428 384 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 429 385 FROM ttt ORDER BY a 430 386 } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5} 431 387 432 -do_test 4.5.12.2 { 433 - set myres {} 434 - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 435 - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 436 - FROM ttt ORDER BY a}] { 437 - lappend myres [format %.2f [set r]] 438 - } 439 - set myres 440 -} {1.00 1.00 1.50 2.00 2.00 3.00 2.00 2.00 2.20 3.00 2.50 4.00 2.57 3.00 2.75 4.00 3.00 5.00} 388 +do_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} 441 393 442 394 do_execsql_test 4.5.13.1 { 443 395 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 444 396 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 445 397 FROM ttt ORDER BY a 446 398 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 447 399 448 -do_test 4.5.13.2 { 449 - set myres {} 450 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 451 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 452 - FROM ttt ORDER BY a}] { 453 - lappend myres [format %.2f [set r]] 454 - } 455 - set myres 456 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 400 +do_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} 457 405 458 406 do_execsql_test 4.5.14.1 { 459 407 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 460 408 min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 461 409 FROM ttt ORDER BY a 462 410 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 463 411 464 -do_test 4.5.14.2 { 465 - set myres {} 466 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 467 - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 468 - FROM ttt ORDER BY a}] { 469 - lappend myres [format %.2f [set r]] 470 - } 471 - set myres 472 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 412 +do_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} 473 417 474 418 do_execsql_test 4.5.15.1 { 475 419 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 476 420 min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 477 421 FROM ttt ORDER BY a 478 422 } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} 479 423 480 -do_test 4.5.15.2 { 481 - set myres {} 482 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 483 - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 484 - FROM ttt ORDER BY a}] { 485 - lappend myres [format %.2f [set r]] 486 - } 487 - set myres 488 -} {1.00 1.00 2.00 1.50 3.00 2.00 2.00 2.00 3.00 2.20 4.00 2.50 3.00 2.57 4.00 2.75 5.00 3.00} 424 +do_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} 489 429 490 430 do_execsql_test 4.5.16.1 { 491 431 SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 492 432 min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 493 433 FROM ttt ORDER BY a 494 434 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 495 435 496 -do_test 4.5.16.2 { 497 - set myres {} 498 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 499 - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 500 - FROM ttt ORDER BY a}] { 501 - lappend myres [format %.2f [set r]] 502 - } 503 - set myres 504 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 436 +do_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} 505 441 506 442 do_execsql_test 4.5.17.1 { 507 443 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 508 444 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 509 445 FROM ttt ORDER BY a 510 446 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 511 447 512 -do_test 4.5.17.2 { 513 - set myres {} 514 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 515 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 516 - FROM ttt ORDER BY a}] { 517 - lappend myres [format %.2f [set r]] 518 - } 519 - set myres 520 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 448 +do_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} 521 453 522 454 do_execsql_test 4.5.18.1 { 523 455 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 524 456 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 525 457 FROM ttt ORDER BY a 526 458 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 527 459 528 -do_test 4.5.18.2 { 529 - set myres {} 530 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 531 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 532 - FROM ttt ORDER BY a}] { 533 - lappend myres [format %.2f [set r]] 534 - } 535 - set myres 536 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 2.00 3.00 3.00 4.00 4.00} 460 +do_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} 537 465 538 466 do_execsql_test 4.5.19.1 { 539 467 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 540 468 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 541 469 FROM ttt ORDER BY a 542 470 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 543 471 544 -do_test 4.5.19.2 { 545 - set myres {} 546 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 547 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 548 - FROM ttt ORDER BY a}] { 549 - lappend myres [format %.2f [set r]] 550 - } 551 - set myres 552 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 3.00 3.00 4.00 4.00 5.00} 472 +do_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} 553 477 554 478 do_execsql_test 4.5.20.1 { 555 479 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 556 480 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 557 481 FROM ttt ORDER BY a 558 482 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 559 483 560 -do_test 4.5.20.2 { 561 - set myres {} 562 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 563 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 564 - FROM ttt ORDER BY a}] { 565 - lappend myres [format %.2f [set r]] 566 - } 567 - set myres 568 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.50 2.50 3.50 3.50 4.50 2.00 3.00 3.00 4.00 4.00 5.00} 484 +do_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} 569 489 570 490 do_execsql_test 4.5.21.1 { 571 491 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 572 492 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 573 493 FROM ttt ORDER BY a 574 494 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 575 495 576 -do_test 4.5.21.2 { 577 - set myres {} 578 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 579 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 580 - FROM ttt ORDER BY a}] { 581 - lappend myres [format %.2f [set r]] 582 - } 583 - set myres 584 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 496 +do_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} 585 501 586 502 do_execsql_test 4.5.22.1 { 587 503 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 588 504 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 589 505 FROM ttt ORDER BY a 590 506 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 591 507 592 -do_test 4.5.22.2 { 593 - set myres {} 594 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 595 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 596 - FROM ttt ORDER BY a}] { 597 - lappend myres [format %.2f [set r]] 598 - } 599 - set myres 600 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.00 3.00 3.00 4.00 4.00} 508 +do_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} 601 513 602 514 do_execsql_test 4.5.23.1 { 603 515 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 604 516 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 605 517 FROM ttt ORDER BY a 606 518 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 607 519 608 -do_test 4.5.23.2 { 609 - set myres {} 610 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 611 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 612 - FROM ttt ORDER BY a}] { 613 - lappend myres [format %.2f [set r]] 614 - } 615 - set myres 616 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 2.00 3.00 3.00 4.00 4.00 5.00} 520 +do_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} 617 525 618 526 do_execsql_test 4.5.24.1 { 619 527 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 620 528 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 621 529 FROM ttt ORDER BY a 622 530 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 623 531 624 -do_test 4.5.24.2 { 625 - set myres {} 626 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 627 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 628 - FROM ttt ORDER BY a}] { 629 - lappend myres [format %.2f [set r]] 630 - } 631 - set myres 632 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.50 3.00 3.50 4.00 4.50 2.00 3.00 3.00 4.00 4.00 5.00} 532 +do_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} 633 537 634 538 do_execsql_test 4.5.25.1 { 635 539 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 636 540 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 637 541 FROM ttt ORDER BY a 638 542 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 639 543 640 -do_test 4.5.25.2 { 641 - set myres {} 642 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 643 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 644 - FROM ttt ORDER BY a}] { 645 - lappend myres [format %.2f [set r]] 646 - } 647 - set myres 648 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 544 +do_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} 649 549 650 550 do_execsql_test 4.5.26.1 { 651 551 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 652 552 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 653 553 FROM ttt ORDER BY a 654 554 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 655 555 656 -do_test 4.5.26.2 { 657 - set myres {} 658 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 659 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 660 - FROM ttt ORDER BY a}] { 661 - lappend myres [format %.2f [set r]] 662 - } 663 - set myres 664 -} {1.00 2.00 2.00 3.00 3.00 4.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 2.00 4.00 3.00 5.00 4.00} 556 +do_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} 665 561 666 562 do_execsql_test 4.5.27.1 { 667 563 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 668 564 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 669 565 FROM ttt ORDER BY a 670 566 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 671 567 672 -do_test 4.5.27.2 { 673 - set myres {} 674 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 675 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 676 - FROM ttt ORDER BY a}] { 677 - lappend myres [format %.2f [set r]] 678 - } 679 - set myres 680 -} {1.00 1.00 2.00 2.00 3.00 3.00 2.00 2.00 3.00 3.00 4.00 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 568 +do_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} 681 573 682 574 do_execsql_test 4.5.28.1 { 683 575 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 684 576 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 685 577 FROM ttt ORDER BY a 686 578 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 687 579 688 -do_test 4.5.28.2 { 689 - set myres {} 690 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 691 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 692 - FROM ttt ORDER BY a}] { 693 - lappend myres [format %.2f [set r]] 694 - } 695 - set myres 696 -} {1.00 2.00 2.00 3.00 3.00 4.00 2.00 2.50 3.00 3.50 4.00 4.50 3.00 3.00 4.00 4.00 5.00 5.00} 580 +do_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} 697 585 698 586 do_execsql_test 4.5.29.1 { 699 587 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 700 588 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 701 589 FROM ttt ORDER BY a 702 590 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 703 591 704 -do_test 4.5.29.2 { 705 - set myres {} 706 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 707 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 708 - FROM ttt ORDER BY a}] { 709 - lappend myres [format %.2f [set r]] 710 - } 711 - set myres 712 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.50 1.50 3.50 2.50 4.50 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 592 +do_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} 713 597 714 598 do_execsql_test 4.5.30.1 { 715 599 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 716 600 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 717 601 FROM ttt ORDER BY a 718 602 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 719 603 720 -do_test 4.5.30.2 { 721 - set myres {} 722 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 723 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 724 - FROM ttt ORDER BY a}] { 725 - lappend myres [format %.2f [set r]] 726 - } 727 - set myres 728 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.50 2.00 3.50 3.00 4.50 4.00 3.00 2.00 4.00 3.00 5.00 4.00} 604 +do_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} 729 609 730 610 do_execsql_test 4.5.31.1 { 731 611 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 732 612 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 733 613 FROM ttt ORDER BY a 734 614 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 735 615 736 -do_test 4.5.31.2 { 737 - set myres {} 738 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 739 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 740 - FROM ttt ORDER BY a}] { 741 - lappend myres [format %.2f [set r]] 742 - } 743 - set myres 744 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.50 2.00 3.50 3.00 4.50 4.00 3.00 3.00 4.00 4.00 5.00 5.00} 616 +do_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} 745 621 746 622 do_execsql_test 4.5.32.1 { 747 623 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 748 624 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 749 625 FROM ttt ORDER BY a 750 626 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 751 627 752 -do_test 4.5.32.2 { 753 - set myres {} 754 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 755 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 756 - FROM ttt ORDER BY a}] { 757 - lappend myres [format %.2f [set r]] 758 - } 759 - set myres 760 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.50 2.50 3.50 3.50 4.50 4.50 3.00 3.00 4.00 4.00 5.00 5.00} 628 +do_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} 761 633 762 634 do_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 + 640 +do_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 + 646 +do_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 + 652 +do_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 + 658 +do_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 + 664 +do_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 + 670 +do_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 + 676 +do_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 + 682 +do_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 + 688 +do_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 + 694 +do_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 + 700 +do_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 + 706 +do_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 + 712 +do_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 + 718 +do_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 + 724 +do_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 + 730 +do_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 + 736 +do_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 + 742 +do_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 + 748 +do_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 + 754 +do_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 + 760 +do_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 + 766 +do_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 + 772 +do_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 + 778 +do_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 + 784 +do_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 + 790 +do_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 + 796 +do_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 + 802 +do_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 + 808 +do_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 + 814 +do_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 + 820 +do_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 + 826 +do_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 + 832 +do_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 + 838 +do_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 + 844 +do_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 + 850 +do_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 + 856 +do_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 + 862 +do_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 + 868 +do_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 + 874 +do_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 + 880 +do_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 + 886 +do_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 + 892 +do_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 + 898 +do_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 + 904 +do_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 + 910 +do_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 + 916 +do_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 + 922 +do_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 + 928 +do_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 + 934 +do_execsql_test 4.5.58.1 { 763 935 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 764 936 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 765 937 FROM ttt ORDER BY a 766 938 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 767 939 768 -do_test 4.5.33.2 { 769 - set myres {} 770 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 771 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 772 - FROM ttt ORDER BY a}] { 773 - lappend myres [format %.2f [set r]] 774 - } 775 - set myres 776 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 940 +do_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} 777 945 778 -do_execsql_test 4.5.34.1 { 946 +do_execsql_test 4.5.59.1 { 779 947 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 780 948 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 781 949 FROM ttt ORDER BY a 782 950 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 783 951 784 -do_test 4.5.34.2 { 785 - set myres {} 786 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 787 - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 788 - FROM ttt ORDER BY a}] { 789 - lappend myres [format %.2f [set r]] 790 - } 791 - set myres 792 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.50 2.50 3.50 3.50 4.50 2.00 3.00 3.00 4.00 4.00 5.00} 952 +do_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} 793 957 794 -do_execsql_test 4.5.35.1 { 958 +do_execsql_test 4.5.60.1 { 795 959 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 796 960 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 797 961 FROM ttt ORDER BY a 798 962 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 799 963 800 -do_test 4.5.35.2 { 801 - set myres {} 802 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 803 - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 804 - FROM ttt ORDER BY a}] { 805 - lappend myres [format %.2f [set r]] 806 - } 807 - set myres 808 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 2.00 3.00 3.00 4.00 4.00} 964 +do_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} 809 969 810 -do_execsql_test 4.5.36.1 { 970 +do_execsql_test 4.5.61.1 { 811 971 SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 812 972 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 813 973 FROM ttt ORDER BY a 814 974 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 815 975 816 -do_test 4.5.36.2 { 817 - set myres {} 818 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 819 - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 820 - FROM ttt ORDER BY a}] { 821 - lappend myres [format %.2f [set r]] 822 - } 823 - set myres 824 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 976 +do_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} 825 981 826 -do_execsql_test 4.5.37.1 { 982 +do_execsql_test 4.5.62.1 { 827 983 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 828 984 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 829 985 FROM ttt ORDER BY a 830 986 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 831 987 832 -do_test 4.5.37.2 { 833 - set myres {} 834 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 835 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 836 - FROM ttt ORDER BY a}] { 837 - lappend myres [format %.2f [set r]] 838 - } 839 - set myres 840 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.50 1.50 3.50 2.50 4.50 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 988 +do_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} 841 993 842 -do_execsql_test 4.5.38.1 { 994 +do_execsql_test 4.5.63.1 { 843 995 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 844 996 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 845 997 FROM ttt ORDER BY a 846 998 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 847 999 848 -do_test 4.5.38.2 { 849 - set myres {} 850 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 851 - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 852 - FROM ttt ORDER BY a}] { 853 - lappend myres [format %.2f [set r]] 854 - } 855 - set myres 856 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.50 2.50 3.50 3.50 4.50 4.50 3.00 3.00 4.00 4.00 5.00 5.00} 1000 +do_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} 857 1005 858 -do_execsql_test 4.5.39.1 { 1006 +do_execsql_test 4.5.64.1 { 859 1007 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 860 1008 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 861 1009 FROM ttt ORDER BY a 862 1010 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 863 1011 864 -do_test 4.5.39.2 { 865 - set myres {} 866 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 867 - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 868 - FROM ttt ORDER BY a}] { 869 - lappend myres [format %.2f [set r]] 870 - } 871 - set myres 872 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.50 2.00 3.50 3.00 4.50 4.00 3.00 2.00 4.00 3.00 5.00 4.00} 1012 +do_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} 873 1017 874 -do_execsql_test 4.5.40.1 { 1018 +do_execsql_test 4.5.65.1 { 875 1019 SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 876 1020 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 877 1021 FROM ttt ORDER BY a 878 1022 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 879 1023 880 -do_test 4.5.40.2 { 881 - set myres {} 882 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 883 - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 884 - FROM ttt ORDER BY a}] { 885 - lappend myres [format %.2f [set r]] 886 - } 887 - set myres 888 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.50 1.50 3.50 2.50 4.50 3.50 3.00 2.00 4.00 3.00 5.00 4.00} 1024 +do_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} 889 1029 890 -do_execsql_test 4.5.41.1 { 1030 +do_execsql_test 4.5.66.1 { 891 1031 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 892 1032 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 893 1033 FROM ttt ORDER BY a 894 1034 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 895 1035 896 -do_test 4.5.41.2 { 897 - set myres {} 898 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 899 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 900 - FROM ttt ORDER BY a}] { 901 - lappend myres [format %.2f [set r]] 902 - } 903 - set myres 904 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 1036 +do_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} 905 1041 906 -do_execsql_test 4.5.42.1 { 1042 +do_execsql_test 4.5.67.1 { 907 1043 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 908 1044 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 909 1045 FROM ttt ORDER BY a 910 1046 } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} 911 1047 912 -do_test 4.5.42.2 { 913 - set myres {} 914 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 915 - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 916 - FROM ttt ORDER BY a}] { 917 - lappend myres [format %.2f [set r]] 918 - } 919 - set myres 920 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.50 3.00 3.50 4.00 4.50 2.00 3.00 3.00 4.00 4.00 5.00} 1048 +do_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} 921 1053 922 -do_execsql_test 4.5.43.1 { 1054 +do_execsql_test 4.5.68.1 { 923 1055 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 924 1056 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 925 1057 FROM ttt ORDER BY a 926 1058 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 927 1059 928 -do_test 4.5.43.2 { 929 - set myres {} 930 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 931 - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 932 - FROM ttt ORDER BY a}] { 933 - lappend myres [format %.2f [set r]] 934 - } 935 - set myres 936 -} {2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.00 3.00 3.00 4.00 4.00 2.00 2.00 3.00 3.00 4.00 4.00} 1060 +do_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} 937 1065 938 -do_execsql_test 4.5.44.1 { 1066 +do_execsql_test 4.5.69.1 { 939 1067 SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 940 1068 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 941 1069 FROM ttt ORDER BY a 942 1070 } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} 943 1071 944 -do_test 4.5.44.2 { 945 - set myres {} 946 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 947 - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 948 - FROM ttt ORDER BY a}] { 949 - lappend myres [format %.2f [set r]] 950 - } 951 - set myres 952 -} {2.00 1.00 3.00 2.00 4.00 3.00 2.00 1.50 3.00 2.50 4.00 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 1072 +do_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} 953 1077 954 -do_execsql_test 4.5.45.1 { 1078 +do_execsql_test 4.5.70.1 { 955 1079 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 956 1080 min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 957 1081 FROM ttt ORDER BY a 958 1082 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 959 1083 960 -do_test 4.5.45.2 { 961 - set myres {} 962 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 963 - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 964 - FROM ttt ORDER BY a}] { 965 - lappend myres [format %.2f [set r]] 966 - } 967 - set myres 968 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 1084 +do_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} 969 1089 970 -do_execsql_test 4.5.46.1 { 1090 +do_execsql_test 4.5.71.1 { 971 1091 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 972 1092 min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 973 1093 FROM ttt ORDER BY a 974 1094 } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} 975 1095 976 -do_test 4.5.46.2 { 977 - set myres {} 978 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 979 - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 980 - FROM ttt ORDER BY a}] { 981 - lappend myres [format %.2f [set r]] 982 - } 983 - set myres 984 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.50 2.50 3.50 3.50 4.50 2.00 3.00 3.00 4.00 4.00 5.00} 1096 +do_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} 985 1101 986 -do_execsql_test 4.5.47.1 { 1102 +do_execsql_test 4.5.72.1 { 987 1103 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 988 1104 min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 989 1105 FROM ttt ORDER BY a 990 1106 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 991 1107 992 -do_test 4.5.47.2 { 993 - set myres {} 994 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 995 - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 996 - FROM ttt ORDER BY a}] { 997 - lappend myres [format %.2f [set r]] 998 - } 999 - set myres 1000 -} {1.00 2.00 2.00 3.00 3.00 4.00 1.50 2.00 2.50 3.00 3.50 4.00 2.00 2.00 3.00 3.00 4.00 4.00} 1108 +do_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} 1001 1113 1002 -do_execsql_test 4.5.48.1 { 1114 +do_execsql_test 4.5.73.1 { 1003 1115 SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1004 1116 min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 1005 1117 FROM ttt ORDER BY a 1006 1118 } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} 1007 1119 1008 -do_test 4.5.48.2 { 1009 - set myres {} 1010 - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1011 - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 1012 - FROM ttt ORDER BY a}] { 1013 - lappend myres [format %.2f [set r]] 1014 - } 1015 - set myres 1016 -} {1.00 1.00 2.00 2.00 3.00 3.00 1.50 1.50 2.50 2.50 3.50 3.50 2.00 2.00 3.00 3.00 4.00 4.00} 1120 +do_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} 1017 1125 1018 1126 finish_test