/ Check-in [62678be3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment: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 | descendants | both | trunk
Files: files | file ages | folders
SHA1:62678be3df35cdcb09172ba8c860f7b73517f1ea
User & Date: drh 2012-08-24 23:56:19
References
2012-10-04
15:26 New ticket [d02e1406] LEFT JOIN with an OR in the ON clause causes segfault. artifact: 28fef2d3 user: drh
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
Context
2012-08-25
02:11
Fix a harmless compiler warning. check-in: 929b5184 user: drh tags: trunk
01:38
Merge all the latest trunk changes into the apple-osx branch. check-in: 6723add4 user: drh tags: apple-osx
01:21
Merge the latest trunk changes into the sessions branch. check-in: aa62d688 user: drh tags: sessions
2012-08-24
23:56
When the same index is used for all OR-terms in a WHERE clause, then try to use that index as a covering index. check-in: 62678be3 user: drh tags: trunk
23:24
Move field WhereLevel.pCovidx inside the union to WhereLevel.u.pCovidx. Closed-Leaf check-in: b722143d user: drh tags: multi-or-covering-index
01:07
Merge the nested aggregate query enhancements into trunk. check-in: d4cd6017 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   367    367       int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
   368    368       int regRowid;                   /* Actual register containing rowids */
   369    369   
   370    370       /* Collect rowids of every row to be deleted.
   371    371       */
   372    372       sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
   373    373       pWInfo = sqlite3WhereBegin(
   374         -        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK
          374  +        pParse, pTabList, pWhere, 0, 0, WHERE_DUPLICATES_OK, 0
   375    375       );
   376    376       if( pWInfo==0 ) goto delete_from_cleanup;
   377    377       regRowid = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, iRowid, 0);
   378    378       sqlite3VdbeAddOp2(v, OP_RowSetAdd, iRowSet, regRowid);
   379    379       if( db->flags & SQLITE_CountRows ){
   380    380         sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
   381    381       }

Changes to src/fkey.c.

   556    556     sNameContext.pParse = pParse;
   557    557     sqlite3ResolveExprNames(&sNameContext, pWhere);
   558    558   
   559    559     /* Create VDBE to loop through the entries in pSrc that match the WHERE
   560    560     ** clause. If the constraint is not deferred, throw an exception for
   561    561     ** each row found. Otherwise, for deferred constraints, increment the
   562    562     ** deferred constraint counter by nIncr for each row selected.  */
   563         -  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0);
          563  +  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
   564    564     if( nIncr>0 && pFKey->isDeferred==0 ){
   565    565       sqlite3ParseToplevel(pParse)->mayAbort = 1;
   566    566     }
   567    567     sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
   568    568     if( pWInfo ){
   569    569       sqlite3WhereEnd(pWInfo);
   570    570     }

Changes to src/select.c.

  4020   4020     }
  4021   4021   
  4022   4022     /* Aggregate and non-aggregate queries are handled differently */
  4023   4023     if( !isAgg && pGroupBy==0 ){
  4024   4024       ExprList *pDist = (isDistinct ? p->pEList : 0);
  4025   4025   
  4026   4026       /* Begin the database scan. */
  4027         -    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0);
         4027  +    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pOrderBy, pDist, 0,0);
  4028   4028       if( pWInfo==0 ) goto select_end;
  4029   4029       if( pWInfo->nRowOut < p->nSelectRow ) p->nSelectRow = pWInfo->nRowOut;
  4030   4030   
  4031   4031       /* If sorting index that was created by a prior OP_OpenEphemeral 
  4032   4032       ** instruction ended up not being needed, then change the OP_OpenEphemeral
  4033   4033       ** into an OP_Noop.
  4034   4034       */
