Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -246,27 +246,21 @@ error_out: sqlite3_result_error( pCtx, "second argument to nth_value must be a positive integer", -1 ); } -static void nth_valueValueFunc(sqlite3_context *pCtx){ - struct NthValueCtx *p; - p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); - if( p && p->pValue ){ - sqlite3_result_value(pCtx, p->pValue); - } -} static void nth_valueFinalizeFunc(sqlite3_context *pCtx){ struct NthValueCtx *p; - p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); + p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, 0); if( p && p->pValue ){ sqlite3_result_value(pCtx, p->pValue); sqlite3_value_free(p->pValue); p->pValue = 0; } } #define nth_valueInvFunc noopStepFunc +#define nth_valueValueFunc noopValueFunc static void first_valueStepFunc( sqlite3_context *pCtx, int nArg, sqlite3_value **apArg @@ -280,17 +274,10 @@ } } UNUSED_PARAMETER(nArg); UNUSED_PARAMETER(apArg); } -static void first_valueValueFunc(sqlite3_context *pCtx){ - struct NthValueCtx *p; - p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); - if( p && p->pValue ){ - sqlite3_result_value(pCtx, p->pValue); - } -} static void first_valueFinalizeFunc(sqlite3_context *pCtx){ struct NthValueCtx *p; p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); if( p && p->pValue ){ sqlite3_result_value(pCtx, p->pValue); @@ -297,10 +284,11 @@ sqlite3_value_free(p->pValue); p->pValue = 0; } } #define first_valueInvFunc noopStepFunc +#define first_valueValueFunc noopValueFunc /* ** Implementation of built-in window function rank(). Assumes that ** the window frame has been set to: ** @@ -402,12 +390,12 @@ p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); p->nStep++; } static void cume_distValueFunc(sqlite3_context *pCtx){ struct CallCount *p; - p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p)); - if( p && p->nTotal ){ + p = (struct CallCount*)sqlite3_aggregate_context(pCtx, 0); + if( p ){ double r = (double)(p->nStep) / (double)(p->nTotal); sqlite3_result_double(pCtx, r); } } #define cume_distFinalizeFunc cume_distValueFunc @@ -527,11 +515,11 @@ } } } static void last_valueValueFunc(sqlite3_context *pCtx){ struct LastValueCtx *p; - p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); + p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, 0); if( p && p->pVal ){ sqlite3_result_value(pCtx, p->pVal); } } static void last_valueFinalizeFunc(sqlite3_context *pCtx){ @@ -1403,12 +1391,10 @@ assert( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ); assert( bInverse==0 || bInverse==1 ); sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1); - }else if( pFunc->zName==leadName || pFunc->zName==lagName ){ - /* no-op */ }else if( pFunc->xSFunc!=noopStepFunc ){ int addrIf = 0; if( pWin->pFilter ){ int regTmp; assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr ); @@ -1504,13 +1490,10 @@ sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult); sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); - if( bFin ){ - sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); - } }else if( pWin->regApp ){ assert( pMWin->regStartRowid==0 ); }else{ int nArg = windowArgCount(pWin); if( bFin ){ @@ -1524,10 +1507,16 @@ } } } } +/* +** Generate code to calculate the current values of all window functions in the +** p->pMWin list by doing a full scan of the current window frame. Store the +** results in the Window.regResult registers, ready to return the upper +** layer. +*/ static void windowFullScan(WindowCodeArg *p){ Window *pWin; Parse *pParse = p->pParse; Window *pMWin = p->pMWin; Vdbe *v = p->pVdbe; @@ -1826,15 +1815,21 @@ sqlite3ReleaseTempReg(pParse, reg1); sqlite3ReleaseTempReg(pParse, reg2); } +/* +** Helper function for sqlite3WindowCodeStep(). Each call to this function +** generates VM code for a single RETURN_ROW, AGGSTEP or AGGINVERSE +** operation. Refer to the header comment for sqlite3WindowCodeStep() for +** details. +*/ static int windowCodeOp( - WindowCodeArg *p, - int op, - int regCountdown, - int jumpOnEof + WindowCodeArg *p, /* Context object */ + int op, /* WINDOW_RETURN_ROW, AGGSTEP or AGGINVERSE */ + int regCountdown, /* Register for OP_IfPos countdown */ + int jumpOnEof /* Jump here if stepped cursor reaches EOF */ ){ int csr, reg; Parse *pParse = p->pParse; Window *pMWin = p->pMWin; int ret = 0; @@ -1855,44 +1850,32 @@ } if( regCountdown>0 ){ if( pMWin->eType==TK_RANGE ){ addrNextRange = sqlite3VdbeCurrentAddr(v); - - switch( op ){ - case WINDOW_RETURN_ROW: { - assert( 0 ); - break; - } - - case WINDOW_AGGINVERSE: { - if( pMWin->eStart==TK_FOLLOWING ){ - windowCodeRangeTest( - p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone - ); - }else{ - windowCodeRangeTest( - p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone - ); - } - break; - } - - case WINDOW_AGGSTEP: { - windowCodeRangeTest( - p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone - ); - break; - } - } - + assert( op==WINDOW_AGGINVERSE || op==WINDOW_AGGSTEP ); + if( op==WINDOW_AGGINVERSE ){ + if( pMWin->eStart==TK_FOLLOWING ){ + windowCodeRangeTest( + p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone + ); + }else{ + windowCodeRangeTest( + p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone + ); + } + }else{ + windowCodeRangeTest( + p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone + ); + } }else{ addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1); } } - if( op==WINDOW_RETURN_ROW ){ + if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){ windowAggFinal(p, 0); } addrContinue = sqlite3VdbeCurrentAddr(v); switch( op ){ case WINDOW_RETURN_ROW: @@ -1910,11 +1893,12 @@ }else{ windowAggStep(pParse, pMWin, csr, 1, p->regArg); } break; - case WINDOW_AGGSTEP: + default: + assert( op==WINDOW_AGGSTEP ); csr = p->end.csr; reg = p->end.reg; if( pMWin->regStartRowid ){ assert( pMWin->regEndRowid ); sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1); @@ -2419,12 +2403,10 @@ default: s.eDelete = WINDOW_AGGINVERSE; break; } - s.eDelete = 0; - /* Allocate registers for the array of values from the sub-query, the ** samve values in record form, and the rowid used to insert said record ** into the ephemeral table. */ regNew = pParse->nMem+1; pParse->nMem += nInput; @@ -2499,11 +2481,11 @@ if( regEnd ){ sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0)); } - if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){ + if( pMWin->eStart==pMWin->eEnd && regStart ){ int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le); int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd); windowAggFinal(&s, 0); sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1); windowReturnOneRow(&s); Index: test/window3.test ================================================================== --- test/window3.test +++ test/window3.test cannot compute difference between binary files Index: test/windowerr.tcl ================================================================== --- test/windowerr.tcl +++ test/windowerr.tcl @@ -36,10 +36,12 @@ 5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING" 6 "ORDER BY a GROUPS BETWEEN 1 PRECEDING AND -1 FOLLOWING" 7 "ORDER BY a,b RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING" + + 8 "PARTITION BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING" } { errorsql_test 1.$tn " SELECT a, sum(b) OVER ( $frame ) FROM t1 ORDER BY 1 Index: test/windowerr.test ================================================================== --- test/windowerr.test +++ test/windowerr.test @@ -76,7 +76,14 @@ do_test 1.7 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a,b RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) FROM t1 ORDER BY 1 } } } 1 + +# PG says ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column +do_test 1.8 { catch { execsql { + SELECT a, sum(b) OVER ( + PARTITION BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING + ) FROM t1 ORDER BY 1 +} } } 1 finish_test