Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Allow min() and max() to be used as window functions. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
c16125a884a9131b707ac20033968c4c |
| User & Date: | dan 2018-05-30 20:44:58 |
Context
|
2018-06-01
| ||
| 21:00 | Allow an entire partition to be cached in a temp table for all types of window frames. This is required by nth_value() and others. check-in: b5b18f66 user: dan tags: exp-window-functions | |
|
2018-05-30
| ||
| 20:44 | Allow min() and max() to be used as window functions. check-in: c16125a8 user: dan tags: exp-window-functions | |
|
2018-05-28
| ||
| 18:30 | Further window frame tests and fixes. check-in: e74c6e91 user: dan tags: exp-window-functions | |
Changes
Changes to src/sqliteInt.h.
3475 3475 ExprList *pOrderBy; 3476 3476 u8 eType; /* TK_RANGE or TK_ROWS */ 3477 3477 u8 eStart; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ 3478 3478 u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ 3479 3479 Expr *pStart; /* Expression for "<expr> PRECEDING" */ 3480 3480 Expr *pEnd; /* Expression for "<expr> FOLLOWING" */ 3481 3481 Window *pNextWin; /* Next window function belonging to this SELECT */ 3482 + FuncDef *pFunc; 3483 + int nArg; 3484 + 3482 3485 int iEphCsr; /* Temp table used by this window */ 3483 3486 int regAccum; 3484 3487 int regResult; 3485 - FuncDef *pFunc; 3486 - int nArg; 3488 + 3489 + int csrApp; /* Function cursor (used by min/max) */ 3490 + int regApp; /* Function register (also used by min/max) */ 3487 3491 3488 3492 int regPart; 3489 3493 Expr *pOwner; /* Expression object this window is attached to */ 3490 3494 int nBufferCol; /* Number of columns in buffer table */ 3491 3495 int iArgCol; /* Offset of first argument for this function */ 3492 3496 }; 3493 3497
Changes to src/window.c.
61 61 if( p1->eEnd!=p2->eEnd ) return 1; 62 62 if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1; 63 63 if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1; 64 64 if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1; 65 65 if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1; 66 66 return 0; 67 67 } 68 + 69 +static void windowAggInit(Parse *pParse, Window *pMWin){ 70 + Window *pWin; 71 + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 72 + int funcFlags = pWin->pFunc->funcFlags; 73 + if( (funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){ 74 + ExprList *pList = pWin->pOwner->x.pList; 75 + KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pList, 0, 0); 76 + Vdbe *v = sqlite3GetVdbe(pParse); 77 + pWin->csrApp = pParse->nTab++; 78 + pWin->regApp = pParse->nMem+1; 79 + pParse->nMem += 3; 80 + if( pKeyInfo && pWin->pFunc->zName[1]=='i' ){ 81 + assert( pKeyInfo->aSortOrder[0]==0 ); 82 + pKeyInfo->aSortOrder[0] = 1; 83 + } 84 + sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pWin->csrApp, 2); 85 + sqlite3VdbeAppendP4(v, pKeyInfo, P4_KEYINFO); 86 + sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); 87 + } 88 + } 89 +} 68 90 69 91 void sqlite3WindowCodeInit(Parse *pParse, Window *pWin){ 70 92 Vdbe *v = sqlite3GetVdbe(pParse); 71 93 int nPart = (pWin->pPartition ? pWin->pPartition->nExpr : 0); 72 94 nPart += (pWin->pOrderBy ? pWin->pOrderBy->nExpr : 0); 73 95 if( nPart ){ 74 96 pWin->regPart = pParse->nMem+1; 75 97 pParse->nMem += nPart; 76 98 sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1); 77 99 } 100 + windowAggInit(pParse, pWin); 78 101 } 79 102 80 103 static void windowCheckFrameValue(Parse *pParse, int reg, int bEnd){ 81 104 static const char *azErr[] = { 82 105 "frame starting offset must be a non-negative integer", 83 106 "frame ending offset must be a non-negative integer" 84 107 }; 85 108 Vdbe *v = sqlite3GetVdbe(pParse); 86 109 int regZero = ++pParse->nMem; 87 110 88 - 89 111 sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero); 90 112 sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2); 91 113 sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg); 92 114 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort); 93 115 sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC); 94 116 } 95 117 118 +/* 119 +** Generate VM code to invoke either xStep() (if bInverse is 0) or 120 +** xInverse (if bInverse is non-zero) for each window function in the 121 +** linked list starting at pMWin. 122 +*/ 96 123 static void windowAggStep( 97 124 Parse *pParse, 98 125 Window *pMWin, 99 126 int csr, 100 127 int bInverse, 101 128 int reg 102 129 ){ 103 130 Vdbe *v = sqlite3GetVdbe(pParse); 104 131 Window *pWin; 105 132 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 106 - int i; 107 - for(i=0; i<pWin->nArg; i++){ 108 - sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i); 133 + int regArg; 134 + if( csr>=0 ){ 135 + int i; 136 + for(i=0; i<pWin->nArg; i++){ 137 + sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i); 138 + } 139 + regArg = reg; 140 + }else{ 141 + regArg = reg + pWin->iArgCol; 109 142 } 110 - sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, reg, pWin->regAccum); 111 - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); 112 - sqlite3VdbeChangeP5(v, (u8)pWin->nArg); 143 + 144 + if( pWin->csrApp ){ 145 + if( bInverse==0 ){ 146 + sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1); 147 + sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp); 148 + sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2); 149 + sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2); 150 + }else{ 151 + sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1); 152 + sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp); 153 + sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); 154 + } 155 + }else{ 156 + if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 157 + CollSeq *pColl; 158 + pColl = sqlite3ExprCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr); 159 + sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ); 160 + } 161 + sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, regArg, pWin->regAccum); 162 + sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); 163 + sqlite3VdbeChangeP5(v, (u8)pWin->nArg); 164 + } 113 165 } 114 166 } 115 167 116 168 static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){ 117 169 Vdbe *v = sqlite3GetVdbe(pParse); 118 170 Window *pWin; 119 171 120 172 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 121 - if( bFinal==0 ){ 173 + if( pWin->csrApp ){ 122 174 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); 123 - } 124 - sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg); 125 - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); 126 - if( bFinal ){ 127 - sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult); 175 + sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp); 176 + sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult); 177 + sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); 178 + if( bFinal ){ 179 + sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); 180 + } 128 181 }else{ 129 - sqlite3VdbeChangeP3(v, -1, pWin->regResult); 182 + if( bFinal==0 ){ 183 + sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); 184 + } 185 + sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg); 186 + sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); 187 + if( bFinal ){ 188 + sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult); 189 + }else{ 190 + sqlite3VdbeChangeP3(v, -1, pWin->regResult); 191 + } 130 192 } 131 193 } 132 194 } 133 195 134 196 135 197 /* 136 198 ** ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING ................................................................................ 329 391 sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord); 330 392 331 393 /* Check if this is the start of a new partition. If so, call the 332 394 ** flush_partition sub-routine. */ 333 395 if( pMWin->pPartition ){ 334 396 ExprList *pPart = pMWin->pPartition; 335 397 int nPart = (pPart ? pPart->nExpr : 0); 336 - int addrJump = 0; 337 398 int regNewPart = reg + pMWin->nBufferCol; 338 399 KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0); 339 400 340 401 addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart); 341 402 sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO); 342 - addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2); 403 + sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2); 343 404 sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart); 344 405 sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart); 345 406 } 346 407 347 408 /* Buffer the current row in the ephemeral table. */ 348 409 sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid); 349 410 sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid); ................................................................................ 351 412 /* End of the input loop */ 352 413 sqlite3WhereEnd(pWInfo); 353 414 354 415 /* Invoke "flush_partition" to deal with the final (or only) partition */ 355 416 sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart); 356 417 addrGoto = sqlite3VdbeAddOp0(v, OP_Goto); 357 418 358 - /* flush_partition: */ 419 + /* Start of "flush_partition" */ 359 420 sqlite3VdbeResolveLabel(v, lblFlushPart); 360 421 sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3); 361 422 sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr); 362 423 sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr); 363 424 364 425 /* If either regStart or regEnd are not non-negative integers, throw 365 426 ** an exception. */ ................................................................................ 366 427 if( pMWin->pStart ){ 367 428 sqlite3ExprCode(pParse, pMWin->pStart, regStart); 368 429 windowCheckFrameValue(pParse, regStart, 0); 369 430 } 370 431 if( pMWin->pEnd ){ 371 432 sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); 372 433 windowCheckFrameValue(pParse, regEnd, 1); 373 - if( pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){ 374 - assert( pMWin->eEnd==TK_FOLLOWING ); 375 - sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd); 376 - } 377 434 } 378 435 436 + /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do: 437 + ** 438 + ** regEnd = regEnd - regStart; 439 + */ 440 + if( pMWin->pEnd && pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){ 441 + assert( pMWin->eEnd==TK_FOLLOWING ); 442 + sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd); 443 + } 444 + 445 + /* Initialize the accumulator register for each window function to NULL */ 379 446 for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 380 447 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); 381 448 } 382 449 383 450 sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblFlushDone); 384 451 sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, lblFlushDone); 385 452 sqlite3VdbeChangeP5(v, 1); ................................................................................ 587 654 v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1 588 655 ); 589 656 590 657 if( addrJump ) sqlite3VdbeJumpHere(v, addrJump); 591 658 } 592 659 593 660 /* Invoke step function for window functions */ 594 - for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ 595 - sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum); 596 - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); 597 - sqlite3VdbeChangeP5(v, (u8)pWin->nArg); 598 - } 661 + windowAggStep(pParse, pMWin, -1, 0, reg); 599 662 600 663 /* Buffer the current row in the ephemeral table. */ 601 664 if( pMWin->nBufferCol>0 ){ 602 665 sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, pMWin->nBufferCol, regRecord); 603 666 }else{ 604 667 sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord); 605 668 sqlite3VdbeAppendP4(v, (void*)"", 0);
Added test/pg_common.tcl.
1 +# 2018 May 19 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 + 13 +package require sqlite3 14 +package require Pgtcl 15 + 16 +set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] 17 +sqlite3 sqlite "" 18 + 19 +proc execsql {sql} { 20 + 21 + set lSql [list] 22 + set frag "" 23 + while {[string length $sql]>0} { 24 + set i [string first ";" $sql] 25 + if {$i>=0} { 26 + append frag [string range $sql 0 $i] 27 + set sql [string range $sql $i+1 end] 28 + if {[sqlite complete $frag]} { 29 + lappend lSql $frag 30 + set frag "" 31 + } 32 + } else { 33 + set frag $sql 34 + set sql "" 35 + } 36 + } 37 + if {$frag != ""} { 38 + lappend lSql $frag 39 + } 40 + #puts $lSql 41 + 42 + set ret "" 43 + foreach stmt $lSql { 44 + set res [pg_exec $::db $stmt] 45 + set err [pg_result $res -error] 46 + if {$err!=""} { error $err } 47 + for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} { 48 + if {$i==0} { 49 + set ret [pg_result $res -getTuple 0] 50 + } else { 51 + append ret " [pg_result $res -getTuple $i]" 52 + } 53 + # lappend ret {*}[pg_result $res -getTuple $i] 54 + } 55 + pg_result $res -clear 56 + } 57 + 58 + set ret 59 +} 60 + 61 +proc execsql_test {tn sql} { 62 + set res [execsql $sql] 63 + puts $::fd "do_execsql_test $tn {" 64 + puts $::fd " [string trim $sql]" 65 + puts $::fd "} {$res}" 66 + puts $::fd "" 67 +} 68 + 69 +proc start_test {name date} { 70 + set dir [file dirname $::argv0] 71 + set output [file join $dir $name.test] 72 + set ::fd [open $output w] 73 +puts $::fd [string trimleft " 74 +# $date 75 +# 76 +# The author disclaims copyright to this source code. In place of 77 +# a legal notice, here is a blessing: 78 +# 79 +# May you do good and not evil. 80 +# May you find forgiveness for yourself and forgive others. 81 +# May you share freely, never taking more than you give. 82 +# 83 +#*********************************************************************** 84 +# This file implements regression tests for SQLite library. 85 +# 86 + 87 +#################################################### 88 +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 89 +#################################################### 90 +"] 91 + puts $::fd {set testdir [file dirname $argv0]} 92 + puts $::fd {source $testdir/tester.tcl} 93 + puts $::fd "set testprefix $name" 94 + puts $::fd "" 95 +} 96 + 97 +proc -- {args} { 98 + puts $::fd "# $args" 99 +} 100 + 101 +proc ========== {args} { 102 + puts $::fd "#[string repeat = 74]" 103 + puts $::fd "" 104 +} 105 + 106 +proc finish_test {} { 107 + puts $::fd finish_test 108 + close $::fd 109 +} 110 +
Changes to test/window2.tcl.
6 6 # May you do good and not evil. 7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # 12 12 13 -package require sqlite3 14 -package require Pgtcl 15 - 16 -set db [pg_connect -conninfo "dbname=postgres user=postgres password=postgres"] 17 -sqlite3 sqlite "" 18 - 19 -proc execsql {sql} { 20 - 21 - set lSql [list] 22 - set frag "" 23 - while {[string length $sql]>0} { 24 - set i [string first ";" $sql] 25 - if {$i>=0} { 26 - append frag [string range $sql 0 $i] 27 - set sql [string range $sql $i+1 end] 28 - if {[sqlite complete $frag]} { 29 - lappend lSql $frag 30 - set frag "" 31 - } 32 - } else { 33 - set frag $sql 34 - set sql "" 35 - } 36 - } 37 - if {$frag != ""} { 38 - lappend lSql $frag 39 - } 40 - #puts $lSql 41 - 42 - set ret "" 43 - foreach stmt $lSql { 44 - set res [pg_exec $::db $stmt] 45 - set err [pg_result $res -error] 46 - if {$err!=""} { error $err } 47 - for {set i 0} {$i < [pg_result $res -numTuples]} {incr i} { 48 - if {$i==0} { 49 - set ret [pg_result $res -getTuple 0] 50 - } else { 51 - append ret " [pg_result $res -getTuple $i]" 52 - } 53 - # lappend ret {*}[pg_result $res -getTuple $i] 54 - } 55 - pg_result $res -clear 56 - } 57 - 58 - set ret 59 -} 60 - 61 -proc execsql_test {tn sql} { 62 - set res [execsql $sql] 63 - puts $::fd "do_execsql_test $tn {" 64 - puts $::fd " [string trim $sql]" 65 - puts $::fd "} {$res}" 66 - puts $::fd "" 67 -} 68 - 69 -proc start_test {name date} { 70 - set dir [file dirname $::argv0] 71 - set output [file join $dir $name.test] 72 - set ::fd [open $output w] 73 -puts $::fd [string trimleft " 74 -# $date 75 -# 76 -# The author disclaims copyright to this source code. In place of 77 -# a legal notice, here is a blessing: 78 -# 79 -# May you do good and not evil. 80 -# May you find forgiveness for yourself and forgive others. 81 -# May you share freely, never taking more than you give. 82 -# 83 -#*********************************************************************** 84 -# This file implements regression tests for SQLite library. 85 -# 86 - 87 -#################################################### 88 -# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 89 -#################################################### 90 -"] 91 - puts $::fd {set testdir [file dirname $argv0]} 92 - puts $::fd {source $testdir/tester.tcl} 93 - puts $::fd "set testprefix $name" 94 - puts $::fd "" 95 -} 96 - 97 -proc -- {args} { 98 - puts $::fd "# $args" 99 -} 100 - 101 -proc ========== {args} { 102 - puts $::fd "#[string repeat = 74]" 103 - puts $::fd "" 104 -} 105 - 106 -proc finish_test {} { 107 - puts $::fd finish_test 108 - close $::fd 109 -} 13 +source [file join [file dirname $argv0] pg_common.tcl] 110 14 111 15 #========================================================================= 112 16 113 17 114 18 start_test window2 "2018 May 19" 115 19 116 20 execsql_test 1.0 {
Added test/window3.tcl.
1 +# 2018 May 19 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# 12 + 13 +source [file join [file dirname $argv0] pg_common.tcl] 14 + 15 +#========================================================================= 16 + 17 +start_test window3 "2018 May 31" 18 + 19 +execsql_test 1.0 { 20 + DROP TABLE IF EXISTS t2; 21 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); 22 + INSERT INTO t2(a, b) VALUES 23 + (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), 24 + (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), 25 + (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), 26 + (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), 27 + (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), 28 + (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), 29 + (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), 30 + (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), 31 + (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), 32 + (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), 33 + (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), 34 + (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), 35 + (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), 36 + (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), 37 + (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 38 + (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 39 + (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), 40 + (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), 41 + (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), 42 + (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), 43 + (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), 44 + (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), 45 + (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), 46 + (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), 47 + (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), 48 + (195,34), (196,83), (197,27), (198,82), (199,17), (200,7); 49 +} 50 + 51 +execsql_test 1.1 { 52 + SELECT max(b) OVER ( 53 + ORDER BY a 54 + ) FROM t2 55 +} 56 + 57 +foreach {tn window} { 58 + 1 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 59 + 2 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 60 + 3 "RANGE BETWEEN CURRENT ROW AND CURRENT ROW" 61 + 4 "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" 62 + 5 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING" 63 + 6 "ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING" 64 + 7 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 65 + 8 "ROWS BETWEEN 4 PRECEDING AND CURRENT ROW" 66 + 9 "ROWS BETWEEN CURRENT ROW AND CURRENT ROW" 67 + 10 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING" 68 + 11 "ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING" 69 + 12 "ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING" 70 + 13 "ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING" 71 + 14 "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 72 + 15 "ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING" 73 + 16 "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" 74 + 17 "ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING" 75 +} { 76 + execsql_test 1.2.1.$tn "SELECT max(b) OVER ( ORDER BY a $window ) FROM t2" 77 + execsql_test 1.2.2.$tn "SELECT min(b) OVER ( ORDER BY a $window ) FROM t2" 78 +} 79 + 80 +finish_test 81 +
Added test/window3.test.
1 +# 2018 May 31 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. 12 +# 13 + 14 +#################################################### 15 +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 16 +#################################################### 17 + 18 +set testdir [file dirname $argv0] 19 +source $testdir/tester.tcl 20 +set testprefix window3 21 + 22 +do_execsql_test 1.0 { 23 + DROP TABLE IF EXISTS t2; 24 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); 25 + INSERT INTO t2(a, b) VALUES 26 + (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), 27 + (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), 28 + (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), 29 + (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), 30 + (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), 31 + (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), 32 + (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), 33 + (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), 34 + (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), 35 + (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), 36 + (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), 37 + (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), 38 + (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), 39 + (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), 40 + (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 41 + (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 42 + (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), 43 + (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), 44 + (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), 45 + (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), 46 + (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), 47 + (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), 48 + (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), 49 + (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), 50 + (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), 51 + (195,34), (196,83), (197,27), (198,82), (199,17), (200,7); 52 +} {} 53 + 54 +do_execsql_test 1.1 { 55 + SELECT max(b) OVER ( 56 + ORDER BY a 57 + ) FROM t2 58 +} {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} 59 + 60 +do_execsql_test 1.2.1.1 { 61 + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 62 +} {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} 63 + 64 +do_execsql_test 1.2.2.1 { 65 + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 66 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 67 + 68 +do_execsql_test 1.2.1.2 { 69 + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 70 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 71 + 72 +do_execsql_test 1.2.2.2 { 73 + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 74 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 75 + 76 +do_execsql_test 1.2.1.3 { 77 + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 78 +} {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} 79 + 80 +do_execsql_test 1.2.2.3 { 81 + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 82 +} {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} 83 + 84 +do_execsql_test 1.2.1.4 { 85 + SELECT max(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 86 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 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} 87 + 88 +do_execsql_test 1.2.2.4 { 89 + SELECT min(b) OVER ( ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 90 +} {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} 91 + 92 +do_execsql_test 1.2.1.5 { 93 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 94 +} {{} {} {} {} 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} 95 + 96 +do_execsql_test 1.2.2.5 { 97 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING ) FROM t2 98 +} {{} {} {} {} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 99 + 100 +do_execsql_test 1.2.1.6 { 101 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 102 +} {{} {} 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} 103 + 104 +do_execsql_test 1.2.2.6 { 105 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING ) FROM t2 106 +} {{} {} 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} 107 + 108 +do_execsql_test 1.2.1.7 { 109 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 110 +} {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} 111 + 112 +do_execsql_test 1.2.2.7 { 113 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 114 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 115 + 116 +do_execsql_test 1.2.1.8 { 117 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 118 +} {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} 119 + 120 +do_execsql_test 1.2.2.8 { 121 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) FROM t2 122 +} {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} 123 + 124 +do_execsql_test 1.2.1.9 { 125 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 126 +} {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} 127 + 128 +do_execsql_test 1.2.2.9 { 129 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t2 130 +} {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} 131 + 132 +do_execsql_test 1.2.1.10 { 133 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 134 +} {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} 135 + 136 +do_execsql_test 1.2.2.10 { 137 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING ) FROM t2 138 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 139 + 140 +do_execsql_test 1.2.1.11 { 141 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 142 +} {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} 143 + 144 +do_execsql_test 1.2.2.11 { 145 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND 2 FOLLOWING ) FROM t2 146 +} {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} 147 + 148 +do_execsql_test 1.2.1.12 { 149 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 150 +} {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} 151 + 152 +do_execsql_test 1.2.2.12 { 153 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) FROM t2 154 +} {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} 155 + 156 +do_execsql_test 1.2.1.13 { 157 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 158 +} {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 {} {}} 159 + 160 +do_execsql_test 1.2.2.13 { 161 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING ) FROM t2 162 +} {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 {} {}} 163 + 164 +do_execsql_test 1.2.1.14 { 165 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 166 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99} 167 + 168 +do_execsql_test 1.2.2.14 { 169 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 170 +} {0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 171 + 172 +do_execsql_test 1.2.1.15 { 173 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 174 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 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} 175 + 176 +do_execsql_test 1.2.2.15 { 177 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t2 178 +} {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} 179 + 180 +do_execsql_test 1.2.1.16 { 181 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 182 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 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} 183 + 184 +do_execsql_test 1.2.2.16 { 185 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t2 186 +} {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} 187 + 188 +do_execsql_test 1.2.1.17 { 189 + SELECT max(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 190 +} {99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 99 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 {} {} {} {}} 191 + 192 +do_execsql_test 1.2.2.17 { 193 + SELECT min(b) OVER ( ORDER BY a ROWS BETWEEN 4 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t2 194 +} {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 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 {} {} {} {}} 195 + 196 +finish_test