Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3477,15 +3477,19 @@ u8 eStart; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ Expr *pStart; /* Expression for " PRECEDING" */ Expr *pEnd; /* Expression for " FOLLOWING" */ Window *pNextWin; /* Next window function belonging to this SELECT */ + FuncDef *pFunc; + int nArg; + int iEphCsr; /* Temp table used by this window */ int regAccum; int regResult; - FuncDef *pFunc; - int nArg; + + int csrApp; /* Function cursor (used by min/max) */ + int regApp; /* Function register (also used by min/max) */ int regPart; Expr *pOwner; /* Expression object this window is attached to */ int nBufferCol; /* Number of columns in buffer table */ int iArgCol; /* Offset of first argument for this function */ Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -63,10 +63,32 @@ if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1; if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1; if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1; return 0; } + +static void windowAggInit(Parse *pParse, Window *pMWin){ + Window *pWin; + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ + int funcFlags = pWin->pFunc->funcFlags; + if( (funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){ + ExprList *pList = pWin->pOwner->x.pList; + KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pList, 0, 0); + Vdbe *v = sqlite3GetVdbe(pParse); + pWin->csrApp = pParse->nTab++; + pWin->regApp = pParse->nMem+1; + pParse->nMem += 3; + if( pKeyInfo && pWin->pFunc->zName[1]=='i' ){ + assert( pKeyInfo->aSortOrder[0]==0 ); + pKeyInfo->aSortOrder[0] = 1; + } + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pWin->csrApp, 2); + sqlite3VdbeAppendP4(v, pKeyInfo, P4_KEYINFO); + sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); + } + } +} void sqlite3WindowCodeInit(Parse *pParse, Window *pWin){ Vdbe *v = sqlite3GetVdbe(pParse); int nPart = (pWin->pPartition ? pWin->pPartition->nExpr : 0); nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0); @@ -73,10 +95,11 @@ if( nPart ){ pWin->regPart = pParse->nMem+1; pParse->nMem += nPart; sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1); } + windowAggInit(pParse, pWin); } static void windowCheckFrameValue(Parse *pParse, int reg, int bEnd){ static const char *azErr[] = { "frame starting offset must be a non-negative integer", @@ -83,18 +106,22 @@ "frame ending offset must be a non-negative integer" }; Vdbe *v = sqlite3GetVdbe(pParse); int regZero = ++pParse->nMem; - sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero); sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2); sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg); sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort); sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC); } +/* +** Generate VM code to invoke either xStep() (if bInverse is 0) or +** xInverse (if bInverse is non-zero) for each window function in the +** linked list starting at pMWin. +*/ static void windowAggStep( Parse *pParse, Window *pMWin, int csr, int bInverse, @@ -101,34 +128,69 @@ int reg ){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - int i; - for(i=0; inArg; i++){ - sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i); + int regArg; + if( csr>=0 ){ + int i; + for(i=0; inArg; i++){ + sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i); + } + regArg = reg; + }else{ + regArg = reg + pWin->iArgCol; } - sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, reg, pWin->regAccum); - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, (u8)pWin->nArg); + + if( pWin->csrApp ){ + if( bInverse==0 ){ + sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1); + sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp); + sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2); + sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2); + }else{ + sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1); + sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp); + sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); + } + }else{ + if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ + CollSeq *pColl; + pColl = sqlite3ExprCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr); + sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ); + } + sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, regArg, pWin->regAccum); + sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); + sqlite3VdbeChangeP5(v, (u8)pWin->nArg); + } } } static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - if( bFinal==0 ){ + if( pWin->csrApp ){ sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); - } - sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg); - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); - if( bFinal ){ - sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult); + sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp); + sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult); + sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); + if( bFinal ){ + sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); + } }else{ - sqlite3VdbeChangeP3(v, -1, pWin->regResult); + if( bFinal==0 ){ + sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); + } + sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg); + sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); + if( bFinal ){ + sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult); + }else{ + sqlite3VdbeChangeP3(v, -1, pWin->regResult); + } } } } @@ -331,17 +393,16 @@ /* Check if this is the start of a new partition. If so, call the ** flush_partition sub-routine. */ if( pMWin->pPartition ){ ExprList *pPart = pMWin->pPartition; int nPart = (pPart ? pPart->nExpr : 0); - int addrJump = 0; int regNewPart = reg + pMWin->nBufferCol; KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0); addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart); sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO); - addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2); + sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2); sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart); sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart); } /* Buffer the current row in the ephemeral table. */ @@ -353,11 +414,11 @@ /* Invoke "flush_partition" to deal with the final (or only) partition */ sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart); addrGoto = sqlite3VdbeAddOp0(v, OP_Goto); - /* flush_partition: */ + /* Start of "flush_partition" */ sqlite3VdbeResolveLabel(v, lblFlushPart); sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr); sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr); @@ -368,16 +429,22 @@ windowCheckFrameValue(pParse, regStart, 0); } if( pMWin->pEnd ){ sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); windowCheckFrameValue(pParse, regEnd, 1); - if( pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){ - assert( pMWin->eEnd==TK_FOLLOWING ); - sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd); - } } + /* If this is "ROWS FOLLOWING AND ROWS FOLLOWING", do: + ** + ** regEnd = regEnd - regStart; + */ + if( pMWin->pEnd && pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){ + assert( pMWin->eEnd==TK_FOLLOWING ); + sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd); + } + + /* Initialize the accumulator register for each window function to NULL */ for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); } sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblFlushDone); @@ -589,15 +656,11 @@ if( addrJump ) sqlite3VdbeJumpHere(v, addrJump); } /* Invoke step function for window functions */ - for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum); - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, (u8)pWin->nArg); - } + windowAggStep(pParse, pMWin, -1, 0, reg); /* Buffer the current row in the ephemeral table. */ if( pMWin->nBufferCol>0 ){ sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord); }else{ ADDED test/pg_common.tcl Index: test/pg_common.tcl ================================================================== --- test/pg_common.tcl +++ test/pg_common.tcl @@ -0,0 +1,110 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +package require sqlite3 +package require Pgtcl + +set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] +sqlite3 sqlite "" + +proc execsql {sql} { + + set lSql [list] + set frag "" + while {[string length $sql]>0} { + set i [string first ";" $sql] + if {$i>=0} { + append frag [string range $sql 0 $i] + set sql [string range $sql $i+1 end] + if {[sqlite complete $frag]} { + lappend lSql $frag + set frag "" + } + } else { + set frag $sql + set sql "" + } + } + if {$frag != ""} { + lappend lSql $frag + } + #puts $lSql + + set ret "" + foreach stmt $lSql { + set res [pg_exec $::db $stmt] + set err [pg_result $res -error] + if {$err!=""} { error $err } + for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} { + if {$i==0} { + set ret [pg_result $res -getTuple 0] + } else { + append ret " [pg_result $res -getTuple $i]" + } + # lappend ret {*}[pg_result $res -getTuple $i] + } + pg_result $res -clear + } + + set ret +} + +proc execsql_test {tn sql} { + set res [execsql $sql] + puts $::fd "do_execsql_test $tn {" + puts $::fd " [string trim $sql]" + puts $::fd "} {$res}" + puts $::fd "" +} + +proc start_test {name date} { + set dir [file dirname $::argv0] + set output [file join $dir $name.test] + set ::fd [open $output w] +puts $::fd [string trimleft " +# $date +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### +"] + puts $::fd {set testdir [file dirname $argv0]} + puts $::fd {source $testdir/tester.tcl} + puts $::fd "set testprefix $name" + puts $::fd "" +} + +proc -- {args} { + puts $::fd "# $args" +} + +proc ========== {args} { + puts $::fd "#[string repeat = 74]" + puts $::fd "" +} + +proc finish_test {} { + puts $::fd finish_test + close $::fd +} + Index: test/window2.tcl ================================================================== --- test/window2.tcl +++ test/window2.tcl @@ -8,107 +8,11 @@ # May you share freely, never taking more than you give. # #*********************************************************************** # -package require sqlite3 -package require Pgtcl - -set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] -sqlite3 sqlite "" - -proc execsql {sql} { - - set lSql [list] - set frag "" - while {[string length $sql]>0} { - set i [string first ";" $sql] - if {$i>=0} { - append frag [string range $sql 0 $i] - set sql [string range $sql $i+1 end] - if {[sqlite complete $frag]} { - lappend lSql $frag - set frag "" - } - } else { - set frag $sql - set sql "" - } - } - if {$frag != ""} { - lappend lSql $frag - } - #puts $lSql - - set ret "" - foreach stmt $lSql { - set res [pg_exec $::db $stmt] - set err [pg_result $res -error] - if {$err!=""} { error $err } - for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} { - if {$i==0} { - set ret [pg_result $res -getTuple 0] - } else { - append ret " [pg_result $res -getTuple $i]" - } - # lappend ret {*}[pg_result $res -getTuple $i] - } - pg_result $res -clear - } - - set ret -} - -proc execsql_test {tn sql} { - set res [execsql $sql] - puts $::fd "do_execsql_test $tn {" - puts $::fd " [string trim $sql]" - puts $::fd "} {$res}" - puts $::fd "" -} - -proc start_test {name date} { - set dir [file dirname $::argv0] - set output [file join $dir $name.test] - set ::fd [open $output w] -puts $::fd [string trimleft " -# $date -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# This file implements regression tests for SQLite library. -# - -#################################################### -# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! -#################################################### -"] - puts $::fd {set testdir [file dirname $argv0]} - puts $::fd {source $testdir/tester.tcl} - puts $::fd "set testprefix $name" - puts $::fd "" -} - -proc -- {args} { - puts $::fd "# $args" -} - -proc ========== {args} { - puts $::fd "#[string repeat = 74]" - puts $::fd "" -} - -proc finish_test {} { - puts $::fd finish_test - close $::fd -} +source [file join [file dirname $argv0] pg_common.tcl] #========================================================================= start_test window2 "2018 May 19" ADDED test/window3.tcl Index: test/window3.tcl ================================================================== --- test/window3.tcl +++ test/window3.tcl @@ -0,0 +1,81 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + +start_test window3 "2018 May 31" + +execsql_test 1.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); + INSERT INTO t2(a, b) VALUES + (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), + (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), + (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), + (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), + (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), + (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), + (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), + (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), + (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), + (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), + (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), + (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), + (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), + (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), + (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), + (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), + (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), + (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), + (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), + (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), + (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), + (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), + (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), + (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), + (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), + (195,34), (196,83), (197,27), (198,82), (199,17), (200,7); +} + +execsql_test 1.1 { + SELECT max(b) OVER ( + ORDER BY a + ) FROM t2 +} + +foreach {tn window} { + 1 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" + 2 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" + 3 "RANGE BETWEEN CURRENT ROW AND CURRENT ROW" + 4 "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" + 5 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING" + 6 "ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING" + 7 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" + 8 "ROWS BETWEEN 4 PRECEDING AND CURRENT ROW" + 9 "ROWS BETWEEN CURRENT ROW AND CURRENT ROW" + 10 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING" + 11 "ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING" + 12 "ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING" + 13 "ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING" + 14 "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" + 15 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING" + 16 "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" + 17 "ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING" +} { + execsql_test 1.2.1.$tn "SELECT max(b) OVER ( ORDER BY a $window ) FROM t2" + execsql_test 1.2.2.$tn "SELECT min(b) OVER ( ORDER BY a $window ) FROM t2" +} + +finish_test + ADDED test/window3.test Index: test/window3.test ================================================================== --- test/window3.test +++ test/window3.test @@ -0,0 +1,196 @@ +# 2018 May 31 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix window3 + +do_execsql_test 1.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); + INSERT INTO t2(a, b) VALUES + (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), + (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), + (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), + (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), + (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), + (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), + (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), + (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), + (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), + (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), + (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), + (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), + (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), + (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), + (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), + (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), + (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), + (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), + (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), + (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), + (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), + (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), + (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), + (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), + (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), + (195,34), (196,83), (197,27), (198,82), (199,17), (200,7); +} {} + +do_execsql_test 1.1 { + SELECT max(b) OVER ( + ORDER BY a + ) FROM t2 +} {0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.1.1 { + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 +} {0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.1 { + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.2 { + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.2 { + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.3 { + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} + +do_execsql_test 1.2.2.3 { + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} + +do_execsql_test 1.2.1.4 { + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7} + +do_execsql_test 1.2.2.4 { + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 +} {0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} + +do_execsql_test 1.2.1.5 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 +} {{} {} {} {} 0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.5 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.6 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 +} {{} {} 0 74 74 74 74 99 99 99 33 89 89 96 96 96 68 68 68 91 91 91 99 99 99 97 78 78 97 97 97 67 93 93 93 84 77 23 93 93 93 65 47 86 86 86 91 91 91 85 85 85 59 59 56 56 91 91 91 90 90 55 89 89 89 47 56 56 56 56 56 75 75 89 98 98 98 81 94 94 94 78 78 78 53 63 63 87 87 87 84 84 84 72 61 73 95 95 95 65 96 98 98 98 74 74 74 65 73 73 73 87 87 87 41 20 31 31 31 95 95 95 79 88 88 88 34 49 49 90 90 96 96 96 75 77 77 77 44 85 85 85 74 74 70 70 59 39 39 47 80 90 90 90 58 58 72 72 72 72 93 93 93 81 81 81 37 37 37 14 62 91 91 91 91 91 34 36 99 99 99 95 95 69 58 52 84 84 84 84 84 39 44 58 58 58 38 83 83 83} + +do_execsql_test 1.2.2.6 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 +} {{} {} 0 0 0 41 23 23 23 26 2 2 2 81 59 38 38 38 39 39 46 6 6 6 27 27 27 46 54 8 8 8 29 29 77 23 16 16 16 16 35 35 7 7 7 61 61 61 24 24 24 43 12 12 12 3 3 3 22 22 15 15 15 25 25 1 1 1 40 40 16 16 16 36 36 76 76 4 4 4 30 30 30 29 29 29 2 2 2 37 37 72 41 9 9 9 61 65 13 13 13 58 1 1 1 21 35 5 5 5 11 11 41 12 8 8 8 20 15 15 15 22 22 73 34 8 8 8 11 34 34 59 59 55 55 55 44 2 2 2 7 57 29 29 29 19 19 19 26 26 26 47 36 36 36 9 9 9 66 33 33 33 64 64 9 9 9 13 12 12 12 14 36 36 33 15 15 15 34 3 3 3 58 52 30 30 30 10 10 10 21 21 21 39 30 30 30 34 27 27} + +do_execsql_test 1.2.1.7 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 +} {0 74 74 74 74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.7 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.8 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 +} {0 74 74 74 74 99 99 99 99 99 89 96 96 96 96 96 68 91 91 91 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 84 93 93 93 93 93 86 86 86 91 91 91 91 91 85 85 85 59 59 91 91 91 91 91 90 90 89 89 89 89 56 56 56 56 75 75 89 98 98 98 98 98 94 94 94 94 78 78 78 63 87 87 87 87 87 84 84 84 73 95 95 95 95 96 98 98 98 98 98 74 74 74 73 73 87 87 87 87 87 41 31 31 95 95 95 95 95 88 88 88 88 49 90 90 96 96 96 96 96 77 77 77 85 85 85 85 85 74 74 70 70 59 47 80 90 90 90 90 90 72 72 72 72 93 93 93 93 93 81 81 81 37 37 62 91 91 91 91 91 91 91 99 99 99 99 99 95 95 69 84 84 84 84 84 84 84 58 58 58 58 83 83 83 83 83} + +do_execsql_test 1.2.2.8 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 +} {0 0 0 0 0 23 23 23 2 2 2 2 2 38 38 38 38 38 39 6 6 6 6 6 27 27 27 8 8 8 8 8 29 23 16 16 16 16 16 16 7 7 7 7 7 61 24 24 24 24 12 12 12 3 3 3 3 3 15 15 15 15 15 1 1 1 1 1 16 16 16 16 16 36 36 4 4 4 4 4 30 29 29 29 2 2 2 2 2 37 37 9 9 9 9 9 13 13 13 13 1 1 1 1 1 5 5 5 5 5 11 11 8 8 8 8 8 15 15 15 15 22 22 8 8 8 8 8 11 34 34 55 55 55 44 2 2 2 2 2 7 29 29 19 19 19 19 19 26 26 26 36 36 9 9 9 9 9 33 33 33 33 9 9 9 9 9 12 12 12 12 14 33 15 15 15 15 3 3 3 3 3 30 30 30 10 10 10 10 10 21 21 21 30 30 30 27 27 17 7} + +do_execsql_test 1.2.1.9 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} + +do_execsql_test 1.2.2.9 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 +} {0 74 41 74 23 99 26 33 2 89 81 96 59 38 68 39 62 91 46 6 99 97 27 46 78 54 97 8 67 29 93 84 77 23 16 16 93 65 35 47 7 86 74 61 91 85 24 85 43 59 12 32 56 3 91 22 90 55 15 28 89 25 47 1 56 40 43 56 16 75 36 89 98 76 81 4 94 42 30 78 33 29 53 63 2 87 37 80 84 72 41 9 61 73 95 65 13 58 96 98 1 21 74 65 35 5 73 11 51 87 41 12 8 20 31 31 15 95 22 73 79 88 34 8 11 49 34 90 59 96 60 55 75 77 44 2 7 85 57 74 29 70 59 19 39 26 26 47 80 90 36 58 47 9 72 72 66 33 93 75 64 81 9 23 37 13 12 14 62 91 36 91 33 15 34 36 99 3 95 69 58 52 30 50 84 10 84 33 21 39 44 58 30 38 34 83 27 82 17 7} + +do_execsql_test 1.2.1.10 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 +} {74 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.10 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.11 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 +} {74 74 74 99 99 99 99 99 99 99 96 96 96 96 96 96 91 91 99 99 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 93 93 93 93 93 93 86 91 91 91 91 91 91 91 85 85 85 91 91 91 91 91 91 91 90 90 89 89 89 89 56 56 75 75 89 98 98 98 98 98 98 98 94 94 94 94 78 78 87 87 87 87 87 87 87 84 84 95 95 95 95 96 98 98 98 98 98 98 98 74 74 74 87 87 87 87 87 87 87 41 95 95 95 95 95 95 95 88 88 88 90 90 96 96 96 96 96 96 96 77 85 85 85 85 85 85 85 74 74 70 70 80 90 90 90 90 90 90 90 72 72 93 93 93 93 93 93 93 81 81 81 62 91 91 91 91 91 91 91 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 58 58 83 83 83 83 83 83 83} + +do_execsql_test 1.2.2.11 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 +} {0 0 0 0 0 23 2 2 2 2 2 2 2 38 38 38 38 6 6 6 6 6 6 6 27 8 8 8 8 8 8 8 16 16 16 16 16 16 7 7 7 7 7 7 7 24 24 24 12 12 12 3 3 3 3 3 3 3 15 15 15 1 1 1 1 1 1 1 16 16 16 16 16 4 4 4 4 4 4 4 29 29 2 2 2 2 2 2 2 9 9 9 9 9 9 9 13 13 1 1 1 1 1 1 1 5 5 5 5 5 8 8 8 8 8 8 8 15 15 15 15 8 8 8 8 8 8 8 11 34 34 55 44 2 2 2 2 2 2 2 7 19 19 19 19 19 19 19 26 26 26 9 9 9 9 9 9 9 33 33 9 9 9 9 9 9 9 12 12 12 12 14 15 15 15 3 3 3 3 3 3 3 30 10 10 10 10 10 10 10 21 21 21 30 27 27 17 7 7 7} + +do_execsql_test 1.2.1.12 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 +} {74 99 99 99 99 99 89 96 96 96 96 96 68 91 91 91 99 99 99 99 99 97 97 97 97 97 97 93 93 93 93 84 93 93 93 93 93 86 86 86 91 91 91 91 91 85 85 85 59 59 91 91 91 91 91 90 90 89 89 89 89 56 56 56 56 75 75 89 98 98 98 98 98 94 94 94 94 78 78 78 63 87 87 87 87 87 84 84 84 73 95 95 95 95 96 98 98 98 98 98 74 74 74 73 73 87 87 87 87 87 41 31 31 95 95 95 95 95 88 88 88 88 49 90 90 96 96 96 96 96 77 77 77 85 85 85 85 85 74 74 70 70 59 47 80 90 90 90 90 90 72 72 72 72 93 93 93 93 93 81 81 81 37 37 62 91 91 91 91 91 91 91 99 99 99 99 99 95 95 69 84 84 84 84 84 84 84 58 58 58 58 83 83 83 83 83 82 82 17 7} + +do_execsql_test 1.2.2.12 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 +} {0 23 23 23 2 2 2 2 2 38 38 38 38 38 39 6 6 6 6 6 27 27 27 8 8 8 8 8 29 23 16 16 16 16 16 16 7 7 7 7 7 61 24 24 24 24 12 12 12 3 3 3 3 3 15 15 15 15 15 1 1 1 1 1 16 16 16 16 16 36 36 4 4 4 4 4 30 29 29 29 2 2 2 2 2 37 37 9 9 9 9 9 13 13 13 13 1 1 1 1 1 5 5 5 5 5 11 11 8 8 8 8 8 15 15 15 15 22 22 8 8 8 8 8 11 34 34 55 55 55 44 2 2 2 2 2 7 29 29 19 19 19 19 19 26 26 26 36 36 9 9 9 9 9 33 33 33 33 9 9 9 9 9 12 12 12 12 14 33 15 15 15 15 3 3 3 3 3 30 30 30 10 10 10 10 10 21 21 21 30 30 30 27 27 17 7 7 7 7 7} + +do_execsql_test 1.2.1.13 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 +} {74 99 99 99 33 89 89 96 96 96 68 68 68 91 91 91 99 99 99 97 78 78 97 97 97 67 93 93 93 84 77 23 93 93 93 65 47 86 86 86 91 91 91 85 85 85 59 59 56 56 91 91 91 90 90 55 89 89 89 47 56 56 56 56 56 75 75 89 98 98 98 81 94 94 94 78 78 78 53 63 63 87 87 87 84 84 84 72 61 73 95 95 95 65 96 98 98 98 74 74 74 65 73 73 73 87 87 87 41 20 31 31 31 95 95 95 79 88 88 88 34 49 49 90 90 96 96 96 75 77 77 77 44 85 85 85 74 74 70 70 59 39 39 47 80 90 90 90 58 58 72 72 72 72 93 93 93 81 81 81 37 37 37 14 62 91 91 91 91 91 34 36 99 99 99 95 95 69 58 52 84 84 84 84 84 39 44 58 58 58 38 83 83 83 82 82 17 7 {} {}} + +do_execsql_test 1.2.2.13 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 +} {23 23 23 26 2 2 2 81 59 38 38 38 39 39 46 6 6 6 27 27 27 46 54 8 8 8 29 29 77 23 16 16 16 16 35 35 7 7 7 61 61 61 24 24 24 43 12 12 12 3 3 3 22 22 15 15 15 25 25 1 1 1 40 40 16 16 16 36 36 76 76 4 4 4 30 30 30 29 29 29 2 2 2 37 37 72 41 9 9 9 61 65 13 13 13 58 1 1 1 21 35 5 5 5 11 11 41 12 8 8 8 20 15 15 15 22 22 73 34 8 8 8 11 34 34 59 59 55 55 55 44 2 2 2 7 57 29 29 29 19 19 19 26 26 26 47 36 36 36 9 9 9 66 33 33 33 64 64 9 9 9 13 12 12 12 14 36 36 33 15 15 15 34 3 3 3 58 52 30 30 30 10 10 10 21 21 21 39 30 30 30 34 27 27 17 7 7 7 {} {}} + +do_execsql_test 1.2.1.14 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} + +do_execsql_test 1.2.2.14 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} + +do_execsql_test 1.2.1.15 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83} + +do_execsql_test 1.2.2.15 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 +} {0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} + +do_execsql_test 1.2.1.16 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7} + +do_execsql_test 1.2.2.16 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 +} {0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7} + +do_execsql_test 1.2.1.17 { + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 95 95 84 84 84 84 84 84 84 84 83 83 83 83 83 83 83 83 83 82 82 17 7 {} {} {} {}} + +do_execsql_test 1.2.2.17 { + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 +} {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 7 {} {} {} {}} + +finish_test