From a0f6b83300ecc6abf83e6cfad20a50d4406cde9b Mon Sep 17 00:00:00 2001 From: dan Date: Thu, 14 Mar 2019 16:36:20 +0000 Subject: [PATCH] Add things to this branch that will be required to support the EXCLUDE clause. FossilOrigin-Name: 7d66cd2013206ebad50c7cdb7dab9211fa8b47f5cb7067dcb314b3e0180875f8 --- manifest | 20 ++--- manifest.uuid | 2 +- src/sqliteInt.h | 4 + src/vdbe.c | 1 + src/window.c | 223 ++++++++++++++++++++++++++++++++-------------- test/window8.tcl | 27 +++++- test/window8.test | 86 +++++++++++++++--- 7 files changed, 272 insertions(+), 91 deletions(-) diff --git a/manifest b/manifest index 152e9700ff..d986fbd0ed 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Merge\slatest\strunk\schanges\sinto\sthis\sbranch. -D 2019-03-13T17:31:20.025 +C Add\sthings\sto\sthis\sbranch\sthat\swill\sbe\srequired\sto\ssupport\sthe\sEXCLUDE\sclause. +D 2019-03-14T16:36:20.268 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F Makefile.in 236d2739dc3e823c3c909bca2d6cef93009bafbefd7018a8f3281074ecb92954 @@ -520,7 +520,7 @@ F src/shell.c.in 01c0cc01391d00d247fdf640052d38c267fc16d975bc4f3154a02277c232dbe F src/sqlite.h.in 02be315feaf20c06028aacf3b032b5e7211e9aae066284eef77b081646b43ea0 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 960f1b86c3610fa23cb6a267572a97dcf286e77aa0dd3b9b23292ffaa1ea8683 -F src/sqliteInt.h 67d22d548eb69608082e78195091215907e03d96caf4c692d72e9d094659b40b +F src/sqliteInt.h bd6a2cc9ccb88e405146d4b1424fda697592b7ecc5c0ca2c65b0d5bb58df85b5 F src/sqliteLimit.h 1513bfb7b20378aa0041e7022d04acb73525de35b80b252f1b83fedb4de6a76b F src/status.c 46e7aec11f79dad50965a5ca5fa9de009f7d6bde08be2156f1538a0a296d4d0e F src/table.c b46ad567748f24a326d9de40e5b9659f96ffff34 @@ -586,7 +586,7 @@ F src/upsert.c 0dd81b40206841814d46942a7337786932475f085716042d0cb2fc7791bf8ca4 F src/utf.c 810fbfebe12359f10bc2a011520a6e10879ab2a163bcb26c74768eab82ea62a5 F src/util.c 82a2e3f691a3b654be872e305dab1f455e565dedf5e6a90c818c1ab307c00432 F src/vacuum.c a9f389f41556c0ec310bc9169dc9476603c30a0a913ad92bfbc75c86886967ca -F src/vdbe.c 6e7a279ea2f1eab28b19cf41b1012379d38e182de571f50f41adc6b7911ec6c0 +F src/vdbe.c ec26b53c0c0a1de89c97410ac0bc22da789d45dc694e4dd49cd7cae12d8c0111 F src/vdbe.h 712bca562eaed1c25506b9faf9680bdc75fc42e2f4a1cd518d883fa79c7a4237 F src/vdbeInt.h a76d5eed62c76bcd8de7afd3147fac1bc40c5a870582664bcd7d071ef437c37f F src/vdbeapi.c 7a052df80d7e2e55382076174633e888f21fe200feb5d49b1b441b4c38ab851a @@ -604,7 +604,7 @@ F src/where.c 8a207cb2ca6b99e1edb1e4bbff9b0504385a759cbf66180d1deb34d80ca4b799 F src/whereInt.h 5f14db426ca46a83eabab1ae9aa6d4b8f27504ad35b64c290916289b1ddb2e88 F src/wherecode.c ce7b21e1be2b981d62683fc59c4ca73a04a7ff2f1ebec23d41baf2da2349afd6 F src/whereexpr.c 90859652920f153d2c03f075488744be2926625ebd36911bcbcb17d0d29c891c -F src/window.c 218582db176f7a367de9cd9c34ad969d525a49924ca75eaafd85a92773a34490 +F src/window.c 8b3081e3fe6c9f5837332e027090dd5e5e9949fc9f1997f186198c18d348e79d F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/affinity2.test a6d901b436328bd67a79b41bb0ac2663918fe3bd F test/affinity3.test 6a101af2fc945ce2912f6fe54dd646018551710d @@ -1687,8 +1687,8 @@ F test/window5.test d328dd18221217c49c144181975eea17339eaeaf0e9aa558cee3afb84652 F test/window6.test 604890f2b0f976339e6a1774cd90d48840e57a54b4f21a8b4b3047aa2c9787d1 F test/window7.tcl 6a1210f05d40ec89c22960213a22cd3f98d4e2f2eb20646c83c8c30d4d76108f F test/window7.test ce7f865241fdd1c5c4db869cd7bb2986c3be836bc2e73649a6846dd920f63e0f -F test/window8.tcl 2aa94709defda0bbe05883cdb61ec51346ab0bacb4d84d724758fc62818c610a -F test/window8.test e33941d2b31a8641dbe40ee38ed08430e81cd884ad409ec9eeaec315e7ae1c3b +F test/window8.tcl da939e7d6930b4cb0c6102be98c29ba6120e99ba347fb91dc5c9f01b45f73451 +F test/window8.test 03e246739234ebb5103bcbead3faafca871b47f80b1dc1c669fbc0e9c860633a F test/windowerr.tcl 4f0b111cdbb8de401a8135cd116604fb8add13042bb47a1f1496dd47a690e864 F test/windowerr.test 461bc504d4af0ae16a081e0c3bdb3724356cdcfb07bdc4b1b2dc4ad91b69bb7e F test/windowfault.test 12ceb6bbb355d13e8fcd88c5731a57256dfdf77b9a7ae20842a76fcd4623df5b @@ -1812,7 +1812,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 180be266238e18c01f8bd52c75dd9aa3e26e553620258141cd95189a0ae59ddb 049e9926202317f58c54b6351ff120fb69bf821f409909b1a1c5e335c0396707 -R 596a5e5aa86c74af47fe5b05e09689b1 +P 0b904517bd6c4c59082381577246d3d2351b4265fc9151438ee4c33cf16f2eb1 +R c5679ad9066e2a8eceb4f5175a82147b U dan -Z 4b80674bad29018c044451db12cfd533 +Z ec90fdebe5542b0f73c320cb419d4b34 diff --git a/manifest.uuid b/manifest.uuid index 241ead4e54..fad7517db6 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -0b904517bd6c4c59082381577246d3d2351b4265fc9151438ee4c33cf16f2eb1 \ No newline at end of file +7d66cd2013206ebad50c7cdb7dab9211fa8b47f5cb7067dcb314b3e0180875f8 \ No newline at end of file diff --git a/src/sqliteInt.h b/src/sqliteInt.h index bb68828326..8c878228e3 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3561,6 +3561,7 @@ struct Window { u8 eStart; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 eEnd; /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */ u8 bImplicitFrame; /* True if frame was implicitly specified */ + u8 eExclude; Expr *pStart; /* Expression for " PRECEDING" */ Expr *pEnd; /* Expression for " FOLLOWING" */ Window *pNextWin; /* Next window function belonging to this SELECT */ @@ -3576,6 +3577,9 @@ struct Window { int nBufferCol; /* Number of columns in buffer table */ int iArgCol; /* Offset of first argument for this function */ int regFirst; + + int regStartRowid; + int regEndRowid; }; #ifndef SQLITE_OMIT_WINDOWFUNC diff --git a/src/vdbe.c b/src/vdbe.c index 66d2fc576d..00792b1448 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -3612,6 +3612,7 @@ case OP_OpenDup: { pCx->pKeyInfo = pOrig->pKeyInfo; pCx->isTable = pOrig->isTable; pCx->pgnoRoot = pOrig->pgnoRoot; + pCx->isOrdered = pOrig->isOrdered; rc = sqlite3BtreeCursor(pOrig->pBtx, pCx->pgnoRoot, BTREE_WRCSR, pCx->pKeyInfo, pCx->uc.pCursor); /* The sqlite3BtreeCursor() routine can only fail for the first cursor diff --git a/src/window.c b/src/window.c index a8649f9018..c17ea1dbbc 100644 --- a/src/window.c +++ b/src/window.c @@ -198,6 +198,50 @@ static void dense_rankValueFunc(sqlite3_context *pCtx){ } } +/* +** Implementation of built-in window function nth_value(). This +** implementation is used in "slow mode" only - when the EXCLUDE clause +** is not set to the default value "NO OTHERS". +*/ +struct NthValueCtx { + i64 nStep; + sqlite3_value *pValue; +}; +static void nth_valueStepFunc( + sqlite3_context *pCtx, + int nArg, + sqlite3_value **apArg +){ + struct NthValueCtx *p; + p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); + if( p ){ + i64 iVal = sqlite3_value_int64(apArg[1]); + p->nStep++; + if( iVal==p->nStep ){ + p->pValue = sqlite3_value_dup(apArg[0]); + } + } + UNUSED_PARAMETER(nArg); + UNUSED_PARAMETER(apArg); +} +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)); + if( p && p->pValue ){ + sqlite3_result_value(pCtx, p->pValue); + sqlite3_value_free(p->pValue); + p->pValue = 0; + } +} +#define nth_valueInvFunc noopStepFunc + /* ** Implementation of built-in window function rank(). Assumes that ** the window frame has been set to: @@ -517,14 +561,11 @@ void sqlite3WindowFunctions(void){ WINDOWFUNCX(row_number, 0, 0), WINDOWFUNCX(dense_rank, 0, 0), WINDOWFUNCX(rank, 0, 0), - // WINDOWFUNCX(percent_rank, 0, SQLITE_FUNC_WINDOW_SIZE), WINDOWFUNCALL(percent_rank, 0, 0), WINDOWFUNCALL(cume_dist, 0, 0), WINDOWFUNCALL(ntile, 1, 0), - // WINDOWFUNCX(cume_dist, 0, SQLITE_FUNC_WINDOW_SIZE), - // WINDOWFUNCX(ntile, 1, SQLITE_FUNC_WINDOW_SIZE), WINDOWFUNCALL(last_value, 1, 0), - WINDOWFUNCNOOP(nth_value, 2, 0), + WINDOWFUNCALL(nth_value, 2, 0), WINDOWFUNCNOOP(first_value, 1, 0), WINDOWFUNCNOOP(lead, 1, 0), WINDOWFUNCNOOP(lead, 2, 0), @@ -622,6 +663,7 @@ void sqlite3WindowUpdate( pWin->eType = aUp[i].eType; pWin->eStart = aUp[i].eStart; pWin->eEnd = aUp[i].eEnd; + pWin->eExclude = 0; if( pWin->eStart==TK_FOLLOWING ){ pWin->pStart = sqlite3Expr(db, TK_INTEGER, "1"); } @@ -1000,6 +1042,7 @@ Window *sqlite3WindowAlloc( pWin->bImplicitFrame = bImplicitFrame; pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd); pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart); + /* pWin->eExclude = 1; */ return pWin; windowAllocErr: @@ -1102,6 +1145,7 @@ int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2){ if( p1->eType!=p2->eType ) return 1; if( p1->eStart!=p2->eStart ) return 1; if( p1->eEnd!=p2->eEnd ) return 1; + if( p1->eExclude!=p2->eExclude ) 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; @@ -1131,6 +1175,16 @@ void sqlite3WindowCodeInit(Parse *pParse, Window *pMWin){ pMWin->regFirst = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst); + if( pMWin->eExclude ){ + pMWin->regStartRowid = ++pParse->nMem; + pMWin->regEndRowid = ++pParse->nMem; + pMWin->csrApp = pParse->nTab++; + sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid); + sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid); + sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->csrApp, pMWin->iEphCsr); + return; + } + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *p = pWin->pFunc; if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){ @@ -1245,27 +1299,24 @@ static void windowAggStep( 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 */ - int regPartSize /* Register containing size of partition */ + int reg /* Array of registers */ ){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ + FuncDef *pFunc = pWin->pFunc; int regArg; int nArg = windowArgCount(pWin); + int i; - if( csr>=0 ){ - int i; - for(i=0; iiArgCol+i, reg+i); - } - regArg = reg; - }else{ - regArg = reg + pWin->iArgCol; + for(i=0; iiArgCol+i, reg+i); } + regArg = reg; - if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) - && pWin->eStart!=TK_UNBOUNDED + if( pMWin->regStartRowid==0 + && (pFunc->funcFlags & SQLITE_FUNC_MINMAX) + && (pWin->eStart!=TK_UNBOUNDED) ){ int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg); VdbeCoverage(v); @@ -1282,34 +1333,26 @@ static void windowAggStep( } sqlite3VdbeJumpHere(v, addrIsNull); }else if( pWin->regApp ){ - assert( pWin->pFunc->zName==nth_valueName - || pWin->pFunc->zName==first_valueName + 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( pWin->pFunc->zName==leadName - || pWin->pFunc->zName==lagName - ){ + }else if( pFunc->zName==leadName || pFunc->zName==lagName ){ /* no-op */ - }else{ + }else if( pFunc->xSFunc!=noopStepFunc ){ int addrIf = 0; if( pWin->pFilter ){ int regTmp; assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr ); assert( nArg || pWin->pOwner->x.pList==0 ); - if( csr>0 ){ - regTmp = sqlite3GetTempReg(pParse); - sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp); - }else{ - regTmp = regArg + nArg; - } + regTmp = sqlite3GetTempReg(pParse); + sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp); addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1); VdbeCoverage(v); - if( csr>0 ){ - sqlite3ReleaseTempReg(pParse, regTmp); - } + sqlite3ReleaseTempReg(pParse, regTmp); } - if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ + if( pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl; assert( nArg>0 ); pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr); @@ -1317,7 +1360,7 @@ static void windowAggStep( } sqlite3VdbeAddOp3(v, bInverse? OP_AggInverse : OP_AggStep, bInverse, regArg, pWin->regAccum); - sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); + sqlite3VdbeAppendP4(v, pFunc, P4_FUNCDEF); sqlite3VdbeChangeP5(v, (u8)nArg); if( addrIf ) sqlite3VdbeJumpHere(v, addrIf); } @@ -1325,37 +1368,57 @@ static void windowAggStep( } /* -** Generate VM code to invoke either xValue() (bFinal==0) or xFinalize() -** (bFinal==1) for each window function in the linked list starting at +** Generate VM code to invoke either xValue() (bFin==0) or xFinalize() +** (bFin==1) for each window function in the linked list starting at ** pMWin. Or, for built-in window-functions that do not use the standard ** API, generate the equivalent VM code. */ -static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){ +static void windowAggFinal(Parse *pParse, Window *pMWin, int regArg, int bFin){ Vdbe *v = sqlite3GetVdbe(pParse); Window *pWin; + if( pMWin->regStartRowid ){ + int addrNext; + int regRowid = sqlite3GetTempReg(pParse); + assert( pMWin->csrApp ); + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ + sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); + } + sqlite3VdbeAddOp3(v, OP_SeekGE, pMWin->csrApp, 0, pMWin->regStartRowid); + addrNext = sqlite3VdbeCurrentAddr(v); + sqlite3VdbeAddOp2(v, OP_Rowid, pMWin->csrApp, regRowid); + sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, 0, regRowid); + windowAggStep(pParse, pMWin, pMWin->csrApp, 0, regArg); + sqlite3VdbeAddOp2(v, OP_Next, pMWin->csrApp, addrNext); + sqlite3VdbeJumpHere(v, addrNext-1); + sqlite3VdbeJumpHere(v, addrNext+1); + sqlite3ReleaseTempReg(pParse, regRowid); + } + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ - if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) - && pWin->eStart!=TK_UNBOUNDED + if( pMWin->regStartRowid==0 + && (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) + && (pWin->eStart!=TK_UNBOUNDED) ){ 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( bFinal ){ + if( bFin ){ sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); } }else if( pWin->regApp ){ + assert( pMWin->regStartRowid==0 ); }else{ - if( bFinal ){ - sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, windowArgCount(pWin)); + int nArg = windowArgCount(pWin); + if( bFin ){ + sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, nArg); sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult); sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); }else{ - sqlite3VdbeAddOp3(v, OP_AggValue, pWin->regAccum, windowArgCount(pWin), - pWin->regResult); + sqlite3VdbeAddOp3(v, OP_AggValue,pWin->regAccum,nArg,pWin->regResult); sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF); } } @@ -1388,23 +1451,38 @@ static void windowReturnOneRow( if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){ - int csr = pWin->csrApp; int lbl = sqlite3VdbeMakeLabel(pParse); int tmpReg = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); if( pFunc->zName==nth_valueName ){ - sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg); + sqlite3VdbeAddOp3(v, OP_Column,pMWin->iEphCsr,pWin->iArgCol+1,tmpReg); windowCheckValue(pParse, tmpReg, 2); }else{ sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg); } - sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg); - sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg); - VdbeCoverageNeverNull(v); - sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg); - VdbeCoverageNeverTaken(v); - sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult); + if( pWin->eExclude==0 ){ + int csr = pWin->csrApp; + sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg); + sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg); + VdbeCoverageNeverNull(v); + sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg); + VdbeCoverageNeverTaken(v); + sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult); + }else{ + int regRowid = sqlite3GetTempReg(pParse); + int csr = pMWin->csrApp; + int addrNext; + sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+1, 1); + sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lbl, pMWin->regStartRowid); + addrNext = sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid); + sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lbl, regRowid); + sqlite3VdbeAddOp3(v, OP_IfPos, tmpReg, sqlite3VdbeCurrentAddr(v)+3, 1); + sqlite3VdbeAddOp3(v, OP_Column, csr, pMWin->iArgCol, pWin->regResult); + sqlite3VdbeAddOp2(v, OP_Goto, 0, lbl); + sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext); + sqlite3ReleaseTempReg(pParse, regRowid); + } sqlite3VdbeResolveLabel(v, lbl); sqlite3ReleaseTempReg(pParse, tmpReg); } @@ -1457,17 +1535,17 @@ static int windowInitAccum(Parse *pParse, Window *pMWin){ FuncDef *pFunc = pWin->pFunc; sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); nArg = MAX(nArg, windowArgCount(pWin)); - if( pFunc->zName==nth_valueName - || pFunc->zName==first_valueName - ){ - sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp); - sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); - } + if( pWin->eExclude==0 ){ + if( pFunc->zName==nth_valueName || pFunc->zName==first_valueName ){ + sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp); + sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); + } - if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){ - assert( pWin->eStart!=TK_UNBOUNDED ); - sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); - sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); + if( (pFunc->funcFlags & SQLITE_FUNC_MINMAX) && pWin->csrApp ){ + assert( pWin->eStart!=TK_UNBOUNDED ); + sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp); + sqlite3VdbeAddOp2(v, OP_Integer, 0, pWin->regApp+1); + } } } regArg = pParse->nMem+1; @@ -1481,6 +1559,7 @@ static int windowInitAccum(Parse *pParse, Window *pMWin){ */ static int windowCacheFrame(Window *pMWin){ Window *pWin; + if( pMWin->regStartRowid ) return 1; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; if( (pFunc->zName==nth_valueName) @@ -1684,7 +1763,7 @@ static int windowCodeOp( } if( op==WINDOW_RETURN_ROW ){ - windowAggFinal(pParse, pMWin, 0); + windowAggFinal(pParse, pMWin, p->regArg, 0); } addrContinue = sqlite3VdbeCurrentAddr(v); switch( op ){ @@ -1697,13 +1776,23 @@ static int windowCodeOp( case WINDOW_AGGINVERSE: csr = p->start.csr; reg = p->start.reg; - windowAggStep(pParse, pMWin, csr, 1, p->regArg, 0); + if( pMWin->regStartRowid ){ + assert( pMWin->regEndRowid ); + sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1); + }else{ + windowAggStep(pParse, pMWin, csr, 1, p->regArg); + } break; case WINDOW_AGGSTEP: csr = p->end.csr; reg = p->end.reg; - windowAggStep(pParse, pMWin, csr, 0, p->regArg, 0); + if( pMWin->regStartRowid ){ + assert( pMWin->regEndRowid ); + sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1); + }else{ + windowAggStep(pParse, pMWin, csr, 0, p->regArg); + } break; } @@ -2279,7 +2368,7 @@ void sqlite3WindowCodeStep( if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){ int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le); int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd); - windowAggFinal(pParse, pMWin, 0); + windowAggFinal(pParse, pMWin, s.regArg, 0); sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1); windowReturnOneRow(pParse, pMWin, regGosub, addrGosub); sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr); @@ -2414,6 +2503,10 @@ void sqlite3WindowCodeStep( sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr); sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst); if( pMWin->pPartition ){ + if( pMWin->regStartRowid ){ + sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regStartRowid); + sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regEndRowid); + } sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v)); sqlite3VdbeAddOp1(v, OP_Return, regFlushPart); } diff --git a/test/window8.tcl b/test/window8.tcl index 1230be09c2..31c90cc98a 100644 --- a/test/window8.tcl +++ b/test/window8.tcl @@ -89,9 +89,32 @@ foreach {tn frame} { " } + +foreach {tn ex} { + 1 { EXCLUDE NO OTHERS } + 2 { EXCLUDE CURRENT ROW } + 3 { EXCLUDE GROUP } + 4 { EXCLUDE TIES } +} { + execsql_test 2.$tn.1 " + SELECT row_number() OVER win + FROM t3 + WINDOW win AS ( + ORDER BY c, b, a + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex + ) + " + + execsql_test 2.$tn.2 " + SELECT nth_value(c, 14) OVER win + FROM t3 + WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex) + " +} + ========== -execsql_test 2.0 { +execsql_test 3.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a REAL, b INTEGER); INSERT INTO t1 VALUES @@ -114,7 +137,7 @@ foreach {tn frame} { 11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING } 12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING } } { - execsql_test 2.$tn " + execsql_test 3.$tn " SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame) " } diff --git a/test/window8.test b/test/window8.test index 71c8598de1..5741f0ef88 100644 --- a/test/window8.test +++ b/test/window8.test @@ -433,9 +433,69 @@ do_execsql_test 1.19.5 { SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3; } {AA aa 102 AA aa 102 AA aa 102 AA aa 102 AA bb 102 AA bb 102 AA bb 102 AA bb 102 BB aa 102 BB aa 102 BB aa 102 BB aa 102 BB aa 102 BB aa 102 BB bb 102 BB bb 102 BB bb 102 BB bb 102 BB bb 102 BB bb 102 CC aa 102 CC aa 102 CC aa 102 CC aa 102 CC bb 102 CC bb 102 DD aa 102 DD aa 102 DD aa 102 DD bb 102 DD bb 102 DD bb 102 DD bb 102 EE aa 102 EE aa 102 EE bb 102 EE bb 102 EE bb 102 FF aa 102 FF aa 102 FF aa 102 FF aa 102 FF bb 113 FF bb 113 FF bb 113 FF bb 113 FF bb 113 FF bb 113 GG aa 113 GG aa 113 GG aa 113 GG aa 113 GG bb 113 GG bb 113 GG bb 113 GG bb 113 HH aa 113 HH aa 113 HH aa 113 HH bb 113 HH bb 113 HH bb 113 HH bb 113 HH bb 113 HH bb 113 II aa 113 II aa 113 II bb 113 II bb 113 II bb 113 II bb 113 II bb 113 JJ aa 257 JJ aa 257 JJ aa 257 JJ aa 257 JJ bb {} JJ bb {} JJ bb {} JJ bb {}} +do_execsql_test 2.1.1 { + SELECT row_number() OVER win + FROM t3 + WINDOW win AS ( + ORDER BY c, b, a + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS + ) +} {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} + +do_execsql_test 2.1.2 { + SELECT nth_value(c, 14) OVER win + FROM t3 + WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS ) +} {938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938} + +do_execsql_test 2.2.1 { + SELECT row_number() OVER win + FROM t3 + WINDOW win AS ( + ORDER BY c, b, a + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW + ) +} {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} + +do_execsql_test 2.2.2 { + SELECT nth_value(c, 14) OVER win + FROM t3 + WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW ) +} {660 660 660 660 660 660 660 660 660 660 660 660 660 660 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938 938} + +do_execsql_test 2.3.1 { + SELECT row_number() OVER win + FROM t3 + WINDOW win AS ( + ORDER BY c, b, a + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP + ) +} {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} + +do_execsql_test 2.3.2 { + SELECT nth_value(c, 14) OVER win + FROM t3 + WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP ) +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + +do_execsql_test 2.4.1 { + SELECT row_number() OVER win + FROM t3 + WINDOW win AS ( + ORDER BY c, b, a + ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES + ) +} {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} + +do_execsql_test 2.4.2 { + SELECT nth_value(c, 14) OVER win + FROM t3 + WINDOW win AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES ) +} {{} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} + #========================================================================== -do_execsql_test 2.0 { +do_execsql_test 3.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a REAL, b INTEGER); INSERT INTO t1 VALUES @@ -443,51 +503,51 @@ do_execsql_test 2.0 { (15, 30), (20, 40), (22,80), (30, 90); } {} -do_execsql_test 2.1 { +do_execsql_test 3.1 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING ) } {5 30 10 112 13 102 13 102 15 142 20 150 22 120 30 90} -do_execsql_test 2.2 { +do_execsql_test 3.2 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING ) } {5 {} 10 10 13 10 13 10 15 30 20 102 22 82 30 120} -do_execsql_test 2.3 { +do_execsql_test 3.3 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING ) } {5 {} 10 52 13 30 13 30 15 {} 20 80 22 {} 30 {}} -do_execsql_test 2.4 { +do_execsql_test 3.4 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING ) } {30 90 22 120 20 150 15 142 13 102 13 102 10 112 5 30} -do_execsql_test 2.5 { +do_execsql_test 3.5 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING ) } {30 {} 22 90 20 90 15 120 13 120 13 120 10 70 5 102} -do_execsql_test 2.6 { +do_execsql_test 3.6 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING ) } {30 {} 22 40 20 {} 15 52 13 20 13 20 10 {} 5 {}} -do_execsql_test 2.7 { +do_execsql_test 3.7 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING ) } {5 30 10 112 13 102 13 102 15 142 20 150 22 120 30 90} -do_execsql_test 2.8 { +do_execsql_test 3.8 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING ) } {5 {} 10 {} 13 10 13 10 15 10 20 72 22 82 30 120} -do_execsql_test 2.9 { +do_execsql_test 3.9 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING ) } {5 {} 10 52 13 {} 13 {} 15 {} 20 {} 22 {} 30 {}} -do_execsql_test 2.10 { +do_execsql_test 3.10 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING ) } {30 90 22 120 20 150 15 142 13 102 13 102 10 112 5 30} -do_execsql_test 2.11 { +do_execsql_test 3.11 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING ) } {30 {} 22 90 20 90 15 170 13 210 13 210 10 210 5 292} -do_execsql_test 2.12 { +do_execsql_test 3.12 { SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING ) } {30 232 22 112 20 112 15 30 13 30 13 30 10 10 5 {}} -- 2.47.2