/ Check-in [865dfcba]
Login

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: 865dfcbaa59431422b5980c66c8e29e80c4ed7c9
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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