................................................................................
  4193   4193   
  4194   4194         /* Begin a loop that will extract all source rows in GROUP BY order.
  4195   4195         ** This might involve two separate loops with an OP_Sort in between, or
  4196   4196         ** it might be a single loop that uses an index to extract information
  4197   4197         ** in the right order to begin with.
  4198   4198         */
  4199   4199         sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset);
  4200         -      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0);
         4200  +      pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pGroupBy, 0, 0, 0);
  4201   4201         if( pWInfo==0 ) goto select_end;
  4202   4202         if( pGroupBy==0 ){
  4203   4203           /* The optimizer is able to deliver rows in group by order so
  4204   4204           ** we do not have to sort.  The OP_OpenEphemeral table will be
  4205   4205           ** cancelled later because we still need to use the pKeyInfo
  4206   4206           */
  4207   4207           pGroupBy = p->pGroupBy;
................................................................................
  4462   4462           }
  4463   4463     
  4464   4464           /* This case runs if the aggregate has no GROUP BY clause.  The
  4465   4465           ** processing is much simpler since there is only a single row
  4466   4466           ** of output.
  4467   4467           */
  4468   4468           resetAccumulator(pParse, &sAggInfo);
  4469         -        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax, 0, flag);
         4469  +        pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, &pMinMax,0,flag,0);
  4470   4470           if( pWInfo==0 ){
  4471   4471             sqlite3ExprListDelete(db, pDel);
  4472   4472             goto select_end;
  4473   4473           }
  4474   4474           updateAccumulator(pParse, &sAggInfo);
  4475   4475           if( !pMinMax && flag ){
  4476   4476             sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak);

Changes to src/sqliteInt.h.

  1943   1943       struct {
  1944   1944         int nIn;              /* Number of entries in aInLoop[] */
  1945   1945         struct InLoop {
  1946   1946           int iCur;              /* The VDBE cursor used by this IN operator */
  1947   1947           int addrInTop;         /* Top of the IN loop */
  1948   1948         } *aInLoop;           /* Information about each nested IN operator */
  1949   1949       } in;                 /* Used when plan.wsFlags&WHERE_IN_ABLE */
         1950  +    Index *pCovidx;       /* Possible covering index for WHERE_MULTI_OR */
  1950   1951     } u;
  1951   1952   
  1952   1953     /* The following field is really not part of the current level.  But
  1953   1954     ** we need a place to cache virtual table index information for each
  1954   1955     ** virtual table in the FROM clause and the WhereLevel structure is
  1955   1956     ** a convenient place since there is one WhereLevel for each FROM clause
  1956   1957     ** element.
................................................................................
  2802   2803   int sqlite3IsReadOnly(Parse*, Table*, int);
  2803   2804   void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int);
  2804   2805   #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY)
  2805   2806   Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
  2806   2807   #endif
  2807   2808   void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
  2808   2809   void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
  2809         -WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**,ExprList*,u16);
         2810  +WhereInfo *sqlite3WhereBegin(
         2811  +    Parse*,SrcList*,Expr*,ExprList**,ExprList*,u16,int);
  2810   2812   void sqlite3WhereEnd(WhereInfo*);
  2811   2813   int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int, u8);
  2812   2814   void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
  2813   2815   void sqlite3ExprCodeMove(Parse*, int, int, int);
  2814   2816   void sqlite3ExprCodeCopy(Parse*, int, int, int);
  2815   2817   void sqlite3ExprCacheStore(Parse*, int, int, int);
  2816   2818   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     sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, 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.

  3618   3618     /* Evaluate the equality constraints
  3619   3619     */
  3620   3620     assert( pIdx->nColumn>=nEq );
  3621   3621     for(j=0; j<nEq; j++){
  3622   3622       int r1;
  3623   3623       int k = pIdx->aiColumn[j];
  3624   3624       pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx);
  3625         -    if( NEVER(pTerm==0) ) break;
         3625  +    if( pTerm==0 ) break;
  3626   3626       /* The following true for indices with redundant columns. 
  3627   3627       ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
  3628   3628       testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
  3629   3629       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  3630   3630       r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
  3631   3631       if( r1!=regBase+j ){
  3632   3632         if( nReg==1 ){
................................................................................
  4293   4293       **          Return     2                # Jump back to the Gosub
  4294   4294       **
  4295   4295       **       B: <after the loop>
  4296   4296       **
  4297   4297       */
  4298   4298       WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
  4299   4299       SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
         4300  +    Index *pCov = 0;             /* Potential covering index (or NULL) */
         4301  +    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */
  4300   4302   
  4301   4303       int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  4302   4304       int regRowset = 0;                        /* Register for RowSet object */
  4303   4305       int regRowid = 0;                         /* Register holding rowid */
  4304   4306       int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  4305   4307       int iRetInit;                             /* Address of regReturn init */
  4306   4308       int untestedTerms = 0;             /* Some terms not completely tested */
