Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Have where.c ignore any plan from a virtual table that tries to use LIMIT/OFFSET without also using all WHERE constraints. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | vtab-limit-fix |
Files: | files | file ages | folders |
SHA3-256: |
7d30596496c6a7a37b925f13d8d94d5d |
User & Date: | dan 2024-04-26 17:19:59 |
Context
2024-04-26
| ||
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) | |
17:09 | Further fixes and improvements to the generate_series() enhancements on this branch. (check-in: b7d9bd7e user: drh tags: vtab-limit-fix) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 | ** Return true if pTerm is a virtual table LIMIT or OFFSET term. */ static int isLimitTerm(WhereTerm *pTerm){ assert( pTerm->eOperator==WO_AUX || pTerm->eMatchOp==0 ); return pTerm->eMatchOp>=SQLITE_INDEX_CONSTRAINT_LIMIT && pTerm->eMatchOp<=SQLITE_INDEX_CONSTRAINT_OFFSET; } /* ** Argument pIdxInfo is already populated with all constraints that may ** be used by the virtual table identified by pBuilder->pNew->iTab. This ** function marks a subset of those constraints usable, invokes the ** xBestIndex method and adds the returned plan to pBuilder. ** | > > > > > > > > > > > > > > > | 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 | ** Return true if pTerm is a virtual table LIMIT or OFFSET term. */ static int isLimitTerm(WhereTerm *pTerm){ assert( pTerm->eOperator==WO_AUX || pTerm->eMatchOp==0 ); return pTerm->eMatchOp>=SQLITE_INDEX_CONSTRAINT_LIMIT && pTerm->eMatchOp<=SQLITE_INDEX_CONSTRAINT_OFFSET; } /* ** Return true if the first nCons constraints in the pUsage array are ** marked as in-use (have argvIndex>0). False otherwise. */ static int allConstraintsUsed( struct sqlite3_index_constraint_usage *aUsage, int nCons ){ int ii; for(ii=0; ii<nCons; ii++){ if( aUsage[ii].argvIndex<=0 ) return 0; } return 1; } /* ** Argument pIdxInfo is already populated with all constraints that may ** be used by the virtual table identified by pBuilder->pNew->iTab. This ** function marks a subset of those constraints usable, invokes the ** xBestIndex method and adds the returned plan to pBuilder. ** |
︙ | ︙ | |||
4193 4194 4195 4196 4197 4198 4199 4200 | ** (2) Multiple outputs from a single IN value will not merge ** together. */ pIdxInfo->orderByConsumed = 0; pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE; *pbIn = 1; assert( (mExclude & WO_IN)==0 ); } assert( pbRetryLimit || !isLimitTerm(pTerm) ); | > > > > > | | > > | | | 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 | ** (2) Multiple outputs from a single IN value will not merge ** together. */ pIdxInfo->orderByConsumed = 0; pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE; *pbIn = 1; assert( (mExclude & WO_IN)==0 ); } /* Unless pbRetryLimit is non-NULL, there should be no LIMIT/OFFSET ** terms. And if there are any, they should follow all other terms. */ assert( pbRetryLimit || !isLimitTerm(pTerm) ); assert( !isLimitTerm(pTerm) || i>=nConstraint-2 ); assert( !isLimitTerm(pTerm) || i==nConstraint-1 || isLimitTerm(pTerm+1) ); if( isLimitTerm(pTerm) && (*pbIn || !allConstraintsUsed(pUsage, i)) ){ /* If there is an IN(...) term handled as an == (separate call to ** xFilter for each value on the RHS of the IN) and a LIMIT or ** OFFSET term handled as well, the plan is unusable. Similarly, ** if there is a LIMIT/OFFSET and there are other unused terms, ** the plan cannot be used. In these cases set variable *pbRetryLimit ** to true to tell the caller to retry with LIMIT and OFFSET ** disabled. */ if( pIdxInfo->needToFreeIdxStr ){ sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; pIdxInfo->needToFreeIdxStr = 0; } *pbRetryLimit = 1; return SQLITE_OK; |
︙ | ︙ |
Changes to test/bestindexC.test.
|
| | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # 2024-04-26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix bestindexC ifcapable !vtab { finish_test return } register_tcl_module db |
︙ | ︙ | |||
149 150 151 152 153 154 155 156 157 | SELECT * FROM y1 WHERE a = '2' LIMIT 3 } {2} load_static_extension db series do_execsql_test 3.3 { SELECT * FROM generate_series(1, 5) WHERE value = (value & 14) LIMIT 3 } {2 4} finish_test | > > > > | 149 150 151 152 153 154 155 156 157 158 159 160 161 | SELECT * FROM y1 WHERE a = '2' LIMIT 3 } {2} load_static_extension db series 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 |