Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -1480,12 +1480,10 @@ 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; }; @@ -1597,42 +1595,11 @@ sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp); addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1); 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 regPeer = sqlite3GetTempReg(pParse); - int regString = sqlite3GetTempReg(pParse); - int lbl = sqlite3VdbeMakeLabel(pParse); - VdbeModuleComment((v, "windowAggStep \"peer is numeric?\" test")); - assert( pMWin->eStart==TK_PRECEDING ); /* because pWin same as pMWin */ - sqlite3VdbeAddOp3(v, OP_Le, p->regStart, lbl, p->regEnd); - VdbeCoverageNeverNull(v); /* because 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; nArg = pWin->pOwner->x.pList->nExpr; @@ -2178,10 +2145,28 @@ if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){ windowAggFinal(p, 0); } addrContinue = sqlite3VdbeCurrentAddr(v); + + /* If this is a (RANGE BETWEEN a FOLLOWING AND b FOLLOWING) or + ** (RANGE BETWEEN b PRECEDING AND a PRECEDING) frame, ensure the + ** start cursor does not advance past the end cursor within the + ** temporary table. It otherwise might, if (a>b). */ + if( pMWin->eStart==pMWin->eEnd && regCountdown + && 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); + assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ); + } + switch( op ){ case WINDOW_RETURN_ROW: csr = p->current.csr; reg = p->current.reg; windowReturnOneRow(p); @@ -2228,27 +2213,11 @@ } } if( bPeer ){ int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 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); + int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0); windowReadPeerValues(p, csr, regTmp); windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue); sqlite3ReleaseTempRange(pParse, regTmp, nReg); } @@ -2609,24 +2578,24 @@ ** regStart = ** }else{ ** 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 ** ** ... loop started by sqlite3WhereBegin() ... ** if( new partition ){ @@ -2687,10 +2656,12 @@ int regNewPeer = 0; /* Peer values for new row (part of regNew) */ int regPeer = 0; /* Peer values for current row */ int regFlushPart = 0; /* Register for "Gosub flush_partition" */ WindowCodeArg s; /* Context object for sub-routines */ int lblWhereEnd; /* Label just before sqlite3WhereEnd() code */ + int regStart = 0; /* Value of PRECEDING */ + int regEnd = 0; /* Value of FOLLOWING */ assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED ); assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT @@ -2757,14 +2728,14 @@ /* If the window frame contains an " PRECEDING" or " FOLLOWING" ** clause, allocate registers to store the results of evaluating each ** . */ if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){ - s.regStart = ++pParse->nMem; + regStart = ++pParse->nMem; } if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){ - s.regEnd = ++pParse->nMem; + regEnd = ++pParse->nMem; } /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of ** registers to store copies of the ORDER BY expressions (peer values) ** for the main loop, and for each cursor (start, current and end). */ @@ -2815,22 +2786,22 @@ VdbeCoverageNeverNull(v); /* This block is run for the first row of each partition */ s.regArg = windowInitAccum(pParse, pMWin); - if( s.regStart ){ - sqlite3ExprCode(pParse, pMWin->pStart, s.regStart); - windowCheckValue(pParse, s.regStart, 0 + (pMWin->eFrmType==TK_RANGE?3:0)); + if( regStart ){ + sqlite3ExprCode(pParse, pMWin->pStart, regStart); + windowCheckValue(pParse, regStart, 0 + (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( regEnd ){ + sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); + windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE?3:0)); } - if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && s.regStart ){ + if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && regStart ){ int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le); - int addrGe = sqlite3VdbeAddOp3(v, op, s.regStart, 0, s.regEnd); + int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd); VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound */ VdbeCoverageNeverNullIf(v, op==OP_Le); /* values previously checked */ windowAggFinal(&s, 0); sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1); VdbeCoverageNeverTaken(v); @@ -2837,13 +2808,13 @@ windowReturnOneRow(&s); sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr); sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd); sqlite3VdbeJumpHere(v, addrGe); } - if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && s.regEnd ){ + if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && regEnd ){ assert( pMWin->eEnd==TK_FOLLOWING ); - sqlite3VdbeAddOp3(v, OP_Subtract, s.regStart, s.regEnd, s.regStart); + sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart); } if( pMWin->eStart!=TK_UNBOUNDED ){ sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1); VdbeCoverageNeverTaken(v); @@ -2871,52 +2842,52 @@ windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); if( pMWin->eEnd!=TK_UNBOUNDED ){ if( pMWin->eFrmType==TK_RANGE ){ int lbl = sqlite3VdbeMakeLabel(pParse); int addrNext = sqlite3VdbeCurrentAddr(v); - windowCodeRangeTest(&s, OP_Ge, s.current.csr, s.regEnd, s.end.csr, lbl); - windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl); + windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext); sqlite3VdbeResolveLabel(v, lbl); }else{ - windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0); + windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); } } }else if( pMWin->eEnd==TK_PRECEDING ){ int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE); - windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0); - if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0); + if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); }else{ int addr = 0; windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); if( pMWin->eEnd!=TK_UNBOUNDED ){ if( pMWin->eFrmType==TK_RANGE ){ int lbl = 0; addr = sqlite3VdbeCurrentAddr(v); - if( s.regEnd ){ + if( regEnd ){ lbl = sqlite3VdbeMakeLabel(pParse); - windowCodeRangeTest(&s, OP_Ge, s.current.csr,s.regEnd,s.end.csr,lbl); + windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl); } windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); - if( s.regEnd ){ + windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + if( regEnd ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, addr); sqlite3VdbeResolveLabel(v, lbl); } }else{ - if( s.regEnd ){ - addr = sqlite3VdbeAddOp3(v, OP_IfPos, s.regEnd, 0, 1); + if( regEnd ){ + addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1); VdbeCoverage(v); } windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); - windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); - if( s.regEnd ) sqlite3VdbeJumpHere(v, addr); + windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); + if( regEnd ) sqlite3VdbeJumpHere(v, addr); } } } /* End of the main input loop */ @@ -2931,33 +2902,33 @@ addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite); VdbeCoverage(v); if( pMWin->eEnd==TK_PRECEDING ){ int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE); - windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0); - if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0); + if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0); }else if( pMWin->eStart==TK_FOLLOWING ){ int addrStart; int addrBreak1; int addrBreak2; int addrBreak3; windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); if( pMWin->eFrmType==TK_RANGE ){ addrStart = sqlite3VdbeCurrentAddr(v); - addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1); + addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 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, s.regStart, 1); + addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1); addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1); }else{ assert( pMWin->eEnd==TK_FOLLOWING ); addrStart = sqlite3VdbeCurrentAddr(v); - addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 1); - addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1); + addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 1); + addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1); } sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart); sqlite3VdbeJumpHere(v, addrBreak2); addrStart = sqlite3VdbeCurrentAddr(v); addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1); @@ -2968,11 +2939,11 @@ int addrBreak; int addrStart; windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0); addrStart = sqlite3VdbeCurrentAddr(v); addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1); - windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0); + windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart); sqlite3VdbeJumpHere(v, addrBreak); } sqlite3VdbeJumpHere(v, addrEmpty); Index: test/windowB.test ================================================================== --- test/windowB.test +++ test/windowB.test @@ -294,7 +294,46 @@ 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} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 7.0 { + CREATE TABLE t1(a, c); + CREATE INDEX i1 ON t1(a); + + INSERT INTO t1 VALUES(NULL, 46); + INSERT INTO t1 VALUES(NULL, 45); + INSERT INTO t1 VALUES(7, 997); + INSERT INTO t1 VALUES(7, 1000); + INSERT INTO t1 VALUES(8, 997); + INSERT INTO t1 VALUES(8, 1000); + INSERT INTO t1 VALUES('abc', 1001); + INSERT INTO t1 VALUES('abc', 1004); + INSERT INTO t1 VALUES('xyz', 3333); +} + +do_execsql_test 7.1 { + SELECT a, max(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING + ) FROM t1; +} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} +do_execsql_test 7.2 { + SELECT a, min(c) OVER ( + ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING + ) FROM t1; +} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} + +do_execsql_test 7.3 { + SELECT a, max(c) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING + ) FROM t1; +} {{} 46 {} 46 7 {} 7 {} 8 {} 8 {} abc 1004 abc 1004 xyz 3333} +do_execsql_test 7.4 { + SELECT a, min(c) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING + ) FROM t1; +} {{} 45 {} 45 7 {} 7 {} 8 {} 8 {} abc 1001 abc 1001 xyz 3333} finish_test