Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix LIMIT and OFFSET so that they work and do not leak memory even on complex queries involving deeply nested views of UNION ALL compounds. Ticket [db4d96798da8]. Secondary to ticket [d58ccbb3f1b7]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
497ee36cb8d07c936e6896135163c5cd |
| User & Date: | drh 2013-01-29 23:55:50 |
Context
|
2013-01-30
| ||
| 01:46 | Remove some debugging logic accidently left in the limit.test module. check-in: 7d346250 user: drh tags: trunk | |
|
2013-01-29
| ||
| 23:55 | Fix LIMIT and OFFSET so that they work and do not leak memory even on complex queries involving deeply nested views of UNION ALL compounds. Ticket [db4d96798da8]. Secondary to ticket [d58ccbb3f1b7]. check-in: 497ee36c user: drh tags: trunk | |
| 19:14 | New debugging pragmas: PRAGMA vdbe_debug=ON is short-hand for the sql_trace, vdbe_listing, and vdbe_trace pragmas. PRAGMA vdbe_debug enables tracing of sqlite3VdbeAddOp() calls. None of this is active unless compiled with SQLITE_DEBUG. check-in: ae565ff3 user: drh tags: trunk | |
Changes
Changes to src/select.c.
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
....
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
|
/* Generate code for the left and right SELECT statements.
*/
switch( p->op ){
case TK_ALL: {
int addr = 0;
int nLimit;
assert( !pPrior->pLimit );
pPrior->pLimit = p->pLimit;
pPrior->pOffset = p->pOffset;
explainSetInteger(iSub1, pParse->iNextSelectId);
rc = sqlite3Select(pParse, pPrior, &dest);
p->pLimit = 0;
p->pOffset = 0;
if( rc ){
................................................................................
**
** We call this the "compound-subquery flattening".
*/
for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
Select *pNew;
ExprList *pOrderBy = p->pOrderBy;
Expr *pLimit = p->pLimit;
Select *pPrior = p->pPrior;
p->pOrderBy = 0;
p->pSrc = 0;
p->pPrior = 0;
p->pLimit = 0;
pNew = sqlite3SelectDup(db, p, 0);
p->pLimit = pLimit;
p->pOrderBy = pOrderBy;
p->pSrc = pSrc;
p->op = TK_ALL;
p->pRightmost = 0;
if( pNew==0 ){
pNew = pPrior;
|
>
>
>
>
>
|
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
....
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
|
/* Generate code for the left and right SELECT statements.
*/
switch( p->op ){
case TK_ALL: {
int addr = 0;
int nLimit;
assert( !pPrior->pLimit );
pPrior->iLimit = p->iLimit;
pPrior->iOffset = p->iOffset;
pPrior->pLimit = p->pLimit;
pPrior->pOffset = p->pOffset;
explainSetInteger(iSub1, pParse->iNextSelectId);
rc = sqlite3Select(pParse, pPrior, &dest);
p->pLimit = 0;
p->pOffset = 0;
if( rc ){
................................................................................
**
** We call this the "compound-subquery flattening".
*/
for(pSub=pSub->pPrior; pSub; pSub=pSub->pPrior){
Select *pNew;
ExprList *pOrderBy = p->pOrderBy;
Expr *pLimit = p->pLimit;
Expr *pOffset = p->pOffset;
Select *pPrior = p->pPrior;
p->pOrderBy = 0;
p->pSrc = 0;
p->pPrior = 0;
p->pLimit = 0;
p->pOffset = 0;
pNew = sqlite3SelectDup(db, p, 0);
p->pOffset = pOffset;
p->pLimit = pLimit;
p->pOrderBy = pOrderBy;
p->pSrc = pSrc;
p->op = TK_ALL;
p->pRightmost = 0;
if( pNew==0 ){
pNew = pPrior;
|
Changes to test/limit.test.
464 465 466 467 468 469 470 471 472 |
} {1 {no such column: x}}
do_test limit-12.4 {
catchsql {
SELECT * FROM t1 LIMIT 1 OFFSET x
}
} {1 {no such column: x}}
finish_test
|
> > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 |
} {1 {no such column: x}}
do_test limit-12.4 {
catchsql {
SELECT * FROM t1 LIMIT 1 OFFSET x
}
} {1 {no such column: x}}
db close
sqlite3_shutdown
sqlite3_config_lookaside 0 0
sqlite3_initialize
sqlite3 db :memory:
# Ticket [db4d96798da8b]
# LIMIT does not work with nested views containing UNION ALL
#
do_test limit-13.1 {
db eval {
CREATE TABLE t13(x);
INSERT INTO t13 VALUES(1),(2);
CREATE VIEW v13a AS SELECT x AS y FROM t13;
CREATE VIEW v13b AS SELECT y AS z FROM v13a UNION ALL SELECT y+10 FROM v13a;
CREATE VIEW v13c AS SELECT z FROM v13b UNION ALL SELECT z+20 FROM v13b;
}
} {}
do_test limit-13.2 {
db eval {SELECT z FROM v13c LIMIT 1}
} {1}
do_test limit-13.3 {
db eval {SELECT z FROM v13c LIMIT 2}
} {1 2}
do_test limit-13.4 {
db eval {SELECT z FROM v13c LIMIT 3}
} {1 2 11}
do_test limit-13.5 {
db eval {SELECT z FROM v13c LIMIT 4}
} {1 2 11 12}
do_test limit-13.6 {
db eval {SELECT z FROM v13c LIMIT 5}
} {1 2 11 12 21}
do_test limit-13.7 {
db eval {SELECT z FROM v13c LIMIT 6}
} {1 2 11 12 21 22}
do_test limit-13.8 {
db eval {SELECT z FROM v13c LIMIT 7}
} {1 2 11 12 21 22 31}
do_test limit-13.9 {
db eval {SELECT z FROM v13c LIMIT 8}
} {1 2 11 12 21 22 31 32}
do_test limit-13.10 {
db eval {SELECT z FROM v13c LIMIT 9}
} {1 2 11 12 21 22 31 32}
do_test limit-13.11 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 1}
} {2}
do_test limit-13.12 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 1}
} {2 11}
do_test limit-13.13 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 1}
} {2 11 12}
do_test limit-13.14 {
db eval {SELECT z FROM v13c LIMIT 4 OFFSET 1}
} {2 11 12 21}
do_test limit-13.15 {
db eval {SELECT z FROM v13c LIMIT 5 OFFSET 1}
} {2 11 12 21 22}
do_test limit-13.16 {
db eval {SELECT z FROM v13c LIMIT 6 OFFSET 1}
} {2 11 12 21 22 31}
do_test limit-13.17 {
db eval {SELECT z FROM v13c LIMIT 7 OFFSET 1}
} {2 11 12 21 22 31 32}
do_test limit-13.18 {
db eval {SELECT z FROM v13c LIMIT 8 OFFSET 1}
} {2 11 12 21 22 31 32}
do_test limit-13.21 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 2}
} {11}
do_test limit-13.22 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 2}
} {11 12}
do_test limit-13.23 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 2}
} {11 12 21}
do_test limit-13.24 {
db eval {SELECT z FROM v13c LIMIT 4 OFFSET 2}
} {11 12 21 22}
do_test limit-13.25 {
db eval {SELECT z FROM v13c LIMIT 5 OFFSET 2}
} {11 12 21 22 31}
do_test limit-13.26 {
db eval {SELECT z FROM v13c LIMIT 6 OFFSET 2}
} {11 12 21 22 31 32}
do_test limit-13.27 {
db eval {SELECT z FROM v13c LIMIT 7 OFFSET 2}
} {11 12 21 22 31 32}
do_test limit-13.31 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 3}
} {12}
do_test limit-13.32 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 3}
} {12 21}
do_test limit-13.33 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 3}
} {12 21 22}
do_test limit-13.34 {
db eval {SELECT z FROM v13c LIMIT 4 OFFSET 3}
} {12 21 22 31}
do_test limit-13.35 {
db eval {SELECT z FROM v13c LIMIT 5 OFFSET 3}
} {12 21 22 31 32}
do_test limit-13.36 {
db eval {SELECT z FROM v13c LIMIT 6 OFFSET 3}
} {12 21 22 31 32}
do_test limit-13.41 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 4}
} {21}
do_test limit-13.42 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 4}
} {21 22}
do_test limit-13.43 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 4}
} {21 22 31}
do_test limit-13.44 {
db eval {SELECT z FROM v13c LIMIT 4 OFFSET 4}
} {21 22 31 32}
do_test limit-13.45 {
db eval {SELECT z FROM v13c LIMIT 5 OFFSET 4}
} {21 22 31 32}
do_test limit-13.51 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 5}
} {22}
do_test limit-13.52 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 5}
} {22 31}
do_test limit-13.53 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 5}
} {22 31 32}
do_test limit-13.54 {
db eval {SELECT z FROM v13c LIMIT 4 OFFSET 5}
} {22 31 32}
do_test limit-13.61 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 6}
} {31}
do_test limit-13.62 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 6}
} {31 32}
do_test limit-13.63 {
db eval {SELECT z FROM v13c LIMIT 3 OFFSET 6}
} {31 32}
do_test limit-13.71 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 7}
} {32}
do_test limit-13.72 {
db eval {SELECT z FROM v13c LIMIT 2 OFFSET 7}
} {32}
do_test limit-13.81 {
db eval {SELECT z FROM v13c LIMIT 1 OFFSET 8}
} {}
finish_test
|