Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Throw an error if the second argument passed to nth_value() is not a positive integer. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
1a06e57a0b4279fa580c7ff4f152645f |
| User & Date: | dan 2018-07-09 13:31:18 |
Context
|
2018-07-09
| ||
| 16:24 | Simplification to the grammar rules for window functions. Fix a memory leak that can follow an OOM while parsing a comma-separated list of window definitions. check-in: a568f9c9 user: drh tags: trunk | |
| 13:31 | Throw an error if the second argument passed to nth_value() is not a positive integer. check-in: 1a06e57a user: dan tags: trunk | |
| 06:51 | Fix a bad assert() in window.c. check-in: fe8aaf0c user: dan tags: trunk | |
Changes
Changes to src/window.c.
998 998 pWin->csrApp = pParse->nTab++; 999 999 sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr); 1000 1000 } 1001 1001 } 1002 1002 } 1003 1003 1004 1004 /* 1005 -** A "PRECEDING <expr>" (bEnd==0) or "FOLLOWING <expr>" (bEnd==1) has just 1006 -** been evaluated and the result left in register reg. This function generates 1007 -** VM code to check that the value is a non-negative integer and throws 1008 -** an exception if it is not. 1005 +** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the 1006 +** value of the second argument to nth_value() (eCond==2) has just been 1007 +** evaluated and the result left in register reg. This function generates VM 1008 +** code to check that the value is a non-negative integer and throws an 1009 +** exception if it is not. 1009 1010 */ 1010 -static void windowCheckFrameOffset(Parse *pParse, int reg, int bEnd){ 1011 +static void windowCheckIntValue(Parse *pParse, int reg, int eCond){ 1011 1012 static const char *azErr[] = { 1012 1013 "frame starting offset must be a non-negative integer", 1013 - "frame ending offset must be a non-negative integer" 1014 + "frame ending offset must be a non-negative integer", 1015 + "second argument to nth_value must be a positive integer" 1014 1016 }; 1017 + static int aOp[] = { OP_Ge, OP_Ge, OP_Gt }; 1015 1018 Vdbe *v = sqlite3GetVdbe(pParse); 1016 1019 int regZero = sqlite3GetTempReg(pParse); 1020 + assert( eCond==0 || eCond==1 || eCond==2 ); 1017 1021 sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero); 1018 1022 sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2); 1019 1023 VdbeCoverage(v); 1020 - sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg); 1024 + sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg); 1021 1025 VdbeCoverage(v); 1022 1026 sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort); 1023 - sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC); 1027 + sqlite3VdbeAppendP4(v, (void*)azErr[eCond], P4_STATIC); 1024 1028 sqlite3ReleaseTempReg(pParse, regZero); 1025 1029 } 1026 1030 1027 1031 /* 1028 1032 ** Return the number of arguments passed to the window-function associated 1029 1033 ** with the object passed as the only argument to this function. 1030 1034 */ ................................................................................ 1274 1278 int csr = pWin->csrApp; 1275 1279 int lbl = sqlite3VdbeMakeLabel(v); 1276 1280 int tmpReg = sqlite3GetTempReg(pParse); 1277 1281 sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); 1278 1282 1279 1283 if( pFunc->zName==nth_valueName ){ 1280 1284 sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg); 1285 + windowCheckIntValue(pParse, tmpReg, 2); 1281 1286 }else{ 1282 1287 sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg); 1283 1288 } 1284 1289 sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg); 1285 1290 sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg); 1286 1291 VdbeCoverage(v); 1287 1292 sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg); ................................................................................ 1572 1577 sqlite3VdbeAddOp2(v, OP_OpenDup, csrStart, pMWin->iEphCsr); 1573 1578 sqlite3VdbeAddOp2(v, OP_OpenDup, csrEnd, pMWin->iEphCsr); 1574 1579 1575 1580 /* If either regStart or regEnd are not non-negative integers, throw 1576 1581 ** an exception. */ 1577 1582 if( pMWin->pStart ){ 1578 1583 sqlite3ExprCode(pParse, pMWin->pStart, regStart); 1579 - windowCheckFrameOffset(pParse, regStart, 0); 1584 + windowCheckIntValue(pParse, regStart, 0); 1580 1585 } 1581 1586 if( pMWin->pEnd ){ 1582 1587 sqlite3ExprCode(pParse, pMWin->pEnd, regEnd); 1583 - windowCheckFrameOffset(pParse, regEnd, 1); 1588 + windowCheckIntValue(pParse, regEnd, 1); 1584 1589 } 1585 1590 1586 1591 /* If this is "ROWS <expr1> FOLLOWING AND ROWS <expr2> FOLLOWING", do: 1587 1592 ** 1588 1593 ** if( regEnd<regStart ){ 1589 1594 ** // The frame always consists of 0 rows 1590 1595 ** regStart = regSize;
Changes to test/window4.tcl.
346 346 INSERT INTO t7(id, a, b) VALUES 347 347 (1, 1, 2), (2, 1, NULL), (3, 1, 4), 348 348 (4, 3, NULL), (5, 3, 8), (6, 3, 1); 349 349 } 350 350 execsql_test 10.1 { 351 351 SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7; 352 352 } 353 + 354 +execsql_test 10.2 { 355 + SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7; 356 +} 357 +execsql_test 10.3 { 358 + SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7; 359 +} 360 + 353 361 354 362 355 363 finish_test 356 364
Changes to test/window4.test.
1257 1257 (4, 3, NULL), (5, 3, 8), (6, 3, 1); 1258 1258 } {} 1259 1259 1260 1260 do_execsql_test 10.1 { 1261 1261 SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7; 1262 1262 } {1 2 2 2 3 2 4 {} 5 8 6 1} 1263 1263 1264 +do_execsql_test 10.2 { 1265 + SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7; 1266 +} {1 {} 2 2 3 {} 4 {} 5 {} 6 8} 1267 + 1268 +do_execsql_test 10.3 { 1269 + SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7; 1270 +} {1 {} 2 4 3 {} 4 8 5 1 6 {}} 1271 + 1264 1272 finish_test
Changes to test/window6.test.
278 278 279 279 do_execsql_test 10.0 { 280 280 WITH t1(a,b) AS (VALUES(1,2)) 281 281 SELECT count() FILTER (where b<>5) OVER w1 282 282 FROM t1 283 283 WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); 284 284 } {1} 285 + 286 +foreach {tn stmt} { 287 + 1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1" 288 + 2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1" 289 + 3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1" 290 + 4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1" 291 + 5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1" 292 +} { 293 + do_catchsql_test 10.1.$tn " 294 + WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) ) 295 + $stmt 296 + " {1 {second argument to nth_value must be a positive integer}} 297 +} 298 + 299 +foreach {tn stmt res} { 300 + 1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1" {2 2 2} 301 + 2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1" {{} 3 3} 302 + 3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1" {{} 3 3} 303 + 4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1" {{} 3 3} 304 + 5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1" {{} 3 3} 305 + 6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1" {{} {} {}} 306 +} { 307 + do_execsql_test 10.2.$tn " 308 + WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) ) 309 + $stmt 310 + " $res 311 +} 285 312 286 313 finish_test