Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -91,10 +91,54 @@ sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg); sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort); sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC); } +/* +** ROWS BETWEEN PRECEDING AND FOLLOWING +** +** ... +** if( new partition ){ +** Gosub flush_partition +** } +** Insert (record in eph-table) +** sqlite3WhereEnd() +** Gosub flush_partition +** +** flush_partition: +** OpenDup (csr -> csr2) +** OpenDup (csr -> csr3) +** regPrec = // PRECEDING expression +** regFollow = // FOLLOWING expression +** if( regPrec<0 || regFollow<0 ) throw exception! +** Rewind (csr,csr2,csr3) // if EOF goto flush_partition_done +** Aggstep (csr3) +** Next(csr3) // if EOF fall-through +** if( (regFollow--)<=0 ){ +** AggFinal (xValue) +** Gosub addrGosub +** Next(csr) // if EOF goto flush_partition_done +** if( (regPrec--)<=0 ){ +** AggStep (csr2, xInverse) +** Next(csr2) +** } +** } +** flush_partition_done: +** Close (csr2) +** Close (csr3) +** ResetSorter (csr) +** Return +** +** ROWS BETWEEN PRECEDING AND CURRENT ROW +** ROWS BETWEEN CURRENT ROW AND FOLLOWING +** ROWS BETWEEN PRECEDING AND UNBOUNDED FOLLOWING +** ROWS BETWEEN UNBOUNDED PRECEDING AND FOLLOWING +** +** These are similar to the above. For "CURRENT ROW", intialize the +** register to 0. For "UNBOUNDED ..." to infinity. +** +*/ static void windowCodeRowExprStep( Parse *pParse, Select *p, WhereInfo *pWInfo, int regGosub, @@ -120,10 +164,19 @@ int regFollow; /* Value of FOLLOWING */ int addrNext; int addrGoto; int addrIfPos1; int addrIfPos2; + + assert( pMWin->eStart==TK_PRECEDING + || pMWin->eStart==TK_CURRENT + || pMWin->eStart==TK_UNBOUNDED + ); + assert( pMWin->eEnd==TK_FOLLOWING + || pMWin->eEnd==TK_CURRENT + || pMWin->eEnd==TK_UNBOUNDED + ); pParse->nMem += nSub + 2; /* Allocate register and label for the "flush_partition" sub-routine. */ regFlushPart = ++pParse->nMem; @@ -171,21 +224,26 @@ sqlite3VdbeResolveLabel(v, addrFlushPart); sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3); sqlite3VdbeAddOp2(v, OP_OpenDup, csrPrec, pMWin->iEphCsr); sqlite3VdbeAddOp2(v, OP_OpenDup, csrFollow, pMWin->iEphCsr); - sqlite3ExprCode(pParse, pMWin->pStart, regPrec); - sqlite3ExprCode(pParse, pMWin->pEnd, regFollow); + /* If either regPrec or regFollow are not non-negative integers, throw + ** an exception. */ + if( pMWin->pStart ){ + assert( pMWin->eStart==TK_PRECEDING ); + sqlite3ExprCode(pParse, pMWin->pStart, regPrec); + windowCheckFrameValue(pParse, regPrec, 0); + } + if( pMWin->pEnd ){ + assert( pMWin->eEnd==TK_FOLLOWING ); + sqlite3ExprCode(pParse, pMWin->pEnd, regFollow); + windowCheckFrameValue(pParse, regFollow, 1); + } sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regResult); sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regAccum); - /* If either regPrec or regFollow are not non-negative integers, throw an - ** exception. */ - windowCheckFrameValue(pParse, regPrec, 0); - windowCheckFrameValue(pParse, regFollow, 1); - sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, addrDone); sqlite3VdbeAddOp2(v, OP_Rewind, csrPrec, addrDone); sqlite3VdbeChangeP5(v, 1); sqlite3VdbeAddOp2(v, OP_Rewind, csrFollow, addrDone); sqlite3VdbeChangeP5(v, 1); @@ -203,13 +261,21 @@ } sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg, pWin->regAccum); sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)pWin->nArg); } - sqlite3VdbeJumpHere(v, addrNext+1); + if( pMWin->eEnd==TK_UNBOUNDED ){ + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext); + sqlite3VdbeJumpHere(v, addrNext+1); + addrNext = sqlite3VdbeCurrentAddr(v); + }else{ + sqlite3VdbeJumpHere(v, addrNext+1); + } - addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regFollow, 0 , 1); + if( pMWin->eEnd==TK_FOLLOWING ){ + addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regFollow, 0 , 1); + } for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ sqlite3VdbeAddOp3(v, OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult ); sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); @@ -216,24 +282,31 @@ } sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub); sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2); sqlite3VdbeAddOp2(v, OP_Goto, 0, addrDone); - addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regPrec, 0 , 1); - sqlite3VdbeAddOp2(v, OP_Next, csrPrec, sqlite3VdbeCurrentAddr(v)+1); - for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - int i; - for(i=0; inArg; i++){ - sqlite3VdbeAddOp3(v, OP_Column, csrPrec, pWin->iArgCol+i, reg+i); + if( pMWin->eStart==TK_CURRENT || pMWin->eStart==TK_PRECEDING ){ + if( pMWin->eStart==TK_PRECEDING ){ + addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regPrec, 0 , 1); + } + sqlite3VdbeAddOp2(v, OP_Next, csrPrec, sqlite3VdbeCurrentAddr(v)+1); + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ + int i; + for(i=0; inArg; i++){ + sqlite3VdbeAddOp3(v, OP_Column, csrPrec, pWin->iArgCol+i, reg+i); + } + sqlite3VdbeAddOp3(v, OP_AggStep0, 1, reg, pWin->regAccum); + sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); + sqlite3VdbeChangeP5(v, (u8)pWin->nArg); } - sqlite3VdbeAddOp3(v, OP_AggStep0, 1, reg, pWin->regAccum); - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); - sqlite3VdbeChangeP5(v, (u8)pWin->nArg); + if( pMWin->eStart==TK_PRECEDING ){ + sqlite3VdbeJumpHere(v, addrIfPos2); + } } - sqlite3VdbeJumpHere(v, addrIfPos2); - - sqlite3VdbeJumpHere(v, addrIfPos1); + if( pMWin->eEnd==TK_FOLLOWING ){ + sqlite3VdbeJumpHere(v, addrIfPos1); + } sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext); /* flush_partition_done: */ sqlite3VdbeResolveLabel(v, addrDone); sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr); @@ -426,52 +499,10 @@ ** ROWS BETWEEN CURRENT ROW AND CURRENT ROW ** ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ** **======================================================================== ** -** ROWS BETWEEN PRECEDING AND FOLLOWING -** -** ... -** if( new partition ){ -** Gosub flush_partition -** } -** Insert (record in eph-table) -** sqlite3WhereEnd() -** Gosub flush_partition -** -** flush_partition: -** OpenDup (csr -> csr2) -** OpenDup (csr -> csr3) -** regPrec = // PRECEDING expression -** regFollow = // FOLLOWING expression -** if( regPrec<0 || regFollow<0 ) throw exception! -** Rewind (csr,csr2,csr3) // if EOF goto flush_partition_done -** Aggstep (csr3) -** Next(csr3) // if EOF fall-through -** if( (regFollow--)<=0 ){ -** AggFinal (xValue) -** Gosub addrGosub -** Next(csr) // if EOF goto flush_partition_done -** if( (regPrec--)<=0 ){ -** AggStep (csr2, xInverse) -** Next(csr2) -** } -** } -** flush_partition_done: -** Close (csr2) -** Close (csr3) -** ResetSorter (csr) -** Return -** -** ROWS BETWEEN PRECEDING AND CURRENT ROW -** ROWS BETWEEN CURRENT ROW AND FOLLOWING -** ROWS BETWEEN PRECEDING AND UNBOUNDED FOLLOWING -** ROWS BETWEEN UNBOUNDED PRECEDING AND FOLLOWING -** -** These are similar to the above. For "CURRENT ROW", intialize the -** register to 0. For "UNBOUNDED ..." to infinity. -** ** ROWS BETWEEN PRECEDING AND PRECEDING ** ** Replace the bit after "Rewind" in the above with: ** ** if( (regFollow--)<=0 ){ @@ -520,12 +551,12 @@ int *pbLoop ){ Window *pMWin = p->pWin; if( pMWin->eType==TK_ROWS - && pMWin->eStart==TK_PRECEDING - && pMWin->eEnd==TK_FOLLOWING + && (pMWin->eStart==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING) + && (pMWin->eStart!=TK_FOLLOWING || pMWin->eEnd==TK_PRECEDING) ){ *pbLoop = 0; windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub); return; } Index: test/window2.tcl ================================================================== --- test/window2.tcl +++ test/window2.tcl @@ -181,10 +181,48 @@ PARTITION BY b ORDER BY d ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t1 } + +execsql_test 2.8 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + ) FROM t1 +} + +execsql_test 2.9 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING + ) FROM t1 +} + +execsql_test 2.10 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + ) FROM t1 +} + +execsql_test 2.11 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN 2 PRECEDING AND CURRENT ROW + ) FROM t1 +} + +execsql_test 2.13 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING + ) FROM t1 +} + + +========== puts $::fd finish_test ========== execsql_test 3.1 { Index: test/window2.test ================================================================== --- test/window2.test +++ test/window2.test @@ -93,44 +93,67 @@ ORDER BY d ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t1 } {2 2 4 4 6 6 1 1 3 3 5 5} +do_execsql_test 2.8 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + ) FROM t1 +} {1 6 2 9 3 12 4 15 5 11 6 6} + +do_execsql_test 2.9 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING + ) FROM t1 +} {1 6 2 10 3 15 4 21 5 21 6 21} + +do_execsql_test 2.10 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + ) FROM t1 +} {1 6 2 9 3 12 4 15 5 11 6 6} + +do_execsql_test 2.11 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN 2 PRECEDING AND CURRENT ROW + ) FROM t1 +} {1 1 2 3 3 6 4 9 5 12 6 15} + +do_execsql_test 2.13 { + SELECT a, sum(d) OVER ( + ORDER BY d + ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING + ) FROM t1 +} {1 21 2 21 3 21 4 20 5 18 6 15} + +#========================================================================== + finish_test #========================================================================== -do_execsql_test 2.1 { +do_execsql_test 3.1 { SELECT a, sum(d) OVER ( PARTITION BY b ORDER BY d RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } {2 12 4 10 6 6 1 9 3 8 5 5} -do_execsql_test 2.2 { +do_execsql_test 3.2 { SELECT a, sum(d) OVER ( ORDER BY b RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t1 } {2 21 4 21 6 21 1 9 3 9 5 9} -do_execsql_test 2.3 { +do_execsql_test 3.3 { SELECT a, sum(d) OVER ( ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t1 } {1 21 2 21 3 21 4 21 5 21 6 21} -do_execsql_test 2.4 { - SELECT a, sum(d) OVER ( - ORDER BY d - ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING - ) FROM t1 -} {1 3 2 6 3 9 4 12 5 15 6 11} - -do_execsql_test 2.5 { - SELECT a, sum(d) OVER ( - ORDER BY d - ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING - ) FROM t1 -} {1 {} 2 1 3 2 4 3 5 4 6 5} - finish_test