From 680f6e8e2e4ae427baf48a05d3d41e53dd066c50 Mon Sep 17 00:00:00 2001 From: dan Date: Mon, 4 Mar 2019 21:07:11 +0000 Subject: [PATCH] Support some "ROWS BETWEEN N PRECEDING AND M FOLLOWING" window functions without caching entire partitions. FossilOrigin-Name: e7a91f12282afb5d5d7d78397a11d18e0268ee0c931d85e21fce00d13929494e --- manifest | 24 +++--- manifest.uuid | 2 +- src/sqliteInt.h | 2 + src/window.c | 184 +++++++++++++++++++++++++++++++++++++++++++- test/pg_common.tcl | 13 ++++ test/window7.tcl | 91 ++++++++++++++++++++++ test/window7.test | 94 ++++++++++++++++++++++ test/windowerr.tcl | 43 +++++++++++ test/windowerr.test | 70 +++++++++++++++++ 9 files changed, 508 insertions(+), 15 deletions(-) create mode 100644 test/window7.tcl create mode 100644 test/window7.test create mode 100644 test/windowerr.tcl create mode 100644 test/windowerr.test diff --git a/manifest b/manifest index 156a5f4bb7..cb38d7f1b8 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\ssupport\sfor\schaining\sof\sWINDOW\sdefinitions. -D 2019-02-16T17:27:51.404 +C Support\ssome\s"ROWS\sBETWEEN\sN\sPRECEDING\sAND\sM\sFOLLOWING"\swindow\sfunctions\swithout\scaching\sentire\spartitions. +D 2019-03-04T21:07:11.233 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F Makefile.in 178d8eb6840771149cee40b322d1b3be30d330198c522c903c1b66fb5a1bfca4 @@ -519,7 +519,7 @@ F src/shell.c.in f2c1adbee3f6f36686b4a38d2168ebfc25298b4ad1e6d95199fc4e95b539251 F src/sqlite.h.in 7da74fd5bd7a9dbe92297060f036935520b26e240457287c5e67c7b9db51a986 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 F src/sqlite3ext.h 960f1b86c3610fa23cb6a267572a97dcf286e77aa0dd3b9b23292ffaa1ea8683 -F src/sqliteInt.h f44de7fea38d4fd03ee8db7f851f2727b701d4ec3fc21a8335e8d3888dda7176 +F src/sqliteInt.h fa802d30bdb2c41a3645875006c08876ff2beca44f4e3715b6794fce9451538a F src/sqliteLimit.h 1513bfb7b20378aa0041e7022d04acb73525de35b80b252f1b83fedb4de6a76b F src/status.c 46e7aec11f79dad50965a5ca5fa9de009f7d6bde08be2156f1538a0a296d4d0e F src/table.c b46ad567748f24a326d9de40e5b9659f96ffff34 @@ -603,7 +603,7 @@ F src/where.c 8a207cb2ca6b99e1edb1e4bbff9b0504385a759cbf66180d1deb34d80ca4b799 F src/whereInt.h 5f14db426ca46a83eabab1ae9aa6d4b8f27504ad35b64c290916289b1ddb2e88 F src/wherecode.c a571d8d7c19d6db786a201f2df8788b320fefcb2842f2a1eb9a85b85e91bc35f F src/whereexpr.c 36b47f7261d6b6f1a72d774c113b74beddf6745aba1018e64b196e29db233442 -F src/window.c a96e58c91e77690726512e1dc5de2f95ece975097171e8554a9e8dbd3cdfb0d9 +F src/window.c b2316f6082849e6a98a7f46209528721082533297c3c8b535edd5581863610d7 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/affinity2.test a6d901b436328bd67a79b41bb0ac2663918fe3bd F test/affinity3.test 6a101af2fc945ce2912f6fe54dd646018551710d @@ -1196,7 +1196,7 @@ F test/pcache.test c8acbedd3b6fd0f9a7ca887a83b11d24a007972b F test/pcache2.test af7f3deb1a819f77a6d0d81534e97d1cf62cd442 F test/percentile.test 4243af26b8f3f4555abe166f723715a1f74c77ff F test/permutations.test 52d2c37fe8cc07ec7362024c214b04bb69432995b3a984a3fbabc60fa6ada3ee -F test/pg_common.tcl 301ac19c1a52fd55166d26db929b3b89165c634d52b5f8ad76ea8cb06960db30 +F test/pg_common.tcl 72d4c464cf323768bbeb260ea2439602af2a780458b2e67d40e459127fd42b8b F test/pragma.test c267bf02742c823a191960895b3d52933cebd7beee26757d1ed694f213fcd867 F test/pragma2.test e5d5c176360c321344249354c0c16aec46214c9f F test/pragma3.test 8300aa9c63cff1027006ca34bf413a148abbd6dcd471fa9a1ded322fe18c0df9 @@ -1684,6 +1684,10 @@ F test/window4.tcl 511425f6b0abf9b953df54cc9c7295cc7c25d78f4ed6f7a74b094eec0120e F test/window4.test c5d6bf3403e4ade2f19df2afe4c16f29fb817c392c6c1c8017edb7165c191a62 F test/window5.test d328dd18221217c49c144181975eea17339eaeaf0e9aa558cee3afb84652821e F test/window6.test 5eae4ae7a590ccf1e605880969ca0bad3955616ac91cad3031baea38748badb3 +F test/window7.tcl 6a1210f05d40ec89c22960213a22cd3f98d4e2f2eb20646c83c8c30d4d76108f +F test/window7.test ce7f865241fdd1c5c4db869cd7bb2986c3be836bc2e73649a6846dd920f63e0f +F test/windowerr.tcl 5211e12818c013af2180c8da8f6dc007ef746e4cf386a5f2a87a5c340cb76829 +F test/windowerr.test 8a38c774e41c1b0c76e20d035c979040a69e104fcc6f10d9d3d2d50daa3cfafd F test/windowfault.test 12ceb6bbb355d13e8fcd88c5731a57256dfdf77b9a7ae20842a76fcd4623df5b F test/with1.test a07b5aad7f77acdf13e52e8814ea94606fcc72e9ea4c99baf293e9d7c63940be F test/with2.test e0030e2f0267a910d6c0e4f46f2dfe941c1cc0d4f659ba69b3597728e7e8f1ab @@ -1804,10 +1808,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P aa61435a4c3800f023788081c1342ad6a05a41449a424fa6c039d5ca46072256 -R f8f3f55ce4dbb9bcc0042a3bde2239b1 -T *branch * window-functions -T *sym-window-functions * -T -sym-trunk * +P c155125fd5dddb438c09d40f5137c47d88defb7a6ede4261f09d7bdaad250d37 +R af19e2d286499d03a5f60c3423d10c17 +T +closed 9c17fdebd5a44754ee5d23b5403b0c51fae6ba98eb4b2b54e1389de4d4b142f7 U dan -Z b3b312efff88db643f5cb6a825d2a2ac +Z 3c7515a96a0c664794629970af14a8d6 diff --git a/manifest.uuid b/manifest.uuid index 1544207bea..d331cc5a12 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -c155125fd5dddb438c09d40f5137c47d88defb7a6ede4261f09d7bdaad250d37 \ No newline at end of file +e7a91f12282afb5d5d7d78397a11d18e0268ee0c931d85e21fce00d13929494e \ No newline at end of file diff --git a/src/sqliteInt.h b/src/sqliteInt.h index dff0f29edc..7d9b5c3c6b 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -3577,6 +3577,8 @@ struct Window { Expr *pOwner; /* Expression object this window is attached to */ int nBufferCol; /* Number of columns in buffer table */ int iArgCol; /* Offset of first argument for this function */ + + int regFirst; }; #ifndef SQLITE_OMIT_WINDOWFUNC diff --git a/src/window.c b/src/window.c index a314558a6d..3a66bc0375 100644 --- a/src/window.c +++ b/src/window.c @@ -788,6 +788,7 @@ int sqlite3WindowRewrite(Parse *pParse, Select *p){ ** The OpenEphemeral instruction is coded later, after it is known how ** many columns the table will have. */ pMWin->iEphCsr = pParse->nTab++; + pParse->nTab += 3; selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist); selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist); @@ -843,6 +844,9 @@ int sqlite3WindowRewrite(Parse *pParse, Select *p){ } sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr); + sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+1, pMWin->iEphCsr); + sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+2, pMWin->iEphCsr); + sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+3, pMWin->iEphCsr); }else{ sqlite3SelectDelete(db, pSub); } @@ -1086,6 +1090,9 @@ void sqlite3WindowCodeInit(Parse *pParse, Window *pMWin){ sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nPart-1); } + pMWin->regFirst = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst); + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *p = pWin->pFunc; if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){ @@ -1429,10 +1436,10 @@ static void windowReturnOneRow( } else if( pFunc->zName==leadName || pFunc->zName==lagName ){ int nArg = pWin->pOwner->x.pList->nExpr; - int iEph = pMWin->iEphCsr; int csr = pWin->csrApp; int lbl = sqlite3VdbeMakeLabel(pParse); int tmpReg = sqlite3GetTempReg(pParse); + int iEph = pMWin->iEphCsr; if( nArg<3 ){ sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); @@ -1834,6 +1841,154 @@ static void windowCodeRowExprStep( sqlite3VdbeJumpHere(v, addrGoto); } +static void windowCodeStep( + Parse *pParse, + Select *p, + WhereInfo *pWInfo, + int regGosub, + int addrGosub +){ + Window *pMWin = p->pWin; + Vdbe *v = sqlite3GetVdbe(pParse); + int regFlushPart; /* Register for "Gosub flush_partition" */ + + int regArg; + int csrCurrent = pMWin->iEphCsr; + int csrWrite = csrCurrent+1; + int csrStart = csrCurrent+2; + int csrEnd = csrCurrent+3; + + int regStart; /* Value of PRECEDING */ + int regEnd; /* Value of FOLLOWING */ + + int iSubCsr = p->pSrc->a[0].iCursor; + int nSub = p->pSrc->a[0].pTab->nCol; + int k; + + int addrGoto; + int addrIf; + int addrIfEnd; + int addrIfStart; + int addrGosubFlush; + int addrInteger; + int addrGoto2; + + int reg = pParse->nMem+1; + int regRecord = reg+nSub; + int regRowid = regRecord+1; + + pParse->nMem += 1 + nSub + 1; + + regFlushPart = ++pParse->nMem; + regStart = ++pParse->nMem; + regEnd = ++pParse->nMem; + + 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 + || pMWin->eEnd==TK_UNBOUNDED + || pMWin->eEnd==TK_PRECEDING + ); + + /* Load the column values for the row returned by the sub-select + ** into an array of registers starting at reg. Assemble them into + ** a record in register regRecord. TODO: An optimization here? */ + for(k=0; kpPartition ){ + int addr; + ExprList *pPart = pMWin->pPartition; + int nPart = pPart->nExpr; + int regNewPart = reg + pMWin->nBufferCol; + KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0); + + addrIf = sqlite3VdbeAddOp1(v, OP_If, pMWin->regFirst); + addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart); + sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO); + sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+3, addr+2); + VdbeCoverageEqNe(v); + addrGosubFlush = sqlite3VdbeAddOp1(v, OP_Gosub, regFlushPart); + VdbeComment((v, "call flush_partition")); + sqlite3VdbeJumpHere(v, addrIf); + } + + /* Insert the new row into the ephemeral table */ + sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid); + sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid); + + /* This block is run for the first row of each partition */ + addrIf = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst); + if( pMWin->pPartition ){ + sqlite3VdbeAddOp3(v, OP_Copy, + reg+pMWin->nBufferCol, pMWin->regPart, pMWin->pPartition->nExpr-1 + ); + } + sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1); sqlite3VdbeChangeP5(v, 1); + sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1); sqlite3VdbeChangeP5(v, 1); + sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1); + regArg = windowInitAccum(pParse, pMWin); + + sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst); + sqlite3ExprCode(pParse, pMWin->pStart, regStart); + windowCheckIntValue(pParse, regStart, 0); + sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); + windowCheckIntValue(pParse, regEnd, 1); + addrGoto = sqlite3VdbeAddOp0(v, OP_Goto); + + /* This block is run for the second and subsequent rows of each partition */ + sqlite3VdbeJumpHere(v, addrIf); + sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1); + addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1); + windowAggFinal(pParse, pMWin, 0); + sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1); + windowReturnOneRow(pParse, pMWin, regGosub, addrGosub); + addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1); + sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1); + windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0); + sqlite3VdbeJumpHere(v, addrIfStart); + sqlite3VdbeJumpHere(v, addrIfEnd); + + sqlite3VdbeJumpHere(v, addrGoto); + windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0); + + /* End of the main input loop */ + sqlite3WhereEnd(pWInfo); + + /* Fall through */ + if( pMWin->pPartition ){ + addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart); + sqlite3VdbeJumpHere(v, addrGosubFlush); + } + + sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+2); + addrGoto = sqlite3VdbeAddOp0(v, OP_Goto); + windowAggFinal(pParse, pMWin, 0); + windowReturnOneRow(pParse, pMWin, regGosub, addrGosub); + addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1); + sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2); + addrGoto2 = sqlite3VdbeAddOp0(v, OP_Goto); + windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0); + sqlite3VdbeJumpHere(v, addrIfStart); + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrGoto-1); + sqlite3VdbeJumpHere(v, addrGoto); + sqlite3VdbeJumpHere(v, addrGoto2); + + sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent); + sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst); + if( pMWin->pPartition ){ + sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v)); + sqlite3VdbeAddOp1(v, OP_Return, regFlushPart); + } +} + /* ** This function does the work of sqlite3WindowCodeStep() for cases that ** would normally be handled by windowCodeDefaultStep() when there are @@ -2296,8 +2451,29 @@ void sqlite3WindowCodeStep( if( pMWin->eType==TK_ROWS && (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy) ){ - VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()")); - windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub); + Window *pWin; + int bCache = 0; /* True to use CacheStep() */ + for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ + FuncDef *pFunc = pWin->pFunc; + if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE) + || (pFunc->zName==nth_valueName) + || (pFunc->zName==first_valueName) + || (pFunc->zName==leadName) + || (pFunc->zName==lagName) + ){ + bCache = 1; + break; + } + } + if( bCache || pMWin->eStart!=TK_PRECEDING || pMWin->eEnd!=TK_FOLLOWING ){ + VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()")); + windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub); + VdbeModuleComment((pParse->pVdbe, "End RowExprStep()")); + }else{ + VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()")); + windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub); + VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()")); + } }else{ Window *pWin; int bCache = 0; /* True to use CacheStep() */ @@ -2323,9 +2499,11 @@ void sqlite3WindowCodeStep( if( bCache ){ VdbeModuleComment((pParse->pVdbe, "Begin CacheStep()")); windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub); + VdbeModuleComment((pParse->pVdbe, "End CacheStep()")); }else{ VdbeModuleComment((pParse->pVdbe, "Begin DefaultStep()")); windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub); + VdbeModuleComment((pParse->pVdbe, "End DefaultStep()")); } } } diff --git a/test/pg_common.tcl b/test/pg_common.tcl index ad545ad0aa..f55eb582c7 100644 --- a/test/pg_common.tcl +++ b/test/pg_common.tcl @@ -67,6 +67,19 @@ proc execsql_test {tn sql} { puts $::fd "" } +proc errorsql_test {tn sql} { + set rc [catch {execsql $sql} msg] + if {$rc==0} { + error "errorsql_test SQL did not cause an error!" + } + puts $::fd "# PG says \"[string trim $msg]\"" + set sql [string map {string_agg group_concat} $sql] + puts $::fd "do_test $tn { catch { execsql {" + puts $::fd " [string trim $sql]" + puts $::fd "} } } 1" + puts $::fd "" +} + # Same as [execsql_test], except coerce all results to floating point values # with two decimal points. # diff --git a/test/window7.tcl b/test/window7.tcl new file mode 100644 index 0000000000..56fc60dd7f --- /dev/null +++ b/test/window7.tcl @@ -0,0 +1,91 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + +start_test window7 "2019 March 01" +ifcapable !windowfunc + +execsql_test 1.0 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER, b INTEGER); + INSERT INTO t3 VALUES + (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), + (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), + (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), + (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), + (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), + (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), + (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), + (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), + (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), + (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), + (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), + (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), + (7, 97), (8, 98), (9, 99), (0, 100); +} + +execsql_test 1.1 { + SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; +} + +execsql_test 1.2 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.3 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.4 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.5 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.6 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.7 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +execsql_test 1.8.1 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} +execsql_test 1.8.2 { + SELECT a, sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} + +finish_test + diff --git a/test/window7.test b/test/window7.test new file mode 100644 index 0000000000..fd04e23c43 --- /dev/null +++ b/test/window7.test @@ -0,0 +1,94 @@ +# 2019 March 01 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix window7 + +ifcapable !windowfunc { finish_test ; return } +do_execsql_test 1.0 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER, b INTEGER); + INSERT INTO t3 VALUES + (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), + (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), + (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), + (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), + (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), + (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), + (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), + (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), + (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), + (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), + (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), + (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), + (7, 97), (8, 98), (9, 99), (0, 100); +} {} + +do_execsql_test 1.1 { + SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; +} {0 550 1 460 2 470 3 480 4 490 5 500 6 510 7 520 8 530 9 540} + +do_execsql_test 1.2 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW + ) FROM t3 ORDER BY 1; +} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} + +do_execsql_test 1.3 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} + +do_execsql_test 1.4 { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} + +do_execsql_test 1.5 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} + +do_execsql_test 1.6 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} + +do_execsql_test 1.7 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} + +do_execsql_test 1.8.1 { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} + +do_execsql_test 1.8.2 { + SELECT a, sum(b) OVER ( + ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1; +} {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070} + +finish_test diff --git a/test/windowerr.tcl b/test/windowerr.tcl new file mode 100644 index 0000000000..f1e3b49d7b --- /dev/null +++ b/test/windowerr.tcl @@ -0,0 +1,43 @@ +# 2018 May 19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +source [file join [file dirname $argv0] pg_common.tcl] + +#========================================================================= + +start_test windowerr "2019 March 01" +ifcapable !windowfunc + +execsql_test 1.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER, b INTEGER); +} + +foreach {tn frame} { + 1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING" + 2 "ORDER BY a ROWS BETWEEN 1 PRECEDING AND -1 FOLLOWING" + + 3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING" + 4 "ORDER BY a RANGE BETWEEN 1 PRECEDING AND -1 FOLLOWING" + + 5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING" + 6 "ORDER BY a GROUPS BETWEEN 1 PRECEDING AND -1 FOLLOWING" +} { + errorsql_test 1.$tn " + SELECT a, sum(b) OVER ( + $frame + ) FROM t3 ORDER BY 1 + " +} + +finish_test + diff --git a/test/windowerr.test b/test/windowerr.test new file mode 100644 index 0000000000..2dcc0a82d1 --- /dev/null +++ b/test/windowerr.test @@ -0,0 +1,70 @@ +# 2019 March 01 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix windowerr + +ifcapable !windowfunc { finish_test ; return } +do_execsql_test 1.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a INTEGER, b INTEGER); +} {} + +# PG says "ERROR: frame starting offset must not be negative" +do_test 1.1 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +# PG says "ERROR: frame ending offset must not be negative" +do_test 1.2 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a ROWS BETWEEN 1 PRECEDING AND -1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +# PG says "ERROR: invalid preceding or following size in window function" +do_test 1.3 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +# PG says "ERROR: invalid preceding or following size in window function" +do_test 1.4 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a RANGE BETWEEN 1 PRECEDING AND -1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +# PG says "ERROR: frame starting offset must not be negative" +do_test 1.5 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +# PG says "ERROR: frame ending offset must not be negative" +do_test 1.6 { catch { execsql { + SELECT a, sum(b) OVER ( + ORDER BY a GROUPS BETWEEN 1 PRECEDING AND -1 FOLLOWING + ) FROM t3 ORDER BY 1 +} } } 1 + +finish_test -- 2.47.2