/ Check-in [b23ae131]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Improved handling of OR terms in the WHERE clause with multi-column indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b23ae131874bc5c621f0f5ea8d76fce1ec089cc2
User & Date: drh 2011-10-07 17:52:40
References
2013-05-09
13:38 New ticket [f2369304] Incorrect results when OR is used in the ON clause of a LEFT JOIN. artifact: 979fa9ab user: drh
2012-03-09
16:58 New ticket [b7c8682c] Incorrect result from LEFT JOIN with OR in the WHERE clause. artifact: 7aedf2b6 user: drh
Context
2011-10-07
18:24
Make sure sqlite3_data_count() behaves as documented, even for EXPLAIN QUERY PLAN queries. check-in: d4f95b3b user: drh tags: trunk
17:52
Improved handling of OR terms in the WHERE clause with multi-column indexes. check-in: b23ae131 user: drh tags: trunk
17:45
Add testcase() macros to ensure good test coverage. Closed-Leaf check-in: 5c132592 user: drh tags: or-opt
16:57
Add the SQLITE_FCNTL_OVERWRITE file-control. Used by SQLite to indicate to the OS layer that the current transaction will overwrite the entire file. check-in: 1da87fcd user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  1975   1975   ** and the WhereInfo.wctrlFlags member.
  1976   1976   */
  1977   1977   #define WHERE_ORDERBY_NORMAL   0x0000 /* No-op */
  1978   1978   #define WHERE_ORDERBY_MIN      0x0001 /* ORDER BY processing for min() func */
  1979   1979   #define WHERE_ORDERBY_MAX      0x0002 /* ORDER BY processing for max() func */
  1980   1980   #define WHERE_ONEPASS_DESIRED  0x0004 /* Want to do one-pass UPDATE/DELETE */
  1981   1981   #define WHERE_DUPLICATES_OK    0x0008 /* Ok to return a row more than once */
  1982         -#define WHERE_OMIT_OPEN        0x0010 /* Table cursors are already open */
  1983         -#define WHERE_OMIT_CLOSE       0x0020 /* Omit close of table & index cursors */
  1984         -#define WHERE_FORCE_TABLE      0x0040 /* Do not use an index-only search */
  1985         -#define WHERE_ONETABLE_ONLY    0x0080 /* Only code the 1st table in pTabList */
         1982  +#define WHERE_OMIT_OPEN_CLOSE  0x0010 /* Table cursors are already open */
         1983  +#define WHERE_FORCE_TABLE      0x0020 /* Do not use an index-only search */
         1984  +#define WHERE_ONETABLE_ONLY    0x0040 /* Only code the 1st table in pTabList */
         1985  +#define WHERE_AND_ONLY         0x0080 /* Don't use indices for OR terms */
  1986   1986   
  1987   1987   /*
  1988   1988   ** The WHERE clause processing routine has two halves.  The
  1989   1989   ** first part does the start of the WHERE loop and the second
  1990   1990   ** half does the tail of the WHERE loop.  An instance of
  1991   1991   ** this structure is returned by the first half and passed
  1992   1992   ** into the second half to give some continuity.

Changes to src/where.c.

   123    123   #else
   124    124   #  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
   125    125   #endif
   126    126   
   127    127   /*
   128    128   ** An instance of the following structure holds all information about a
   129    129   ** WHERE clause.  Mostly this is a container for one or more WhereTerms.
          130  +**
          131  +** Explanation of pOuter:  For a WHERE clause of the form
          132  +**
          133  +**           a AND ((b AND c) OR (d AND e)) AND f
          134  +**
          135  +** There are separate WhereClause objects for the whole clause and for
          136  +** the subclauses "(b AND c)" and "(d AND e)".  The pOuter field of the
          137  +** subclauses points to the WhereClause object for the whole clause.
   130    138   */
   131    139   struct WhereClause {
   132    140     Parse *pParse;           /* The parser context */
   133    141     WhereMaskSet *pMaskSet;  /* Mapping of table cursor numbers to bitmasks */
   134    142     Bitmask vmask;           /* Bitmask identifying virtual table cursors */
          143  +  WhereClause *pOuter;     /* Outer conjunction */
   135    144     u8 op;                   /* Split operator.  TK_AND or TK_OR */
          145  +  u16 wctrlFlags;          /* Might include WHERE_AND_ONLY */
   136    146     int nTerm;               /* Number of terms */
   137    147     int nSlot;               /* Number of entries in a[] */
   138    148     WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
   139    149   #if defined(SQLITE_SMALL_STACK)
   140    150     WhereTerm aStatic[1];    /* Initial static space for a[] */
   141    151   #else
   142    152     WhereTerm aStatic[8];    /* Initial static space for a[] */
................................................................................
   257    267   
   258    268   /*
   259    269   ** Initialize a preallocated WhereClause structure.
   260    270   */
   261    271   static void whereClauseInit(
   262    272     WhereClause *pWC,        /* The WhereClause to be initialized */
   263    273     Parse *pParse,           /* The parsing context */
   264         -  WhereMaskSet *pMaskSet   /* Mapping from table cursor numbers to bitmasks */
          274  +  WhereMaskSet *pMaskSet,  /* Mapping from table cursor numbers to bitmasks */
          275  +  u16 wctrlFlags           /* Might include WHERE_AND_ONLY */
   265    276   ){
   266    277     pWC->pParse = pParse;
   267    278     pWC->pMaskSet = pMaskSet;
          279  +  pWC->pOuter = 0;
   268    280     pWC->nTerm = 0;
   269    281     pWC->nSlot = ArraySize(pWC->aStatic);
   270    282     pWC->a = pWC->aStatic;
   271    283     pWC->vmask = 0;
          284  +  pWC->wctrlFlags = wctrlFlags;
   272    285   }
   273    286   
   274    287   /* Forward reference */
   275    288   static void whereClauseClear(WhereClause*);
   276    289   
   277    290   /*
   278    291   ** Deallocate all memory associated with a WhereOrInfo object.
................................................................................
   580    593     u32 op,               /* Mask of WO_xx values describing operator */
   581    594     Index *pIdx           /* Must be compatible with this index, if not NULL */
   582    595   ){
   583    596     WhereTerm *pTerm;
   584    597     int k;
   585    598     assert( iCur>=0 );
   586    599     op &= WO_ALL;
   587         -  for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
   588         -    if( pTerm->leftCursor==iCur
   589         -       && (pTerm->prereqRight & notReady)==0
   590         -       && pTerm->u.leftColumn==iColumn
   591         -       && (pTerm->eOperator & op)!=0
   592         -    ){
   593         -      if( pIdx && pTerm->eOperator!=WO_ISNULL ){
   594         -        Expr *pX = pTerm->pExpr;
   595         -        CollSeq *pColl;
   596         -        char idxaff;
   597         -        int j;
   598         -        Parse *pParse = pWC->pParse;
   599         -
   600         -        idxaff = pIdx->pTable->aCol[iColumn].affinity;
   601         -        if( !sqlite3IndexAffinityOk(pX, idxaff) ) continue;
   602         -
   603         -        /* Figure out the collation sequence required from an index for
   604         -        ** it to be useful for optimising expression pX. Store this
   605         -        ** value in variable pColl.
   606         -        */
   607         -        assert(pX->pLeft);
   608         -        pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
   609         -        assert(pColl || pParse->nErr);
   610         -
   611         -        for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
   612         -          if( NEVER(j>=pIdx->nColumn) ) return 0;
   613         -        }
   614         -        if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
   615         -      }
   616         -      return pTerm;
          600  +  for(; pWC; pWC=pWC->pOuter){
          601  +    for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
          602  +      if( pTerm->leftCursor==iCur
          603  +         && (pTerm->prereqRight & notReady)==0
          604  +         && pTerm->u.leftColumn==iColumn
          605  +         && (pTerm->eOperator & op)!=0
          606  +      ){
          607  +        if( pIdx && pTerm->eOperator!=WO_ISNULL ){
          608  +          Expr *pX = pTerm->pExpr;
          609  +          CollSeq *pColl;
          610  +          char idxaff;
          611  +          int j;
          612  +          Parse *pParse = pWC->pParse;
          613  +  
          614  +          idxaff = pIdx->pTable->aCol[iColumn].affinity;
          615  +          if( !sqlite3IndexAffinityOk(pX, idxaff) ) continue;
          616  +  
          617  +          /* Figure out the collation sequence required from an index for
          618  +          ** it to be useful for optimising expression pX. Store this
          619  +          ** value in variable pColl.
          620  +          */
          621  +          assert(pX->pLeft);
          622  +          pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
          623  +          assert(pColl || pParse->nErr);
          624  +  
          625  +          for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
          626  +            if( NEVER(j>=pIdx->nColumn) ) return 0;
          627  +          }
          628  +          if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
          629  +        }
          630  +        return pTerm;
          631  +      }
   617    632       }
   618    633     }
   619    634     return 0;
   620    635   }
   621    636   
   622    637   /* Forward reference */
   623    638   static void exprAnalyze(SrcList*, WhereClause*, int);
