Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Backport check-in [62678be3df35cd]: When the same index is used for all OR-terms in a WHERE clause, then try to use that index as a covering index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | branch-3.7.2 |
Files: | files | file ages | folders |
SHA1: |
865dfcbaa59431422b5980c66c8e29e8 |
User & Date: | drh 2012-08-25 00:49:08.886 |
Context
2012-08-25
| ||
00:49 | Backport check-in [62678be3df35cd]: When the same index is used for all OR-terms in a WHERE clause, then try to use that index as a covering index. (Leaf check-in: 865dfcbaa5 user: drh tags: branch-3.7.2) | |
2011-10-25
| ||
21:18 | Cherrypick the [3513bf6ee090d9] so that the sqlite_source_id() function works correctly even with newer versions of Fossil (check-in: 89d63a0e1d user: drh tags: branch-3.7.2) | |
Changes
Changes to src/delete.c.
︙ | |||
359 360 361 362 363 364 365 | 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 | - + | int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ int regRowid; /* Actual register containing rowids */ /* Collect rowids of every row to be deleted. */ sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); pWInfo = sqlite3WhereBegin( |
︙ |
Changes to src/fkey.c.
︙ | |||
544 545 546 547 548 549 550 | 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 | - + | sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ |
︙ |
Changes to src/select.c.
︙ | |||
3833 3834 3835 3836 3837 3838 3839 | 3833 3834 3835 3836 3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 | - + | } /* Aggregate and non-aggregate queries are handled differently */ if( !isAgg && pGroupBy==0 ){ ExprList *pDist = (isDistinct ? p->pEList : 0); /* Begin the database scan. */ |
︙ | |||
4000 4001 4002 4003 4004 4005 4006 | 4000 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 | - + | /* Begin a loop that will extract all source rows in GROUP BY order. ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); |
︙ | |||
4259 4260 4261 4262 4263 4264 4265 | 4259 4260 4261 4262 4263 4264 4265 4266 4267 4268 4269 4270 4271 4272 4273 | - + | } /* This case runs if the aggregate has no GROUP BY clause. The ** processing is much simpler since there is only a single row ** of output. */ resetAccumulator(pParse, &sAggInfo); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 | 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 | + | struct { int nIn; /* Number of entries in aInLoop[] */ struct InLoop { int iCur; /* The VDBE cursor used by this IN operator */ int addrInTop; /* Top of the IN loop */ } *aInLoop; /* Information about each nested IN operator */ } in; /* Used when plan.wsFlags&WHERE_IN_ABLE */ Index *pCovidx; /* Possible covering index for WHERE_MULTI_OR */ } u; /* The following field is really not part of the current level. But ** we need a place to cache virtual table index information for each ** virtual table in the FROM clause and the WhereLevel structure is ** a convenient place since there is one WhereLevel for each FROM clause ** element. |
︙ | |||
2674 2675 2676 2677 2678 2679 2680 | 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 | - + + | int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); #endif void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); |
︙ |
Changes to src/update.c.
︙ | |||
309 310 311 312 313 314 315 | 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 | - + | goto update_cleanup; } /* Begin the database scan */ sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); pWInfo = sqlite3WhereBegin( |
︙ |
Changes to src/where.c.
︙ | |||
3564 3565 3566 3567 3568 3569 3570 | 3564 3565 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 | - + | /* Evaluate the equality constraints */ assert( pIdx->nColumn>=nEq ); for(j=0; j<nEq; j++){ int r1; int k = pIdx->aiColumn[j]; pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx); |
︙ | |||
4238 4239 4240 4241 4242 4243 4244 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 | 4238 4239 4240 4241 4242 4243 4244 4245 4246 4247 4248 4249 4250 4251 4252 4253 4254 4255 4256 4257 4258 4259 4260 4261 4262 4263 4264 4265 4266 4267 4268 4269 4270 4271 4272 | + + - + | ** ** B: <after the loop> ** */ WhereClause *pOrWc; /* The OR-clause broken out into subterms */ WhereTerm *pFinal; /* Final subterm within the OR-clause. */ SrcList *pOrTab; /* Shortened table list or OR-clause generation */ Index *pCov = 0; /* Potential covering index (or NULL) */ int iCovCur = pParse->nTab++; /* Cursor used for index scans (if any) */ int regReturn = ++pParse->nMem; /* Register used with OP_Gosub */ int regRowset = 0; /* Register for RowSet object */ int regRowid = 0; /* Register holding rowid */ int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ int iRetInit; /* Address of regReturn init */ int untestedTerms = 0; /* Some terms not completely tested */ int ii; pTerm = pLevel->plan.u.pTerm; assert( pTerm!=0 ); assert( pTerm->eOperator==WO_OR ); assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); pOrWc = &pTerm->u.pOrInfo->wc; pFinal = &pOrWc->a[pOrWc->nTerm-1]; pLevel->op = OP_Return; pLevel->p1 = regReturn; |
︙ | |||
4303 4304 4305 4306 4307 4308 4309 | 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 4322 4323 4324 4325 4326 4327 4328 4329 4330 4331 4332 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 4351 4352 4353 4354 4355 4356 4357 4358 4359 4360 4361 4362 4363 4364 4365 4366 4367 4368 4369 4370 4371 4372 | - + + + + + + + + + + + + + + + + + + + + + + + + + + + + | for(ii=0; ii<pOrWc->nTerm; ii++){ WhereTerm *pOrTerm = &pOrWc->a[ii]; if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ WhereInfo *pSubWInfo; /* Info for single OR-term scan */ /* Loop through table entries that match term pOrTerm. */ pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, 0, WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | |
︙ | |||
4545 4546 4547 4548 4549 4550 4551 | 4574 4575 4576 4577 4578 4579 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 | - + + | */ WhereInfo *sqlite3WhereBegin( Parse *pParse, /* The parser context */ SrcList *pTabList, /* A list of all tables to be scanned */ Expr *pWhere, /* The WHERE clause */ ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ ExprList *pDistinct, /* The select-list for DISTINCT queries - or NULL */ |
︙ | |||
4862 4863 4864 4865 4866 4867 4868 | 4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 4906 4907 4908 4909 4910 4911 4912 | + + + + + - + + | pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ if( (wctrlFlags & WHERE_ONETABLE_ONLY) && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 ){ pLevel->iIdxCur = iIdxCur; }else{ |
︙ | |||
5114 5115 5116 5117 5118 5119 5120 5121 5122 5123 5124 5125 5126 5127 | 5150 5151 5152 5153 5154 5155 5156 5157 5158 5159 5160 5161 5162 5163 5164 | + | */ sqlite3VdbeResolveLabel(v, pWInfo->iBreak); /* Close all of the cursors that were opened by sqlite3WhereBegin. */ assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc ); for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){ Index *pIdx = 0; struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom]; Table *pTab = pTabItem->pTab; assert( pTab!=0 ); if( (pTab->tabFlags & TF_Ephemeral)==0 && pTab->pSelect==0 && (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0 ){ |
︙ | |||
5143 5144 5145 5146 5147 5148 5149 | 5180 5181 5182 5183 5184 5185 5186 5187 5188 5189 5190 5191 5192 5193 5194 5195 5196 5197 5198 5199 5200 5201 5202 | - + + + + + + - - | ** ** Calls to the code generator in between sqlite3WhereBegin and ** sqlite3WhereEnd will have created code that references the table ** directly. This loop scans all that code looking for opcodes ** that reference the table and converts them into opcodes that ** reference the index. */ |
︙ |
Changes to test/fts3query.test.
︙ | |||
112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | + - + - | DROP TABLE IF EXISTS t1; CREATE TABLE t1(number INTEGER PRIMARY KEY, date); CREATE INDEX i1 ON t1(date); CREATE VIRTUAL TABLE ft USING fts3(title); CREATE TABLE bt(title); } } {} if 0 { do_test fts3query-4.2 { eqp "SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date" } {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE ft VIRTUAL TABLE INDEX 1:}} do_test fts3query-4.3 { eqp "SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date" } {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE ft VIRTUAL TABLE INDEX 1:}} do_test fts3query-4.4 { eqp "SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date" } {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE bt USING PRIMARY KEY}} do_test fts3query-4.5 { eqp "SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date" } {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE bt USING PRIMARY KEY}} |
Added test/whereD.test.