Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow virtual table implementations to handle OFFSET but not LIMIT, but not LIMIT but not OFFSET. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | vtab-limit-fix |
Files: | files | file ages | folders |
SHA3-256: |
90e5c8226a695e838e8c1703a9b8598e |
User & Date: | dan 2024-04-26 18:13:11 |
Context
2024-04-26
| ||
19:10 | Fix issues in LIMIT/OFFSET support for virtual tables. The first problem was reported by forum post c243b8f856. That report prompted an enhancement to the generate_series() (also included in this merge) which in turn identified other similar issues. (check-in: 5f6c079d user: drh tags: trunk) | |
18:13 | Allow virtual table implementations to handle OFFSET but not LIMIT, but not LIMIT but not OFFSET. (Closed-Leaf check-in: 90e5c822 user: dan tags: vtab-limit-fix) | |
17:19 | Have where.c ignore any plan from a virtual table that tries to use LIMIT/OFFSET without also using all WHERE constraints. (check-in: 7d305964 user: dan tags: vtab-limit-fix) | |
Changes
Changes to src/whereexpr.c.
︙ | ︙ | |||
1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 | if( pExpr->iTable!=iCsr ) return; if( pOrderBy->a[ii].fg.sortFlags & KEYINFO_ORDER_BIGNULL ) return; } } /* All conditions are met. Add the terms to the where-clause object. */ assert( p->pLimit->op==TK_LIMIT ); if( p->iOffset==0 || (p->selFlags & SF_Compound)==0 ){ whereAddLimitExpr(pWC, p->iLimit, p->pLimit->pLeft, iCsr, SQLITE_INDEX_CONSTRAINT_LIMIT); } | > > > > < < < < | 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 | if( pExpr->iTable!=iCsr ) return; if( pOrderBy->a[ii].fg.sortFlags & KEYINFO_ORDER_BIGNULL ) return; } } /* All conditions are met. Add the terms to the where-clause object. */ assert( p->pLimit->op==TK_LIMIT ); if( p->iOffset!=0 && (p->selFlags & SF_Compound)==0 ){ whereAddLimitExpr(pWC, p->iOffset, p->pLimit->pRight, iCsr, SQLITE_INDEX_CONSTRAINT_OFFSET); } if( p->iOffset==0 || (p->selFlags & SF_Compound)==0 ){ whereAddLimitExpr(pWC, p->iLimit, p->pLimit->pLeft, iCsr, SQLITE_INDEX_CONSTRAINT_LIMIT); } } } /* ** Initialize a preallocated WhereClause structure. */ void sqlite3WhereClauseInit( |
︙ | ︙ |
Changes to test/bestindex8.test.
︙ | ︙ | |||
154 155 156 157 158 159 160 | set ::lFilterArgs } {10} do_test 2.2 { set ::lFilterArgs [list] execsql { SELECT * FROM vt1 LIMIT 5 OFFSET 50 } set ::lFilterArgs | | | 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | set ::lFilterArgs } {10} do_test 2.2 { set ::lFilterArgs [list] execsql { SELECT * FROM vt1 LIMIT 5 OFFSET 50 } set ::lFilterArgs } {{50 5}} do_test 2.3 { set ::lFilterArgs [list] execsql { SELECT * FROM vt1 ORDER BY a, b LIMIT 1 OFFSET 1 } set ::lFilterArgs } {{1 1}} |
︙ | ︙ |
Changes to test/bestindexC.test.
︙ | ︙ | |||
36 37 38 39 40 41 42 | set idxstr [list] set res [list] set idx 0 foreach c $clist { array set a $c if {$a(usable)==0} continue | | | | > > > > | 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | set idxstr [list] set res [list] set idx 0 foreach c $clist { array set a $c if {$a(usable)==0} continue if {$a(op)=="limit" && ![info exists ::do_not_use_limit]} { lappend idxstr limit lappend res omit $idx } if {$a(op)=="offset" && ![info exists ::do_not_use_offset]} { lappend idxstr offset lappend res omit $idx } incr idx } return "cost 1000000 rows 1000000 idxnum 0 idxstr {$idxstr} $res" } xFilter { set idxstr [lindex $args 1] set LIMIT "" foreach a $idxstr b [lindex $args 2] { set x($a) $b } if {![info exists x(limit)]} { set x(limit) -1 } if {![info exists x(offset)]} { set x(offset) -1 } set LIMIT " LIMIT $x(limit) OFFSET $x(offset)" set idx 1 foreach v $lVal { lappend lRow "($idx, '$v')" incr idx } |
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 | do_execsql_test 3.3 { SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3 } {2 4} do_execsql_test 3.4 { SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1; } {4 5 6 7} finish_test | > > > > > > > > > > > > > | 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | do_execsql_test 3.3 { SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3 } {2 4} do_execsql_test 3.4 { SELECT value FROM generate_series(1,10) WHERE value>2 LIMIT 4 OFFSET 1; } {4 5 6 7} set ::do_not_use_limit 1 do_execsql_test 3.5 { SELECT * FROM y1 LIMIT 5 OFFSET 3 } {4 5 6 7 8} unset ::do_not_use_limit set ::do_not_use_offset 1 do_execsql_test 3.6 { SELECT * FROM y1 LIMIT 5 OFFSET 3 } {4 5 6 7 8} unset ::do_not_use_offset finish_test |