................................................................................
  4311   4313       assert( pTerm!=0 );
  4312   4314       assert( pTerm->eOperator==WO_OR );
  4313   4315       assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
  4314   4316       pOrWc = &pTerm->u.pOrInfo->wc;
  4315   4317       pLevel->op = OP_Return;
  4316   4318       pLevel->p1 = regReturn;
  4317   4319   
  4318         -    /* Set up a new SrcList ni pOrTab containing the table being scanned
         4320  +    /* Set up a new SrcList in pOrTab containing the table being scanned
  4319   4321       ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
  4320   4322       ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
  4321   4323       */
  4322   4324       if( pWInfo->nLevel>1 ){
  4323   4325         int nNotReady;                 /* The number of notReady tables */
  4324   4326         struct SrcList_item *origSrc;     /* Original list of tables */
  4325   4327         nNotReady = pWInfo->nLevel - iLevel - 1;
................................................................................
  4388   4390           if( pAndExpr ){
  4389   4391             pAndExpr->pLeft = pOrExpr;
  4390   4392             pOrExpr = pAndExpr;
  4391   4393           }
  4392   4394           /* Loop through table entries that match term pOrTerm. */
  4393   4395           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  4394   4396                           WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY |
  4395         -                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
         4397  +                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY, iCovCur);
         4398  +        assert( pSubWInfo || pParse->nErr || pParse->db->mallocFailed );
  4396   4399           if( pSubWInfo ){
         4400  +          WhereLevel *pLvl;
  4397   4401             explainOneScan(
  4398   4402                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  4399   4403             );
  4400   4404             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  4401   4405               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  4402   4406               int r;
  4403   4407               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
................................................................................
  4409   4413   
  4410   4414             /* The pSubWInfo->untestedTerms flag means that this OR term
  4411   4415             ** contained one or more AND term from a notReady table.  The
  4412   4416             ** terms from the notReady table could not be tested and will
  4413   4417             ** need to be tested later.
  4414   4418             */
  4415   4419             if( pSubWInfo->untestedTerms ) untestedTerms = 1;
         4420  +
         4421  +          /* If all of the OR-connected terms are optimized using the same
         4422  +          ** index, and the index is opened using the same cursor number
         4423  +          ** by each call to sqlite3WhereBegin() made by this loop, it may
         4424  +          ** be possible to use that index as a covering index.
         4425  +          **
         4426  +          ** If the call to sqlite3WhereBegin() above resulted in a scan that
         4427  +          ** uses an index, and this is either the first OR-connected term
         4428  +          ** processed or the index is the same as that used by all previous
         4429  +          ** terms, set pCov to the candidate covering index. Otherwise, set 
         4430  +          ** pCov to NULL to indicate that no candidate covering index will 
         4431  +          ** be available.
         4432  +          */
         4433  +          pLvl = &pSubWInfo->a[0];
         4434  +          if( (pLvl->plan.wsFlags & WHERE_INDEXED)!=0
         4435  +           && (pLvl->plan.wsFlags & WHERE_TEMP_INDEX)==0
         4436  +           && (ii==0 || pLvl->plan.u.pIdx==pCov)
         4437  +          ){
         4438  +            assert( pLvl->iIdxCur==iCovCur );
         4439  +            pCov = pLvl->plan.u.pIdx;
         4440  +          }else{
         4441  +            pCov = 0;
         4442  +          }
  4416   4443   
  4417   4444             /* Finish the loop through table entries that match term pOrTerm. */
  4418   4445             sqlite3WhereEnd(pSubWInfo);
  4419   4446           }
  4420   4447         }
  4421   4448       }
         4449  +    pLevel->u.pCovidx = pCov;
         4450  +    pLevel->iIdxCur = iCovCur;
  4422   4451       if( pAndExpr ){
  4423   4452         pAndExpr->pLeft = 0;
  4424   4453         sqlite3ExprDelete(pParse->db, pAndExpr);
  4425   4454       }
  4426   4455       sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
  4427   4456       sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
  4428   4457       sqlite3VdbeResolveLabel(v, iLoopBody);
