Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -1935,12 +1935,12 @@ pNew->pPartition = sqlite3ExprListDup(db, p->pPartition, 0); pNew->pOrderBy = sqlite3ExprListDup(db, p->pOrderBy, 0); pNew->eType = p->eType; pNew->eEnd = p->eEnd; pNew->eStart = p->eStart; - pNew->pStart = sqlite3ExprDup(db, pNew->pStart, 0); - pNew->pEnd = sqlite3ExprDup(db, pNew->pEnd, 0); + pNew->pStart = sqlite3ExprDup(db, p->pStart, 0); + pNew->pEnd = sqlite3ExprDup(db, p->pEnd, 0); pNew->pOwner = pOwner; } } return pNew; } Index: test/pg_common.tcl ================================================================== --- test/pg_common.tcl +++ test/pg_common.tcl @@ -72,11 +72,14 @@ # proc execsql_float_test {tn sql} { set F "%.2f" set res [execsql $sql] set res2 [list] - foreach r $res { lappend res2 [format $F $r] } + foreach r $res { + if {$r != ""} { set r [format $F $r] } + lappend res2 $r + } puts $::fd "do_test $tn {" puts $::fd " set myres {}" puts $::fd " foreach r \[db eval {[string trim $sql]}\] {" puts $::fd " lappend myres \[format $F \[set r\]\]" Index: test/window4.tcl ================================================================== --- test/window4.tcl +++ test/window4.tcl @@ -165,56 +165,69 @@ } set lPart [list "PARTITION BY b" "PARTITION BY b, a" "" "PARTITION BY a"] set lOrder [list "ORDER BY a" "ORDER BY a DESC" "" "ORDER BY b, a"] set lRange { - "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" - "BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" - "BETWEEN CURRENT ROW AND CURRENT ROW" - "BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" + "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" + "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" + "RANGE BETWEEN CURRENT ROW AND CURRENT ROW" + "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" +} + +set lRows { + "ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING" + "ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING" + "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" + "ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING" + "ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING" } set tn 1 set SQL { - SELECT max(c) OVER ($p1 $o1 RANGE $r1), - min(c) OVER ($p2 $o2 RANGE $r2) + SELECT max(c) OVER ($p1 $o1 $r1), + min(c) OVER ($p2 $o2 $r2) FROM ttt ORDER BY a } set SQL2 { - SELECT avg(c) OVER ($p1 $o1 RANGE $r1), - avg(c) OVER ($p2 $o2 RANGE $r2) + SELECT sum(c) OVER ($p1 $o1 $r1), + sum(c) OVER ($p2 $o2 $r2) FROM ttt ORDER BY a } set o1 [lindex $lOrder 0] set o2 [lindex $lOrder 0] set r1 [lindex $lRange 0] set r2 [lindex $lRange 0] foreach p1 $lPart { foreach p2 $lPart { execsql_test 4.5.$tn.1 [subst $SQL] - execsql_float_test 4.5.$tn.2 [subst $SQL2] + execsql_test 4.5.$tn.2 [subst $SQL2] incr tn }} set o1 [lindex $lOrder 0] set o2 [lindex $lOrder 0] set p1 [lindex $lPart 0] set p2 [lindex $lPart 0] foreach r1 $lRange { foreach r2 $lRange { execsql_test 4.5.$tn.1 [subst $SQL] - execsql_float_test 4.5.$tn.2 [subst $SQL2] + execsql_test 4.5.$tn.2 [subst $SQL2] + incr tn +}} +foreach r1 $lRows { foreach r2 $lRows { + execsql_test 4.5.$tn.1 [subst $SQL] + execsql_test 4.5.$tn.2 [subst $SQL2] incr tn }} set r1 [lindex $lRange 0] set r2 [lindex $lRange 0] set p1 [lindex $lPart 0] set p2 [lindex $lPart 0] foreach o1 $lOrder { foreach o2 $lOrder { execsql_test 4.5.$tn.1 [subst $SQL] - execsql_float_test 4.5.$tn.2 [subst $SQL2] + execsql_test 4.5.$tn.2 [subst $SQL2] incr tn }} finish_test Index: test/window4.test ================================================================== --- test/window4.test +++ test/window4.test @@ -251,768 +251,876 @@ SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.1.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.1.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} do_execsql_test 4.5.2.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.2.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.2.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5} do_execsql_test 4.5.3.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} -do_test 4.5.3.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.3.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 3 3 6 3 8 5 11 7 15 6 18 9 22 12 27} do_execsql_test 4.5.4.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.4.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.4.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5} do_execsql_test 4.5.5.1 { SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.5.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.5.2 { + SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12} do_execsql_test 4.5.6.1 { SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.6.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.6.2 { + SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} do_execsql_test 4.5.7.1 { SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} -do_test 4.5.7.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.7.2 { + SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27} do_execsql_test 4.5.8.1 { SELECT max(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.8.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.8.2 { + SELECT sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} do_execsql_test 4.5.9.1 { SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 3 1 3 2 4 3 4 1 4 2 5 3} -do_test 4.5.9.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.9.2 { + SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 3 2 6 3 8 3 11 5 15 7 18 6 22 9 27 12} do_execsql_test 4.5.10.1 { SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5} -do_test 4.5.10.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.10.2 { + SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5} do_execsql_test 4.5.11.1 { SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 1 3 1 3 1 3 1 4 1 4 1 4 1 5 1} -do_test 4.5.11.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.11.2 { + SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 3 3 6 6 8 8 11 11 15 15 18 18 22 22 27 27} do_execsql_test 4.5.12.1 { SELECT max(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 3 2 3 3 4 4 4 3 4 4 5 5} -do_test 4.5.12.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.12.2 { + SELECT sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 3 2 6 3 8 2 11 3 15 4 18 3 22 4 27 5} do_execsql_test 4.5.13.1 { SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.13.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.13.2 { + SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12} do_execsql_test 4.5.14.1 { SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.14.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.14.2 { + SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b, a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} do_execsql_test 4.5.15.1 { SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 1 3 1 2 1 3 1 4 1 3 1 4 1 5 1} -do_test 4.5.15.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.15.2 { + SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 3 3 6 2 8 3 11 4 15 3 18 4 22 5 27} do_execsql_test 4.5.16.1 { SELECT max(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.16.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.16.2 { + SELECT sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY a ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} do_execsql_test 4.5.17.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.17.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.17.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} do_execsql_test 4.5.18.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.18.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.18.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12} do_execsql_test 4.5.19.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.19.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.19.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 2 5 3 7 4 6 3 9 4 12 5} do_execsql_test 4.5.20.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.20.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.20.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5} do_execsql_test 4.5.21.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.21.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.21.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12} do_execsql_test 4.5.22.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.22.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.22.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12} do_execsql_test 4.5.23.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.23.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.23.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 6 2 9 3 12 4 6 3 9 4 12 5} do_execsql_test 4.5.24.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.24.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.24.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5} do_execsql_test 4.5.25.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.25.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.25.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 3 3 5 4 7 3 6 4 9 5 12} do_execsql_test 4.5.26.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.26.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.26.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 2 6 3 9 4 12 3 6 4 9 5 12} do_execsql_test 4.5.27.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.27.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.27.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} do_execsql_test 4.5.28.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.28.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.28.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 2 5 3 7 4 9 3 3 4 4 5 5} do_execsql_test 4.5.29.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.29.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.29.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12} do_execsql_test 4.5.30.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.30.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.30.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12} do_execsql_test 4.5.31.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.31.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.31.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 5 2 7 3 9 4 3 3 4 4 5 5} do_execsql_test 4.5.32.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.32.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.32.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5} do_execsql_test 4.5.33.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3} + +do_execsql_test 4.5.33.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {3 3 5 5 7 7 6 6 9 9 12 12 6 6 9 9 12 12} + +do_execsql_test 4.5.34.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {2 1 3 2 4 3 3 1 4 2 5 3 3 1 4 2 5 3} + +do_execsql_test 4.5.34.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {3 6 5 9 7 12 6 6 9 9 12 12 6 6 9 9 12 12} + +do_execsql_test 4.5.35.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {2 {} 3 {} 4 {} 3 1 4 2 5 3 3 2 4 3 5 4} + +do_execsql_test 4.5.35.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 5 {} 7 {} 6 1 9 2 12 3 6 2 9 3 12 4} + +do_execsql_test 4.5.36.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {2 {} 3 {} 4 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}} + +do_execsql_test 4.5.36.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}} + +do_execsql_test 4.5.37.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {2 2 3 3 4 4 3 3 4 4 5 5 3 {} 4 {} 5 {}} + +do_execsql_test 4.5.37.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {3 5 5 7 7 9 6 3 9 4 12 5 6 {} 9 {} 12 {}} + +do_execsql_test 4.5.38.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} + +do_execsql_test 4.5.38.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {6 3 9 5 12 7 6 6 9 9 12 12 6 6 9 9 12 12} + +do_execsql_test 4.5.39.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} + +do_execsql_test 4.5.39.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12} + +do_execsql_test 4.5.40.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 4 {} 5 {} 3 1 4 2 5 3 3 2 4 3 5 4} + +do_execsql_test 4.5.40.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {6 {} 9 {} 12 {} 6 1 9 2 12 3 6 2 9 3 12 4} + +do_execsql_test 4.5.41.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} 3 {} 4 {} 5 {}} + +do_execsql_test 4.5.41.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {}} + +do_execsql_test 4.5.42.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {3 2 4 3 5 4 3 3 4 4 5 5 3 {} 4 {} 5 {}} + +do_execsql_test 4.5.42.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {6 5 9 7 12 9 6 3 9 4 12 5 6 {} 9 {} 12 {}} + +do_execsql_test 4.5.43.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3} + +do_execsql_test 4.5.43.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {{} 3 {} 5 {} 7 1 6 2 9 3 12 2 6 3 9 4 12} + +do_execsql_test 4.5.44.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {{} 1 {} 2 {} 3 1 1 2 2 3 3 2 1 3 2 4 3} + +do_execsql_test 4.5.44.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {{} 6 {} 9 {} 12 1 6 2 9 3 12 2 6 3 9 4 12} + +do_execsql_test 4.5.45.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4} + +do_execsql_test 4.5.45.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} 1 1 2 2 3 3 2 2 3 3 4 4} + +do_execsql_test 4.5.46.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}} + +do_execsql_test 4.5.46.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4 {}} + +do_execsql_test 4.5.47.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {{} 2 {} 3 {} 4 1 3 2 4 3 5 2 {} 3 {} 4 {}} + +do_execsql_test 4.5.47.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {{} 5 {} 7 {} 9 1 3 2 4 3 5 2 {} 3 {} 4 {}} + +do_execsql_test 4.5.48.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3} + +do_execsql_test 4.5.48.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {{} 3 {} 5 {} 7 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12} + +do_execsql_test 4.5.49.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {{} 1 {} 2 {} 3 {} 1 {} 2 {} 3 {} 1 {} 2 {} 3} + +do_execsql_test 4.5.49.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {{} 6 {} 9 {} 12 {} 6 {} 9 {} 12 {} 6 {} 9 {} 12} + +do_execsql_test 4.5.50.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4} + +do_execsql_test 4.5.50.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} {} 1 {} 2 {} 3 {} 2 {} 3 {} 4} + +do_execsql_test 4.5.51.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + +do_execsql_test 4.5.51.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + +do_execsql_test 4.5.52.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {{} 2 {} 3 {} 4 {} 3 {} 4 {} 5 {} {} {} {} {} {}} + +do_execsql_test 4.5.52.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {{} 5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {}} + +do_execsql_test 4.5.53.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3} + +do_execsql_test 4.5.53.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) + FROM ttt ORDER BY a +} {5 3 7 5 9 7 3 6 4 9 5 12 {} 6 {} 9 {} 12} + +do_execsql_test 4.5.54.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {3 1 4 2 5 3 3 1 4 2 5 3 {} 1 {} 2 {} 3} + +do_execsql_test 4.5.54.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING) + FROM ttt ORDER BY a +} {5 6 7 9 9 12 3 6 4 9 5 12 {} 6 {} 9 {} 12} + +do_execsql_test 4.5.55.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 4 {} 5 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4} + +do_execsql_test 4.5.55.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {5 {} 7 {} 9 {} 3 1 4 2 5 3 {} 2 {} 3 {} 4} + +do_execsql_test 4.5.56.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {3 {} 4 {} 5 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}} + +do_execsql_test 4.5.56.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 0 PRECEDING AND 1 PRECEDING) + FROM ttt ORDER BY a +} {5 {} 7 {} 9 {} 3 {} 4 {} 5 {} {} {} {} {} {} {}} + +do_execsql_test 4.5.57.1 { + SELECT max(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + min(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {3 2 4 3 5 4 3 3 4 4 5 5 {} {} {} {} {} {}} + +do_execsql_test 4.5.57.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING), + sum(c) OVER (PARTITION BY b ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 500 FOLLOWING) + FROM ttt ORDER BY a +} {5 5 7 7 9 9 3 3 4 4 5 5 {} {} {} {} {} {}} + +do_execsql_test 4.5.58.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.33.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.58.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} -do_execsql_test 4.5.34.1 { +do_execsql_test 4.5.59.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.34.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.59.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5} -do_execsql_test 4.5.35.1 { +do_execsql_test 4.5.60.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.35.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.60.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12} -do_execsql_test 4.5.36.1 { +do_execsql_test 4.5.61.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.36.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.61.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} -do_execsql_test 4.5.37.1 { +do_execsql_test 4.5.62.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.37.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.62.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12} -do_execsql_test 4.5.38.1 { +do_execsql_test 4.5.63.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.38.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.63.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 5 5 7 7 9 9 3 3 4 4 5 5} -do_execsql_test 4.5.39.1 { +do_execsql_test 4.5.64.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.39.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.64.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 5 6 7 9 9 12 3 6 4 9 5 12} -do_execsql_test 4.5.40.1 { +do_execsql_test 4.5.65.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.40.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.65.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 5 3 7 5 9 7 3 6 4 9 5 12} -do_execsql_test 4.5.41.1 { +do_execsql_test 4.5.66.1 { SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.41.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.66.2 { + SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12} -do_execsql_test 4.5.42.1 { +do_execsql_test 4.5.67.1 { SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 2 4 3 5 4 3 3 4 4 5 5} -do_test 4.5.42.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.67.2 { + SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 6 5 9 7 12 9 6 3 9 4 12 5} -do_execsql_test 4.5.43.1 { +do_execsql_test 4.5.68.1 { SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.43.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.68.2 { + SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 6 9 9 12 12 6 6 9 9 12 12 6 6 9 9 12 12} -do_execsql_test 4.5.44.1 { +do_execsql_test 4.5.69.1 { SELECT max(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {3 1 4 2 5 3 3 1 4 2 5 3 3 1 4 2 5 3} -do_test 4.5.44.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.69.2 { + SELECT sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {6 1 9 2 12 3 6 3 9 5 12 7 6 6 9 9 12 12} -do_execsql_test 4.5.45.1 { +do_execsql_test 4.5.70.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.45.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.70.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} -do_execsql_test 4.5.46.1 { +do_execsql_test 4.5.71.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 2 3 3 4 4 3 3 4 4 5 5} -do_test 4.5.46.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.71.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 5 5 7 7 9 6 3 9 4 12 5} -do_execsql_test 4.5.47.1 { +do_execsql_test 4.5.72.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.47.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.72.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 6 2 9 3 12 3 6 5 9 7 12 6 6 9 9 12 12} -do_execsql_test 4.5.48.1 { +do_execsql_test 4.5.73.1 { SELECT max(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), min(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} -do_test 4.5.48.2 { - set myres {} - foreach r [db eval {SELECT avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - avg(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FROM ttt ORDER BY a}] { - lappend myres [format %.2f [set r]] - } - set myres -} {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} +do_execsql_test 4.5.73.2 { + SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + FROM ttt ORDER BY a +} {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} finish_test