Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | window-functions |
Files: | files | file ages | folders |
SHA3-256: |
2879a691aca9304aea5acb46bab8e82b |
User & Date: | dan 2019-03-18 18:55:35.969 |
Context
2019-03-18
| ||
21:19 | Add further tests for new window function functionality. (check-in: 1fbddf01b1 user: dan tags: window-functions) | |
18:55 | Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set. (check-in: 2879a691ac user: dan tags: window-functions) | |
16:51 | Fixes for RANGE windows and NULL values. (check-in: 723c84be3e user: dan tags: window-functions) | |
Changes
Changes to src/vdbe.c.
︙ | ︙ | |||
6536 6537 6538 6539 6540 6541 6542 6543 6544 6545 6546 6547 6548 6549 | Mem *pMem; assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) ); assert( pOp->p3==0 || pOp->opcode==OP_AggValue ); pMem = &aMem[pOp->p1]; assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 ); #ifndef SQLITE_OMIT_WINDOWFUNC if( pOp->p3 ){ rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc); pMem = &aMem[pOp->p3]; }else #endif { rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc); } | > | 6536 6537 6538 6539 6540 6541 6542 6543 6544 6545 6546 6547 6548 6549 6550 | Mem *pMem; assert( pOp->p1>0 && pOp->p1<=(p->nMem+1 - p->nCursor) ); assert( pOp->p3==0 || pOp->opcode==OP_AggValue ); pMem = &aMem[pOp->p1]; assert( (pMem->flags & ~(MEM_Null|MEM_Agg))==0 ); #ifndef SQLITE_OMIT_WINDOWFUNC if( pOp->p3 ){ memAboutToChange(p, &aMem[pOp->p3]); rc = sqlite3VdbeMemAggValue(pMem, &aMem[pOp->p3], pOp->p4.pFunc); pMem = &aMem[pOp->p3]; }else #endif { rc = sqlite3VdbeMemFinalize(pMem, pOp->p4.pFunc); } |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
211 212 213 214 215 216 217 | sqlite3_context *pCtx, int nArg, sqlite3_value **apArg ){ struct NthValueCtx *p; p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); if( p ){ | > > > | > > > > > > > > > > > > > > > > > > > > > | 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | sqlite3_context *pCtx, int nArg, sqlite3_value **apArg ){ struct NthValueCtx *p; p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); if( p ){ i64 iVal; switch( sqlite3_value_numeric_type(apArg[1]) ){ case SQLITE_INTEGER: iVal = sqlite3_value_int64(apArg[1]); break; case SQLITE_FLOAT: { double fVal = sqlite3_value_double(apArg[1]); if( ((i64)fVal)!=fVal ) goto error_out; iVal = (i64)fVal; break; } default: goto error_out; } if( iVal<=0 ) goto error_out; p->nStep++; if( iVal==p->nStep ){ p->pValue = sqlite3_value_dup(apArg[0]); if( !p->pValue ){ sqlite3_result_error_nomem(pCtx); } } } UNUSED_PARAMETER(nArg); UNUSED_PARAMETER(apArg); return; 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); } |
︙ | ︙ | |||
247 248 249 250 251 252 253 254 255 256 257 258 259 260 | int nArg, sqlite3_value **apArg ){ struct NthValueCtx *p; p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); if( p && p->pValue==0 ){ p->pValue = sqlite3_value_dup(apArg[0]); } 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)); | > > > | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 | int nArg, sqlite3_value **apArg ){ struct NthValueCtx *p; p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p)); if( p && p->pValue==0 ){ p->pValue = sqlite3_value_dup(apArg[0]); if( !p->pValue ){ sqlite3_result_error_nomem(pCtx); } } 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)); |
︙ | ︙ | |||
1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 | } pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( pWin==0 ) goto windowAllocErr; pWin->eType = eType; pWin->eStart = eStart; pWin->eEnd = eEnd; pWin->eExclude = eExclude; pWin->bImplicitFrame = bImplicitFrame; pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd); pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart); return pWin; windowAllocErr: | > > > | 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 | } pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( pWin==0 ) goto windowAllocErr; pWin->eType = eType; pWin->eStart = eStart; pWin->eEnd = eEnd; if( eExclude==0 && OptimizationDisabled(pParse->db, SQLITE_QueryFlattener) ){ eExclude = TK_NO; } pWin->eExclude = eExclude; pWin->bImplicitFrame = bImplicitFrame; pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd); pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart); return pWin; windowAllocErr: |
︙ | ︙ | |||
1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 | sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); } sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lblBrk, pMWin->regStartRowid); addrNext = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid); sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lblBrk, regRowid); if( pMWin->eExclude==TK_CURRENT ){ sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid); }else if( pMWin->eExclude!=TK_NO ){ int addr; | > | > > | | 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 | sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); } sqlite3VdbeAddOp3(v, OP_SeekGE, csr, lblBrk, pMWin->regStartRowid); addrNext = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_Rowid, csr, regRowid); sqlite3VdbeAddOp3(v, OP_Gt, pMWin->regEndRowid, lblBrk, regRowid); if( pMWin->eExclude==TK_CURRENT ){ sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid); }else if( pMWin->eExclude!=TK_NO ){ int addr; int addrEq = 0; KeyInfo *pKeyInfo; pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0); if( pMWin->eExclude==TK_TIES ){ addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid); } windowReadPeerValues(p, csr, regPeer); sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer); sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO); addr = sqlite3VdbeCurrentAddr(v)+1; |
︙ | ︙ | |||
1664 1665 1666 1667 1668 1669 1670 | int regArg; int nArg = 0; Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); nArg = MAX(nArg, windowArgCount(pWin)); | | | 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 | int regArg; int nArg = 0; Window *pWin; for(pWin=pMWin; pWin; pWin=pWin->pNextWin){ FuncDef *pFunc = pWin->pFunc; sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); nArg = MAX(nArg, windowArgCount(pWin)); if( pMWin->regStartRowid==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 ); |
︙ | ︙ | |||
1760 1761 1762 1763 1764 1765 1766 | ){ Parse *pParse = p->pParse; Vdbe *v = sqlite3GetVdbe(pParse); int reg1 = sqlite3GetTempReg(pParse); int reg2 = sqlite3GetTempReg(pParse); int arith = OP_Add; int addrGe; | < | 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 | ){ Parse *pParse = p->pParse; Vdbe *v = sqlite3GetVdbe(pParse); int reg1 = sqlite3GetTempReg(pParse); int reg2 = sqlite3GetTempReg(pParse); int arith = OP_Add; int addrGe; int regString = ++pParse->nMem; assert( op==OP_Ge || op==OP_Gt || op==OP_Le ); assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 ); if( p->pMWin->pOrderBy->a[0].sortOrder ){ switch( op ){ |
︙ | ︙ | |||
2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 | int lblWhereEnd; /* Label just before sqlite3WhereEnd() code */ 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 ); lblWhereEnd = sqlite3VdbeMakeLabel(pParse); /* Fill in the context object */ memset(&s, 0, sizeof(WindowCodeArg)); s.pParse = pParse; | > > > > | 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 | int lblWhereEnd; /* Label just before sqlite3WhereEnd() code */ 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 ); assert( pMWin->eExclude==0 || pMWin->eExclude==TK_CURRENT || pMWin->eExclude==TK_GROUP || pMWin->eExclude==TK_TIES || pMWin->eExclude==TK_NO ); lblWhereEnd = sqlite3VdbeMakeLabel(pParse); /* Fill in the context object */ memset(&s, 0, sizeof(WindowCodeArg)); s.pParse = pParse; |
︙ | ︙ |
Changes to test/permutations.test.
︙ | ︙ | |||
1028 1029 1030 1031 1032 1033 1034 | } -files [ test_set [glob -nocomplain $::testdir/../ext/rbu/*.test] -exclude rbu.test ] test_suite "no_optimization" -description { Run test scripts with optimizations disabled using the sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface. | | > > | | | | | | | 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 | } -files [ test_set [glob -nocomplain $::testdir/../ext/rbu/*.test] -exclude rbu.test ] test_suite "no_optimization" -description { Run test scripts with optimizations disabled using the sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface. } -files [ test_set \ [glob -nocomplain $::testdir/window*.test] \ where.test where2.test where3.test where4.test where5.test \ where6.test where7.test where8.test where9.test \ whereA.test whereB.test wherelimit.test \ select1.test select2.test select3.test select4.test select5.test \ select7.test select8.test selectA.test selectC.test ] -dbconfig { optimization_control $::dbhandle all 0 } test_suite "prepare" -description { Run tests with the db connection using sqlite3_prepare() instead of _v2(). } -dbconfig { $::dbhandle version -use-legacy-prepare 1 |
︙ | ︙ |
Changes to test/window4.test.
︙ | ︙ | |||
146 147 148 149 150 151 152 | SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4 } {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G} do_execsql_test 2.4.1 { SELECT group_concat(b, '.') OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t4 | | > | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 | SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4 } {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G} do_execsql_test 2.4.1 { SELECT group_concat(b, '.') OVER ( ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t4 } {A.B.C.D.E.F.G.H.I.J B.C.D.E.F.G.H.I.J C.D.E.F.G.H.I.J D.E.F.G.H.I.J E.F.G.H.I.J F.G.H.I.J G.H.I.J H.I.J I.J J} do_execsql_test 3.0 { DROP TABLE IF EXISTS t5; CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); INSERT INTO t5 VALUES(1, 'A', 'one', 5); INSERT INTO t5 VALUES(2, 'B', 'two', 4); INSERT INTO t5 VALUES(3, 'A', 'three', 3); |
︙ | ︙ |
Changes to test/windowfault.test.
︙ | ︙ | |||
24 25 26 27 28 29 30 | CREATE TABLE t1(a, b, c, d); INSERT INTO t1 VALUES(1, 2, 3, 4); INSERT INTO t1 VALUES(5, 6, 7, 8); INSERT INTO t1 VALUES(9, 10, 11, 12); } faultsim_save_and_close | | | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | CREATE TABLE t1(a, b, c, d); INSERT INTO t1 VALUES(1, 2, 3, 4); INSERT INTO t1 VALUES(5, 6, 7, 8); INSERT INTO t1 VALUES(9, 10, 11, 12); } faultsim_save_and_close do_faultsim_test 1 -start 1 -faults oom-t* -prep { faultsim_restore_and_reopen } -body { execsql { SELECT row_number() OVER win, rank() OVER win, dense_rank() OVER win, ntile(2) OVER win, |
︙ | ︙ |