Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
cb3e2be674316e1d39968eb6567f1fe1 |
| User & Date: | dan 2019-09-04 06:56:43 |
Context
|
2019-09-09
| ||
| 20:17 | Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Fix for [a7debbe0]. check-in: b9ec7220 user: dan tags: trunk | |
| 19:49 | Ensure the columns of views and sub-selects in the FROM clause of a select are always assigned implicit collation sequences, just as table columns are. Possible fix for [a7debbe0]. Closed-Leaf check-in: 1863b7bf user: dan tags: tkt-a7debbe0. | |
|
2019-09-07
| ||
| 18:20 | Add the SQLITE_SUBTYPE flag, which can be passed to sqlite3_create_function() and similar to indicate to the core that a user function is likely to use sqlite3_result_subtype(). check-in: 6aa438ce user: dan tags: window-functions-subtype-fix | |
|
2019-09-04
| ||
| 06:56 | Fix handling of NULL, text and blob values in window queries that use "RANGE BETWEEN A FOLLOWING AND B FOLLOWING", or "B PRECEDING AND A PRECEDING", where A>B. check-in: cb3e2be6 user: dan tags: trunk | |
|
2019-09-03
| ||
| 16:23 | Updates to the default settings in Makefile.linux-gcc. check-in: 3044cf69 user: drh tags: trunk | |
Changes
Changes to src/window.c.
2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 |
windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
}
if( regEnd ){
sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
}
if( pMWin->eStart==pMWin->eEnd && regStart ){
int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
VdbeCoverageNeverNullIf(v, op==OP_Le); /* values previously checked */
windowAggFinal(&s, 0);
sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
VdbeCoverageNeverTaken(v);
|
| |
2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 |
windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
}
if( regEnd ){
sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE ? 3 : 0));
}
if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && regStart ){
int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
VdbeCoverageNeverNullIf(v, op==OP_Le); /* values previously checked */
windowAggFinal(&s, 0);
sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
VdbeCoverageNeverTaken(v);
|
Changes to test/window8.tcl.
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 |
DROP TABLE IF EXISTS t2; CREATE TABLE t2(a INTEGER, b INTEGER); INSERT INTO t2 VALUES(1, 65); INSERT INTO t2 VALUES(2, NULL); INSERT INTO t2 VALUES(3, NULL); INSERT INTO t2 VALUES(4, NULL); } execsql_test 7.1 { SELECT sum(a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); } finish_test |
> > > > > > > > | | > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > |
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 |
DROP TABLE IF EXISTS t2; CREATE TABLE t2(a INTEGER, b INTEGER); INSERT INTO t2 VALUES(1, 65); INSERT INTO t2 VALUES(2, NULL); INSERT INTO t2 VALUES(3, NULL); INSERT INTO t2 VALUES(4, NULL); INSERT INTO t2 VALUES(5, 66); INSERT INTO t2 VALUES(6, 67); } foreach {tn f ex} { 1 sum "" 2 min "" 3 sum "EXCLUDE CURRENT ROW" 4 max "EXCLUDE CURRENT ROW" } { execsql_test 7.$tn.1 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); " execsql_test 7.$tn.2 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); " execsql_test 7.$tn.3 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); " execsql_test 7.$tn.4 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); " execsql_test 7.$tn.5 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); " execsql_test 7.$tn.6 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING ); " execsql_test 7.$tn.7 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); " execsql_test 7.$tn.8 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING ); " execsql_test 7.$tn.9 " SELECT $f (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); " } finish_test |
Changes to test/window8.test.
6209 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 |
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER, b INTEGER);
INSERT INTO t2 VALUES(1, 65);
INSERT INTO t2 VALUES(2, NULL);
INSERT INTO t2 VALUES(3, NULL);
INSERT INTO t2 VALUES(4, NULL);
} {}
do_execsql_test 7.1 {
SELECT sum(a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
} {9 9 9 9}
finish_test
|
> > | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
6209 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 6233 6234 6235 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 6246 6247 6248 6249 6250 6251 6252 6253 6254 6255 6256 6257 6258 6259 6260 6261 6262 6263 6264 6265 6266 6267 6268 6269 6270 6271 6272 6273 6274 6275 6276 6277 6278 6279 6280 6281 6282 6283 6284 6285 6286 6287 6288 6289 6290 6291 6292 6293 6294 6295 6296 6297 6298 6299 6300 6301 6302 6303 6304 6305 6306 6307 6308 6309 6310 6311 6312 6313 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 6331 6332 6333 6334 6335 6336 6337 6338 6339 6340 6341 6342 6343 6344 6345 6346 6347 6348 6349 6350 6351 6352 6353 6354 6355 6356 6357 6358 6359 6360 6361 6362 6363 6364 6365 6366 6367 6368 6369 6370 6371 6372 6373 6374 6375 6376 6377 6378 6379 6380 6381 6382 6383 6384 6385 6386 6387 6388 6389 6390 6391 6392 6393 6394 6395 6396 6397 6398 6399 6400 6401 6402 6403 6404 6405 6406 6407 6408 6409 6410 6411 6412 6413 6414 6415 6416 6417 6418 6419 6420 6421 6422 6423 6424 6425 6426 6427 6428 6429 6430 6431 6432 6433 6434 6435 6436 6437 6438 6439 6440 6441 6442 6443 6444 6445 6446 6447 6448 6449 6450 6451 6452 6453 6454 6455 6456 6457 6458 6459 6460 6461 6462 6463 6464 6465 6466 6467 6468 6469 6470 6471 6472 |
DROP TABLE IF EXISTS t2; CREATE TABLE t2(a INTEGER, b INTEGER); INSERT INTO t2 VALUES(1, 65); INSERT INTO t2 VALUES(2, NULL); INSERT INTO t2 VALUES(3, NULL); INSERT INTO t2 VALUES(4, NULL); INSERT INTO t2 VALUES(5, 66); INSERT INTO t2 VALUES(6, 67); } {} do_execsql_test 7.1.1 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); } {9 9 9 9 9 9} do_execsql_test 7.1.2 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {{} {} {} 9 9 9} do_execsql_test 7.1.3 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {{} {} {} 9 9 9} do_execsql_test 7.1.4 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {9 9 9 {} {} {}} do_execsql_test 7.1.5 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {9 9 9 {} {} {}} do_execsql_test 7.1.6 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING ); } {{} {} 1 9 9 9} do_execsql_test 7.1.7 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {{} {} {} 9 9 9} do_execsql_test 7.1.8 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING ); } {9 9 9 {} {} {}} do_execsql_test 7.1.9 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {9 9 9 {} {} {}} do_execsql_test 7.2.1 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); } {2 2 2 2 2 2} do_execsql_test 7.2.2 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {{} {} {} 2 2 2} do_execsql_test 7.2.3 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {{} {} {} 2 2 2} do_execsql_test 7.2.4 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {2 2 2 {} {} {}} do_execsql_test 7.2.5 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {2 2 2 {} {} {}} do_execsql_test 7.2.6 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING ); } {{} {} 1 2 2 2} do_execsql_test 7.2.7 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {{} {} {} 2 2 2} do_execsql_test 7.2.8 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING ); } {2 2 2 {} {} {}} do_execsql_test 7.2.9 { SELECT min (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {2 2 2 {} {} {}} do_execsql_test 7.3.1 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); } {9 9 9 9 9 9} do_execsql_test 7.3.2 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {{} {} {} 9 9 9} do_execsql_test 7.3.3 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {{} {} {} 9 9 9} do_execsql_test 7.3.4 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {9 9 9 {} {} {}} do_execsql_test 7.3.5 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {9 9 9 {} {} {}} do_execsql_test 7.3.6 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING ); } {{} {} 1 9 9 9} do_execsql_test 7.3.7 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {{} {} {} 9 9 9} do_execsql_test 7.3.8 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING ); } {9 9 9 {} {} {}} do_execsql_test 7.3.9 { SELECT sum (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {9 9 9 {} {} {}} do_execsql_test 7.4.1 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING ); } {4 4 4 4 4 4} do_execsql_test 7.4.2 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {{} {} {} 4 4 4} do_execsql_test 7.4.3 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {{} {} {} 4 4 4} do_execsql_test 7.4.4 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING ); } {4 4 4 {} {} {}} do_execsql_test 7.4.5 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING ); } {4 4 4 {} {} {}} do_execsql_test 7.4.6 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING ); } {{} {} 1 4 4 4} do_execsql_test 7.4.7 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {{} {} {} 4 4 4} do_execsql_test 7.4.8 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING ); } {4 4 4 {} {} {}} do_execsql_test 7.4.9 { SELECT max (a) OVER win FROM t2 WINDOW win AS ( ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING ); } {4 4 4 {} {} {}} finish_test |