................................................................................
   873    888     */
   874    889     assert( (pTerm->wtFlags & (TERM_DYNAMIC|TERM_ORINFO|TERM_ANDINFO))==0 );
   875    890     assert( pExpr->op==TK_OR );
   876    891     pTerm->u.pOrInfo = pOrInfo = sqlite3DbMallocZero(db, sizeof(*pOrInfo));
   877    892     if( pOrInfo==0 ) return;
   878    893     pTerm->wtFlags |= TERM_ORINFO;
   879    894     pOrWc = &pOrInfo->wc;
   880         -  whereClauseInit(pOrWc, pWC->pParse, pMaskSet);
          895  +  whereClauseInit(pOrWc, pWC->pParse, pMaskSet, pWC->wctrlFlags);
   881    896     whereSplit(pOrWc, pExpr, TK_OR);
   882    897     exprAnalyzeAll(pSrc, pOrWc);
   883    898     if( db->mallocFailed ) return;
   884    899     assert( pOrWc->nTerm>=2 );
   885    900   
   886    901     /*
   887    902     ** Compute the set of tables that might satisfy cases 1 or 2.
................................................................................
   900    915           WhereTerm *pAndTerm;
   901    916           int j;
   902    917           Bitmask b = 0;
   903    918           pOrTerm->u.pAndInfo = pAndInfo;
   904    919           pOrTerm->wtFlags |= TERM_ANDINFO;
   905    920           pOrTerm->eOperator = WO_AND;
   906    921           pAndWC = &pAndInfo->wc;
   907         -        whereClauseInit(pAndWC, pWC->pParse, pMaskSet);
          922  +        whereClauseInit(pAndWC, pWC->pParse, pMaskSet, pWC->wctrlFlags);
   908    923           whereSplit(pAndWC, pOrTerm->pExpr, TK_AND);
   909    924           exprAnalyzeAll(pSrc, pAndWC);
          925  +        pAndWC->pOuter = pWC;
   910    926           testcase( db->mallocFailed );
   911    927           if( !db->mallocFailed ){
   912    928             for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
   913    929               assert( pAndTerm->pExpr );
   914    930               if( allowedOp(pAndTerm->pExpr->op) ){
   915    931                 b |= getMask(pMaskSet, pAndTerm->leftCursor);
   916    932               }
................................................................................
  1797   1813   ){
  1798   1814   #ifndef SQLITE_OMIT_OR_OPTIMIZATION
  1799   1815     const int iCur = pSrc->iCursor;   /* The cursor of the table to be accessed */
  1800   1816     const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur);  /* Bitmask for pSrc */
  1801   1817     WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm];        /* End of pWC->a[] */
  1802   1818     WhereTerm *pTerm;                 /* A single term of the WHERE clause */
  1803   1819   
  1804         -  /* No OR-clause optimization allowed if the INDEXED BY or NOT INDEXED clauses
  1805         -  ** are used */
         1820  +  /* The OR-clause optimization is disallowed if the INDEXED BY or
         1821  +  ** NOT INDEXED clauses are used or if the WHERE_AND_ONLY bit is set. */
  1806   1822     if( pSrc->notIndexed || pSrc->pIndex!=0 ){
  1807   1823       return;
         1824  +  }
         1825  +  if( pWC->wctrlFlags & WHERE_AND_ONLY ){
         1826  +    return;
  1808   1827     }
  1809   1828   
  1810   1829     /* Search the WHERE clause terms for a usable WO_OR term. */
  1811   1830     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1812   1831       if( pTerm->eOperator==WO_OR 
  1813   1832        && ((pTerm->prereqAll & ~maskSrc) & notReady)==0
  1814   1833        && (pTerm->u.pOrInfo->indexable & maskSrc)!=0 
................................................................................
  1829   1848           if( pOrTerm->eOperator==WO_AND ){
  1830   1849             WhereClause *pAndWC = &pOrTerm->u.pAndInfo->wc;
  1831   1850             bestIndex(pParse, pAndWC, pSrc, notReady, notValid, 0, &sTermCost);
  1832   1851           }else if( pOrTerm->leftCursor==iCur ){
  1833   1852             WhereClause tempWC;
  1834   1853             tempWC.pParse = pWC->pParse;
  1835   1854             tempWC.pMaskSet = pWC->pMaskSet;
         1855  +          tempWC.pOuter = pWC;
  1836   1856             tempWC.op = TK_AND;
  1837   1857             tempWC.a = pOrTerm;
  1838   1858             tempWC.nTerm = 1;
  1839   1859             bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost);
  1840   1860           }else{
  1841   1861             continue;
  1842   1862           }
