Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | window-functions |
Files: | files | file ages | folders |
SHA3-256: |
25ff7091cb12c63b1864ce68a9151f84 |
User & Date: | dan 2019-03-12 18:28:51.117 |
Context
2019-03-13
| ||
08:28 | Minor optimization in sqlite3WindowCodeStep(). (check-in: b1322ffb6e user: dan tags: window-functions) | |
2019-03-12
| ||
18:28 | Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames. (check-in: 25ff7091cb user: dan tags: window-functions) | |
15:21 | Expand on header comment for sqlite3WindowCodeStep(). Further simplify the implementation of the same. (check-in: 5129bcc996 user: dan tags: window-functions) | |
Changes
Changes to src/vdbe.c.
︙ | ︙ | |||
1719 1720 1721 1722 1723 1724 1725 | pIn1 = &aMem[pOp->p1]; memAboutToChange(p, pIn1); sqlite3VdbeMemIntegerify(pIn1); pIn1->u.i += pOp->p2; break; } | | | | | > > > > > > | | | | | 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 | pIn1 = &aMem[pOp->p1]; memAboutToChange(p, pIn1); sqlite3VdbeMemIntegerify(pIn1); pIn1->u.i += pOp->p2; break; } /* Opcode: MustBeInt P1 P2 * * P5 ** ** If P5 is 0, force the value in register P1 to be an integer. If ** the value in P1 is not an integer and cannot be converted into an ** integer without data loss, then jump immediately to P2, or if P2==0 ** raise an SQLITE_MISMATCH exception. ** ** Or, if P5 is non-zero, then force the register in P1 to be a number ** (real or integer). Jump to P2 if this cannot be accomplished without ** data loss. P2 must be non-zero in this case. */ case OP_MustBeInt: { /* jump, in1 */ u8 f; f = (pOp->p5 ? (MEM_Int|MEM_Real) : MEM_Int); pIn1 = &aMem[pOp->p1]; if( (pIn1->flags & f)==0 ){ applyAffinity(pIn1, SQLITE_AFF_NUMERIC, encoding); VdbeBranchTaken((pIn1->flags&f)==0, 2); if( (pIn1->flags & f)==0 ){ if( pOp->p2==0 ){ rc = SQLITE_MISMATCH; goto abort_due_to_error; }else{ goto jump_to_p2; } } } if( f==MEM_Int ) MemSetTypeFlag(pIn1, MEM_Int); break; } #ifndef SQLITE_OMIT_FLOATING_POINT /* Opcode: RealAffinity P1 * * * * ** ** If register P1 holds an integer convert it to a real value. |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 | assert( pMWin->iEphCsr ); pWin->csrApp = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr); } } } /* ** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the ** value of the second argument to nth_value() (eCond==2) has just been ** evaluated and the result left in register reg. This function generates VM ** code to check that the value is a non-negative integer and throws an ** exception if it is not. */ | > > > > > > | | > > | | > | 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 | assert( pMWin->iEphCsr ); pWin->csrApp = pParse->nTab++; sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr); } } } #define WINDOW_STARTING_INT 0 #define WINDOW_ENDING_INT 1 #define WINDOW_NTH_VALUE_INT 2 #define WINDOW_STARTING_NUM 3 #define WINDOW_ENDING_NUM 4 /* ** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the ** value of the second argument to nth_value() (eCond==2) has just been ** evaluated and the result left in register reg. This function generates VM ** code to check that the value is a non-negative integer and throws an ** exception if it is not. */ static void windowCheckValue(Parse *pParse, int reg, int eCond){ static const char *azErr[] = { "frame starting offset must be a non-negative integer", "frame ending offset must be a non-negative integer", "second argument to nth_value must be a positive integer", "frame starting offset must be a non-negative number", "frame ending offset must be a non-negative number", }; static int aOp[] = { OP_Ge, OP_Ge, OP_Gt, OP_Ge, OP_Ge }; Vdbe *v = sqlite3GetVdbe(pParse); int regZero = sqlite3GetTempReg(pParse); assert( eCond>=0 && eCond<ArraySize(azErr) ); sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero); sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2); if( eCond>=WINDOW_STARTING_NUM ) sqlite3VdbeChangeP5(v, 1); VdbeCoverageIf(v, eCond==0); VdbeCoverageIf(v, eCond==1); VdbeCoverageIf(v, eCond==2); sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg); VdbeCoverageNeverNullIf(v, eCond==0); VdbeCoverageNeverNullIf(v, eCond==1); VdbeCoverageNeverNullIf(v, eCond==2); |
︙ | ︙ | |||
1379 1380 1381 1382 1383 1384 1385 | 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); | | | 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 | 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); 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); |
︙ | ︙ | |||
2210 2211 2212 2213 2214 2215 2216 | addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst); /* This block is run for the first row of each partition */ s.regArg = windowInitAccum(pParse, pMWin); if( regStart ){ sqlite3ExprCode(pParse, pMWin->pStart, regStart); | | | | 2219 2220 2221 2222 2223 2224 2225 2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 | addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst); /* This block is run for the first row of each partition */ s.regArg = windowInitAccum(pParse, pMWin); if( regStart ){ sqlite3ExprCode(pParse, pMWin->pStart, regStart); windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0)); } if( regEnd ){ sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0)); } 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); sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1); |
︙ | ︙ |
Changes to test/window8.tcl.
︙ | ︙ | |||
89 90 91 92 93 94 95 | " } ========== execsql_test 2.0 { DROP TABLE IF EXISTS t1; | | > > > > > > > > | > | 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | " } ========== execsql_test 2.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a REAL, b INTEGER); INSERT INTO t1 VALUES (5, 10), (10, 20), (13, 26), (13, 26), (15, 30), (20, 40), (22,80), (30, 90); } foreach {tn frame} { 1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING } 2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING } 3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING } 4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING } 5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING } 6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING } 7 { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING } 8 { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING } 9 { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING } 10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING } 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 " SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame) " } finish_test |
Changes to test/window8.test.
︙ | ︙ | |||
433 434 435 436 437 438 439 | 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.0 { DROP TABLE IF EXISTS t1; | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > | 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 | 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.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a REAL, b INTEGER); INSERT INTO t1 VALUES (5, 10), (10, 20), (13, 26), (13, 26), (15, 30), (20, 40), (22,80), (30, 90); } {} do_execsql_test 2.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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 { 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 {}} finish_test |