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 | SQL archive |
| Timelines: | family | ancestors | branch-3.7.2 |
| Files: | files | file ages | folders |
| SHA1: |
865dfcbaa59431422b5980c66c8e29e8 |
| User & Date: | drh 2012-08-25 00:49:08 |
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: 865dfcba 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: 89d63a0e user: drh tags: branch-3.7.2 | |
Changes
Changes to src/delete.c.
359 359 int iRowid = ++pParse->nMem; /* Used for storing rowid values. */ 360 360 int regRowid; /* Actual register containing rowids */ 361 361 362 362 /* Collect rowids of every row to be deleted. 363 363 */ 364 364 sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet); 365 365 pWInfo = sqlite3WhereBegin( 366 - pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK 366 + pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0 367 367 ); 368 368 if( pWInfo==0 ) goto delete_from_cleanup; 369 369 regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid); 370 370 sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid); 371 371 if( db->flags & SQLITE_CountRows ){ 372 372 sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1); 373 373 }
Changes to src/fkey.c.
544 544 sNameContext.pParse = pParse; 545 545 sqlite3ResolveExprNames(&sNameContext, pWhere); 546 546 547 547 /* Create VDBE to loop through the entries in pSrc that match the WHERE 548 548 ** clause. If the constraint is not deferred, throw an exception for 549 549 ** each row found. Otherwise, for deferred constraints, increment the 550 550 ** deferred constraint counter by nIncr for each row selected. */ 551 - pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0); 551 + pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0); 552 552 if( nIncr>0 && pFKey->isDeferred==0 ){ 553 553 sqlite3ParseToplevel(pParse)->mayAbort = 1; 554 554 } 555 555 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); 556 556 if( pWInfo ){ 557 557 sqlite3WhereEnd(pWInfo); 558 558 }
Changes to src/select.c.
3833 3833 } 3834 3834 3835 3835 /* Aggregate and non-aggregate queries are handled differently */ 3836 3836 if( !isAgg && pGroupBy==0 ){ 3837 3837 ExprList *pDist = (isDistinct ? p->pEList : 0); 3838 3838 3839 3839 /* Begin the database scan. */ 3840 - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0); 3840 + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0,0); 3841 3841 if( pWInfo==0 ) goto select_end; 3842 3842 if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut; 3843 3843 3844 3844 /* If sorting index that was created by a prior OP_OpenEphemeral 3845 3845 ** instruction ended up not being needed, then change the OP_OpenEphemeral 3846 3846 ** into an OP_Noop. 3847 3847 */ ................................................................................ 4000 4000 4001 4001 /* Begin a loop that will extract all source rows in GROUP BY order. 4002 4002 ** This might involve two separate loops with an OP_Sort in between, or 4003 4003 ** it might be a single loop that uses an index to extract information 4004 4004 ** in the right order to begin with. 4005 4005 */ 4006 4006 sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); 4007 - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0); 4007 + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0, 0); 4008 4008 if( pWInfo==0 ) goto select_end; 4009 4009 if( pGroupBy==0 ){ 4010 4010 /* The optimizer is able to deliver rows in group by order so 4011 4011 ** we do not have to sort. The OP_OpenEphemeral table will be 4012 4012 ** cancelled later because we still need to use the pKeyInfo 4013 4013 */ 4014 4014 pGroupBy = p->pGroupBy; ................................................................................ 4259 4259 } 4260 4260 4261 4261 /* This case runs if the aggregate has no GROUP BY clause. The 4262 4262 ** processing is much simpler since there is only a single row 4263 4263 ** of output. 4264 4264 */ 4265 4265 resetAccumulator(pParse, &sAggInfo); 4266 - pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, 0, flag); 4266 + pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax,0,flag,0); 4267 4267 if( pWInfo==0 ){ 4268 4268 sqlite3ExprListDelete(db, pDel); 4269 4269 goto select_end; 4270 4270 } 4271 4271 updateAccumulator(pParse, &sAggInfo); 4272 4272 if( !pMinMax && flag ){ 4273 4273 sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak);
Changes to src/sqliteInt.h.
1873 1873 struct { 1874 1874 int nIn; /* Number of entries in aInLoop[] */ 1875 1875 struct InLoop { 1876 1876 int iCur; /* The VDBE cursor used by this IN operator */ 1877 1877 int addrInTop; /* Top of the IN loop */ 1878 1878 } *aInLoop; /* Information about each nested IN operator */ 1879 1879 } in; /* Used when plan.wsFlags&WHERE_IN_ABLE */ 1880 + Index *pCovidx; /* Possible covering index for WHERE_MULTI_OR */ 1880 1881 } u; 1881 1882 1882 1883 /* The following field is really not part of the current level. But 1883 1884 ** we need a place to cache virtual table index information for each 1884 1885 ** virtual table in the FROM clause and the WhereLevel structure is 1885 1886 ** a convenient place since there is one WhereLevel for each FROM clause 1886 1887 ** element. ................................................................................ 2674 2675 int sqlite3IsReadOnly(Parse*, Table*, int); 2675 2676 void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); 2676 2677 #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) 2677 2678 Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *); 2678 2679 #endif 2679 2680 void sqlite3DeleteFrom(Parse*, SrcList*, Expr*); 2680 2681 void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int); 2681 -WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**,ExprList*,u16); 2682 +WhereInfo *sqlite3WhereBegin( 2683 + Parse*,SrcList*,Expr*,ExprList**,ExprList*,u16,int); 2682 2684 void sqlite3WhereEnd(WhereInfo*); 2683 2685 int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int); 2684 2686 void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int); 2685 2687 void sqlite3ExprCodeMove(Parse*, int, int, int); 2686 2688 void sqlite3ExprCodeCopy(Parse*, int, int, int); 2687 2689 void sqlite3ExprCacheStore(Parse*, int, int, int); 2688 2690 void sqlite3ExprCachePush(Parse*);
Changes to src/update.c.
309 309 goto update_cleanup; 310 310 } 311 311 312 312 /* Begin the database scan 313 313 */ 314 314 sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid); 315 315 pWInfo = sqlite3WhereBegin( 316 - pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED 316 + pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, 0 317 317 ); 318 318 if( pWInfo==0 ) goto update_cleanup; 319 319 okOnePass = pWInfo->okOnePass; 320 320 321 321 /* Remember the rowid of every item to be updated. 322 322 */ 323 323 sqlite3VdbeAddOp2(v, OP_Rowid, iCur, regOldRowid);
Changes to src/where.c.
3564 3564 /* Evaluate the equality constraints 3565 3565 */ 3566 3566 assert( pIdx->nColumn>=nEq ); 3567 3567 for(j=0; j<nEq; j++){ 3568 3568 int r1; 3569 3569 int k = pIdx->aiColumn[j]; 3570 3570 pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx); 3571 - if( NEVER(pTerm==0) ) break; 3571 + if( pTerm==0 ) break; 3572 3572 /* The following true for indices with redundant columns. 3573 3573 ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ 3574 3574 testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); 3575 3575 testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ 3576 3576 r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j); 3577 3577 if( r1!=regBase+j ){ 3578 3578 if( nReg==1 ){ ................................................................................ 4238 4238 ** 4239 4239 ** B: <after the loop> 4240 4240 ** 4241 4241 */ 4242 4242 WhereClause *pOrWc; /* The OR-clause broken out into subterms */ 4243 4243 WhereTerm *pFinal; /* Final subterm within the OR-clause. */ 4244 4244 SrcList *pOrTab; /* Shortened table list or OR-clause generation */ 4245 + Index *pCov = 0; /* Potential covering index (or NULL) */ 4246 + int iCovCur = pParse->nTab++; /* Cursor used for index scans (if any) */ 4245 4247 4246 4248 int regReturn = ++pParse->nMem; /* Register used with OP_Gosub */ 4247 4249 int regRowset = 0; /* Register for RowSet object */ 4248 4250 int regRowid = 0; /* Register holding rowid */ 4249 4251 int iLoopBody = sqlite3VdbeMakeLabel(v); /* Start of loop body */ 4250 4252 int iRetInit; /* Address of regReturn init */ 4251 4253 int untestedTerms = 0; /* Some terms not completely tested */ ................................................................................ 4256 4258 assert( pTerm->eOperator==WO_OR ); 4257 4259 assert( (pTerm->wtFlags & TERM_ORINFO)!=0 ); 4258 4260 pOrWc = &pTerm->u.pOrInfo->wc; 4259 4261 pFinal = &pOrWc->a[pOrWc->nTerm-1]; 4260 4262 pLevel->op = OP_Return; 4261 4263 pLevel->p1 = regReturn; 4262 4264 4263 - /* Set up a new SrcList ni pOrTab containing the table being scanned 4265 + /* Set up a new SrcList in pOrTab containing the table being scanned 4264 4266 ** by this loop in the a[0] slot and all notReady tables in a[1..] slots. 4265 4267 ** This becomes the SrcList in the recursive call to sqlite3WhereBegin(). 4266 4268 */ 4267 4269 if( pWInfo->nLevel>1 ){ 4268 4270 int nNotReady; /* The number of notReady tables */ 4269 4271 struct SrcList_item *origSrc; /* Original list of tables */ 4270 4272 nNotReady = pWInfo->nLevel - iLevel - 1; ................................................................................ 4303 4305 for(ii=0; ii<pOrWc->nTerm; ii++){ 4304 4306 WhereTerm *pOrTerm = &pOrWc->a[ii]; 4305 4307 if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){ 4306 4308 WhereInfo *pSubWInfo; /* Info for single OR-term scan */ 4307 4309 /* Loop through table entries that match term pOrTerm. */ 4308 4310 pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, 0, 4309 4311 WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE | 4310 - WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY); 4312 + WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur); 4313 + assert( pSubWInfo || pParse->nErr || pParse->db->mallocFailed ); 4311 4314 if( pSubWInfo ){ 4315 + WhereLevel *pLvl; 4312 4316 explainOneScan( 4313 4317 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0 4314 4318 ); 4315 4319 if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ 4316 4320 int iSet = ((ii==pOrWc->nTerm-1)?-1:ii); 4317 4321 int r; 4318 4322 r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, ................................................................................ 4324 4328 4325 4329 /* The pSubWInfo->untestedTerms flag means that this OR term 4326 4330 ** contained one or more AND term from a notReady table. The 4327 4331 ** terms from the notReady table could not be tested and will 4328 4332 ** need to be tested later. 4329 4333 */ 4330 4334 if( pSubWInfo->untestedTerms ) untestedTerms = 1; 4335 + 4336 + /* If all of the OR-connected terms are optimized using the same 4337 + ** index, and the index is opened using the same cursor number 4338 + ** by each call to sqlite3WhereBegin() made by this loop, it may 4339 + ** be possible to use that index as a covering index. 4340 + ** 4341 + ** If the call to sqlite3WhereBegin() above resulted in a scan that 4342 + ** uses an index, and this is either the first OR-connected term 4343 + ** processed or the index is the same as that used by all previous 4344 + ** terms, set pCov to the candidate covering index. Otherwise, set 4345 + ** pCov to NULL to indicate that no candidate covering index will 4346 + ** be available. 4347 + */ 4348 + pLvl = &pSubWInfo->a[0]; 4349 + if( (pLvl->plan.wsFlags & WHERE_INDEXED)!=0 4350 + && (pLvl->plan.wsFlags & WHERE_TEMP_INDEX)==0 4351 + && (ii==0 || pLvl->plan.u.pIdx==pCov) 4352 + ){ 4353 + assert( pLvl->iIdxCur==iCovCur ); 4354 + pCov = pLvl->plan.u.pIdx; 4355 + }else{ 4356 + pCov = 0; 4357 + } 4331 4358 4332 4359 /* Finish the loop through table entries that match term pOrTerm. */ 4333 4360 sqlite3WhereEnd(pSubWInfo); 4334 4361 } 4335 4362 } 4336 4363 } 4364 + pLevel->u.pCovidx = pCov; 4365 + pLevel->iIdxCur = iCovCur; 4337 4366 sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v)); 4338 4367 sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk); 4339 4368 sqlite3VdbeResolveLabel(v, iLoopBody); 4340 4369 4341 4370 if( pWInfo->nLevel>1 ) sqlite3StackFree(pParse->db, pOrTab); 4342 4371 if( !untestedTerms ) disableTerm(pLevel, pTerm); 4343 4372 }else ................................................................................ 4545 4574 */ 4546 4575 WhereInfo *sqlite3WhereBegin( 4547 4576 Parse *pParse, /* The parser context */ 4548 4577 SrcList *pTabList, /* A list of all tables to be scanned */ 4549 4578 Expr *pWhere, /* The WHERE clause */ 4550 4579 ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */ 4551 4580 ExprList *pDistinct, /* The select-list for DISTINCT queries - or NULL */ 4552 - u16 wctrlFlags /* One of the WHERE_* flags defined in sqliteInt.h */ 4581 + u16 wctrlFlags, /* One of the WHERE_* flags defined in sqliteInt.h */ 4582 + int iIdxCur /* If WHERE_ONETABLE_ONLY is set, index cursor number */ 4553 4583 ){ 4554 4584 int i; /* Loop counter */ 4555 4585 int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */ 4556 4586 int nTabList; /* Number of elements in pTabList */ 4557 4587 WhereInfo *pWInfo; /* Will become the return value of this function */ 4558 4588 Vdbe *v = pParse->pVdbe; /* The virtual database engine */ 4559 4589 Bitmask notReady; /* Cursors that are not yet positioned */ ................................................................................ 4862 4892 pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; 4863 4893 } 4864 4894 andFlags &= bestPlan.plan.wsFlags; 4865 4895 pLevel->plan = bestPlan.plan; 4866 4896 testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); 4867 4897 testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); 4868 4898 if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){ 4869 - pLevel->iIdxCur = pParse->nTab++; 4899 + if( (wctrlFlags & WHERE_ONETABLE_ONLY) 4900 + && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 4901 + ){ 4902 + pLevel->iIdxCur = iIdxCur; 4903 + }else{ 4904 + pLevel->iIdxCur = pParse->nTab++; 4905 + } 4870 4906 }else{ 4871 4907 pLevel->iIdxCur = -1; 4872 4908 } 4873 4909 notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor); 4874 4910 pLevel->iFrom = (u8)bestJ; 4875 4911 if( bestPlan.plan.nRow>=(double)1 ){ 4876 4912 pParse->nQueryLoop *= bestPlan.plan.nRow; ................................................................................ 5114 5150 */ 5115 5151 sqlite3VdbeResolveLabel(v, pWInfo->iBreak); 5116 5152 5117 5153 /* Close all of the cursors that were opened by sqlite3WhereBegin. 5118 5154 */ 5119 5155 assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc ); 5120 5156 for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){ 5157 + Index *pIdx = 0; 5121 5158 struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom]; 5122 5159 Table *pTab = pTabItem->pTab; 5123 5160 assert( pTab!=0 ); 5124 5161 if( (pTab->tabFlags & TF_Ephemeral)==0 5125 5162 && pTab->pSelect==0 5126 5163 && (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0 5127 5164 ){ ................................................................................ 5143 5180 ** 5144 5181 ** Calls to the code generator in between sqlite3WhereBegin and 5145 5182 ** sqlite3WhereEnd will have created code that references the table 5146 5183 ** directly. This loop scans all that code looking for opcodes 5147 5184 ** that reference the table and converts them into opcodes that 5148 5185 ** reference the index. 5149 5186 */ 5150 - if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 && !db->mallocFailed){ 5187 + if( pLevel->plan.wsFlags & WHERE_INDEXED ){ 5188 + pIdx = pLevel->plan.u.pIdx; 5189 + }else if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){ 5190 + pIdx = pLevel->u.pCovidx; 5191 + } 5192 + if( pIdx && !db->mallocFailed){ 5151 5193 int k, j, last; 5152 5194 VdbeOp *pOp; 5153 - Index *pIdx = pLevel->plan.u.pIdx; 5154 5195 5155 - assert( pIdx!=0 ); 5156 5196 pOp = sqlite3VdbeGetOp(v, pWInfo->iTop); 5157 5197 last = sqlite3VdbeCurrentAddr(v); 5158 5198 for(k=pWInfo->iTop; k<last; k++, pOp++){ 5159 5199 if( pOp->p1!=pLevel->iTabCur ) continue; 5160 5200 if( pOp->opcode==OP_Column ){ 5161 5201 for(j=0; j<pIdx->nColumn; j++){ 5162 5202 if( pOp->p2==pIdx->aiColumn[j] ){
Changes to test/fts3query.test.
112 112 DROP TABLE IF EXISTS t1; 113 113 CREATE TABLE t1(number INTEGER PRIMARY KEY, date); 114 114 CREATE INDEX i1 ON t1(date); 115 115 CREATE VIRTUAL TABLE ft USING fts3(title); 116 116 CREATE TABLE bt(title); 117 117 } 118 118 } {} 119 +if 0 { 119 120 do_test fts3query-4.2 { 120 121 eqp "SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date" 121 122 } {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE ft VIRTUAL TABLE INDEX 1:}} 122 123 do_test fts3query-4.3 { 123 124 eqp "SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date" 124 125 } {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE ft VIRTUAL TABLE INDEX 1:}} 125 126 do_test fts3query-4.4 { 126 127 eqp "SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date" 127 128 } {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE bt USING PRIMARY KEY}} 128 129 do_test fts3query-4.5 { 129 130 eqp "SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date" 130 131 } {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE bt USING PRIMARY KEY}} 131 - 132 +} 132 133 133 134 finish_test 134 -
Added test/whereD.test.
1 +# 2012 August 24 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. The 12 +# focus of this file is testing that an index may be used as a covering 13 +# index when there are OR expressions in the WHERE clause. 14 +# 15 + 16 + 17 +set testdir [file dirname $argv0] 18 +source $testdir/tester.tcl 19 +set ::testprefix whereD 20 + 21 +do_execsql_test 1.1 { 22 + CREATE TABLE t(i,j,k,m,n); 23 + CREATE INDEX ijk ON t(i,j,k); 24 + CREATE INDEX jmn ON t(j,m,n); 25 + 26 + INSERT INTO t VALUES(3, 3, 'three', 3, 'tres'); 27 + INSERT INTO t VALUES(2, 2, 'two', 2, 'dos'); 28 + INSERT INTO t VALUES(1, 1, 'one', 1, 'uno'); 29 + INSERT INTO t VALUES(4, 4, 'four', 4, 'cuatro'); 30 +} {} 31 + 32 +do_execsql_test 1.2 { 33 + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 34 +} {one two} 35 +do_execsql_test 1.3 { 36 + SELECT k FROM t WHERE (i=1 AND j=1) OR (+i=2 AND j=2); 37 +} {one two} 38 +do_execsql_test 1.4 { 39 + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 40 +} {uno dos} 41 +do_execsql_test 1.5 { 42 + SELECT k, n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2); 43 +} {one uno two dos} 44 +do_execsql_test 1.6 { 45 + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 46 +} {one two three} 47 +do_execsql_test 1.7 { 48 + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 49 +} {uno dos tres} 50 +do_execsql_test 1.8 { 51 + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2); 52 +} {one two} 53 +do_execsql_test 1.9 { 54 + SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 55 +} {one two three} 56 +do_execsql_test 1.10 { 57 + SELECT n FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3); 58 +} {uno dos tres} 59 +do_execsql_test 1.11 { 60 + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 61 +} {one two three} 62 +do_execsql_test 1.12 { 63 + SELECT n FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3); 64 +} {uno dos tres} 65 +do_execsql_test 1.13 { 66 + SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3); 67 +} {one two three} 68 +do_execsql_test 1.14 { 69 + SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3); 70 +} {one two three} 71 +do_execsql_test 1.15 { 72 + SELECT k FROM t WHERE (i=1 AND j=2) OR (i=2 AND j=1) OR (i=3 AND j=4); 73 +} {} 74 +do_execsql_test 1.16 { 75 + SELECT k FROM t WHERE (i=1 AND (j=1 or j=2)) OR (i=3 AND j=3); 76 +} {one three} 77 + 78 +do_execsql_test 2.0 { 79 + CREATE TABLE t1(a,b,c,d); 80 + CREATE INDEX t1b ON t1(b); 81 + CREATE INDEX t1c ON t1(c); 82 + CREATE INDEX t1d ON t1(d); 83 + CREATE TABLE t2(x,y); 84 + CREATE INDEX t2y ON t2(y); 85 + 86 + INSERT INTO t1 VALUES(1,2,3,4); 87 + INSERT INTO t1 VALUES(5,6,7,8); 88 + INSERT INTO t2 VALUES(1,2); 89 + INSERT INTO t2 VALUES(2,7); 90 + INSERT INTO t2 VALUES(3,4); 91 +} {} 92 +do_execsql_test 2.1 { 93 + SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x; 94 +} {1 3} 95 +do_execsql_test 2.2 { 96 + SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x; 97 +} {1 3} 98 + 99 + 100 +# Similar to [do_execsql_test], except that two elements are appended 101 +# to the result - the string "search" and the number of times test variable 102 +# sqlite3_search_count is incremented by running the supplied SQL. e.g. 103 +# 104 +# do_searchcount_test 1.0 { SELECT * FROM t1 } {x y search 2} 105 +# 106 +proc do_searchcount_test {tn sql res} { 107 + uplevel [subst -nocommands { 108 + do_test $tn { 109 + set ::sqlite_search_count 0 110 + concat [db eval {$sql}] search [set ::sqlite_search_count] 111 + } [list $res] 112 + }] 113 +} 114 + 115 +do_execsql_test 3.0 { 116 + CREATE TABLE t3(a, b, c); 117 + CREATE UNIQUE INDEX i3 ON t3(a, b); 118 + INSERT INTO t3 VALUES(1, 'one', 'i'); 119 + INSERT INTO t3 VALUES(3, 'three', 'iii'); 120 + INSERT INTO t3 VALUES(6, 'six', 'vi'); 121 + INSERT INTO t3 VALUES(2, 'two', 'ii'); 122 + INSERT INTO t3 VALUES(4, 'four', 'iv'); 123 + INSERT INTO t3 VALUES(5, 'five', 'v'); 124 + 125 + CREATE TABLE t4(x PRIMARY KEY, y); 126 + INSERT INTO t4 VALUES('a', 'one'); 127 + INSERT INTO t4 VALUES('b', 'two'); 128 +} {} 129 + 130 +do_searchcount_test 3.1 { 131 + SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 132 +} {1 one 2 two search 2} 133 + 134 +do_searchcount_test 3.2 { 135 + SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR (a=2 AND b='two') 136 +} {1 i 2 ii search 4} 137 + 138 +do_searchcount_test 3.4.1 { 139 + SELECT y FROM t4 WHERE x='a' 140 +} {one search 2} 141 +do_searchcount_test 3.4.2 { 142 + SELECT a, b FROM t3 WHERE 143 + (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 144 + OR (a=2 AND b='two') 145 +} {1 one 2 two search 4} 146 +do_searchcount_test 3.4.3 { 147 + SELECT a, b FROM t3 WHERE 148 + (a=2 AND b='two') 149 + OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 150 +} {2 two 1 one search 4} 151 +do_searchcount_test 3.4.4 { 152 + SELECT a, b FROM t3 WHERE 153 + (a=2 AND b=(SELECT y FROM t4 WHERE x='b')) 154 + OR (a=1 AND b=(SELECT y FROM t4 WHERE x='a')) 155 +} {2 two 1 one search 6} 156 + 157 +do_searchcount_test 3.5.1 { 158 + SELECT a, b FROM t3 WHERE (a=1 AND b='one') OR rowid=4 159 +} {1 one 2 two search 2} 160 +do_searchcount_test 3.5.2 { 161 + SELECT a, c FROM t3 WHERE (a=1 AND b='one') OR rowid=4 162 +} {1 i 2 ii search 2} 163 + 164 +finish_test