................................................................................
  3008   3028   
  3009   3029       /* Determine the values of nEq and nInMul */
  3010   3030       for(nEq=0; nEq<pProbe->nColumn; nEq++){
  3011   3031         int j = pProbe->aiColumn[nEq];
  3012   3032         pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
  3013   3033         if( pTerm==0 ) break;
  3014   3034         wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);
         3035  +      testcase( pTerm->pWC!=pWC );
  3015   3036         if( pTerm->eOperator & WO_IN ){
  3016   3037           Expr *pExpr = pTerm->pExpr;
  3017   3038           wsFlags |= WHERE_COLUMN_IN;
  3018   3039           if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  3019   3040             /* "x IN (SELECT ...)":  Assume the SELECT returns 25 rows */
  3020   3041             nInMul *= 25;
  3021   3042             bInEst = 1;
................................................................................
  3039   3060           WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx);
  3040   3061           WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx);
  3041   3062           whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &rangeDiv);
  3042   3063           if( pTop ){
  3043   3064             nBound = 1;
  3044   3065             wsFlags |= WHERE_TOP_LIMIT;
  3045   3066             used |= pTop->prereqRight;
         3067  +          testcase( pTop->pWC!=pWC );
  3046   3068           }
  3047   3069           if( pBtm ){
  3048   3070             nBound++;
  3049   3071             wsFlags |= WHERE_BTM_LIMIT;
  3050   3072             used |= pBtm->prereqRight;
         3073  +          testcase( pBtm->pWC!=pWC );
  3051   3074           }
  3052   3075           wsFlags |= (WHERE_COLUMN_RANGE|WHERE_ROWID_RANGE);
  3053   3076         }
  3054   3077       }else if( pProbe->onError!=OE_None ){
  3055   3078         testcase( wsFlags & WHERE_COLUMN_IN );
  3056   3079         testcase( wsFlags & WHERE_COLUMN_NULL );
  3057   3080         if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){
................................................................................
  3765   3788   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3766   3789   ** implementation described by pWInfo.
  3767   3790   */
  3768   3791   static Bitmask codeOneLoopStart(
  3769   3792     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
  3770   3793     int iLevel,          /* Which level of pWInfo->a[] should be coded */
  3771   3794     u16 wctrlFlags,      /* One of the WHERE_* flags defined in sqliteInt.h */
  3772         -  Bitmask notReady     /* Which tables are currently available */
         3795  +  Bitmask notReady,    /* Which tables are currently available */
         3796  +  Expr *pWhere         /* Complete WHERE clause */
  3773   3797   ){
  3774   3798     int j, k;            /* Loop counters */
  3775   3799     int iCur;            /* The VDBE cursor for the table */
  3776   3800     int addrNxt;         /* Where to jump to continue with the next IN case */
  3777   3801     int omitTable;       /* True if we use the index only */
  3778   3802     int bRev;            /* True if we need to scan in reverse order */
  3779   3803     WhereLevel *pLevel;  /* The where level to be coded */
................................................................................
  4247   4271   
  4248   4272       int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  4249   4273       int regRowset = 0;                        /* Register for RowSet object */
  4250   4274       int regRowid = 0;                         /* Register holding rowid */
  4251   4275       int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  4252   4276       int iRetInit;                             /* Address of regReturn init */
  4253   4277       int untestedTerms = 0;             /* Some terms not completely tested */
  4254         -    int ii;
         4278  +    int ii;                            /* Loop counter */
         4279  +    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
  4255   4280      
  4256   4281       pTerm = pLevel->plan.u.pTerm;
  4257   4282       assert( pTerm!=0 );
  4258   4283       assert( pTerm->eOperator==WO_OR );
  4259   4284       assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
  4260   4285       pOrWc = &pTerm->u.pOrInfo->wc;
  4261   4286       pLevel->op = OP_Return;
................................................................................
  4296   4321       */
  4297   4322       if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  4298   4323         regRowset = ++pParse->nMem;
  4299   4324         regRowid = ++pParse->nMem;
  4300   4325         sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset);
  4301   4326       }
  4302   4327       iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);
         4328  +
         4329  +    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
         4330  +    ** Then for every term xN, evaluate as the subexpression: xN AND z
         4331  +    ** That way, terms in y that are factored into the disjunction will
         4332  +    ** be picked up by the recursive calls to sqlite3WhereBegin() below.
         4333  +    */
         4334  +    if( pWC->nTerm>1 ){
         4335  +      pAndExpr = sqlite3ExprAlloc(pParse->db, TK_AND, 0, 0);
         4336  +      pAndExpr->pRight = pWhere;
         4337  +    }
  4303   4338   
  4304   4339       for(ii=0; ii<pOrWc->nTerm; ii++){
  4305   4340         WhereTerm *pOrTerm = &pOrWc->a[ii];
  4306   4341         if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
  4307   4342           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
         4343  +        Expr *pOrExpr = pOrTerm->pExpr;
         4344  +        if( pAndExpr ){
         4345  +          pAndExpr->pLeft = pOrExpr;
         4346  +          pOrExpr = pAndExpr;
         4347  +        }
  4308   4348           /* Loop through table entries that match term pOrTerm. */
  4309         -        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0, 0,
  4310         -                        WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
         4349  +        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
         4350  +                        WHERE_OMIT_OPEN_CLOSE | WHERE_AND_ONLY |
  4311   4351                           WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  4312   4352           if( pSubWInfo ){
  4313   4353             explainOneScan(
  4314   4354                 pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  4315   4355             );
  4316   4356             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  4317   4357               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
................................................................................
  4331   4371             if( pSubWInfo->untestedTerms ) untestedTerms = 1;
  4332   4372   
  4333   4373             /* Finish the loop through table entries that match term pOrTerm. */
  4334   4374             sqlite3WhereEnd(pSubWInfo);
  4335   4375           }
  4336   4376         }
  4337   4377       }
         4378  +    sqlite3DbFree(pParse->db, pAndExpr);
  4338   4379       sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
  4339   4380       sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
  4340   4381       sqlite3VdbeResolveLabel(v, iLoopBody);
  4341   4382   
  4342   4383       if( pWInfo->nLevel>1 ) sqlite3StackFree(pParse->db, pOrTab);
  4343   4384       if( !untestedTerms ) disableTerm(pLevel, pTerm);
  4344   4385     }else
