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