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 |
| 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.014 |
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: b5b18f6613 user: dan tags: exp-window-functions) | |
|
2018-05-30
| ||
| 20:44 | Allow min() and max() to be used as window functions. (check-in: c16125a884 user: dan tags: exp-window-functions) | |
|
2018-05-28
| ||
| 18:30 | Further window frame tests and fixes. (check-in: e74c6e9185 user: dan tags: exp-window-functions) | |
Changes
Changes to src/sqliteInt.h.
| ︙ | ︙ | |||
3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 | ExprList *pOrderBy; u8 eType; /* TK_RANGE or TK_ROWS */ u8 eStart; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ Expr *pStart; /* Expression for "<expr> PRECEDING" */ Expr *pEnd; /* Expression for "<expr> FOLLOWING" */ Window *pNextWin; /* Next window function belonging to this SELECT */ int iEphCsr; /* Temp table used by this window */ int regAccum; int regResult; | > > > | | > | 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 3489 3490 3491 3492 3493 3494 3495 3496 3497 | ExprList *pOrderBy; u8 eType; /* TK_RANGE or TK_ROWS */ u8 eStart; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ Expr *pStart; /* Expression for "<expr> PRECEDING" */ Expr *pEnd; /* Expression for "<expr> 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; 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 */ }; |
| ︙ | ︙ |
Changes to src/window.c.
| ︙ | ︙ | |||
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
if( p1->eEnd!=p2->eEnd ) return 1;
if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
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;
}
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);
if( nPart ){
pWin->regPart = pParse->nMem+1;
pParse->nMem += nPart;
sqlite3VdbeAddOp3(v, OP_Null, 0, pWin->regPart, pWin->regPart+nPart-1);
}
}
static void windowCheckFrameValue(Parse *pParse, int reg, int bEnd){
static const char *azErr[] = {
"frame starting offset must be a non-negative integer",
"frame ending offset must be a non-negative integer"
};
Vdbe *v = sqlite3GetVdbe(pParse);
int regZero = ++pParse->nMem;
| > > > > > > > > > > > > > > > > > > > > > > > < > > > > > > > | | | | > > > > > > > > > > > > > > > > > > > > > > | | | > > > > > > > > > > | | | | | | | | | > | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 |
if( p1->eEnd!=p2->eEnd ) return 1;
if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1;
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);
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",
"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,
int reg
){
Vdbe *v = sqlite3GetVdbe(pParse);
Window *pWin;
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
int regArg;
if( csr>=0 ){
int i;
for(i=0; i<pWin->nArg; i++){
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+i, reg+i);
}
regArg = reg;
}else{
regArg = reg + pWin->iArgCol;
}
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( pWin->csrApp ){
sqlite3VdbeAddOp2(v, OP_Null, 0, 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{
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);
}
}
}
}
/*
** ROWS BETWEEN <expr1> PRECEDING AND <expr2> FOLLOWING
|
| ︙ | ︙ | |||
329 330 331 332 333 334 335 |
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);
/* 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);
| < | | > > > > > > | | | | | | | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 |
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);
/* 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 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);
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. */
sqlite3VdbeAddOp2(v, OP_NewRowid, pMWin->iEphCsr, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, pMWin->iEphCsr, regRecord, regRowid);
/* End of the input loop */
sqlite3WhereEnd(pWInfo);
/* Invoke "flush_partition" to deal with the final (or only) partition */
sqlite3VdbeAddOp2(v, OP_Gosub, regFlushPart, lblFlushPart);
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
/* 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);
/* If either regStart or regEnd are not non-negative integers, throw
** an exception. */
if( pMWin->pStart ){
sqlite3ExprCode(pParse, pMWin->pStart, regStart);
windowCheckFrameValue(pParse, regStart, 0);
}
if( pMWin->pEnd ){
sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
windowCheckFrameValue(pParse, regEnd, 1);
}
/* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> 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);
sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, lblFlushDone);
sqlite3VdbeChangeP5(v, 1);
|
| ︙ | ︙ | |||
587 588 589 590 591 592 593 |
v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
);
if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
}
/* Invoke step function for window functions */
| < | < < < | 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 |
v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
);
if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
}
/* Invoke step function for window functions */
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{
sqlite3VdbeAddOp2(v, OP_Blob, 0, regRecord);
sqlite3VdbeAppendP4(v, (void*)"", 0);
|
| ︙ | ︙ |
Added test/pg_common.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 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
}
|
Changes to test/window2.tcl.
1 2 3 4 5 6 7 8 9 10 11 12 | # 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. # #*********************************************************************** # | < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# 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 window2 "2018 May 19"
execsql_test 1.0 {
|
| ︙ | ︙ |
Added test/window3.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 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.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 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
|