From 37d296a717abb896c72368e3e09d833de2415838 Mon Sep 17 00:00:00 2001 From: dan Date: Tue, 24 Sep 2019 20:20:05 +0000 Subject: [PATCH] Bug fixes for window frames of the form (... RANGE BETWEEN b PRECEDING AND a PRECEDING) or (... RANGE a FOLLOWING AND b FOLLOWING) where (a > b). FossilOrigin-Name: 040e196a8be3ca41b9365310ab88c2a3cc84b918a6511c77a6d95d4b4e0da3ed --- manifest | 16 +-- manifest.uuid | 2 +- src/window.c | 355 ++++++++++++++++++++++++++-------------------- test/windowB.test | 67 +++++++++ 4 files changed, 279 insertions(+), 161 deletions(-) diff --git a/manifest b/manifest index 5e5647e6e9..88b5581386 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Omit\sthe\ssqlite3IntTokens\sarray\sconstant\sfor\sa\scode\ssimplification. -D 2019-09-23T12:38:10.519 +C Bug\sfixes\sfor\swindow\sframes\sof\sthe\sform\s(...\sRANGE\sBETWEEN\sb\sPRECEDING\sAND\sa\sPRECEDING)\sor\s(...\sRANGE\sa\sFOLLOWING\sAND\sb\sFOLLOWING)\swhere\s(a\s>\sb). +D 2019-09-24T20:20:05.843 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -615,7 +615,7 @@ F src/where.c 9f3f23efc45934e7b7ea6c0c1042420b73053e7c3264feef6faf9ce6fbd5df61 F src/whereInt.h 2c6bae136a7c0be6ff75dc36950d1968c67d005c8e51d7a9d77cb996bb4843d9 F src/wherecode.c 535c8e228478fd971b9a5b6cb6773995b0fbf7020d5989508a5094ce5b8cd95b F src/whereexpr.c 05c283d26aa9c3f5d1bf13a5f6a573b43295b9db280eff18e26f97d7d7f119b4 -F src/window.c af649dd0627e925b4ad0d6ae9fd12c3d4ececdb8b872a1785a28da41fcc06148 +F src/window.c 1dcacaeee25487eb222f9c5e226eb6821265a22a71432f570b3c41d9754717ba F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/affinity2.test da465d3d490ab24ef64f7715b5953343a4967762b9350b29eb1462879ff3fb9e F test/affinity3.test 6a101af2fc945ce2912f6fe54dd646018551710d @@ -1721,7 +1721,7 @@ F test/window8.tcl f2711aa3571e4e6b0dad98db8d95fd6cb8d9db0c92bbdf535f153b07606a1 F test/window8.test c4331b27a6f66d69fa8f8bab10cc731db1a81d293ae108a68f7c3487fa94e65b F test/window9.test 20a6b590be718b6bc98a5356d4396d6cdf19329c547da084fa225b92d68e1693 F test/windowA.test 6d63dc1260daa17141a55007600581778523a8b420629f1282d2acfc36af23be -F test/windowB.test be2ecc1298021e87eef375af35906288cc929fc21f2651139f7dd50a5844b120 +F test/windowB.test f9a79e1bd669d513c6cb2a7b448773f7954b8e35e2b843b6c371475402b412b0 F test/windowerr.tcl f5acd6fbc210d7b5546c0e879d157888455cd4a17a1d3f28f07c1c8a387019e0 F test/windowerr.test a8b752402109c15aa1c5efe1b93ccb0ce1ef84fa964ae1cd6684dd0b3cc1819b F test/windowfault.test a90b397837209f15e54afa62e8be39b2759a0101fae04e05a08bcc50e243a452 @@ -1845,7 +1845,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 82e5dcf5c1d500ed82f398b38fdae0f30033804e897fbab3c10f1e15e2abedef -R 6576418b2448b3d64f3c208d7dfcd054 -U drh -Z be0d84726c6176673fd11b7076b1f111 +P f907395ef5a2dc1d084b6a286af00de4c742cf12d4f347c21e1b757786508f57 +R 0b002338bf10d899e34ef318ab4f702e +U dan +Z a41a183eaebde05b792c6177f74029dd diff --git a/manifest.uuid b/manifest.uuid index 66b72c1f20..acbb0d2b1c 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f907395ef5a2dc1d084b6a286af00de4c742cf12d4f347c21e1b757786508f57 \ No newline at end of file +040e196a8be3ca41b9365310ab88c2a3cc84b918a6511c77a6d95d4b4e0da3ed \ No newline at end of file diff --git a/src/window.c b/src/window.c index afbb7aaa18..f6fa7f6841 100644 --- a/src/window.c +++ b/src/window.c @@ -1408,6 +1408,110 @@ static int windowArgCount(Window *pWin){ return (pList ? pList->nExpr : 0); } +typedef struct WindowCodeArg WindowCodeArg; +typedef struct WindowCsrAndReg WindowCsrAndReg; + +/* +** See comments above struct WindowCodeArg. +*/ +struct WindowCsrAndReg { + int csr; /* Cursor number */ + int reg; /* First in array of peer values */ +}; + +/* +** A single instance of this structure is allocated on the stack by +** sqlite3WindowCodeStep() and a pointer to it passed to the various helper +** routines. This is to reduce the number of arguments required by each +** helper function. +** +** regArg: +** Each window function requires an accumulator register (just as an +** ordinary aggregate function does). This variable is set to the first +** in an array of accumulator registers - one for each window function +** in the WindowCodeArg.pMWin list. +** +** eDelete: +** The window functions implementation sometimes caches the input rows +** that it processes in a temporary table. If it is not zero, this +** variable indicates when rows may be removed from the temp table (in +** order to reduce memory requirements - it would always be safe just +** to leave them there). Possible values for eDelete are: +** +** WINDOW_RETURN_ROW: +** An input row can be discarded after it is returned to the caller. +** +** WINDOW_AGGINVERSE: +** An input row can be discarded after the window functions xInverse() +** callbacks have been invoked in it. +** +** WINDOW_AGGSTEP: +** An input row can be discarded after the window functions xStep() +** callbacks have been invoked in it. +** +** start,current,end +** Consider a window-frame similar to the following: +** +** (ORDER BY a, b GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING) +** +** The windows functions implmentation caches the input rows in a temp +** table, sorted by "a, b" (it actually populates the cache lazily, and +** aggressively removes rows once they are no longer required, but that's +** a mere detail). It keeps three cursors open on the temp table. One +** (current) that points to the next row to return to the query engine +** once its window function values have been calculated. Another (end) +** points to the next row to call the xStep() method of each window function +** on (so that it is 2 groups ahead of current). And a third (start) that +** points to the next row to call the xInverse() method of each window +** function on. +** +** Each cursor (start, current and end) consists of a VDBE cursor +** (WindowCsrAndReg.csr) and an array of registers (starting at +** WindowCodeArg.reg) that always contains a copy of the peer values +** read from the corresponding cursor. +** +** Depending on the window-frame in question, all three cursors may not +** be required. In this case both WindowCodeArg.csr and reg are set to +** 0. +*/ +struct WindowCodeArg { + Parse *pParse; /* Parse context */ + Window *pMWin; /* First in list of functions being processed */ + Vdbe *pVdbe; /* VDBE object */ + int addrGosub; /* OP_Gosub to this address to return one row */ + int regGosub; /* Register used with OP_Gosub(addrGosub) */ + int regArg; /* First in array of accumulator registers */ + int eDelete; /* See above */ + int regStart; /* Value of PRECEDING */ + int regEnd; /* Value of FOLLOWING */ + + WindowCsrAndReg start; + WindowCsrAndReg current; + WindowCsrAndReg end; +}; + +/* +** Generate VM code to read the window frames peer values from cursor csr into +** an array of registers starting at reg. +*/ +static void windowReadPeerValues( + WindowCodeArg *p, + int csr, + int reg +){ + Window *pMWin = p->pMWin; + ExprList *pOrderBy = pMWin->pOrderBy; + if( pOrderBy ){ + Vdbe *v = sqlite3GetVdbe(p->pParse); + ExprList *pPart = pMWin->pPartition; + int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0); + int i; + for(i=0; inExpr; i++){ + sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i); + } + } +} + /* ** Generate VM code to invoke either xStep() (if bInverse is 0) or ** xInverse (if bInverse is non-zero) for each window function in the @@ -1428,12 +1532,13 @@ static int windowArgCount(Window *pWin){ ** number of rows in the current partition. */ static void windowAggStep( - Parse *pParse, + WindowCodeArg *p, Window *pMWin, /* Linked list of window functions */ int csr, /* Read arguments from this cursor */ int bInverse, /* True to invoke xInverse instead of xStep */ int reg /* Array of registers */ ){ + Parse *pParse = p->pParse; Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ @@ -1479,6 +1584,7 @@ static void windowAggStep( sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1); }else if( pFunc->xSFunc!=noopStepFunc ){ int addrIf = 0; + int addrIf2 = 0; if( pWin->pFilter ){ int regTmp; assert( pWin->bExprArgs || !nArg ||nArg==pWin->pOwner->x.pList->nExpr ); @@ -1489,6 +1595,39 @@ static void windowAggStep( VdbeCoverage(v); sqlite3ReleaseTempReg(pParse, regTmp); } + + + /* If this is a (RANGE BETWEEN a PRECEDING AND b PRECEDING) or a + ** (RANGE BETWEEN b FOLLOWING AND a FOLLOWING) frame and (b > a), + ** omit the OP_AggStep or OP_AggInverse if the peer value is numeric. + ** A numeric peer value is one for which the following is true: + ** + ** (peer IS NOT NULL AND peer < '') + */ + if( pWin->eFrmType==TK_RANGE + && pWin->eStart==pWin->eEnd + && pWin->eStart==TK_PRECEDING + ){ + int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le); + int regPeer = sqlite3GetTempReg(pParse); + int regString = sqlite3GetTempReg(pParse); + int lbl = sqlite3VdbeMakeLabel(pParse); + VdbeModuleComment((v, "windowAggStep \"peer is numeric?\" test")); + sqlite3VdbeAddOp3(v, op, p->regStart, lbl, p->regEnd); + VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because */ + VdbeCoverageNeverNullIf(v, op==OP_Le); /* values previously checked */ + windowReadPeerValues(p, csr, regPeer); + sqlite3VdbeAddOp2(v, OP_IsNull, regPeer, lbl); + sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC); + addrIf2 = sqlite3VdbeAddOp3(v, OP_Lt, regString, 0, regPeer); + sqlite3ReleaseTempReg(pParse, regPeer); + sqlite3ReleaseTempReg(pParse, regString); + sqlite3VdbeResolveLabel(v, lbl); + VdbeModuleComment((v, "windowAggStep end \"peer is numeric?\"")); + assert( pWin->eStart==TK_PRECEDING || pWin->eStart==TK_FOLLOWING ); + assert( pMWin->pOrderBy && pMWin->pOrderBy->nExpr==1 ); + } + if( pWin->bExprArgs ){ int iStart = sqlite3VdbeCurrentAddr(v); VdbeOp *pOp, *pEnd; @@ -1518,90 +1657,11 @@ static void windowAggStep( sqlite3ReleaseTempRange(pParse, regArg, nArg); } if( addrIf ) sqlite3VdbeJumpHere(v, addrIf); + if( addrIf2 ) sqlite3VdbeJumpHere(v, addrIf2); } } } -typedef struct WindowCodeArg WindowCodeArg; -typedef struct WindowCsrAndReg WindowCsrAndReg; - -/* -** See comments above struct WindowCodeArg. -*/ -struct WindowCsrAndReg { - int csr; /* Cursor number */ - int reg; /* First in array of peer values */ -}; - -/* -** A single instance of this structure is allocated on the stack by -** sqlite3WindowCodeStep() and a pointer to it passed to the various helper -** routines. This is to reduce the number of arguments required by each -** helper function. -** -** regArg: -** Each window function requires an accumulator register (just as an -** ordinary aggregate function does). This variable is set to the first -** in an array of accumulator registers - one for each window function -** in the WindowCodeArg.pMWin list. -** -** eDelete: -** The window functions implementation sometimes caches the input rows -** that it processes in a temporary table. If it is not zero, this -** variable indicates when rows may be removed from the temp table (in -** order to reduce memory requirements - it would always be safe just -** to leave them there). Possible values for eDelete are: -** -** WINDOW_RETURN_ROW: -** An input row can be discarded after it is returned to the caller. -** -** WINDOW_AGGINVERSE: -** An input row can be discarded after the window functions xInverse() -** callbacks have been invoked in it. -** -** WINDOW_AGGSTEP: -** An input row can be discarded after the window functions xStep() -** callbacks have been invoked in it. -** -** start,current,end -** Consider a window-frame similar to the following: -** -** (ORDER BY a, b GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING) -** -** The windows functions implmentation caches the input rows in a temp -** table, sorted by "a, b" (it actually populates the cache lazily, and -** aggressively removes rows once they are no longer required, but that's -** a mere detail). It keeps three cursors open on the temp table. One -** (current) that points to the next row to return to the query engine -** once its window function values have been calculated. Another (end) -** points to the next row to call the xStep() method of each window function -** on (so that it is 2 groups ahead of current). And a third (start) that -** points to the next row to call the xInverse() method of each window -** function on. -** -** Each cursor (start, current and end) consists of a VDBE cursor -** (WindowCsrAndReg.csr) and an array of registers (starting at -** WindowCodeArg.reg) that always contains a copy of the peer values -** read from the corresponding cursor. -** -** Depending on the window-frame in question, all three cursors may not -** be required. In this case both WindowCodeArg.csr and reg are set to -** 0. -*/ -struct WindowCodeArg { - Parse *pParse; /* Parse context */ - Window *pMWin; /* First in list of functions being processed */ - Vdbe *pVdbe; /* VDBE object */ - int addrGosub; /* OP_Gosub to this address to return one row */ - int regGosub; /* Register used with OP_Gosub(addrGosub) */ - int regArg; /* First in array of accumulator registers */ - int eDelete; /* See above */ - - WindowCsrAndReg start; - WindowCsrAndReg current; - WindowCsrAndReg end; -}; - /* ** Values that may be passed as the second argument to windowCodeOp(). */ @@ -1609,28 +1669,6 @@ struct WindowCodeArg { #define WINDOW_AGGINVERSE 2 #define WINDOW_AGGSTEP 3 -/* -** Generate VM code to read the window frames peer values from cursor csr into -** an array of registers starting at reg. -*/ -static void windowReadPeerValues( - WindowCodeArg *p, - int csr, - int reg -){ - Window *pMWin = p->pMWin; - ExprList *pOrderBy = pMWin->pOrderBy; - if( pOrderBy ){ - Vdbe *v = sqlite3GetVdbe(p->pParse); - ExprList *pPart = pMWin->pPartition; - int iColOff = pMWin->nBufferCol + (pPart ? pPart->nExpr : 0); - int i; - for(i=0; inExpr; i++){ - sqlite3VdbeAddOp3(v, OP_Column, csr, iColOff+i, reg+i); - } - } -} - /* ** Generate VM code to invoke either xValue() (bFin==0) or xFinalize() ** (bFin==1) for each window function in the linked list starting at @@ -1693,6 +1731,8 @@ static void windowFullScan(WindowCodeArg *p){ int addrNext; int csr; + VdbeModuleComment((v, "windowFullScan begin")); + assert( pMWin!=0 ); csr = pMWin->csrApp; nPeer = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0); @@ -1749,7 +1789,7 @@ static void windowFullScan(WindowCodeArg *p){ if( addrEq ) sqlite3VdbeJumpHere(v, addrEq); } - windowAggStep(pParse, pMWin, csr, 0, p->regArg); + windowAggStep(p, pMWin, csr, 0, p->regArg); sqlite3VdbeResolveLabel(v, lblNext); sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext); @@ -1764,6 +1804,7 @@ static void windowFullScan(WindowCodeArg *p){ } windowAggFinal(p, 1); + VdbeModuleComment((v, "windowFullScan end")); } /* @@ -2094,9 +2135,7 @@ static int windowCodeOp( Window *pMWin = p->pMWin; int ret = 0; Vdbe *v = p->pVdbe; - int addrIf = 0; int addrContinue = 0; - int addrGoto = 0; int bPeer = (pMWin->eFrmType!=TK_ROWS); int lblDone = sqlite3VdbeMakeLabel(pParse); @@ -2129,7 +2168,7 @@ static int windowCodeOp( ); } }else{ - addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1); + sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, lblDone, 1); VdbeCoverage(v); } } @@ -2152,7 +2191,7 @@ static int windowCodeOp( assert( pMWin->regEndRowid ); sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1); }else{ - windowAggStep(pParse, pMWin, csr, 1, p->regArg); + windowAggStep(p, pMWin, csr, 1, p->regArg); } break; @@ -2164,7 +2203,7 @@ static int windowCodeOp( assert( pMWin->regEndRowid ); sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1); }else{ - windowAggStep(pParse, pMWin, csr, 0, p->regArg); + windowAggStep(p, pMWin, csr, 0, p->regArg); } break; } @@ -2182,13 +2221,29 @@ static int windowCodeOp( sqlite3VdbeAddOp2(v, OP_Next, csr, sqlite3VdbeCurrentAddr(v)+1+bPeer); VdbeCoverage(v); if( bPeer ){ - addrGoto = sqlite3VdbeAddOp0(v, OP_Goto); + sqlite3VdbeAddOp2(v, OP_Goto, 0, lblDone); } } if( bPeer ){ int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0); - int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0); + int regTmp; + + /* If this is a (RANGE BETWEEN a FOLLOWING AND b FOLLOWING), ensure + ** the start cursor does not advance past the end cursor within the + ** temporary table. It otherwise might, if (a>b). */ + if( pMWin->eStart==TK_FOLLOWING && pMWin->eEnd==TK_FOLLOWING + && pMWin->eFrmType==TK_RANGE && op==WINDOW_AGGINVERSE + ){ + int regRowid1 = sqlite3GetTempReg(pParse); + int regRowid2 = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp2(v, OP_Rowid, p->start.csr, regRowid1); + sqlite3VdbeAddOp2(v, OP_Rowid, p->end.csr, regRowid2); + sqlite3VdbeAddOp3(v, OP_Ge, regRowid2, lblDone, regRowid1); + sqlite3ReleaseTempReg(pParse, regRowid1); + sqlite3ReleaseTempReg(pParse, regRowid2); + } + regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0); windowReadPeerValues(p, csr, regTmp); windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue); sqlite3ReleaseTempRange(pParse, regTmp, nReg); @@ -2198,8 +2253,6 @@ static int windowCodeOp( sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNextRange); } sqlite3VdbeResolveLabel(v, lblDone); - if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto); - if( addrIf ) sqlite3VdbeJumpHere(v, addrIf); return ret; } @@ -2552,23 +2605,23 @@ static int windowExprGtZero(Parse *pParse, Expr *pExpr){ ** regEnd = ** regStart = ** }else{ -** if( (csrEnd.key + regEnd) <= csrCurrent.key ){ +** while( (csrEnd.key + regEnd) <= csrCurrent.key ){ ** AGGSTEP ** } +** RETURN_ROW ** while( (csrStart.key + regStart) < csrCurrent.key ){ ** AGGINVERSE ** } -** RETURN_ROW ** } ** } ** flush: ** while( (csrEnd.key + regEnd) <= csrCurrent.key ){ ** AGGSTEP ** } +** RETURN_ROW ** while( (csrStart.key + regStart) < csrCurrent.key ){ ** AGGINVERSE ** } -** RETURN_ROW ** ** RANGE BETWEEN FOLLOWING AND FOLLOWING ** @@ -2625,8 +2678,6 @@ void sqlite3WindowCodeStep( int addrGosubFlush = 0; /* Address of OP_Gosub to flush: */ int addrInteger = 0; /* Address of OP_Integer */ int addrEmpty; /* Address of OP_Rewind in flush: */ - int regStart = 0; /* Value of PRECEDING */ - int regEnd = 0; /* Value of FOLLOWING */ int regNew; /* Array of registers holding new input row */ int regRecord; /* regNew array in record form */ int regRowid; /* Rowid for regRecord in eph table */ @@ -2705,10 +2756,10 @@ void sqlite3WindowCodeStep( ** clause, allocate registers to store the results of evaluating each ** . */ if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){ - regStart = ++pParse->nMem; + s.regStart = ++pParse->nMem; } if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){ - regEnd = ++pParse->nMem; + s.regEnd = ++pParse->nMem; } /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of @@ -2763,18 +2814,18 @@ void sqlite3WindowCodeStep( /* This block is run for the first row of each partition */ s.regArg = windowInitAccum(pParse, pMWin); - if( regStart ){ - sqlite3ExprCode(pParse, pMWin->pStart, regStart); - windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE ? 3 : 0)); + if( s.regStart ){ + sqlite3ExprCode(pParse, pMWin->pStart, s.regStart); + windowCheckValue(pParse, s.regStart, 0 + (pMWin->eFrmType==TK_RANGE?3:0)); } - if( regEnd ){ - sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); - windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE ? 3 : 0)); + if( s.regEnd ){ + sqlite3ExprCode(pParse, pMWin->pEnd, s.regEnd); + windowCheckValue(pParse, s.regEnd, 1 + (pMWin->eFrmType==TK_RANGE?3:0)); } - if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && regStart ){ + if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && s.regStart ){ int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le); - int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd); + int addrGe = sqlite3VdbeAddOp3(v, op, s.regStart, 0, s.regEnd); VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound */ VdbeCoverageNeverNullIf(v, op==OP_Le); /* values previously checked */ windowAggFinal(&s, 0); @@ -2785,9 +2836,9 @@ void sqlite3WindowCodeStep( sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd); sqlite3VdbeJumpHere(v, addrGe); } - if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && regEnd ){ + if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && s.regEnd ){ assert( pMWin->eEnd==TK_FOLLOWING ); - sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart); + sqlite3VdbeAddOp3(v, OP_Subtract, s.regStart, s.regEnd, s.regStart); } if( pMWin->eStart!=TK_UNBOUNDED ){ @@ -2819,23 +2870,23 @@ void sqlite3WindowCodeStep( if( pMWin->eFrmType==TK_RANGE ){ int lbl = sqlite3VdbeMakeLabel(pParse); int addrNext = sqlite3VdbeCurrentAddr(v); - windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl); - windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + windowCodeRangeTest(&s, OP_Ge, s.current.csr, s.regEnd, s.end.csr, lbl); + windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext); sqlite3VdbeResolveLabel(v, lbl); }else{ - windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 0); + windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); } } }else if( pMWin->eEnd==TK_PRECEDING ){ int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE); - windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0); - if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0); + if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); }else{ int addr = 0; windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); @@ -2843,24 +2894,24 @@ void sqlite3WindowCodeStep( if( pMWin->eFrmType==TK_RANGE ){ int lbl = 0; addr = sqlite3VdbeCurrentAddr(v); - if( regEnd ){ + if( s.regEnd ){ lbl = sqlite3VdbeMakeLabel(pParse); - windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl); + windowCodeRangeTest(&s, OP_Ge, s.current.csr,s.regEnd,s.end.csr,lbl); } windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); - if( regEnd ){ + windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + if( s.regEnd ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, lbl); } }else{ - if( regEnd ){ - addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1); + if( s.regEnd ){ + addr = sqlite3VdbeAddOp3(v, OP_IfPos, s.regEnd, 0, 1); VdbeCoverage(v); } windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); - if( regEnd ) sqlite3VdbeJumpHere(v, addr); + windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + if( s.regEnd ) sqlite3VdbeJumpHere(v, addr); } } } @@ -2879,8 +2930,8 @@ void sqlite3WindowCodeStep( VdbeCoverage(v); if( pMWin->eEnd==TK_PRECEDING ){ int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE); - windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0); - if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0); + if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); }else if( pMWin->eStart==TK_FOLLOWING ){ int addrStart; @@ -2890,18 +2941,18 @@ void sqlite3WindowCodeStep( windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); if( pMWin->eFrmType==TK_RANGE ){ addrStart = sqlite3VdbeCurrentAddr(v); - addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1); + addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1); addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1); }else if( pMWin->eEnd==TK_UNBOUNDED ){ addrStart = sqlite3VdbeCurrentAddr(v); - addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1); + addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, s.regStart, 1); addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1); }else{ assert( pMWin->eEnd==TK_FOLLOWING ); addrStart = sqlite3VdbeCurrentAddr(v); - addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 1); - addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1); + addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 1); + addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1); } sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart); sqlite3VdbeJumpHere(v, addrBreak2); @@ -2916,7 +2967,7 @@ void sqlite3WindowCodeStep( windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); addrStart = sqlite3VdbeCurrentAddr(v); addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1); - windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart); sqlite3VdbeJumpHere(v, addrBreak); } diff --git a/test/windowB.test b/test/windowB.test index d28ee0da19..7390556779 100644 --- a/test/windowB.test +++ b/test/windowB.test @@ -230,4 +230,71 @@ do_catchsql_test 4.3 { SELECT 1 WINDOW win AS (PARTITION BY fake_column); } {0 1} +#------------------------------------------------------------------------- +reset_db +do_execsql_test 5.0 { + CREATE TABLE t1(a, c); + CREATE INDEX i1 ON t1(a); + + INSERT INTO t1 VALUES(0, 421); + INSERT INTO t1 VALUES(1, 844); + INSERT INTO t1 VALUES(2, 1001); +} + +do_execsql_test 5.1 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING + ) FROM t1; +} {0 {} 1 {} 2 {}} + +do_execsql_test 5.2 { + INSERT INTO t1 VALUES(NULL, 123); + INSERT INTO t1 VALUES(NULL, 111); + INSERT INTO t1 VALUES('xyz', 222); + INSERT INTO t1 VALUES('xyz', 333); + + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING + ) FROM t1; +} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} + +do_execsql_test 5.3 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING + ) FROM t1; +} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} + +do_execsql_test 5.4 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS + ) FROM t1; +} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} + +do_execsql_test 5.5 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS + ) FROM t1; +} {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 6.0 { + CREATE TABLE t1(a, c); + CREATE INDEX i1 ON t1(a); + + INSERT INTO t1 VALUES(7, 997); + INSERT INTO t1 VALUES(8, 997); + INSERT INTO t1 VALUES('abc', 1001); +} +do_execsql_test 6.1 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING + ) FROM t1; +} {7 {} 8 {} abc 1001} +do_execsql_test 6.2 { + SELECT a, sum(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS + ) FROM t1; +} {7 {} 8 {} abc 1001} + finish_test -- 2.47.2