................................................................................
  4632   4661   */
  4633   4662   WhereInfo *sqlite3WhereBegin(
  4634   4663     Parse *pParse,        /* The parser context */
  4635   4664     SrcList *pTabList,    /* A list of all tables to be scanned */
  4636   4665     Expr *pWhere,         /* The WHERE clause */
  4637   4666     ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
  4638   4667     ExprList *pDistinct,  /* The select-list for DISTINCT queries - or NULL */
  4639         -  u16 wctrlFlags        /* One of the WHERE_* flags defined in sqliteInt.h */
         4668  +  u16 wctrlFlags,       /* One of the WHERE_* flags defined in sqliteInt.h */
         4669  +  int iIdxCur           /* If WHERE_ONETABLE_ONLY is set, index cursor number */
  4640   4670   ){
  4641   4671     int i;                     /* Loop counter */
  4642   4672     int nByteWInfo;            /* Num. bytes allocated for WhereInfo struct */
  4643   4673     int nTabList;              /* Number of elements in pTabList */
  4644   4674     WhereInfo *pWInfo;         /* Will become the return value of this function */
  4645   4675     Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  4646   4676     Bitmask notReady;          /* Cursors that are not yet positioned */
................................................................................
  4952   4982         pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
  4953   4983       }
  4954   4984       andFlags &= bestPlan.plan.wsFlags;
  4955   4985       pLevel->plan = bestPlan.plan;
  4956   4986       testcase( bestPlan.plan.wsFlags & WHERE_INDEXED );
  4957   4987       testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX );
  4958   4988       if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){
  4959         -      pLevel->iIdxCur = pParse->nTab++;
         4989  +      if( (wctrlFlags & WHERE_ONETABLE_ONLY) 
         4990  +       && (bestPlan.plan.wsFlags & WHERE_TEMP_INDEX)==0 
         4991  +      ){
         4992  +        pLevel->iIdxCur = iIdxCur;
         4993  +      }else{
         4994  +        pLevel->iIdxCur = pParse->nTab++;
         4995  +      }
  4960   4996       }else{
  4961   4997         pLevel->iIdxCur = -1;
  4962   4998       }
  4963   4999       notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor);
  4964   5000       pLevel->iFrom = (u8)bestJ;
  4965   5001       if( bestPlan.plan.nRow>=(double)1 ){
  4966   5002         pParse->nQueryLoop *= bestPlan.plan.nRow;
................................................................................
  5204   5240     */
  5205   5241     sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
  5206   5242   
  5207   5243     /* Close all of the cursors that were opened by sqlite3WhereBegin.
  5208   5244     */
  5209   5245     assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc );
  5210   5246     for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
         5247  +    Index *pIdx = 0;
  5211   5248       struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
  5212   5249       Table *pTab = pTabItem->pTab;
  5213   5250       assert( pTab!=0 );
  5214   5251       if( (pTab->tabFlags & TF_Ephemeral)==0
  5215   5252        && pTab->pSelect==0
  5216   5253        && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
  5217   5254       ){
................................................................................
  5233   5270       ** 
  5234   5271       ** Calls to the code generator in between sqlite3WhereBegin and
  5235   5272       ** sqlite3WhereEnd will have created code that references the table
  5236   5273       ** directly.  This loop scans all that code looking for opcodes
  5237   5274       ** that reference the table and converts them into opcodes that
  5238   5275       ** reference the index.
  5239   5276       */
  5240         -    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 && !db->mallocFailed){
         5277  +    if( pLevel->plan.wsFlags & WHERE_INDEXED ){
         5278  +      pIdx = pLevel->plan.u.pIdx;
         5279  +    }else if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
         5280  +      pIdx = pLevel->u.pCovidx;
         5281  +    }
         5282  +    if( pIdx && !db->mallocFailed){
  5241   5283         int k, j, last;
  5242   5284         VdbeOp *pOp;
  5243         -      Index *pIdx = pLevel->plan.u.pIdx;
  5244   5285   
  5245         -      assert( pIdx!=0 );
  5246   5286         pOp = sqlite3VdbeGetOp(v, pWInfo->iTop);
  5247   5287         last = sqlite3VdbeCurrentAddr(v);
  5248   5288         for(k=pWInfo->iTop; k<last; k++, pOp++){
  5249   5289           if( pOp->p1!=pLevel->iTabCur ) continue;
  5250   5290           if( pOp->opcode==OP_Column ){
  5251   5291             for(j=0; j<pIdx->nColumn; j++){
  5252   5292               if( pOp->p2==pIdx->aiColumn[j] ){

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