................................................................................
  4612   4653     ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */
  4613   4654     if( db->flags & SQLITE_DistinctOpt ) pDistinct = 0;
  4614   4655   
  4615   4656     /* Split the WHERE clause into separate subexpressions where each
  4616   4657     ** subexpression is separated by an AND operator.
  4617   4658     */
  4618   4659     initMaskSet(pMaskSet);
  4619         -  whereClauseInit(pWC, pParse, pMaskSet);
         4660  +  whereClauseInit(pWC, pParse, pMaskSet, wctrlFlags);
  4620   4661     sqlite3ExprCodeConstants(pParse, pWhere);
  4621   4662     whereSplit(pWC, pWhere, TK_AND);   /* IMP: R-15842-53296 */
  4622   4663       
  4623   4664     /* Special case: a WHERE clause that is constant.  Evaluate the
  4624   4665     ** expression and either jump over all of the code or fall thru.
  4625   4666     */
  4626   4667     if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
................................................................................
  4940   4981       if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  4941   4982         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
  4942   4983         int iCur = pTabItem->iCursor;
  4943   4984         sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
  4944   4985       }else
  4945   4986   #endif
  4946   4987       if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0
  4947         -         && (wctrlFlags & WHERE_OMIT_OPEN)==0 ){
         4988  +         && (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){
  4948   4989         int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
  4949   4990         sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
  4950   4991         testcase( pTab->nCol==BMS-1 );
  4951   4992         testcase( pTab->nCol==BMS );
  4952   4993         if( !pWInfo->okOnePass && pTab->nCol<BMS ){
  4953   4994           Bitmask b = pTabItem->colUsed;
  4954   4995           int n = 0;
................................................................................
  4985   5026     ** loop below generates code for a single nested loop of the VM
  4986   5027     ** program.
  4987   5028     */
  4988   5029     notReady = ~(Bitmask)0;
  4989   5030     for(i=0; i<nTabList; i++){
  4990   5031       pLevel = &pWInfo->a[i];
  4991   5032       explainOneScan(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags);
  4992         -    notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
         5033  +    notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady, pWhere);
  4993   5034       pWInfo->iContinue = pLevel->addrCont;
  4994   5035     }
  4995   5036   
  4996   5037   #ifdef SQLITE_TEST  /* For testing and debugging use only */
  4997   5038     /* Record in the query plan information about the current table
  4998   5039     ** and the index used to access it (if any).  If the table itself
  4999   5040     ** is not used, its name is just '{}'.  If no index is used
................................................................................
  5120   5161     assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc );
  5121   5162     for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){
  5122   5163       struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
  5123   5164       Table *pTab = pTabItem->pTab;
  5124   5165       assert( pTab!=0 );
  5125   5166       if( (pTab->tabFlags & TF_Ephemeral)==0
  5126   5167        && pTab->pSelect==0
  5127         -     && (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0
         5168  +     && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
  5128   5169       ){
  5129   5170         int ws = pLevel->plan.wsFlags;
  5130   5171         if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
  5131   5172           sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
  5132   5173         }
  5133   5174         if( (ws & WHERE_INDEXED)!=0 && (ws & WHERE_TEMP_INDEX)==0 ){
  5134   5175           sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);

Changes to test/where7.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13         -#
    14         -# $Id: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $
    15     13   
    16     14   set testdir [file dirname $argv0]
    17     15   source $testdir/tester.tcl
    18     16   
    19     17   ifcapable !or_opt {
    20     18     finish_test
    21     19     return
................................................................................
 23337  23335       FROM t302 JOIN t301 ON t302.c8 = t301.c8
 23338  23336       WHERE t302.c2 = 19571
 23339  23337         AND t302.c3 > 1287603136
 23340  23338         AND (t301.c4 = 1407449685622784
 23341  23339              OR t301.c8 = 1407424651264000)
 23342  23340      ORDER BY t302.c5 LIMIT 200;
 23343  23341   } {
 23344         -  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
        23342  +  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 
 23345  23343     0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
 23346  23344     0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
 23347  23345     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 23348  23346   }
 23349  23347   
 23350  23348   finish_test

Changes to test/where9.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13     13   #
    14         -# $Id: where9.test,v 1.9 2009/06/05 17:09:12 drh Exp $
    15     14   
    16     15   set testdir [file dirname $argv0]
    17     16   source $testdir/tester.tcl
    18     17   
    19     18   ifcapable !or_opt {
    20     19     finish_test
    21     20     return
................................................................................
   361    360     do_execsql_test where9-3.1 {
   362    361       EXPLAIN QUERY PLAN
   363    362       SELECT t2.a FROM t1, t2
   364    363       WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   365    364     } {
   366    365       0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   367    366       0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   368         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          367  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
   369    368     }
   370    369     do_execsql_test where9-3.2 {
   371    370       EXPLAIN QUERY PLAN
   372    371       SELECT coalesce(t2.a,9999)
   373    372       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
   374    373       WHERE t1.a=80
   375    374     } {
   376    375       0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   377    376       0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   378         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          377  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
   379    378     }
   380    379   } 
   381    380   
   382    381   # Make sure that INDEXED BY and multi-index OR clauses play well with
   383    382   # one another.
   384    383   #
   385    384   do_test where9-4.1 {
................................................................................
   450    449   ifcapable explain {
   451    450     # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   452    451     # the former is an equality test which is expected to return fewer rows.
   453    452     #
   454    453     do_execsql_test where9-5.1 {
   455    454       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
   456    455     } {
   457         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
   458         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
          456  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
          457  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
   459    458     }
   460    459   
   461    460     # In contrast, b=1000 is preferred over any OR-clause.
   462    461     #
   463    462     do_execsql_test where9-5.2 {
   464    463       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
   465    464     } {
................................................................................
   778    777     catchsql {
   779    778       UPDATE t1 INDEXED BY t1b SET a=a+100
   780    779        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   781    780           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   782    781           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   783    782     }
   784    783   } {1 {cannot use index: t1b}}
          784  +
          785  +############################################################################
          786  +# Test cases where terms inside an OR series are combined with AND terms
          787  +# external to the OR clause.  In other words, cases where
          788  +#
          789  +#              x AND (y OR z)
          790  +#
          791  +# is able to use indices on x,y and x,z, or indices y,x and z,x.
          792  +#
          793  +do_test where9-7.0 {
          794  +  execsql {
          795  +    CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
          796  +    INSERT INTO t5
          797  +     SELECT a, b, c, e, d, f, g,
          798  +            CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
          799  +            CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
          800  +       FROM t1;
          801  +    CREATE INDEX t5xb ON t5(x, b);
          802  +    CREATE INDEX t5xc ON t5(x, c);
          803  +    CREATE INDEX t5xd ON t5(x, d);
          804  +    CREATE INDEX t5xe ON t5(x, e);
          805  +    CREATE INDEX t5xf ON t5(x, f);
          806  +    CREATE INDEX t5xg ON t5(x, g);
          807  +    CREATE INDEX t5yb ON t5(y, b);
          808  +    CREATE INDEX t5yc ON t5(y, c);
          809  +    CREATE INDEX t5yd ON t5(y, d);
          810  +    CREATE INDEX t5ye ON t5(y, e);
          811  +    CREATE INDEX t5yf ON t5(y, f);
          812  +    CREATE INDEX t5yg ON t5(y, g);
          813  +    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
          814  +    INSERT INTO t6 SELECT * FROM t5;
          815  +    ANALYZE t5;
          816  +  }
          817  +} {}
          818  +do_test where9-7.1.1 {
          819  +  count_steps {
          820  +    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
          821  +  }
          822  +} {79 81 83 scan 0 sort 1}
          823  +do_test where9-7.1.2 {
          824  +  execsql {
          825  +    SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
          826  +  }
          827  +} {79 81 83}
          828  +do_test where9-7.1.3 {
          829  +  count_steps {
          830  +    SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
          831  +  }
          832  +} {80 scan 0 sort 1}
          833  +do_test where9-7.1.4 {
          834  +  execsql {
          835  +    SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
          836  +  }
          837  +} {80}
          838  +do_test where9-7.2.1 {
          839  +  count_steps {
          840  +    SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
          841  +  }
          842  +} {83 scan 0 sort 1}
          843  +do_test where9-7.2.2 {
          844  +  execsql {
          845  +    SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
          846  +  }
          847  +} {83}
          848  +do_test where9-7.3.1 {
          849  +  count_steps {
          850  +    SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
          851  +  }
          852  +} {79 81 scan 0 sort 1}
          853  +do_test where9-7.3.2 {
          854  +  execsql {
          855  +    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
          856  +  }
          857  +} {79 81}
          858  +
   785    859   
   786    860   finish_test