/ Check-in [faa0e420]
Login

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

Overview
Comment:Split out the bulk of the actual VDBE code generation logic from where.c into a new file, leaving behind the analysis logic. This makes the original where.c smaller and hopefully easier to edit.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | view-optimization
Files: files | file ages | folders
SHA1: faa0e420e93a2bc1c84df9eb9fef4748d29ce339
User & Date: drh 2015-06-06 20:12:09
Context
2015-06-08
14:23
Split more subfunctions of where.c out into a new whereexpr.c source file, for improved maintainability. check-in: 46ef95c1 user: drh tags: view-optimization
2015-06-06
20:12
Split out the bulk of the actual VDBE code generation logic from where.c into a new file, leaving behind the analysis logic. This makes the original where.c smaller and hopefully easier to edit. check-in: faa0e420 user: drh tags: view-optimization
18:30
Code simplifications in select.c and where.c. check-in: 4f20ac90 user: drh tags: view-optimization
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   180    180            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
   181    181            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
   182    182            pager.lo parse.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
   183    183            random.lo resolve.lo rowset.lo rtree.lo select.lo status.lo \
   184    184            table.lo threads.lo tokenize.lo trigger.lo \
   185    185            update.lo util.lo vacuum.lo \
   186    186            vdbe.lo vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
   187         -         vdbetrace.lo wal.lo walker.lo where.lo utf.lo vtab.lo
          187  +         vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo utf.lo vtab.lo
   188    188   
   189    189   # Object files for the amalgamation.
   190    190   #
   191    191   LIBOBJS1 = sqlite3.lo
   192    192   
   193    193   # Determine the real value of LIBOBJ based on the 'configure' script
   194    194   #
................................................................................
   289    289     $(TOP)/src/vdbeInt.h \
   290    290     $(TOP)/src/vtab.c \
   291    291     $(TOP)/src/vxworks.h \
   292    292     $(TOP)/src/wal.c \
   293    293     $(TOP)/src/wal.h \
   294    294     $(TOP)/src/walker.c \
   295    295     $(TOP)/src/where.c \
          296  +  $(TOP)/src/wherecode.c \
   296    297     $(TOP)/src/whereInt.h
   297    298   
   298    299   # Source code for extensions
   299    300   #
   300    301   SRC += \
   301    302     $(TOP)/ext/fts1/fts1.c \
   302    303     $(TOP)/ext/fts1/fts1.h \
................................................................................
   450    451     $(TOP)/src/util.c \
   451    452     $(TOP)/src/vdbeapi.c \
   452    453     $(TOP)/src/vdbeaux.c \
   453    454     $(TOP)/src/vdbe.c \
   454    455     $(TOP)/src/vdbemem.c \
   455    456     $(TOP)/src/vdbetrace.c \
   456    457     $(TOP)/src/where.c \
          458  +  $(TOP)/src/wherecode.c \
   457    459     parse.c \
   458    460     $(TOP)/ext/fts3/fts3.c \
   459    461     $(TOP)/ext/fts3/fts3_aux.c \
   460    462     $(TOP)/ext/fts3/fts3_expr.c \
   461    463     $(TOP)/ext/fts3/fts3_term.c \
   462    464     $(TOP)/ext/fts3/fts3_tokenizer.c \
   463    465     $(TOP)/ext/fts3/fts3_write.c \
................................................................................
   845    847   
   846    848   walker.lo:	$(TOP)/src/walker.c $(HDR)
   847    849   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/walker.c
   848    850   
   849    851   where.lo:	$(TOP)/src/where.c $(HDR)
   850    852   	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/where.c
   851    853   
          854  +wherecode.lo:	$(TOP)/src/wherecode.c $(HDR)
          855  +	$(LTCOMPILE) $(TEMP_STORE) -c $(TOP)/src/wherecode.c
          856  +
   852    857   tclsqlite.lo:	$(TOP)/src/tclsqlite.c $(HDR)
   853    858   	$(LTCOMPILE) -DUSE_TCL_STUBS=1 -c $(TOP)/src/tclsqlite.c
   854    859   
   855    860   tclsqlite-shell.lo:	$(TOP)/src/tclsqlite.c $(HDR)
   856    861   	$(LTCOMPILE) -DTCLSH=1 -o $@ -c $(TOP)/src/tclsqlite.c
   857    862   
   858    863   tclsqlite-stubs.lo:	$(TOP)/src/tclsqlite.c $(HDR)

Changes to Makefile.msc.

   834    834            mutex.lo mutex_noop.lo mutex_unix.lo mutex_w32.lo \
   835    835            notify.lo opcodes.lo os.lo os_unix.lo os_win.lo \
   836    836            pager.lo pcache.lo pcache1.lo pragma.lo prepare.lo printf.lo \
   837    837            random.lo resolve.lo rowset.lo rtree.lo select.lo status.lo \
   838    838            table.lo threads.lo tokenize.lo trigger.lo \
   839    839            update.lo util.lo vacuum.lo \
   840    840            vdbeapi.lo vdbeaux.lo vdbeblob.lo vdbemem.lo vdbesort.lo \
   841         -         vdbetrace.lo wal.lo walker.lo where.lo utf.lo vtab.lo
          841  +         vdbetrace.lo wal.lo walker.lo where.lo wherecode.lo utf.lo vtab.lo
   842    842   
   843    843   # Object files for the amalgamation.
   844    844   #
   845    845   LIBOBJS1 = sqlite3.lo
   846    846   
   847    847   # Determine the real value of LIBOBJ based on the 'configure' script
   848    848   #
................................................................................
   955    955     $(TOP)\src\vdbeInt.h \
   956    956     $(TOP)\src\vtab.c \
   957    957     $(TOP)\src\vxworks.h \
   958    958     $(TOP)\src\wal.c \
   959    959     $(TOP)\src\wal.h \
   960    960     $(TOP)\src\walker.c \
   961    961     $(TOP)\src\where.c \
          962  +  $(TOP)\src\wherecode.c \
   962    963     $(TOP)\src\whereInt.h
   963    964   
   964    965   # Source code for extensions
   965    966   #
   966    967   SRC3 = \
   967    968     $(TOP)\ext\fts1\fts1.c \
   968    969     $(TOP)\ext\fts1\fts1.h \
................................................................................
  1117   1118     $(TOP)\src\vdbeapi.c \
  1118   1119     $(TOP)\src\vdbeaux.c \
  1119   1120     $(TOP)\src\vdbe.c \
  1120   1121     $(TOP)\src\vdbemem.c \
  1121   1122     $(TOP)\src\vdbesort.c \
  1122   1123     $(TOP)\src\vdbetrace.c \
  1123   1124     $(TOP)\src\where.c \
         1125  +  $(TOP)\src\wherecode.c \
  1124   1126     parse.c \
  1125   1127     $(TOP)\ext\fts3\fts3.c \
  1126   1128     $(TOP)\ext\fts3\fts3_aux.c \
  1127   1129     $(TOP)\ext\fts3\fts3_expr.c \
  1128   1130     $(TOP)\ext\fts3\fts3_tokenizer.c \
  1129   1131     $(TOP)\ext\fts3\fts3_tokenize_vtab.c \
  1130   1132     $(TOP)\ext\fts3\fts3_unicode.c \
................................................................................
  1526   1528   
  1527   1529   walker.lo:	$(TOP)\src\walker.c $(HDR)
  1528   1530   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\walker.c
  1529   1531   
  1530   1532   where.lo:	$(TOP)\src\where.c $(HDR)
  1531   1533   	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\where.c
  1532   1534   
         1535  +wherecode.lo:	$(TOP)\src\wherecode.c $(HDR)
         1536  +	$(LTCOMPILE) $(CORE_COMPILE_OPTS) -c $(TOP)\src\wherecode.c
         1537  +
  1533   1538   tclsqlite.lo:	$(TOP)\src\tclsqlite.c $(HDR)
  1534   1539   	$(LTCOMPILE) $(NO_WARN) -DUSE_TCL_STUBS=1 -DBUILD_sqlite -I$(TCLINCDIR) -c $(TOP)\src\tclsqlite.c
  1535   1540   
  1536   1541   tclsqlite-shell.lo:	$(TOP)\src\tclsqlite.c $(HDR)
  1537   1542   	$(LTCOMPILE) $(NO_WARN) -DTCLSH=1 -DBUILD_sqlite -I$(TCLINCDIR) -c $(TOP)\src\tclsqlite.c
  1538   1543   
  1539   1544   tclsqlite3.exe:	tclsqlite-shell.lo $(SQLITE3C) $(LIBRESOBJS)

Changes to main.mk.

    65     65            mutex.o mutex_noop.o mutex_unix.o mutex_w32.o \
    66     66            notify.o opcodes.o os.o os_unix.o os_win.o \
    67     67            pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \
    68     68            random.o resolve.o rowset.o rtree.o select.o sqlite3ota.o status.o \
    69     69            table.o threads.o tokenize.o trigger.o \
    70     70            update.o userauth.o util.o vacuum.o \
    71     71            vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \
    72         -	 vdbetrace.o wal.o walker.o where.o utf.o vtab.o
           72  +	 vdbetrace.o wal.o walker.o where.o wherecode.o utf.o vtab.o
    73     73   
    74     74   
    75     75   
    76     76   # All of the source code files.
    77     77   #
    78     78   SRC = \
    79     79     $(TOP)/src/alter.c \
................................................................................
   167    167     $(TOP)/src/vdbeInt.h \
   168    168     $(TOP)/src/vtab.c \
   169    169     $(TOP)/src/vxworks.h \
   170    170     $(TOP)/src/wal.c \
   171    171     $(TOP)/src/wal.h \
   172    172     $(TOP)/src/walker.c \
   173    173     $(TOP)/src/where.c \
          174  +  $(TOP)/src/wherecode.c \
   174    175     $(TOP)/src/whereInt.h
   175    176   
   176    177   # Source code for extensions
   177    178   #
   178    179   SRC += \
   179    180     $(TOP)/ext/fts1/fts1.c \
   180    181     $(TOP)/ext/fts1/fts1.h \
................................................................................
   333    334     $(TOP)/src/utf.c \
   334    335     $(TOP)/src/util.c \
   335    336     $(TOP)/src/vdbeapi.c \
   336    337     $(TOP)/src/vdbeaux.c \
   337    338     $(TOP)/src/vdbe.c \
   338    339     $(TOP)/src/vdbemem.c \
   339    340     $(TOP)/src/where.c \
          341  +  $(TOP)/src/wherecode.c \
   340    342     parse.c \
   341    343     $(TOP)/ext/fts3/fts3.c \
   342    344     $(TOP)/ext/fts3/fts3_aux.c \
   343    345     $(TOP)/ext/fts3/fts3_expr.c \
   344    346     $(TOP)/ext/fts3/fts3_tokenizer.c \
   345    347     $(TOP)/ext/fts3/fts3_write.c \
   346    348     $(TOP)/ext/async/sqlite3async.c 

Changes to src/where.c.

    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "whereInt.h"
           21  +
           22  +/* Forward declaration of methods */
           23  +static int whereLoopResize(sqlite3*, WhereLoop*, int);
           24  +
           25  +/* Test variable that can be set to enable WHERE tracing */
           26  +#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
           27  +/***/ int sqlite3WhereTrace = 0;
           28  +#endif
           29  +
    21     30   
    22     31   /*
    23     32   ** Return the estimated number of output rows from a WHERE clause
    24     33   */
    25     34   u64 sqlite3WhereOutputRowCount(WhereInfo *pWInfo){
    26     35     return sqlite3LogEstToInt(pWInfo->nRowOut);
    27     36   }
................................................................................
   271    280   */
   272    281   #define initMaskSet(P)  (P)->n=0
   273    282   
   274    283   /*
   275    284   ** Return the bitmask for the given cursor number.  Return 0 if
   276    285   ** iCursor is not in the set.
   277    286   */
   278         -static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){
          287  +Bitmask sqlite3WhereGetMask(WhereMaskSet *pMaskSet, int iCursor){
   279    288     int i;
   280    289     assert( pMaskSet->n<=(int)sizeof(Bitmask)*8 );
   281    290     for(i=0; i<pMaskSet->n; i++){
   282    291       if( pMaskSet->ix[i]==iCursor ){
   283    292         return MASKBIT(i);
   284    293       }
   285    294     }
................................................................................
   306    315   */
   307    316   static Bitmask exprListTableUsage(WhereMaskSet*, ExprList*);
   308    317   static Bitmask exprSelectTableUsage(WhereMaskSet*, Select*);
   309    318   static Bitmask exprTableUsage(WhereMaskSet *pMaskSet, Expr *p){
   310    319     Bitmask mask = 0;
   311    320     if( p==0 ) return 0;
   312    321     if( p->op==TK_COLUMN ){
   313         -    mask = getMask(pMaskSet, p->iTable);
          322  +    mask = sqlite3WhereGetMask(pMaskSet, p->iTable);
   314    323       return mask;
   315    324     }
   316    325     mask = exprTableUsage(pMaskSet, p->pRight);
   317    326     mask |= exprTableUsage(pMaskSet, p->pLeft);
   318    327     if( ExprHasProperty(p, EP_xIsSelect) ){
   319    328       mask |= exprSelectTableUsage(pMaskSet, p->x.pSelect);
   320    329     }else{
................................................................................
   580    589   ** If there are multiple terms in the WHERE clause of the form "X <op> <expr>"
   581    590   ** then try for the one with no dependencies on <expr> - in other words where
   582    591   ** <expr> is a constant expression of some kind.  Only return entries of
   583    592   ** the form "X <op> Y" where Y is a column in another table if no terms of
   584    593   ** the form "X <op> <const-expr>" exist.   If no terms with a constant RHS
   585    594   ** exist, try to return a term that does not use WO_EQUIV.
   586    595   */
   587         -static WhereTerm *findTerm(
          596  +WhereTerm *sqlite3WhereFindTerm(
   588    597     WhereClause *pWC,     /* The WHERE clause to be searched */
   589    598     int iCur,             /* Cursor number of LHS */
   590    599     int iColumn,          /* Column number of LHS */
   591    600     Bitmask notReady,     /* RHS must not overlap with this mask */
   592    601     u32 op,               /* Mask of WO_xx values describing operator */
   593    602     Index *pIdx           /* Must be compatible with this index, if not NULL */
   594    603   ){
................................................................................
  1002   1011           exprAnalyzeAll(pSrc, pAndWC);
  1003   1012           pAndWC->pOuter = pWC;
  1004   1013           testcase( db->mallocFailed );
  1005   1014           if( !db->mallocFailed ){
  1006   1015             for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
  1007   1016               assert( pAndTerm->pExpr );
  1008   1017               if( allowedOp(pAndTerm->pExpr->op) ){
  1009         -              b |= getMask(&pWInfo->sMaskSet, pAndTerm->leftCursor);
         1018  +              b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor);
  1010   1019               }
  1011   1020             }
  1012   1021           }
  1013   1022           indexable &= b;
  1014   1023         }
  1015   1024       }else if( pOrTerm->wtFlags & TERM_COPIED ){
  1016   1025         /* Skip this term for now.  We revisit it when we process the
  1017   1026         ** corresponding TERM_VIRTUAL term */
  1018   1027       }else{
  1019   1028         Bitmask b;
  1020         -      b = getMask(&pWInfo->sMaskSet, pOrTerm->leftCursor);
         1029  +      b = sqlite3WhereGetMask(&pWInfo->sMaskSet, pOrTerm->leftCursor);
  1021   1030         if( pOrTerm->wtFlags & TERM_VIRTUAL ){
  1022   1031           WhereTerm *pOther = &pOrWc->a[pOrTerm->iParent];
  1023         -        b |= getMask(&pWInfo->sMaskSet, pOther->leftCursor);
         1032  +        b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pOther->leftCursor);
  1024   1033         }
  1025   1034         indexable &= b;
  1026   1035         if( (pOrTerm->eOperator & WO_EQ)==0 ){
  1027   1036           chngToIN = 0;
  1028   1037         }else{
  1029   1038           chngToIN &= b;
  1030   1039         }
................................................................................
  1092   1101           pOrTerm->wtFlags &= ~TERM_OR_OK;
  1093   1102           if( pOrTerm->leftCursor==iCursor ){
  1094   1103             /* This is the 2-bit case and we are on the second iteration and
  1095   1104             ** current term is from the first iteration.  So skip this term. */
  1096   1105             assert( j==1 );
  1097   1106             continue;
  1098   1107           }
  1099         -        if( (chngToIN & getMask(&pWInfo->sMaskSet, pOrTerm->leftCursor))==0 ){
         1108  +        if( (chngToIN & sqlite3WhereGetMask(&pWInfo->sMaskSet, pOrTerm->leftCursor))==0 ){
  1100   1109             /* This term must be of the form t1.a==t2.b where t2 is in the
  1101   1110             ** chngToIN set but t1 is not.  This term will be either preceded
  1102   1111             ** or follwed by an inverted copy (t2.b==t1.a).  Skip this term 
  1103   1112             ** and use its inversion. */
  1104   1113             testcase( pOrTerm->wtFlags & TERM_COPIED );
  1105   1114             testcase( pOrTerm->wtFlags & TERM_VIRTUAL );
  1106   1115             assert( pOrTerm->wtFlags & (TERM_COPIED|TERM_VIRTUAL) );
................................................................................
  1111   1120           break;
  1112   1121         }
  1113   1122         if( i<0 ){
  1114   1123           /* No candidate table+column was found.  This can only occur
  1115   1124           ** on the second iteration */
  1116   1125           assert( j==1 );
  1117   1126           assert( IsPowerOfTwo(chngToIN) );
  1118         -        assert( chngToIN==getMask(&pWInfo->sMaskSet, iCursor) );
         1127  +        assert( chngToIN==sqlite3WhereGetMask(&pWInfo->sMaskSet, iCursor) );
  1119   1128           break;
  1120   1129         }
  1121   1130         testcase( j==1 );
  1122   1131   
  1123   1132         /* We have found a candidate table and column.  Check to see if that
  1124   1133         ** table and column is common to every term in the OR clause */
  1125   1134         okToChngToIN = 1;
................................................................................
  1283   1292     }else if( op==TK_ISNULL ){
  1284   1293       pTerm->prereqRight = 0;
  1285   1294     }else{
  1286   1295       pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
  1287   1296     }
  1288   1297     prereqAll = exprTableUsage(pMaskSet, pExpr);
  1289   1298     if( ExprHasProperty(pExpr, EP_FromJoin) ){
  1290         -    Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
         1299  +    Bitmask x = sqlite3WhereGetMask(pMaskSet, pExpr->iRightJoinTable);
  1291   1300       prereqAll |= x;
  1292   1301       extraRight = x-1;  /* ON clause terms may not be used with an index
  1293   1302                          ** on left table of a LEFT JOIN.  Ticket #3015 */
  1294   1303     }
  1295   1304     pTerm->prereqAll = prereqAll;
  1296   1305     pTerm->leftCursor = -1;
  1297   1306     pTerm->iParent = -1;
................................................................................
  1637   1646     **   3. All of those index columns for which the WHERE clause does not
  1638   1647     **      contain a "col=X" term are subject to a NOT NULL constraint.
  1639   1648     */
  1640   1649     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  1641   1650       if( !IsUniqueIndex(pIdx) ) continue;
  1642   1651       for(i=0; i<pIdx->nKeyCol; i++){
  1643   1652         i16 iCol = pIdx->aiColumn[i];
  1644         -      if( 0==findTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){
         1653  +      if( 0==sqlite3WhereFindTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){
  1645   1654           int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i);
  1646   1655           if( iIdxCol<0 || pTab->aCol[iCol].notNull==0 ){
  1647   1656             break;
  1648   1657           }
  1649   1658         }
  1650   1659       }
  1651   1660       if( i==pIdx->nKeyCol ){
................................................................................
  2759   2768       WHERETRACE(0x10,("IN row estimate: est=%d\n", nRowEst));
  2760   2769     }
  2761   2770     assert( pBuilder->nRecValid==nRecValid );
  2762   2771     return rc;
  2763   2772   }
  2764   2773   #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */
  2765   2774   
  2766         -/*
  2767         -** Disable a term in the WHERE clause.  Except, do not disable the term
  2768         -** if it controls a LEFT OUTER JOIN and it did not originate in the ON
  2769         -** or USING clause of that join.
  2770         -**
  2771         -** Consider the term t2.z='ok' in the following queries:
  2772         -**
  2773         -**   (1)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
  2774         -**   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
  2775         -**   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
  2776         -**
  2777         -** The t2.z='ok' is disabled in the in (2) because it originates
  2778         -** in the ON clause.  The term is disabled in (3) because it is not part
  2779         -** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
  2780         -**
  2781         -** Disabling a term causes that term to not be tested in the inner loop
  2782         -** of the join.  Disabling is an optimization.  When terms are satisfied
  2783         -** by indices, we disable them to prevent redundant tests in the inner
  2784         -** loop.  We would get the correct results if nothing were ever disabled,
  2785         -** but joins might run a little slower.  The trick is to disable as much
  2786         -** as we can without disabling too much.  If we disabled in (1), we'd get
  2787         -** the wrong answer.  See ticket #813.
  2788         -**
  2789         -** If all the children of a term are disabled, then that term is also
  2790         -** automatically disabled.  In this way, terms get disabled if derived
  2791         -** virtual terms are tested first.  For example:
  2792         -**
  2793         -**      x GLOB 'abc*' AND x>='abc' AND x<'acd'
  2794         -**      \___________/     \______/     \_____/
  2795         -**         parent          child1       child2
  2796         -**
  2797         -** Only the parent term was in the original WHERE clause.  The child1
  2798         -** and child2 terms were added by the LIKE optimization.  If both of
  2799         -** the virtual child terms are valid, then testing of the parent can be 
  2800         -** skipped.
  2801         -**
  2802         -** Usually the parent term is marked as TERM_CODED.  But if the parent
  2803         -** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.
  2804         -** The TERM_LIKECOND marking indicates that the term should be coded inside
  2805         -** a conditional such that is only evaluated on the second pass of a
  2806         -** LIKE-optimization loop, when scanning BLOBs instead of strings.
  2807         -*/
  2808         -static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
  2809         -  int nLoop = 0;
  2810         -  while( pTerm
  2811         -      && (pTerm->wtFlags & TERM_CODED)==0
  2812         -      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
  2813         -      && (pLevel->notReady & pTerm->prereqAll)==0
  2814         -  ){
  2815         -    if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){
  2816         -      pTerm->wtFlags |= TERM_LIKECOND;
  2817         -    }else{
  2818         -      pTerm->wtFlags |= TERM_CODED;
  2819         -    }
  2820         -    if( pTerm->iParent<0 ) break;
  2821         -    pTerm = &pTerm->pWC->a[pTerm->iParent];
  2822         -    pTerm->nChild--;
  2823         -    if( pTerm->nChild!=0 ) break;
  2824         -    nLoop++;
  2825         -  }
  2826         -}
  2827         -
  2828         -/*
  2829         -** Code an OP_Affinity opcode to apply the column affinity string zAff
  2830         -** to the n registers starting at base. 
  2831         -**
  2832         -** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
  2833         -** beginning and end of zAff are ignored.  If all entries in zAff are
  2834         -** SQLITE_AFF_BLOB, then no code gets generated.
  2835         -**
  2836         -** This routine makes its own copy of zAff so that the caller is free
  2837         -** to modify zAff after this routine returns.
  2838         -*/
  2839         -static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
  2840         -  Vdbe *v = pParse->pVdbe;
  2841         -  if( zAff==0 ){
  2842         -    assert( pParse->db->mallocFailed );
  2843         -    return;
  2844         -  }
  2845         -  assert( v!=0 );
  2846         -
  2847         -  /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
  2848         -  ** and end of the affinity string.
  2849         -  */
  2850         -  while( n>0 && zAff[0]==SQLITE_AFF_BLOB ){
  2851         -    n--;
  2852         -    base++;
  2853         -    zAff++;
  2854         -  }
  2855         -  while( n>1 && zAff[n-1]==SQLITE_AFF_BLOB ){
  2856         -    n--;
  2857         -  }
  2858         -
  2859         -  /* Code the OP_Affinity opcode if there is anything left to do. */
  2860         -  if( n>0 ){
  2861         -    sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
  2862         -    sqlite3VdbeChangeP4(v, -1, zAff, n);
  2863         -    sqlite3ExprCacheAffinityChange(pParse, base, n);
  2864         -  }
  2865         -}
  2866         -
  2867         -
  2868         -/*
  2869         -** Generate code for a single equality term of the WHERE clause.  An equality
  2870         -** term can be either X=expr or X IN (...).   pTerm is the term to be 
  2871         -** coded.
  2872         -**
  2873         -** The current value for the constraint is left in register iReg.
  2874         -**
  2875         -** For a constraint of the form X=expr, the expression is evaluated and its
  2876         -** result is left on the stack.  For constraints of the form X IN (...)
  2877         -** this routine sets up a loop that will iterate over all values of X.
  2878         -*/
  2879         -static int codeEqualityTerm(
  2880         -  Parse *pParse,      /* The parsing context */
  2881         -  WhereTerm *pTerm,   /* The term of the WHERE clause to be coded */
  2882         -  WhereLevel *pLevel, /* The level of the FROM clause we are working on */
  2883         -  int iEq,            /* Index of the equality term within this level */
  2884         -  int bRev,           /* True for reverse-order IN operations */
  2885         -  int iTarget         /* Attempt to leave results in this register */
  2886         -){
  2887         -  Expr *pX = pTerm->pExpr;
  2888         -  Vdbe *v = pParse->pVdbe;
  2889         -  int iReg;                  /* Register holding results */
  2890         -
  2891         -  assert( iTarget>0 );
  2892         -  if( pX->op==TK_EQ || pX->op==TK_IS ){
  2893         -    iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
  2894         -  }else if( pX->op==TK_ISNULL ){
  2895         -    iReg = iTarget;
  2896         -    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
  2897         -#ifndef SQLITE_OMIT_SUBQUERY
  2898         -  }else{
  2899         -    int eType;
  2900         -    int iTab;
  2901         -    struct InLoop *pIn;
  2902         -    WhereLoop *pLoop = pLevel->pWLoop;
  2903         -
  2904         -    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
  2905         -      && pLoop->u.btree.pIndex!=0
  2906         -      && pLoop->u.btree.pIndex->aSortOrder[iEq]
  2907         -    ){
  2908         -      testcase( iEq==0 );
  2909         -      testcase( bRev );
  2910         -      bRev = !bRev;
  2911         -    }
  2912         -    assert( pX->op==TK_IN );
  2913         -    iReg = iTarget;
  2914         -    eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0);
  2915         -    if( eType==IN_INDEX_INDEX_DESC ){
  2916         -      testcase( bRev );
  2917         -      bRev = !bRev;
  2918         -    }
  2919         -    iTab = pX->iTable;
  2920         -    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  2921         -    VdbeCoverageIf(v, bRev);
  2922         -    VdbeCoverageIf(v, !bRev);
  2923         -    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
  2924         -    pLoop->wsFlags |= WHERE_IN_ABLE;
  2925         -    if( pLevel->u.in.nIn==0 ){
  2926         -      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  2927         -    }
  2928         -    pLevel->u.in.nIn++;
  2929         -    pLevel->u.in.aInLoop =
  2930         -       sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop,
  2931         -                              sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn);
  2932         -    pIn = pLevel->u.in.aInLoop;
  2933         -    if( pIn ){
  2934         -      pIn += pLevel->u.in.nIn - 1;
  2935         -      pIn->iCur = iTab;
  2936         -      if( eType==IN_INDEX_ROWID ){
  2937         -        pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg);
  2938         -      }else{
  2939         -        pIn->addrInTop = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
  2940         -      }
  2941         -      pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
  2942         -      sqlite3VdbeAddOp1(v, OP_IsNull, iReg); VdbeCoverage(v);
  2943         -    }else{
  2944         -      pLevel->u.in.nIn = 0;
  2945         -    }
  2946         -#endif
  2947         -  }
  2948         -  disableTerm(pLevel, pTerm);
  2949         -  return iReg;
  2950         -}
  2951         -
  2952         -/*
  2953         -** Generate code that will evaluate all == and IN constraints for an
  2954         -** index scan.
  2955         -**
  2956         -** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
  2957         -** Suppose the WHERE clause is this:  a==5 AND b IN (1,2,3) AND c>5 AND c<10
  2958         -** The index has as many as three equality constraints, but in this
  2959         -** example, the third "c" value is an inequality.  So only two 
  2960         -** constraints are coded.  This routine will generate code to evaluate
  2961         -** a==5 and b IN (1,2,3).  The current values for a and b will be stored
  2962         -** in consecutive registers and the index of the first register is returned.
  2963         -**
  2964         -** In the example above nEq==2.  But this subroutine works for any value
  2965         -** of nEq including 0.  If nEq==0, this routine is nearly a no-op.
  2966         -** The only thing it does is allocate the pLevel->iMem memory cell and
  2967         -** compute the affinity string.
  2968         -**
  2969         -** The nExtraReg parameter is 0 or 1.  It is 0 if all WHERE clause constraints
  2970         -** are == or IN and are covered by the nEq.  nExtraReg is 1 if there is
  2971         -** an inequality constraint (such as the "c>=5 AND c<10" in the example) that
  2972         -** occurs after the nEq quality constraints.
  2973         -**
  2974         -** This routine allocates a range of nEq+nExtraReg memory cells and returns
  2975         -** the index of the first memory cell in that range. The code that
  2976         -** calls this routine will use that memory range to store keys for
  2977         -** start and termination conditions of the loop.
  2978         -** key value of the loop.  If one or more IN operators appear, then
  2979         -** this routine allocates an additional nEq memory cells for internal
  2980         -** use.
  2981         -**
  2982         -** Before returning, *pzAff is set to point to a buffer containing a
  2983         -** copy of the column affinity string of the index allocated using
  2984         -** sqlite3DbMalloc(). Except, entries in the copy of the string associated
  2985         -** with equality constraints that use BLOB or NONE affinity are set to
  2986         -** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:
  2987         -**
  2988         -**   CREATE TABLE t1(a TEXT PRIMARY KEY, b);
  2989         -**   SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
  2990         -**
  2991         -** In the example above, the index on t1(a) has TEXT affinity. But since
  2992         -** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,
  2993         -** no conversion should be attempted before using a t2.b value as part of
  2994         -** a key to search the index. Hence the first byte in the returned affinity
  2995         -** string in this example would be set to SQLITE_AFF_BLOB.
  2996         -*/
  2997         -static int codeAllEqualityTerms(
  2998         -  Parse *pParse,        /* Parsing context */
  2999         -  WhereLevel *pLevel,   /* Which nested loop of the FROM we are coding */
  3000         -  int bRev,             /* Reverse the order of IN operators */
  3001         -  int nExtraReg,        /* Number of extra registers to allocate */
  3002         -  char **pzAff          /* OUT: Set to point to affinity string */
  3003         -){
  3004         -  u16 nEq;                      /* The number of == or IN constraints to code */
  3005         -  u16 nSkip;                    /* Number of left-most columns to skip */
  3006         -  Vdbe *v = pParse->pVdbe;      /* The vm under construction */
  3007         -  Index *pIdx;                  /* The index being used for this loop */
  3008         -  WhereTerm *pTerm;             /* A single constraint term */
  3009         -  WhereLoop *pLoop;             /* The WhereLoop object */
  3010         -  int j;                        /* Loop counter */
  3011         -  int regBase;                  /* Base register */
  3012         -  int nReg;                     /* Number of registers to allocate */
  3013         -  char *zAff;                   /* Affinity string to return */
  3014         -
  3015         -  /* This module is only called on query plans that use an index. */
  3016         -  pLoop = pLevel->pWLoop;
  3017         -  assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );
  3018         -  nEq = pLoop->u.btree.nEq;
  3019         -  nSkip = pLoop->nSkip;
  3020         -  pIdx = pLoop->u.btree.pIndex;
  3021         -  assert( pIdx!=0 );
  3022         -
  3023         -  /* Figure out how many memory cells we will need then allocate them.
  3024         -  */
  3025         -  regBase = pParse->nMem + 1;
  3026         -  nReg = pLoop->u.btree.nEq + nExtraReg;
  3027         -  pParse->nMem += nReg;
  3028         -
  3029         -  zAff = sqlite3DbStrDup(pParse->db, sqlite3IndexAffinityStr(v, pIdx));
  3030         -  if( !zAff ){
  3031         -    pParse->db->mallocFailed = 1;
  3032         -  }
  3033         -
  3034         -  if( nSkip ){
  3035         -    int iIdxCur = pLevel->iIdxCur;
  3036         -    sqlite3VdbeAddOp1(v, (bRev?OP_Last:OP_Rewind), iIdxCur);
  3037         -    VdbeCoverageIf(v, bRev==0);
  3038         -    VdbeCoverageIf(v, bRev!=0);
  3039         -    VdbeComment((v, "begin skip-scan on %s", pIdx->zName));
  3040         -    j = sqlite3VdbeAddOp0(v, OP_Goto);
  3041         -    pLevel->addrSkip = sqlite3VdbeAddOp4Int(v, (bRev?OP_SeekLT:OP_SeekGT),
  3042         -                            iIdxCur, 0, regBase, nSkip);
  3043         -    VdbeCoverageIf(v, bRev==0);
  3044         -    VdbeCoverageIf(v, bRev!=0);
  3045         -    sqlite3VdbeJumpHere(v, j);
  3046         -    for(j=0; j<nSkip; j++){
  3047         -      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, j, regBase+j);
  3048         -      assert( pIdx->aiColumn[j]>=0 );
  3049         -      VdbeComment((v, "%s", pIdx->pTable->aCol[pIdx->aiColumn[j]].zName));
  3050         -    }
  3051         -  }    
  3052         -
  3053         -  /* Evaluate the equality constraints
  3054         -  */
  3055         -  assert( zAff==0 || (int)strlen(zAff)>=nEq );
  3056         -  for(j=nSkip; j<nEq; j++){
  3057         -    int r1;
  3058         -    pTerm = pLoop->aLTerm[j];
  3059         -    assert( pTerm!=0 );
  3060         -    /* The following testcase is true for indices with redundant columns. 
  3061         -    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
  3062         -    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
  3063         -    testcase( pTerm->wtFlags & TERM_VIRTUAL );
  3064         -    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j);
  3065         -    if( r1!=regBase+j ){
  3066         -      if( nReg==1 ){
  3067         -        sqlite3ReleaseTempReg(pParse, regBase);
  3068         -        regBase = r1;
  3069         -      }else{
  3070         -        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  3071         -      }
  3072         -    }
  3073         -    testcase( pTerm->eOperator & WO_ISNULL );
  3074         -    testcase( pTerm->eOperator & WO_IN );
  3075         -    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
  3076         -      Expr *pRight = pTerm->pExpr->pRight;
  3077         -      if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
  3078         -        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
  3079         -        VdbeCoverage(v);
  3080         -      }
  3081         -      if( zAff ){
  3082         -        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
  3083         -          zAff[j] = SQLITE_AFF_BLOB;
  3084         -        }
  3085         -        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
  3086         -          zAff[j] = SQLITE_AFF_BLOB;
  3087         -        }
  3088         -      }
  3089         -    }
  3090         -  }
  3091         -  *pzAff = zAff;
  3092         -  return regBase;
  3093         -}
  3094         -
  3095         -#ifndef SQLITE_OMIT_EXPLAIN
  3096         -/*
  3097         -** This routine is a helper for explainIndexRange() below
  3098         -**
  3099         -** pStr holds the text of an expression that we are building up one term
  3100         -** at a time.  This routine adds a new term to the end of the expression.
  3101         -** Terms are separated by AND so add the "AND" text for second and subsequent
  3102         -** terms only.
  3103         -*/
  3104         -static void explainAppendTerm(
  3105         -  StrAccum *pStr,             /* The text expression being built */
  3106         -  int iTerm,                  /* Index of this term.  First is zero */
  3107         -  const char *zColumn,        /* Name of the column */
  3108         -  const char *zOp             /* Name of the operator */
  3109         -){
  3110         -  if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
  3111         -  sqlite3StrAccumAppendAll(pStr, zColumn);
  3112         -  sqlite3StrAccumAppend(pStr, zOp, 1);
  3113         -  sqlite3StrAccumAppend(pStr, "?", 1);
  3114         -}
  3115         -
  3116         -/*
  3117         -** Argument pLevel describes a strategy for scanning table pTab. This 
  3118         -** function appends text to pStr that describes the subset of table
  3119         -** rows scanned by the strategy in the form of an SQL expression.
  3120         -**
  3121         -** For example, if the query:
  3122         -**
  3123         -**   SELECT * FROM t1 WHERE a=1 AND b>2;
  3124         -**
  3125         -** is run and there is an index on (a, b), then this function returns a
  3126         -** string similar to:
  3127         -**
  3128         -**   "a=? AND b>?"
  3129         -*/
  3130         -static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
  3131         -  Index *pIndex = pLoop->u.btree.pIndex;
  3132         -  u16 nEq = pLoop->u.btree.nEq;
  3133         -  u16 nSkip = pLoop->nSkip;
  3134         -  int i, j;
  3135         -  Column *aCol = pTab->aCol;
  3136         -  i16 *aiColumn = pIndex->aiColumn;
  3137         -
  3138         -  if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
  3139         -  sqlite3StrAccumAppend(pStr, " (", 2);
  3140         -  for(i=0; i<nEq; i++){
  3141         -    char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName;
  3142         -    if( i>=nSkip ){
  3143         -      explainAppendTerm(pStr, i, z, "=");
  3144         -    }else{
  3145         -      if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5);
  3146         -      sqlite3XPrintf(pStr, 0, "ANY(%s)", z);
  3147         -    }
  3148         -  }
  3149         -
  3150         -  j = i;
  3151         -  if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
  3152         -    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
  3153         -    explainAppendTerm(pStr, i++, z, ">");
  3154         -  }
  3155         -  if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
  3156         -    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
  3157         -    explainAppendTerm(pStr, i, z, "<");
  3158         -  }
  3159         -  sqlite3StrAccumAppend(pStr, ")", 1);
  3160         -}
  3161         -
  3162         -/*
  3163         -** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
  3164         -** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was
  3165         -** defined at compile-time. If it is not a no-op, a single OP_Explain opcode 
  3166         -** is added to the output to describe the table scan strategy in pLevel.
  3167         -**
  3168         -** If an OP_Explain opcode is added to the VM, its address is returned.
  3169         -** Otherwise, if no OP_Explain is coded, zero is returned.
  3170         -*/
  3171         -static int explainOneScan(
  3172         -  Parse *pParse,                  /* Parse context */
  3173         -  SrcList *pTabList,              /* Table list this loop refers to */
  3174         -  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  3175         -  int iLevel,                     /* Value for "level" column of output */
  3176         -  int iFrom,                      /* Value for "from" column of output */
  3177         -  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
  3178         -){
  3179         -  int ret = 0;
  3180         -#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
  3181         -  if( pParse->explain==2 )
  3182         -#endif
  3183         -  {
  3184         -    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  3185         -    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
  3186         -    sqlite3 *db = pParse->db;     /* Database handle */
  3187         -    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
  3188         -    int isSearch;                 /* True for a SEARCH. False for SCAN. */
  3189         -    WhereLoop *pLoop;             /* The controlling WhereLoop object */
  3190         -    u32 flags;                    /* Flags that describe this loop */
  3191         -    char *zMsg;                   /* Text to add to EQP output */
  3192         -    StrAccum str;                 /* EQP output string */
  3193         -    char zBuf[100];               /* Initial space for EQP output string */
  3194         -
  3195         -    pLoop = pLevel->pWLoop;
  3196         -    flags = pLoop->wsFlags;
  3197         -    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return 0;
  3198         -
  3199         -    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
  3200         -            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
  3201         -            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));
  3202         -
  3203         -    sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
  3204         -    sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN");
  3205         -    if( pItem->pSelect ){
  3206         -      sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId);
  3207         -    }else{
  3208         -      sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName);
  3209         -    }
  3210         -
  3211         -    if( pItem->zAlias ){
  3212         -      sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias);
  3213         -    }
  3214         -    if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
  3215         -      const char *zFmt = 0;
  3216         -      Index *pIdx;
  3217         -
  3218         -      assert( pLoop->u.btree.pIndex!=0 );
  3219         -      pIdx = pLoop->u.btree.pIndex;
  3220         -      assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );
  3221         -      if( !HasRowid(pItem->pTab) && IsPrimaryKeyIndex(pIdx) ){
  3222         -        if( isSearch ){
  3223         -          zFmt = "PRIMARY KEY";
  3224         -        }
  3225         -      }else if( flags & WHERE_PARTIALIDX ){
  3226         -        zFmt = "AUTOMATIC PARTIAL COVERING INDEX";
  3227         -      }else if( flags & WHERE_AUTO_INDEX ){
  3228         -        zFmt = "AUTOMATIC COVERING INDEX";
  3229         -      }else if( flags & WHERE_IDX_ONLY ){
  3230         -        zFmt = "COVERING INDEX %s";
  3231         -      }else{
  3232         -        zFmt = "INDEX %s";
  3233         -      }
  3234         -      if( zFmt ){
  3235         -        sqlite3StrAccumAppend(&str, " USING ", 7);
  3236         -        sqlite3XPrintf(&str, 0, zFmt, pIdx->zName);
  3237         -        explainIndexRange(&str, pLoop, pItem->pTab);
  3238         -      }
  3239         -    }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
  3240         -      const char *zRange;
  3241         -      if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
  3242         -        zRange = "(rowid=?)";
  3243         -      }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
  3244         -        zRange = "(rowid>? AND rowid<?)";
  3245         -      }else if( flags&WHERE_BTM_LIMIT ){
  3246         -        zRange = "(rowid>?)";
  3247         -      }else{
  3248         -        assert( flags&WHERE_TOP_LIMIT);
  3249         -        zRange = "(rowid<?)";
  3250         -      }
  3251         -      sqlite3StrAccumAppendAll(&str, " USING INTEGER PRIMARY KEY ");
  3252         -      sqlite3StrAccumAppendAll(&str, zRange);
  3253         -    }
  3254         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  3255         -    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
  3256         -      sqlite3XPrintf(&str, 0, " VIRTUAL TABLE INDEX %d:%s",
  3257         -                  pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
  3258         -    }
  3259         -#endif
  3260         -#ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
  3261         -    if( pLoop->nOut>=10 ){
  3262         -      sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
  3263         -    }else{
  3264         -      sqlite3StrAccumAppend(&str, " (~1 row)", 9);
  3265         -    }
  3266         -#endif
  3267         -    zMsg = sqlite3StrAccumFinish(&str);
  3268         -    ret = sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg,P4_DYNAMIC);
  3269         -  }
  3270         -  return ret;
  3271         -}
  3272         -#else
  3273         -# define explainOneScan(u,v,w,x,y,z) 0
  3274         -#endif /* SQLITE_OMIT_EXPLAIN */
  3275         -
  3276         -#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  3277         -/*
  3278         -** Configure the VM passed as the first argument with an
  3279         -** sqlite3_stmt_scanstatus() entry corresponding to the scan used to 
  3280         -** implement level pLvl. Argument pSrclist is a pointer to the FROM 
  3281         -** clause that the scan reads data from.
  3282         -**
  3283         -** If argument addrExplain is not 0, it must be the address of an 
  3284         -** OP_Explain instruction that describes the same loop.
  3285         -*/
  3286         -static void addScanStatus(
  3287         -  Vdbe *v,                        /* Vdbe to add scanstatus entry to */
  3288         -  SrcList *pSrclist,              /* FROM clause pLvl reads data from */
  3289         -  WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
  3290         -  int addrExplain                 /* Address of OP_Explain (or 0) */
  3291         -){
  3292         -  const char *zObj = 0;
  3293         -  WhereLoop *pLoop = pLvl->pWLoop;
  3294         -  if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0  &&  pLoop->u.btree.pIndex!=0 ){
  3295         -    zObj = pLoop->u.btree.pIndex->zName;
  3296         -  }else{
  3297         -    zObj = pSrclist->a[pLvl->iFrom].zName;
  3298         -  }
  3299         -  sqlite3VdbeScanStatus(
  3300         -      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
  3301         -  );
  3302         -}
  3303         -#else
  3304         -# define addScanStatus(a, b, c, d) ((void)d)
  3305         -#endif
  3306         -
  3307         -/*
  3308         -** If the most recently coded instruction is a constant range contraint
  3309         -** that originated from the LIKE optimization, then change the P3 to be
  3310         -** pLoop->iLikeRepCntr and set P5.
  3311         -**
  3312         -** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
  3313         -** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
  3314         -** scan loop run twice, once for strings and a second time for BLOBs.
  3315         -** The OP_String opcodes on the second pass convert the upper and lower
  3316         -** bound string contants to blobs.  This routine makes the necessary changes
  3317         -** to the OP_String opcodes for that to happen.
  3318         -*/
  3319         -static void whereLikeOptimizationStringFixup(
  3320         -  Vdbe *v,                /* prepared statement under construction */
  3321         -  WhereLevel *pLevel,     /* The loop that contains the LIKE operator */
  3322         -  WhereTerm *pTerm        /* The upper or lower bound just coded */
  3323         -){
  3324         -  if( pTerm->wtFlags & TERM_LIKEOPT ){
  3325         -    VdbeOp *pOp;
  3326         -    assert( pLevel->iLikeRepCntr>0 );
  3327         -    pOp = sqlite3VdbeGetOp(v, -1);
  3328         -    assert( pOp!=0 );
  3329         -    assert( pOp->opcode==OP_String8 
  3330         -            || pTerm->pWC->pWInfo->pParse->db->mallocFailed );
  3331         -    pOp->p3 = pLevel->iLikeRepCntr;
  3332         -    pOp->p5 = 1;
  3333         -  }
  3334         -}
  3335         -
  3336         -/*
  3337         -** Generate code for the start of the iLevel-th loop in the WHERE clause
  3338         -** implementation described by pWInfo.
  3339         -*/
  3340         -static Bitmask codeOneLoopStart(
  3341         -  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
  3342         -  int iLevel,          /* Which level of pWInfo->a[] should be coded */
  3343         -  Bitmask notReady     /* Which tables are currently available */
  3344         -){
  3345         -  int j, k;            /* Loop counters */
  3346         -  int iCur;            /* The VDBE cursor for the table */
  3347         -  int addrNxt;         /* Where to jump to continue with the next IN case */
  3348         -  int omitTable;       /* True if we use the index only */
  3349         -  int bRev;            /* True if we need to scan in reverse order */
  3350         -  WhereLevel *pLevel;  /* The where level to be coded */
  3351         -  WhereLoop *pLoop;    /* The WhereLoop object being coded */
  3352         -  WhereClause *pWC;    /* Decomposition of the entire WHERE clause */
  3353         -  WhereTerm *pTerm;               /* A WHERE clause term */
  3354         -  Parse *pParse;                  /* Parsing context */
  3355         -  sqlite3 *db;                    /* Database connection */
  3356         -  Vdbe *v;                        /* The prepared stmt under constructions */
  3357         -  struct SrcList_item *pTabItem;  /* FROM clause term being coded */
  3358         -  int addrBrk;                    /* Jump here to break out of the loop */
  3359         -  int addrCont;                   /* Jump here to continue with next cycle */
  3360         -  int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
  3361         -  int iReleaseReg = 0;      /* Temp register to free before returning */
  3362         -
  3363         -  pParse = pWInfo->pParse;
  3364         -  v = pParse->pVdbe;
  3365         -  pWC = &pWInfo->sWC;
  3366         -  db = pParse->db;
  3367         -  pLevel = &pWInfo->a[iLevel];
  3368         -  pLoop = pLevel->pWLoop;
  3369         -  pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
  3370         -  iCur = pTabItem->iCursor;
  3371         -  pLevel->notReady = notReady & ~getMask(&pWInfo->sMaskSet, iCur);
  3372         -  bRev = (pWInfo->revMask>>iLevel)&1;
  3373         -  omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0 
  3374         -           && (pWInfo->wctrlFlags & WHERE_FORCE_TABLE)==0;
  3375         -  VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName));
  3376         -
  3377         -  /* Create labels for the "break" and "continue" instructions
  3378         -  ** for the current loop.  Jump to addrBrk to break out of a loop.
  3379         -  ** Jump to cont to go immediately to the next iteration of the
  3380         -  ** loop.
  3381         -  **
  3382         -  ** When there is an IN operator, we also have a "addrNxt" label that
  3383         -  ** means to continue with the next IN value combination.  When
  3384         -  ** there are no IN operators in the constraints, the "addrNxt" label
  3385         -  ** is the same as "addrBrk".
  3386         -  */
  3387         -  addrBrk = pLevel->addrBrk = pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
  3388         -  addrCont = pLevel->addrCont = sqlite3VdbeMakeLabel(v);
  3389         -
  3390         -  /* If this is the right table of a LEFT OUTER JOIN, allocate and
  3391         -  ** initialize a memory cell that records if this table matches any
  3392         -  ** row of the left table of the join.
  3393         -  */
  3394         -  if( pLevel->iFrom>0 && (pTabItem[0].jointype & JT_LEFT)!=0 ){
  3395         -    pLevel->iLeftJoin = ++pParse->nMem;
  3396         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLeftJoin);
  3397         -    VdbeComment((v, "init LEFT JOIN no-match flag"));
  3398         -  }
  3399         -
  3400         -  /* Special case of a FROM clause subquery implemented as a co-routine */
  3401         -  if( pTabItem->viaCoroutine ){
  3402         -    int regYield = pTabItem->regReturn;
  3403         -    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pTabItem->addrFillSub);
  3404         -    pLevel->p2 =  sqlite3VdbeAddOp2(v, OP_Yield, regYield, addrBrk);
  3405         -    VdbeCoverage(v);
  3406         -    VdbeComment((v, "next row of \"%s\"", pTabItem->pTab->zName));
  3407         -    pLevel->op = OP_Goto;
  3408         -  }else
  3409         -
  3410         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  3411         -  if(  (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  3412         -    /* Case 1:  The table is a virtual-table.  Use the VFilter and VNext
  3413         -    **          to access the data.
  3414         -    */
  3415         -    int iReg;   /* P3 Value for OP_VFilter */
  3416         -    int addrNotFound;
  3417         -    int nConstraint = pLoop->nLTerm;
  3418         -
  3419         -    sqlite3ExprCachePush(pParse);
  3420         -    iReg = sqlite3GetTempRange(pParse, nConstraint+2);
  3421         -    addrNotFound = pLevel->addrBrk;
  3422         -    for(j=0; j<nConstraint; j++){
  3423         -      int iTarget = iReg+j+2;
  3424         -      pTerm = pLoop->aLTerm[j];
  3425         -      if( pTerm==0 ) continue;
  3426         -      if( pTerm->eOperator & WO_IN ){
  3427         -        codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
  3428         -        addrNotFound = pLevel->addrNxt;
  3429         -      }else{
  3430         -        sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget);
  3431         -      }
  3432         -    }
  3433         -    sqlite3VdbeAddOp2(v, OP_Integer, pLoop->u.vtab.idxNum, iReg);
  3434         -    sqlite3VdbeAddOp2(v, OP_Integer, nConstraint, iReg+1);
  3435         -    sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg,
  3436         -                      pLoop->u.vtab.idxStr,
  3437         -                      pLoop->u.vtab.needFree ? P4_MPRINTF : P4_STATIC);
  3438         -    VdbeCoverage(v);
  3439         -    pLoop->u.vtab.needFree = 0;
  3440         -    for(j=0; j<nConstraint && j<16; j++){
  3441         -      if( (pLoop->u.vtab.omitMask>>j)&1 ){
  3442         -        disableTerm(pLevel, pLoop->aLTerm[j]);
  3443         -      }
  3444         -    }
  3445         -    pLevel->op = OP_VNext;
  3446         -    pLevel->p1 = iCur;
  3447         -    pLevel->p2 = sqlite3VdbeCurrentAddr(v);
  3448         -    sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
  3449         -    sqlite3ExprCachePop(pParse);
  3450         -  }else
  3451         -#endif /* SQLITE_OMIT_VIRTUALTABLE */
  3452         -
  3453         -  if( (pLoop->wsFlags & WHERE_IPK)!=0
  3454         -   && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0
  3455         -  ){
  3456         -    /* Case 2:  We can directly reference a single row using an
  3457         -    **          equality comparison against the ROWID field.  Or
  3458         -    **          we reference multiple rows using a "rowid IN (...)"
  3459         -    **          construct.
  3460         -    */
  3461         -    assert( pLoop->u.btree.nEq==1 );
  3462         -    pTerm = pLoop->aLTerm[0];
  3463         -    assert( pTerm!=0 );
  3464         -    assert( pTerm->pExpr!=0 );
  3465         -    assert( omitTable==0 );
  3466         -    testcase( pTerm->wtFlags & TERM_VIRTUAL );
  3467         -    iReleaseReg = ++pParse->nMem;
  3468         -    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
  3469         -    if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
  3470         -    addrNxt = pLevel->addrNxt;
  3471         -    sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); VdbeCoverage(v);
  3472         -    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
  3473         -    VdbeCoverage(v);
  3474         -    sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
  3475         -    sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3476         -    VdbeComment((v, "pk"));
  3477         -    pLevel->op = OP_Noop;
  3478         -  }else if( (pLoop->wsFlags & WHERE_IPK)!=0
  3479         -         && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
  3480         -  ){
  3481         -    /* Case 3:  We have an inequality comparison against the ROWID field.
  3482         -    */
  3483         -    int testOp = OP_Noop;
  3484         -    int start;
  3485         -    int memEndValue = 0;
  3486         -    WhereTerm *pStart, *pEnd;
  3487         -
  3488         -    assert( omitTable==0 );
  3489         -    j = 0;
  3490         -    pStart = pEnd = 0;
  3491         -    if( pLoop->wsFlags & WHERE_BTM_LIMIT ) pStart = pLoop->aLTerm[j++];
  3492         -    if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++];
  3493         -    assert( pStart!=0 || pEnd!=0 );
  3494         -    if( bRev ){
  3495         -      pTerm = pStart;
  3496         -      pStart = pEnd;
  3497         -      pEnd = pTerm;
  3498         -    }
  3499         -    if( pStart ){
  3500         -      Expr *pX;             /* The expression that defines the start bound */
  3501         -      int r1, rTemp;        /* Registers for holding the start boundary */
  3502         -
  3503         -      /* The following constant maps TK_xx codes into corresponding 
  3504         -      ** seek opcodes.  It depends on a particular ordering of TK_xx
  3505         -      */
  3506         -      const u8 aMoveOp[] = {
  3507         -           /* TK_GT */  OP_SeekGT,
  3508         -           /* TK_LE */  OP_SeekLE,
  3509         -           /* TK_LT */  OP_SeekLT,
  3510         -           /* TK_GE */  OP_SeekGE
  3511         -      };
  3512         -      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
  3513         -      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
  3514         -      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */
  3515         -
  3516         -      assert( (pStart->wtFlags & TERM_VNULL)==0 );
  3517         -      testcase( pStart->wtFlags & TERM_VIRTUAL );
  3518         -      pX = pStart->pExpr;
  3519         -      assert( pX!=0 );
  3520         -      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
  3521         -      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
  3522         -      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
  3523         -      VdbeComment((v, "pk"));
  3524         -      VdbeCoverageIf(v, pX->op==TK_GT);
  3525         -      VdbeCoverageIf(v, pX->op==TK_LE);
  3526         -      VdbeCoverageIf(v, pX->op==TK_LT);
  3527         -      VdbeCoverageIf(v, pX->op==TK_GE);
  3528         -      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
  3529         -      sqlite3ReleaseTempReg(pParse, rTemp);
  3530         -      disableTerm(pLevel, pStart);
  3531         -    }else{
  3532         -      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
  3533         -      VdbeCoverageIf(v, bRev==0);
  3534         -      VdbeCoverageIf(v, bRev!=0);
  3535         -    }
  3536         -    if( pEnd ){
  3537         -      Expr *pX;
  3538         -      pX = pEnd->pExpr;
  3539         -      assert( pX!=0 );
  3540         -      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
  3541         -      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
  3542         -      testcase( pEnd->wtFlags & TERM_VIRTUAL );
  3543         -      memEndValue = ++pParse->nMem;
  3544         -      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
  3545         -      if( pX->op==TK_LT || pX->op==TK_GT ){
  3546         -        testOp = bRev ? OP_Le : OP_Ge;
  3547         -      }else{
  3548         -        testOp = bRev ? OP_Lt : OP_Gt;
  3549         -      }
  3550         -      disableTerm(pLevel, pEnd);
  3551         -    }
  3552         -    start = sqlite3VdbeCurrentAddr(v);
  3553         -    pLevel->op = bRev ? OP_Prev : OP_Next;
  3554         -    pLevel->p1 = iCur;
  3555         -    pLevel->p2 = start;
  3556         -    assert( pLevel->p5==0 );
  3557         -    if( testOp!=OP_Noop ){
  3558         -      iRowidReg = ++pParse->nMem;
  3559         -      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, iRowidReg);
  3560         -      sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3561         -      sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, iRowidReg);
  3562         -      VdbeCoverageIf(v, testOp==OP_Le);
  3563         -      VdbeCoverageIf(v, testOp==OP_Lt);
  3564         -      VdbeCoverageIf(v, testOp==OP_Ge);
  3565         -      VdbeCoverageIf(v, testOp==OP_Gt);
  3566         -      sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);
  3567         -    }
  3568         -  }else if( pLoop->wsFlags & WHERE_INDEXED ){
  3569         -    /* Case 4: A scan using an index.
  3570         -    **
  3571         -    **         The WHERE clause may contain zero or more equality 
  3572         -    **         terms ("==" or "IN" operators) that refer to the N
  3573         -    **         left-most columns of the index. It may also contain
  3574         -    **         inequality constraints (>, <, >= or <=) on the indexed
  3575         -    **         column that immediately follows the N equalities. Only 
  3576         -    **         the right-most column can be an inequality - the rest must
  3577         -    **         use the "==" and "IN" operators. For example, if the 
  3578         -    **         index is on (x,y,z), then the following clauses are all 
  3579         -    **         optimized:
  3580         -    **
  3581         -    **            x=5
  3582         -    **            x=5 AND y=10
  3583         -    **            x=5 AND y<10
  3584         -    **            x=5 AND y>5 AND y<10
  3585         -    **            x=5 AND y=5 AND z<=10
  3586         -    **
  3587         -    **         The z<10 term of the following cannot be used, only
  3588         -    **         the x=5 term:
  3589         -    **
  3590         -    **            x=5 AND z<10
  3591         -    **
  3592         -    **         N may be zero if there are inequality constraints.
  3593         -    **         If there are no inequality constraints, then N is at
  3594         -    **         least one.
  3595         -    **
  3596         -    **         This case is also used when there are no WHERE clause
  3597         -    **         constraints but an index is selected anyway, in order
  3598         -    **         to force the output order to conform to an ORDER BY.
  3599         -    */  
  3600         -    static const u8 aStartOp[] = {
  3601         -      0,
  3602         -      0,
  3603         -      OP_Rewind,           /* 2: (!start_constraints && startEq &&  !bRev) */
  3604         -      OP_Last,             /* 3: (!start_constraints && startEq &&   bRev) */
  3605         -      OP_SeekGT,           /* 4: (start_constraints  && !startEq && !bRev) */
  3606         -      OP_SeekLT,           /* 5: (start_constraints  && !startEq &&  bRev) */
  3607         -      OP_SeekGE,           /* 6: (start_constraints  &&  startEq && !bRev) */
  3608         -      OP_SeekLE            /* 7: (start_constraints  &&  startEq &&  bRev) */
  3609         -    };
  3610         -    static const u8 aEndOp[] = {
  3611         -      OP_IdxGE,            /* 0: (end_constraints && !bRev && !endEq) */
  3612         -      OP_IdxGT,            /* 1: (end_constraints && !bRev &&  endEq) */
  3613         -      OP_IdxLE,            /* 2: (end_constraints &&  bRev && !endEq) */
  3614         -      OP_IdxLT,            /* 3: (end_constraints &&  bRev &&  endEq) */
  3615         -    };
  3616         -    u16 nEq = pLoop->u.btree.nEq;     /* Number of == or IN terms */
  3617         -    int regBase;                 /* Base register holding constraint values */
  3618         -    WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
  3619         -    WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
  3620         -    int startEq;                 /* True if range start uses ==, >= or <= */
  3621         -    int endEq;                   /* True if range end uses ==, >= or <= */
  3622         -    int start_constraints;       /* Start of range is constrained */
  3623         -    int nConstraint;             /* Number of constraint terms */
  3624         -    Index *pIdx;                 /* The index we will be using */
  3625         -    int iIdxCur;                 /* The VDBE cursor for the index */
  3626         -    int nExtraReg = 0;           /* Number of extra registers needed */
  3627         -    int op;                      /* Instruction opcode */
  3628         -    char *zStartAff;             /* Affinity for start of range constraint */
  3629         -    char cEndAff = 0;            /* Affinity for end of range constraint */
  3630         -    u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
  3631         -    u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */
  3632         -
  3633         -    pIdx = pLoop->u.btree.pIndex;
  3634         -    iIdxCur = pLevel->iIdxCur;
  3635         -    assert( nEq>=pLoop->nSkip );
  3636         -
  3637         -    /* If this loop satisfies a sort order (pOrderBy) request that 
  3638         -    ** was passed to this function to implement a "SELECT min(x) ..." 
  3639         -    ** query, then the caller will only allow the loop to run for
  3640         -    ** a single iteration. This means that the first row returned
  3641         -    ** should not have a NULL value stored in 'x'. If column 'x' is
  3642         -    ** the first one after the nEq equality constraints in the index,
  3643         -    ** this requires some special handling.
  3644         -    */
  3645         -    assert( pWInfo->pOrderBy==0
  3646         -         || pWInfo->pOrderBy->nExpr==1
  3647         -         || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
  3648         -    if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
  3649         -     && pWInfo->nOBSat>0
  3650         -     && (pIdx->nKeyCol>nEq)
  3651         -    ){
  3652         -      assert( pLoop->nSkip==0 );
  3653         -      bSeekPastNull = 1;
  3654         -      nExtraReg = 1;
  3655         -    }
  3656         -
  3657         -    /* Find any inequality constraint terms for the start and end 
  3658         -    ** of the range. 
  3659         -    */
  3660         -    j = nEq;
  3661         -    if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
  3662         -      pRangeStart = pLoop->aLTerm[j++];
  3663         -      nExtraReg = 1;
  3664         -      /* Like optimization range constraints always occur in pairs */
  3665         -      assert( (pRangeStart->wtFlags & TERM_LIKEOPT)==0 || 
  3666         -              (pLoop->wsFlags & WHERE_TOP_LIMIT)!=0 );
  3667         -    }
  3668         -    if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
  3669         -      pRangeEnd = pLoop->aLTerm[j++];
  3670         -      nExtraReg = 1;
  3671         -      if( (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0 ){
  3672         -        assert( pRangeStart!=0 );                     /* LIKE opt constraints */
  3673         -        assert( pRangeStart->wtFlags & TERM_LIKEOPT );   /* occur in pairs */
  3674         -        pLevel->iLikeRepCntr = ++pParse->nMem;
  3675         -        testcase( bRev );
  3676         -        testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
  3677         -        sqlite3VdbeAddOp2(v, OP_Integer,
  3678         -                          bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
  3679         -                          pLevel->iLikeRepCntr);
  3680         -        VdbeComment((v, "LIKE loop counter"));
  3681         -        pLevel->addrLikeRep = sqlite3VdbeCurrentAddr(v);
  3682         -      }
  3683         -      if( pRangeStart==0
  3684         -       && (j = pIdx->aiColumn[nEq])>=0 
  3685         -       && pIdx->pTable->aCol[j].notNull==0
  3686         -      ){
  3687         -        bSeekPastNull = 1;
  3688         -      }
  3689         -    }
  3690         -    assert( pRangeEnd==0 || (pRangeEnd->wtFlags & TERM_VNULL)==0 );
  3691         -
  3692         -    /* Generate code to evaluate all constraint terms using == or IN
  3693         -    ** and store the values of those terms in an array of registers
  3694         -    ** starting at regBase.
  3695         -    */
  3696         -    regBase = codeAllEqualityTerms(pParse,pLevel,bRev,nExtraReg,&zStartAff);
  3697         -    assert( zStartAff==0 || sqlite3Strlen30(zStartAff)>=nEq );
  3698         -    if( zStartAff ) cEndAff = zStartAff[nEq];
  3699         -    addrNxt = pLevel->addrNxt;
  3700         -
  3701         -    /* If we are doing a reverse order scan on an ascending index, or
  3702         -    ** a forward order scan on a descending index, interchange the 
  3703         -    ** start and end terms (pRangeStart and pRangeEnd).
  3704         -    */
  3705         -    if( (nEq<pIdx->nKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC))
  3706         -     || (bRev && pIdx->nKeyCol==nEq)
  3707         -    ){
  3708         -      SWAP(WhereTerm *, pRangeEnd, pRangeStart);
  3709         -      SWAP(u8, bSeekPastNull, bStopAtNull);
  3710         -    }
  3711         -
  3712         -    testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 );
  3713         -    testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 );
  3714         -    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 );
  3715         -    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 );
  3716         -    startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
  3717         -    endEq =   !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE);
  3718         -    start_constraints = pRangeStart || nEq>0;
  3719         -
  3720         -    /* Seek the index cursor to the start of the range. */
  3721         -    nConstraint = nEq;
  3722         -    if( pRangeStart ){
  3723         -      Expr *pRight = pRangeStart->pExpr->pRight;
  3724         -      sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3725         -      whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
  3726         -      if( (pRangeStart->wtFlags & TERM_VNULL)==0
  3727         -       && sqlite3ExprCanBeNull(pRight)
  3728         -      ){
  3729         -        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3730         -        VdbeCoverage(v);
  3731         -      }
  3732         -      if( zStartAff ){
  3733         -        if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_BLOB){
  3734         -          /* Since the comparison is to be performed with no conversions
  3735         -          ** applied to the operands, set the affinity to apply to pRight to 
  3736         -          ** SQLITE_AFF_BLOB.  */
  3737         -          zStartAff[nEq] = SQLITE_AFF_BLOB;
  3738         -        }
  3739         -        if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){
  3740         -          zStartAff[nEq] = SQLITE_AFF_BLOB;
  3741         -        }
  3742         -      }  
  3743         -      nConstraint++;
  3744         -      testcase( pRangeStart->wtFlags & TERM_VIRTUAL );
  3745         -    }else if( bSeekPastNull ){
  3746         -      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
  3747         -      nConstraint++;
  3748         -      startEq = 0;
  3749         -      start_constraints = 1;
  3750         -    }
  3751         -    codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
  3752         -    op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
  3753         -    assert( op!=0 );
  3754         -    sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  3755         -    VdbeCoverage(v);
  3756         -    VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
  3757         -    VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
  3758         -    VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
  3759         -    VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
  3760         -    VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
  3761         -    VdbeCoverageIf(v, op==OP_SeekLT);  testcase( op==OP_SeekLT );
  3762         -
  3763         -    /* Load the value for the inequality constraint at the end of the
  3764         -    ** range (if any).
  3765         -    */
  3766         -    nConstraint = nEq;
  3767         -    if( pRangeEnd ){
  3768         -      Expr *pRight = pRangeEnd->pExpr->pRight;
  3769         -      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
  3770         -      sqlite3ExprCode(pParse, pRight, regBase+nEq);
  3771         -      whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
  3772         -      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
  3773         -       && sqlite3ExprCanBeNull(pRight)
  3774         -      ){
  3775         -        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
  3776         -        VdbeCoverage(v);
  3777         -      }
  3778         -      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_BLOB
  3779         -       && !sqlite3ExprNeedsNoAffinityChange(pRight, cEndAff)
  3780         -      ){
  3781         -        codeApplyAffinity(pParse, regBase+nEq, 1, &cEndAff);
  3782         -      }
  3783         -      nConstraint++;
  3784         -      testcase( pRangeEnd->wtFlags & TERM_VIRTUAL );
  3785         -    }else if( bStopAtNull ){
  3786         -      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
  3787         -      endEq = 0;
  3788         -      nConstraint++;
  3789         -    }
  3790         -    sqlite3DbFree(db, zStartAff);
  3791         -
  3792         -    /* Top of the loop body */
  3793         -    pLevel->p2 = sqlite3VdbeCurrentAddr(v);
  3794         -
  3795         -    /* Check if the index cursor is past the end of the range. */
  3796         -    if( nConstraint ){
  3797         -      op = aEndOp[bRev*2 + endEq];
  3798         -      sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
  3799         -      testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
  3800         -      testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
  3801         -      testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
  3802         -      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
  3803         -    }
  3804         -
  3805         -    /* Seek the table cursor, if required */
  3806         -    disableTerm(pLevel, pRangeStart);
  3807         -    disableTerm(pLevel, pRangeEnd);
  3808         -    if( omitTable ){
  3809         -      /* pIdx is a covering index.  No need to access the main table. */
  3810         -    }else if( HasRowid(pIdx->pTable) ){
  3811         -      iRowidReg = ++pParse->nMem;
  3812         -      sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  3813         -      sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3814         -      sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg);  /* Deferred seek */
  3815         -    }else if( iCur!=iIdxCur ){
  3816         -      Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
  3817         -      iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol);
  3818         -      for(j=0; j<pPk->nKeyCol; j++){
  3819         -        k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
  3820         -        sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
  3821         -      }
  3822         -      sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
  3823         -                           iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
  3824         -    }
  3825         -
  3826         -    /* Record the instruction used to terminate the loop. Disable 
  3827         -    ** WHERE clause terms made redundant by the index range scan.
  3828         -    */
  3829         -    if( pLoop->wsFlags & WHERE_ONEROW ){
  3830         -      pLevel->op = OP_Noop;
  3831         -    }else if( bRev ){
  3832         -      pLevel->op = OP_Prev;
  3833         -    }else{
  3834         -      pLevel->op = OP_Next;
  3835         -    }
  3836         -    pLevel->p1 = iIdxCur;
  3837         -    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
  3838         -    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
  3839         -      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  3840         -    }else{
  3841         -      assert( pLevel->p5==0 );
  3842         -    }
  3843         -  }else
  3844         -
  3845         -#ifndef SQLITE_OMIT_OR_OPTIMIZATION
  3846         -  if( pLoop->wsFlags & WHERE_MULTI_OR ){
  3847         -    /* Case 5:  Two or more separately indexed terms connected by OR
  3848         -    **
  3849         -    ** Example:
  3850         -    **
  3851         -    **   CREATE TABLE t1(a,b,c,d);
  3852         -    **   CREATE INDEX i1 ON t1(a);
  3853         -    **   CREATE INDEX i2 ON t1(b);
  3854         -    **   CREATE INDEX i3 ON t1(c);
  3855         -    **
  3856         -    **   SELECT * FROM t1 WHERE a=5 OR b=7 OR (c=11 AND d=13)
  3857         -    **
  3858         -    ** In the example, there are three indexed terms connected by OR.
  3859         -    ** The top of the loop looks like this:
  3860         -    **
  3861         -    **          Null       1                # Zero the rowset in reg 1
  3862         -    **
  3863         -    ** Then, for each indexed term, the following. The arguments to
  3864         -    ** RowSetTest are such that the rowid of the current row is inserted
  3865         -    ** into the RowSet. If it is already present, control skips the
  3866         -    ** Gosub opcode and jumps straight to the code generated by WhereEnd().
  3867         -    **
  3868         -    **        sqlite3WhereBegin(<term>)
  3869         -    **          RowSetTest                  # Insert rowid into rowset
  3870         -    **          Gosub      2 A
  3871         -    **        sqlite3WhereEnd()
  3872         -    **
  3873         -    ** Following the above, code to terminate the loop. Label A, the target
  3874         -    ** of the Gosub above, jumps to the instruction right after the Goto.
  3875         -    **
  3876         -    **          Null       1                # Zero the rowset in reg 1
  3877         -    **          Goto       B                # The loop is finished.
  3878         -    **
  3879         -    **       A: <loop body>                 # Return data, whatever.
  3880         -    **
  3881         -    **          Return     2                # Jump back to the Gosub
  3882         -    **
  3883         -    **       B: <after the loop>
  3884         -    **
  3885         -    ** Added 2014-05-26: If the table is a WITHOUT ROWID table, then
  3886         -    ** use an ephemeral index instead of a RowSet to record the primary
  3887         -    ** keys of the rows we have already seen.
  3888         -    **
  3889         -    */
  3890         -    WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
  3891         -    SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
  3892         -    Index *pCov = 0;             /* Potential covering index (or NULL) */
  3893         -    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */
  3894         -
  3895         -    int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
  3896         -    int regRowset = 0;                        /* Register for RowSet object */
  3897         -    int regRowid = 0;                         /* Register holding rowid */
  3898         -    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
  3899         -    int iRetInit;                             /* Address of regReturn init */
  3900         -    int untestedTerms = 0;             /* Some terms not completely tested */
  3901         -    int ii;                            /* Loop counter */
  3902         -    u16 wctrlFlags;                    /* Flags for sub-WHERE clause */
  3903         -    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
  3904         -    Table *pTab = pTabItem->pTab;
  3905         -   
  3906         -    pTerm = pLoop->aLTerm[0];
  3907         -    assert( pTerm!=0 );
  3908         -    assert( pTerm->eOperator & WO_OR );
  3909         -    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
  3910         -    pOrWc = &pTerm->u.pOrInfo->wc;
  3911         -    pLevel->op = OP_Return;
  3912         -    pLevel->p1 = regReturn;
  3913         -
  3914         -    /* Set up a new SrcList in pOrTab containing the table being scanned
  3915         -    ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
  3916         -    ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
  3917         -    */
  3918         -    if( pWInfo->nLevel>1 ){
  3919         -      int nNotReady;                 /* The number of notReady tables */
  3920         -      struct SrcList_item *origSrc;     /* Original list of tables */
  3921         -      nNotReady = pWInfo->nLevel - iLevel - 1;
  3922         -      pOrTab = sqlite3StackAllocRaw(db,
  3923         -                            sizeof(*pOrTab)+ nNotReady*sizeof(pOrTab->a[0]));
  3924         -      if( pOrTab==0 ) return notReady;
  3925         -      pOrTab->nAlloc = (u8)(nNotReady + 1);
  3926         -      pOrTab->nSrc = pOrTab->nAlloc;
  3927         -      memcpy(pOrTab->a, pTabItem, sizeof(*pTabItem));
  3928         -      origSrc = pWInfo->pTabList->a;
  3929         -      for(k=1; k<=nNotReady; k++){
  3930         -        memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
  3931         -      }
  3932         -    }else{
  3933         -      pOrTab = pWInfo->pTabList;
  3934         -    }
  3935         -
  3936         -    /* Initialize the rowset register to contain NULL. An SQL NULL is 
  3937         -    ** equivalent to an empty rowset.  Or, create an ephemeral index
  3938         -    ** capable of holding primary keys in the case of a WITHOUT ROWID.
  3939         -    **
  3940         -    ** Also initialize regReturn to contain the address of the instruction 
  3941         -    ** immediately following the OP_Return at the bottom of the loop. This
  3942         -    ** is required in a few obscure LEFT JOIN cases where control jumps
  3943         -    ** over the top of the loop into the body of it. In this case the 
  3944         -    ** correct response for the end-of-loop code (the OP_Return) is to 
  3945         -    ** fall through to the next instruction, just as an OP_Next does if
  3946         -    ** called on an uninitialized cursor.
  3947         -    */
  3948         -    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3949         -      if( HasRowid(pTab) ){
  3950         -        regRowset = ++pParse->nMem;
  3951         -        sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset);
  3952         -      }else{
  3953         -        Index *pPk = sqlite3PrimaryKeyIndex(pTab);
  3954         -        regRowset = pParse->nTab++;
  3955         -        sqlite3VdbeAddOp2(v, OP_OpenEphemeral, regRowset, pPk->nKeyCol);
  3956         -        sqlite3VdbeSetP4KeyInfo(pParse, pPk);
  3957         -      }
  3958         -      regRowid = ++pParse->nMem;
  3959         -    }
  3960         -    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);
  3961         -
  3962         -    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
  3963         -    ** Then for every term xN, evaluate as the subexpression: xN AND z
  3964         -    ** That way, terms in y that are factored into the disjunction will
  3965         -    ** be picked up by the recursive calls to sqlite3WhereBegin() below.
  3966         -    **
  3967         -    ** Actually, each subexpression is converted to "xN AND w" where w is
  3968         -    ** the "interesting" terms of z - terms that did not originate in the
  3969         -    ** ON or USING clause of a LEFT JOIN, and terms that are usable as 
  3970         -    ** indices.
  3971         -    **
  3972         -    ** This optimization also only applies if the (x1 OR x2 OR ...) term
  3973         -    ** is not contained in the ON clause of a LEFT JOIN.
  3974         -    ** See ticket http://www.sqlite.org/src/info/f2369304e4
  3975         -    */
  3976         -    if( pWC->nTerm>1 ){
  3977         -      int iTerm;
  3978         -      for(iTerm=0; iTerm<pWC->nTerm; iTerm++){
  3979         -        Expr *pExpr = pWC->a[iTerm].pExpr;
  3980         -        if( &pWC->a[iTerm] == pTerm ) continue;
  3981         -        if( ExprHasProperty(pExpr, EP_FromJoin) ) continue;
  3982         -        if( (pWC->a[iTerm].wtFlags & TERM_VIRTUAL)!=0 ) continue;
  3983         -        if( (pWC->a[iTerm].eOperator & WO_ALL)==0 ) continue;
  3984         -        testcase( pWC->a[iTerm].wtFlags & TERM_ORINFO );
  3985         -        pExpr = sqlite3ExprDup(db, pExpr, 0);
  3986         -        pAndExpr = sqlite3ExprAnd(db, pAndExpr, pExpr);
  3987         -      }
  3988         -      if( pAndExpr ){
  3989         -        pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
  3990         -      }
  3991         -    }
  3992         -
  3993         -    /* Run a separate WHERE clause for each term of the OR clause.  After
  3994         -    ** eliminating duplicates from other WHERE clauses, the action for each
  3995         -    ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
  3996         -    */
  3997         -    wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
  3998         -                | WHERE_FORCE_TABLE
  3999         -                | WHERE_ONETABLE_ONLY
  4000         -                | WHERE_NO_AUTOINDEX;
  4001         -    for(ii=0; ii<pOrWc->nTerm; ii++){
  4002         -      WhereTerm *pOrTerm = &pOrWc->a[ii];
  4003         -      if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
  4004         -        WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
  4005         -        Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
  4006         -        int j1 = 0;                     /* Address of jump operation */
  4007         -        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
  4008         -          pAndExpr->pLeft = pOrExpr;
  4009         -          pOrExpr = pAndExpr;
  4010         -        }
  4011         -        /* Loop through table entries that match term pOrTerm. */
  4012         -        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
  4013         -        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
  4014         -                                      wctrlFlags, iCovCur);
  4015         -        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
  4016         -        if( pSubWInfo ){
  4017         -          WhereLoop *pSubLoop;
  4018         -          int addrExplain = explainOneScan(
  4019         -              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
  4020         -          );
  4021         -          addScanStatus(v, pOrTab, &pSubWInfo->a[0], addrExplain);
  4022         -
  4023         -          /* This is the sub-WHERE clause body.  First skip over
  4024         -          ** duplicate rows from prior sub-WHERE clauses, and record the
  4025         -          ** rowid (or PRIMARY KEY) for the current row so that the same
  4026         -          ** row will be skipped in subsequent sub-WHERE clauses.
  4027         -          */
  4028         -          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  4029         -            int r;
  4030         -            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  4031         -            if( HasRowid(pTab) ){
  4032         -              r = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, regRowid, 0);
  4033         -              j1 = sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, 0, r,iSet);
  4034         -              VdbeCoverage(v);
  4035         -            }else{
  4036         -              Index *pPk = sqlite3PrimaryKeyIndex(pTab);
  4037         -              int nPk = pPk->nKeyCol;
  4038         -              int iPk;
  4039         -
  4040         -              /* Read the PK into an array of temp registers. */
  4041         -              r = sqlite3GetTempRange(pParse, nPk);
  4042         -              for(iPk=0; iPk<nPk; iPk++){
  4043         -                int iCol = pPk->aiColumn[iPk];
  4044         -                sqlite3ExprCodeGetColumn(pParse, pTab, iCol, iCur, r+iPk, 0);
  4045         -              }
  4046         -
  4047         -              /* Check if the temp table already contains this key. If so,
  4048         -              ** the row has already been included in the result set and
  4049         -              ** can be ignored (by jumping past the Gosub below). Otherwise,
  4050         -              ** insert the key into the temp table and proceed with processing
  4051         -              ** the row.
  4052         -              **
  4053         -              ** Use some of the same optimizations as OP_RowSetTest: If iSet
  4054         -              ** is zero, assume that the key cannot already be present in
  4055         -              ** the temp table. And if iSet is -1, assume that there is no 
  4056         -              ** need to insert the key into the temp table, as it will never 
  4057         -              ** be tested for.  */ 
  4058         -              if( iSet ){
  4059         -                j1 = sqlite3VdbeAddOp4Int(v, OP_Found, regRowset, 0, r, nPk);
  4060         -                VdbeCoverage(v);
  4061         -              }
  4062         -              if( iSet>=0 ){
  4063         -                sqlite3VdbeAddOp3(v, OP_MakeRecord, r, nPk, regRowid);
  4064         -                sqlite3VdbeAddOp3(v, OP_IdxInsert, regRowset, regRowid, 0);
  4065         -                if( iSet ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  4066         -              }
  4067         -
  4068         -              /* Release the array of temp registers */
  4069         -              sqlite3ReleaseTempRange(pParse, r, nPk);
  4070         -            }
  4071         -          }
  4072         -
  4073         -          /* Invoke the main loop body as a subroutine */
  4074         -          sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);
  4075         -
  4076         -          /* Jump here (skipping the main loop body subroutine) if the
  4077         -          ** current sub-WHERE row is a duplicate from prior sub-WHEREs. */
  4078         -          if( j1 ) sqlite3VdbeJumpHere(v, j1);
  4079         -
  4080         -          /* The pSubWInfo->untestedTerms flag means that this OR term
  4081         -          ** contained one or more AND term from a notReady table.  The
  4082         -          ** terms from the notReady table could not be tested and will
  4083         -          ** need to be tested later.
  4084         -          */
  4085         -          if( pSubWInfo->untestedTerms ) untestedTerms = 1;
  4086         -
  4087         -          /* If all of the OR-connected terms are optimized using the same
  4088         -          ** index, and the index is opened using the same cursor number
  4089         -          ** by each call to sqlite3WhereBegin() made by this loop, it may
  4090         -          ** be possible to use that index as a covering index.
  4091         -          **
  4092         -          ** If the call to sqlite3WhereBegin() above resulted in a scan that
  4093         -          ** uses an index, and this is either the first OR-connected term
  4094         -          ** processed or the index is the same as that used by all previous
  4095         -          ** terms, set pCov to the candidate covering index. Otherwise, set 
  4096         -          ** pCov to NULL to indicate that no candidate covering index will 
  4097         -          ** be available.
  4098         -          */
  4099         -          pSubLoop = pSubWInfo->a[0].pWLoop;
  4100         -          assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
  4101         -          if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
  4102         -           && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
  4103         -           && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex))
  4104         -          ){
  4105         -            assert( pSubWInfo->a[0].iIdxCur==iCovCur );
  4106         -            pCov = pSubLoop->u.btree.pIndex;
  4107         -            wctrlFlags |= WHERE_REOPEN_IDX;
  4108         -          }else{
  4109         -            pCov = 0;
  4110         -          }
  4111         -
  4112         -          /* Finish the loop through table entries that match term pOrTerm. */
  4113         -          sqlite3WhereEnd(pSubWInfo);
  4114         -        }
  4115         -      }
  4116         -    }
  4117         -    pLevel->u.pCovidx = pCov;
  4118         -    if( pCov ) pLevel->iIdxCur = iCovCur;
  4119         -    if( pAndExpr ){
  4120         -      pAndExpr->pLeft = 0;
  4121         -      sqlite3ExprDelete(db, pAndExpr);
  4122         -    }
  4123         -    sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
  4124         -    sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
  4125         -    sqlite3VdbeResolveLabel(v, iLoopBody);
  4126         -
  4127         -    if( pWInfo->nLevel>1 ) sqlite3StackFree(db, pOrTab);
  4128         -    if( !untestedTerms ) disableTerm(pLevel, pTerm);
  4129         -  }else
  4130         -#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  4131         -
  4132         -  {
  4133         -    /* Case 6:  There is no usable index.  We must do a complete
  4134         -    **          scan of the entire table.
  4135         -    */
  4136         -    static const u8 aStep[] = { OP_Next, OP_Prev };
  4137         -    static const u8 aStart[] = { OP_Rewind, OP_Last };
  4138         -    assert( bRev==0 || bRev==1 );
  4139         -    if( pTabItem->isRecursive ){
  4140         -      /* Tables marked isRecursive have only a single row that is stored in
  4141         -      ** a pseudo-cursor.  No need to Rewind or Next such cursors. */
  4142         -      pLevel->op = OP_Noop;
  4143         -    }else{
  4144         -      pLevel->op = aStep[bRev];
  4145         -      pLevel->p1 = iCur;
  4146         -      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
  4147         -      VdbeCoverageIf(v, bRev==0);
  4148         -      VdbeCoverageIf(v, bRev!=0);
  4149         -      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  4150         -    }
  4151         -  }
  4152         -
  4153         -#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  4154         -  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
  4155         -#endif
  4156         -
  4157         -  /* Insert code to test every subexpression that can be completely
  4158         -  ** computed using the current set of tables.
  4159         -  */
  4160         -  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  4161         -    Expr *pE;
  4162         -    int skipLikeAddr = 0;
  4163         -    testcase( pTerm->wtFlags & TERM_VIRTUAL );
  4164         -    testcase( pTerm->wtFlags & TERM_CODED );
  4165         -    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  4166         -    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
  4167         -      testcase( pWInfo->untestedTerms==0
  4168         -               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
  4169         -      pWInfo->untestedTerms = 1;
  4170         -      continue;
  4171         -    }
  4172         -    pE = pTerm->pExpr;
  4173         -    assert( pE!=0 );
  4174         -    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
  4175         -      continue;
  4176         -    }
  4177         -    if( pTerm->wtFlags & TERM_LIKECOND ){
  4178         -      assert( pLevel->iLikeRepCntr>0 );
  4179         -      skipLikeAddr = sqlite3VdbeAddOp1(v, OP_IfNot, pLevel->iLikeRepCntr);
  4180         -      VdbeCoverage(v);
  4181         -    }
  4182         -    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
  4183         -    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
  4184         -    pTerm->wtFlags |= TERM_CODED;
  4185         -  }
  4186         -
  4187         -  /* Insert code to test for implied constraints based on transitivity
  4188         -  ** of the "==" operator.
  4189         -  **
  4190         -  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
  4191         -  ** and we are coding the t1 loop and the t2 loop has not yet coded,
  4192         -  ** then we cannot use the "t1.a=t2.b" constraint, but we can code
  4193         -  ** the implied "t1.a=123" constraint.
  4194         -  */
  4195         -  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
  4196         -    Expr *pE, *pEAlt;
  4197         -    WhereTerm *pAlt;
  4198         -    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  4199         -    if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
  4200         -    if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
  4201         -    if( pTerm->leftCursor!=iCur ) continue;
  4202         -    if( pLevel->iLeftJoin ) continue;
  4203         -    pE = pTerm->pExpr;
  4204         -    assert( !ExprHasProperty(pE, EP_FromJoin) );
  4205         -    assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
  4206         -    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
  4207         -                    WO_EQ|WO_IN|WO_IS, 0);
  4208         -    if( pAlt==0 ) continue;
  4209         -    if( pAlt->wtFlags & (TERM_CODED) ) continue;
  4210         -    testcase( pAlt->eOperator & WO_EQ );
  4211         -    testcase( pAlt->eOperator & WO_IS );
  4212         -    testcase( pAlt->eOperator & WO_IN );
  4213         -    VdbeModuleComment((v, "begin transitive constraint"));
  4214         -    pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
  4215         -    if( pEAlt ){
  4216         -      *pEAlt = *pAlt->pExpr;
  4217         -      pEAlt->pLeft = pE->pLeft;
  4218         -      sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
  4219         -      sqlite3StackFree(db, pEAlt);
  4220         -    }
  4221         -  }
  4222         -
  4223         -  /* For a LEFT OUTER JOIN, generate code that will record the fact that
  4224         -  ** at least one row of the right table has matched the left table.  
  4225         -  */
  4226         -  if( pLevel->iLeftJoin ){
  4227         -    pLevel->addrFirst = sqlite3VdbeCurrentAddr(v);
  4228         -    sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
  4229         -    VdbeComment((v, "record LEFT JOIN hit"));
  4230         -    sqlite3ExprCacheClear(pParse);
  4231         -    for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){
  4232         -      testcase( pTerm->wtFlags & TERM_VIRTUAL );
  4233         -      testcase( pTerm->wtFlags & TERM_CODED );
  4234         -      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
  4235         -      if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
  4236         -        assert( pWInfo->untestedTerms );
  4237         -        continue;
  4238         -      }
  4239         -      assert( pTerm->pExpr );
  4240         -      sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL);
  4241         -      pTerm->wtFlags |= TERM_CODED;
  4242         -    }
  4243         -  }
  4244         -
  4245         -  return pLevel->notReady;
  4246         -}
  4247   2775   
  4248   2776   #ifdef WHERETRACE_ENABLED
  4249   2777   /*
  4250   2778   ** Print the content of a WhereTerm object
  4251   2779   */
  4252   2780   static void whereTermPrint(WhereTerm *pTerm, int iTerm){
  4253   2781     if( pTerm==0 ){
................................................................................
  5666   4194     WhereLoop *pNew;
  5667   4195   
  5668   4196     /* Loop over the tables in the join, from left to right */
  5669   4197     pNew = pBuilder->pNew;
  5670   4198     whereLoopInit(pNew);
  5671   4199     for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
  5672   4200       pNew->iTab = iTab;
  5673         -    pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
         4201  +    pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
  5674   4202       if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
  5675   4203         mExtra = mPrior;
  5676   4204       }
  5677   4205       priorJoinType = pItem->jointype;
  5678   4206       if( IsVirtual(pItem->pTab) ){
  5679   4207         rc = whereLoopAddVirtual(pBuilder, mExtra);
  5680   4208       }else{
................................................................................
  5787   4315       ** loops.
  5788   4316       */
  5789   4317       for(i=0; i<nOrderBy; i++){
  5790   4318         if( MASKBIT(i) & obSat ) continue;
  5791   4319         pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
  5792   4320         if( pOBExpr->op!=TK_COLUMN ) continue;
  5793   4321         if( pOBExpr->iTable!=iCur ) continue;
  5794         -      pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
         4322  +      pTerm = sqlite3WhereFindTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
  5795   4323                          ~ready, WO_EQ|WO_ISNULL|WO_IS, 0);
  5796   4324         if( pTerm==0 ) continue;
  5797   4325         if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
  5798   4326           const char *z1, *z2;
  5799   4327           pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  5800   4328           if( !pColl ) pColl = db->pDfltColl;
  5801   4329           z1 = pColl->zName;
................................................................................
  6403   4931     if( IsVirtual(pTab) ) return 0;
  6404   4932     if( pItem->zIndexedBy ) return 0;
  6405   4933     iCur = pItem->iCursor;
  6406   4934     pWC = &pWInfo->sWC;
  6407   4935     pLoop = pBuilder->pNew;
  6408   4936     pLoop->wsFlags = 0;
  6409   4937     pLoop->nSkip = 0;
  6410         -  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
         4938  +  pTerm = sqlite3WhereFindTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
  6411   4939     if( pTerm ){
  6412   4940       testcase( pTerm->eOperator & WO_IS );
  6413   4941       pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
  6414   4942       pLoop->aLTerm[0] = pTerm;
  6415   4943       pLoop->nLTerm = 1;
  6416   4944       pLoop->u.btree.nEq = 1;
  6417   4945       /* TUNING: Cost of a rowid lookup is 10 */
................................................................................
  6422   4950         assert( pLoop->aLTermSpace==pLoop->aLTerm );
  6423   4951         if( !IsUniqueIndex(pIdx)
  6424   4952          || pIdx->pPartIdxWhere!=0 
  6425   4953          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  6426   4954         ) continue;
  6427   4955         opMask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ;
  6428   4956         for(j=0; j<pIdx->nKeyCol; j++){
  6429         -        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, opMask, pIdx);
         4957  +        pTerm = sqlite3WhereFindTerm(pWC, iCur, pIdx->aiColumn[j], 0, opMask, pIdx);
  6430   4958           if( pTerm==0 ) break;
  6431   4959           testcase( pTerm->eOperator & WO_IS );
  6432   4960           pLoop->aLTerm[j] = pTerm;
  6433   4961         }
  6434   4962         if( j!=pIdx->nKeyCol ) continue;
  6435   4963         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
  6436   4964         if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
................................................................................
  6443   4971         pLoop->rRun = 39;  /* 39==sqlite3LogEst(15) */
  6444   4972         break;
  6445   4973       }
  6446   4974     }
  6447   4975     if( pLoop->wsFlags ){
  6448   4976       pLoop->nOut = (LogEst)1;
  6449   4977       pWInfo->a[0].pWLoop = pLoop;
  6450         -    pLoop->maskSelf = getMask(&pWInfo->sMaskSet, iCur);
         4978  +    pLoop->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, iCur);
  6451   4979       pWInfo->a[0].iTabCur = iCur;
  6452   4980       pWInfo->nRowOut = 1;
  6453   4981       if( pWInfo->pOrderBy ) pWInfo->nOBSat =  pWInfo->pOrderBy->nExpr;
  6454   4982       if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
  6455   4983         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  6456   4984       }
  6457   4985   #ifdef SQLITE_DEBUG
................................................................................
  6683   5211     for(ii=0; ii<pTabList->nSrc; ii++){
  6684   5212       createMask(pMaskSet, pTabList->a[ii].iCursor);
  6685   5213     }
  6686   5214   #ifndef NDEBUG
  6687   5215     {
  6688   5216       Bitmask toTheLeft = 0;
  6689   5217       for(ii=0; ii<pTabList->nSrc; ii++){
  6690         -      Bitmask m = getMask(pMaskSet, pTabList->a[ii].iCursor);
         5218  +      Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor);
  6691   5219         assert( (m-1)==toTheLeft );
  6692   5220         toTheLeft |= m;
  6693   5221       }
  6694   5222     }
  6695   5223   #endif
  6696   5224   
  6697   5225     /* Analyze all of the subexpressions. */
................................................................................
  6932   5460   #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
  6933   5461       if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){
  6934   5462         constructAutomaticIndex(pParse, &pWInfo->sWC,
  6935   5463                   &pTabList->a[pLevel->iFrom], notReady, pLevel);
  6936   5464         if( db->mallocFailed ) goto whereBeginError;
  6937   5465       }
  6938   5466   #endif
  6939         -    addrExplain = explainOneScan(
         5467  +    addrExplain = sqlite3WhereExplainOneScan(
  6940   5468           pParse, pTabList, pLevel, ii, pLevel->iFrom, wctrlFlags
  6941   5469       );
  6942   5470       pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
  6943         -    notReady = codeOneLoopStart(pWInfo, ii, notReady);
         5471  +    notReady = sqlite3WhereCodeOneLoopStart(pWInfo, ii, notReady);
  6944   5472       pWInfo->iContinue = pLevel->addrCont;
  6945   5473       if( (wsFlags&WHERE_MULTI_OR)==0 && (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
  6946         -      addScanStatus(v, pTabList, pLevel, addrExplain);
         5474  +      sqlite3WhereAddScanStatus(v, pTabList, pLevel, addrExplain);
  6947   5475       }
  6948   5476     }
  6949   5477   
  6950   5478     /* Done. */
  6951   5479     VdbeModuleComment((v, "Begin WHERE-core"));
  6952   5480     return pWInfo;
  6953   5481   

Changes to src/whereInt.h.

    15     15   ** a separate source file for easier editing.
    16     16   */
    17     17   
    18     18   /*
    19     19   ** Trace output macros
    20     20   */
    21     21   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
    22         -/***/ int sqlite3WhereTrace = 0;
           22  +/***/ int sqlite3WhereTrace;
    23     23   #endif
    24     24   #if defined(SQLITE_DEBUG) \
    25     25       && (defined(SQLITE_TEST) || defined(SQLITE_ENABLE_WHERETRACE))
    26     26   # define WHERETRACE(K,X)  if(sqlite3WhereTrace&(K)) sqlite3DebugPrintf X
    27     27   # define WHERETRACE_ENABLED 1
    28     28   #else
    29     29   # define WHERETRACE(K,X)
................................................................................
   157    157   */
   158    158   #define N_OR_COST 3
   159    159   struct WhereOrSet {
   160    160     u16 n;                      /* Number of valid a[] entries */
   161    161     WhereOrCost a[N_OR_COST];   /* Set of best costs */
   162    162   };
   163    163   
   164         -
   165         -/* Forward declaration of methods */
   166         -static int whereLoopResize(sqlite3*, WhereLoop*, int);
   167         -
   168    164   /*
   169    165   ** Each instance of this object holds a sequence of WhereLoop objects
   170    166   ** that implement some or all of a query plan.
   171    167   **
   172    168   ** Think of each WhereLoop object as a node in a graph with arcs
   173    169   ** showing dependencies and costs for travelling between nodes.  (That is
   174    170   ** not a completely accurate description because WhereLoop costs are a
................................................................................
   418    414     int iBreak;               /* Jump here to break out of the loop */
   419    415     int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   420    416     int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
   421    417     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   422    418     WhereClause sWC;          /* Decomposition of the WHERE clause */
   423    419     WhereLevel a[1];          /* Information about each nest loop in WHERE */
   424    420   };
          421  +
          422  +/*
          423  +** Private interfaces - callable only by other where.c routines.
          424  +*/
          425  +Bitmask sqlite3WhereGetMask(WhereMaskSet*,int);
          426  +WhereTerm *sqlite3WhereFindTerm(
          427  +  WhereClause *pWC,     /* The WHERE clause to be searched */
          428  +  int iCur,             /* Cursor number of LHS */
          429  +  int iColumn,          /* Column number of LHS */
          430  +  Bitmask notReady,     /* RHS must not overlap with this mask */
          431  +  u32 op,               /* Mask of WO_xx values describing operator */
          432  +  Index *pIdx           /* Must be compatible with this index, if not NULL */
          433  +);
          434  +#ifndef SQLITE_OMIT_EXPLAIN
          435  +int sqlite3WhereExplainOneScan(
          436  +  Parse *pParse,                  /* Parse context */
          437  +  SrcList *pTabList,              /* Table list this loop refers to */
          438  +  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
          439  +  int iLevel,                     /* Value for "level" column of output */
          440  +  int iFrom,                      /* Value for "from" column of output */
          441  +  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
          442  +);
          443  +#else
          444  +# define sqlite3WhereExplainOneScan(u,v,w,x,y,z) 0
          445  +#endif /* SQLITE_OMIT_EXPLAIN */
          446  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          447  +void sqlite3WhereAddScanStatus(
          448  +  Vdbe *v,                        /* Vdbe to add scanstatus entry to */
          449  +  SrcList *pSrclist,              /* FROM clause pLvl reads data from */
          450  +  WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
          451  +  int addrExplain                 /* Address of OP_Explain (or 0) */
          452  +);
          453  +#else
          454  +# define sqlite3WhereAddScanStatus(a, b, c, d) ((void)d)
          455  +#endif
          456  +Bitmask sqlite3WhereCodeOneLoopStart(
          457  +  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
          458  +  int iLevel,          /* Which level of pWInfo->a[] should be coded */
          459  +  Bitmask notReady     /* Which tables are currently available */
          460  +);
          461  +
          462  +
          463  +
          464  +
          465  +
          466  +
   425    467   
   426    468   /*
   427    469   ** Bitmasks for the operators on WhereTerm objects.  These are all
   428    470   ** operators that are of interest to the query planner.  An
   429    471   ** OR-ed combination of these values can be used when searching for
   430    472   ** particular WhereTerms within a WhereClause.
   431    473   */

Added src/wherecode.c.

            1  +/*
            2  +** 2015-06-06
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** This module contains C code that generates VDBE code used to process
           13  +** the WHERE clause of SQL statements.
           14  +**
           15  +** This file was split off from where.c on 2015-06-06 in order to reduce the
           16  +** size of where.c and make it easier to edit.  This file contains the routines
           17  +** that actually generate the bulk of the WHERE loop code.  The original where.c
           18  +** file retains the code that does query planning and analysis.
           19  +*/
           20  +#include "sqliteInt.h"
           21  +#include "whereInt.h"
           22  +
           23  +#ifndef SQLITE_OMIT_EXPLAIN
           24  +/*
           25  +** This routine is a helper for explainIndexRange() below
           26  +**
           27  +** pStr holds the text of an expression that we are building up one term
           28  +** at a time.  This routine adds a new term to the end of the expression.
           29  +** Terms are separated by AND so add the "AND" text for second and subsequent
           30  +** terms only.
           31  +*/
           32  +static void explainAppendTerm(
           33  +  StrAccum *pStr,             /* The text expression being built */
           34  +  int iTerm,                  /* Index of this term.  First is zero */
           35  +  const char *zColumn,        /* Name of the column */
           36  +  const char *zOp             /* Name of the operator */
           37  +){
           38  +  if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
           39  +  sqlite3StrAccumAppendAll(pStr, zColumn);
           40  +  sqlite3StrAccumAppend(pStr, zOp, 1);
           41  +  sqlite3StrAccumAppend(pStr, "?", 1);
           42  +}
           43  +
           44  +/*
           45  +** Argument pLevel describes a strategy for scanning table pTab. This 
           46  +** function appends text to pStr that describes the subset of table
           47  +** rows scanned by the strategy in the form of an SQL expression.
           48  +**
           49  +** For example, if the query:
           50  +**
           51  +**   SELECT * FROM t1 WHERE a=1 AND b>2;
           52  +**
           53  +** is run and there is an index on (a, b), then this function returns a
           54  +** string similar to:
           55  +**
           56  +**   "a=? AND b>?"
           57  +*/
           58  +static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
           59  +  Index *pIndex = pLoop->u.btree.pIndex;
           60  +  u16 nEq = pLoop->u.btree.nEq;
           61  +  u16 nSkip = pLoop->nSkip;
           62  +  int i, j;
           63  +  Column *aCol = pTab->aCol;
           64  +  i16 *aiColumn = pIndex->aiColumn;
           65  +
           66  +  if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
           67  +  sqlite3StrAccumAppend(pStr, " (", 2);
           68  +  for(i=0; i<nEq; i++){
           69  +    char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName;
           70  +    if( i>=nSkip ){
           71  +      explainAppendTerm(pStr, i, z, "=");
           72  +    }else{
           73  +      if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5);
           74  +      sqlite3XPrintf(pStr, 0, "ANY(%s)", z);
           75  +    }
           76  +  }
           77  +
           78  +  j = i;
           79  +  if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
           80  +    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
           81  +    explainAppendTerm(pStr, i++, z, ">");
           82  +  }
           83  +  if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
           84  +    char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
           85  +    explainAppendTerm(pStr, i, z, "<");
           86  +  }
           87  +  sqlite3StrAccumAppend(pStr, ")", 1);
           88  +}
           89  +
           90  +/*
           91  +** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
           92  +** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was
           93  +** defined at compile-time. If it is not a no-op, a single OP_Explain opcode 
           94  +** is added to the output to describe the table scan strategy in pLevel.
           95  +**
           96  +** If an OP_Explain opcode is added to the VM, its address is returned.
           97  +** Otherwise, if no OP_Explain is coded, zero is returned.
           98  +*/
           99  +int sqlite3WhereExplainOneScan(
          100  +  Parse *pParse,                  /* Parse context */
          101  +  SrcList *pTabList,              /* Table list this loop refers to */
          102  +  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
          103  +  int iLevel,                     /* Value for "level" column of output */
          104  +  int iFrom,                      /* Value for "from" column of output */
          105  +  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
          106  +){
          107  +  int ret = 0;
          108  +#if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
          109  +  if( pParse->explain==2 )
          110  +#endif
          111  +  {
          112  +    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
          113  +    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
          114  +    sqlite3 *db = pParse->db;     /* Database handle */
          115  +    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
          116  +    int isSearch;                 /* True for a SEARCH. False for SCAN. */
          117  +    WhereLoop *pLoop;             /* The controlling WhereLoop object */
          118  +    u32 flags;                    /* Flags that describe this loop */
          119  +    char *zMsg;                   /* Text to add to EQP output */
          120  +    StrAccum str;                 /* EQP output string */
          121  +    char zBuf[100];               /* Initial space for EQP output string */
          122  +
          123  +    pLoop = pLevel->pWLoop;
          124  +    flags = pLoop->wsFlags;
          125  +    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return 0;
          126  +
          127  +    isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0
          128  +            || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))
          129  +            || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));
          130  +
          131  +    sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);
          132  +    sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN");
          133  +    if( pItem->pSelect ){
          134  +      sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId);
          135  +    }else{
          136  +      sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName);
          137  +    }
          138  +
          139  +    if( pItem->zAlias ){
          140  +      sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias);
          141  +    }
          142  +    if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
          143  +      const char *zFmt = 0;
          144  +      Index *pIdx;
          145  +
          146  +      assert( pLoop->u.btree.pIndex!=0 );
          147  +      pIdx = pLoop->u.btree.pIndex;
          148  +      assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );
          149  +      if( !HasRowid(pItem->pTab) && IsPrimaryKeyIndex(pIdx) ){
          150  +        if( isSearch ){
          151  +          zFmt = "PRIMARY KEY";
          152  +        }
          153  +      }else if( flags & WHERE_PARTIALIDX ){
          154  +        zFmt = "AUTOMATIC PARTIAL COVERING INDEX";
          155  +      }else if( flags & WHERE_AUTO_INDEX ){
          156  +        zFmt = "AUTOMATIC COVERING INDEX";
          157  +      }else if( flags & WHERE_IDX_ONLY ){
          158  +        zFmt = "COVERING INDEX %s";
          159  +      }else{
          160  +        zFmt = "INDEX %s";
          161  +      }
          162  +      if( zFmt ){
          163  +        sqlite3StrAccumAppend(&str, " USING ", 7);
          164  +        sqlite3XPrintf(&str, 0, zFmt, pIdx->zName);
          165  +        explainIndexRange(&str, pLoop, pItem->pTab);
          166  +      }
          167  +    }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
          168  +      const char *zRange;
          169  +      if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
          170  +        zRange = "(rowid=?)";
          171  +      }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
          172  +        zRange = "(rowid>? AND rowid<?)";
          173  +      }else if( flags&WHERE_BTM_LIMIT ){
          174  +        zRange = "(rowid>?)";
          175  +      }else{
          176  +        assert( flags&WHERE_TOP_LIMIT);
          177  +        zRange = "(rowid<?)";
          178  +      }
          179  +      sqlite3StrAccumAppendAll(&str, " USING INTEGER PRIMARY KEY ");
          180  +      sqlite3StrAccumAppendAll(&str, zRange);
          181  +    }
          182  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          183  +    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
          184  +      sqlite3XPrintf(&str, 0, " VIRTUAL TABLE INDEX %d:%s",
          185  +                  pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
          186  +    }
          187  +#endif
          188  +#ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
          189  +    if( pLoop->nOut>=10 ){
          190  +      sqlite3XPrintf(&str, 0, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut));
          191  +    }else{
          192  +      sqlite3StrAccumAppend(&str, " (~1 row)", 9);
          193  +    }
          194  +#endif
          195  +    zMsg = sqlite3StrAccumFinish(&str);
          196  +    ret = sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg,P4_DYNAMIC);
          197  +  }
          198  +  return ret;
          199  +}
          200  +#endif /* SQLITE_OMIT_EXPLAIN */
          201  +
          202  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
          203  +/*
          204  +** Configure the VM passed as the first argument with an
          205  +** sqlite3_stmt_scanstatus() entry corresponding to the scan used to 
          206  +** implement level pLvl. Argument pSrclist is a pointer to the FROM 
          207  +** clause that the scan reads data from.
          208  +**
          209  +** If argument addrExplain is not 0, it must be the address of an 
          210  +** OP_Explain instruction that describes the same loop.
          211  +*/
          212  +void sqlite3WhereAddScanStatus(
          213  +  Vdbe *v,                        /* Vdbe to add scanstatus entry to */
          214  +  SrcList *pSrclist,              /* FROM clause pLvl reads data from */
          215  +  WhereLevel *pLvl,               /* Level to add scanstatus() entry for */
          216  +  int addrExplain                 /* Address of OP_Explain (or 0) */
          217  +){
          218  +  const char *zObj = 0;
          219  +  WhereLoop *pLoop = pLvl->pWLoop;
          220  +  if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0  &&  pLoop->u.btree.pIndex!=0 ){
          221  +    zObj = pLoop->u.btree.pIndex->zName;
          222  +  }else{
          223  +    zObj = pSrclist->a[pLvl->iFrom].zName;
          224  +  }
          225  +  sqlite3VdbeScanStatus(
          226  +      v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
          227  +  );
          228  +}
          229  +#endif
          230  +
          231  +
          232  +/*
          233  +** Disable a term in the WHERE clause.  Except, do not disable the term
          234  +** if it controls a LEFT OUTER JOIN and it did not originate in the ON
          235  +** or USING clause of that join.
          236  +**
          237  +** Consider the term t2.z='ok' in the following queries:
          238  +**
          239  +**   (1)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
          240  +**   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
          241  +**   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
          242  +**
          243  +** The t2.z='ok' is disabled in the in (2) because it originates
          244  +** in the ON clause.  The term is disabled in (3) because it is not part
          245  +** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
          246  +**
          247  +** Disabling a term causes that term to not be tested in the inner loop
          248  +** of the join.  Disabling is an optimization.  When terms are satisfied
          249  +** by indices, we disable them to prevent redundant tests in the inner
          250  +** loop.  We would get the correct results if nothing were ever disabled,
          251  +** but joins might run a little slower.  The trick is to disable as much
          252  +** as we can without disabling too much.  If we disabled in (1), we'd get
          253  +** the wrong answer.  See ticket #813.
          254  +**
          255  +** If all the children of a term are disabled, then that term is also
          256  +** automatically disabled.  In this way, terms get disabled if derived
          257  +** virtual terms are tested first.  For example:
          258  +**
          259  +**      x GLOB 'abc*' AND x>='abc' AND x<'acd'
          260  +**      \___________/     \______/     \_____/
          261  +**         parent          child1       child2
          262  +**
          263  +** Only the parent term was in the original WHERE clause.  The child1
          264  +** and child2 terms were added by the LIKE optimization.  If both of
          265  +** the virtual child terms are valid, then testing of the parent can be 
          266  +** skipped.
          267  +**
          268  +** Usually the parent term is marked as TERM_CODED.  But if the parent
          269  +** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.
          270  +** The TERM_LIKECOND marking indicates that the term should be coded inside
          271  +** a conditional such that is only evaluated on the second pass of a
          272  +** LIKE-optimization loop, when scanning BLOBs instead of strings.
          273  +*/
          274  +static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
          275  +  int nLoop = 0;
          276  +  while( pTerm
          277  +      && (pTerm->wtFlags & TERM_CODED)==0
          278  +      && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
          279  +      && (pLevel->notReady & pTerm->prereqAll)==0
          280  +  ){
          281  +    if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){
          282  +      pTerm->wtFlags |= TERM_LIKECOND;
          283  +    }else{
          284  +      pTerm->wtFlags |= TERM_CODED;
          285  +    }
          286  +    if( pTerm->iParent<0 ) break;
          287  +    pTerm = &pTerm->pWC->a[pTerm->iParent];
          288  +    pTerm->nChild--;
          289  +    if( pTerm->nChild!=0 ) break;
          290  +    nLoop++;
          291  +  }
          292  +}
          293  +
          294  +/*
          295  +** Code an OP_Affinity opcode to apply the column affinity string zAff
          296  +** to the n registers starting at base. 
          297  +**
          298  +** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
          299  +** beginning and end of zAff are ignored.  If all entries in zAff are
          300  +** SQLITE_AFF_BLOB, then no code gets generated.
          301  +**
          302  +** This routine makes its own copy of zAff so that the caller is free
          303  +** to modify zAff after this routine returns.
          304  +*/
          305  +static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){
          306  +  Vdbe *v = pParse->pVdbe;
          307  +  if( zAff==0 ){
          308  +    assert( pParse->db->mallocFailed );
          309  +    return;
          310  +  }
          311  +  assert( v!=0 );
          312  +
          313  +  /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
          314  +  ** and end of the affinity string.
          315  +  */
          316  +  while( n>0 && zAff[0]==SQLITE_AFF_BLOB ){
          317  +    n--;
          318  +    base++;
          319  +    zAff++;
          320  +  }
          321  +  while( n>1 && zAff[n-1]==SQLITE_AFF_BLOB ){
          322  +    n--;
          323  +  }
          324  +
          325  +  /* Code the OP_Affinity opcode if there is anything left to do. */
          326  +  if( n>0 ){
          327  +    sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
          328  +    sqlite3VdbeChangeP4(v, -1, zAff, n);
          329  +    sqlite3ExprCacheAffinityChange(pParse, base, n);
          330  +  }
          331  +}
          332  +
          333  +
          334  +/*
          335  +** Generate code for a single equality term of the WHERE clause.  An equality
          336  +** term can be either X=expr or X IN (...).   pTerm is the term to be 
          337  +** coded.
          338  +**
          339  +** The current value for the constraint is left in register iReg.
          340  +**
          341  +** For a constraint of the form X=expr, the expression is evaluated and its
          342  +** result is left on the stack.  For constraints of the form X IN (...)
          343  +** this routine sets up a loop that will iterate over all values of X.
          344  +*/
          345  +static int codeEqualityTerm(
          346  +  Parse *pParse,      /* The parsing context */
          347  +  WhereTerm *pTerm,   /* The term of the WHERE clause to be coded */
          348  +  WhereLevel *pLevel, /* The level of the FROM clause we are working on */
          349  +  int iEq,            /* Index of the equality term within this level */
          350  +  int bRev,           /* True for reverse-order IN operations */
          351  +  int iTarget         /* Attempt to leave results in this register */
          352  +){
          353  +  Expr *pX = pTerm->pExpr;
          354  +  Vdbe *v = pParse->pVdbe;
          355  +  int iReg;                  /* Register holding results */
          356  +
          357  +  assert( iTarget>0 );
          358  +  if( pX->op==TK_EQ || pX->op==TK_IS ){
          359  +    iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
          360  +  }else if( pX->op==TK_ISNULL ){
          361  +    iReg = iTarget;
          362  +    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
          363  +#ifndef SQLITE_OMIT_SUBQUERY
          364  +  }else{
          365  +    int eType;
          366  +    int iTab;
          367  +    struct InLoop *pIn;
          368  +    WhereLoop *pLoop = pLevel->pWLoop;
          369  +
          370  +    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
          371  +      && pLoop->u.btree.pIndex!=0
          372  +      && pLoop->u.btree.pIndex->aSortOrder[iEq]
          373  +    ){
          374  +      testcase( iEq==0 );
          375  +      testcase( bRev );
          376  +      bRev = !bRev;
          377  +    }
          378  +    assert( pX->op==TK_IN );
          379  +    iReg = iTarget;
          380  +    eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0);
          381  +    if( eType==IN_INDEX_INDEX_DESC ){
          382  +      testcase( bRev );
          383  +      bRev = !bRev;
          384  +    }
          385  +    iTab = pX->iTable;
          386  +    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
          387  +    VdbeCoverageIf(v, bRev);
          388  +    VdbeCoverageIf(v, !bRev);
          389  +    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
          390  +    pLoop->wsFlags |= WHERE_IN_ABLE;
          391  +    if( pLevel->u.in.nIn==0 ){
          392  +      pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
          393  +    }
          394  +    pLevel->u.in.nIn++;
          395  +    pLevel->u.in.aInLoop =
          396  +       sqlite3DbReallocOrFree(pParse->db, pLevel->u.in.aInLoop,
          397  +                              sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn);
          398  +    pIn = pLevel->u.in.aInLoop;
          399  +    if( pIn ){
          400  +      pIn += pLevel->u.in.nIn - 1;
          401  +      pIn->iCur = iTab;
          402  +      if( eType==IN_INDEX_ROWID ){
          403  +        pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg);
          404  +      }else{
          405  +        pIn->addrInTop = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
          406  +      }
          407  +      pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
          408  +      sqlite3VdbeAddOp1(v, OP_IsNull, iReg); VdbeCoverage(v);
          409  +    }else{
          410  +      pLevel->u.in.nIn = 0;
          411  +    }
          412  +#endif
          413  +  }
          414  +  disableTerm(pLevel, pTerm);
          415  +  return iReg;
          416  +}
          417  +
          418  +/*
          419  +** Generate code that will evaluate all == and IN constraints for an
          420  +** index scan.
          421  +**
          422  +** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
          423  +** Suppose the WHERE clause is this:  a==5 AND b IN (1,2,3) AND c>5 AND c<10
          424  +** The index has as many as three equality constraints, but in this
          425  +** example, the third "c" value is an inequality.  So only two 
          426  +** constraints are coded.  This routine will generate code to evaluate
          427  +** a==5 and b IN (1,2,3).  The current values for a and b will be stored
          428  +** in consecutive registers and the index of the first register is returned.
          429  +**
          430  +** In the example above nEq==2.  But this subroutine works for any value
          431  +** of nEq including 0.  If nEq==0, this routine is nearly a no-op.
          432  +** The only thing it does is allocate the pLevel->iMem memory cell and
          433  +** compute the affinity string.
          434  +**
          435  +** The nExtraReg parameter is 0 or 1.  It is 0 if all WHERE clause constraints
          436  +** are == or IN and are covered by the nEq.  nExtraReg is 1 if there is
          437  +** an inequality constraint (such as the "c>=5 AND c<10" in the example) that
          438  +** occurs after the nEq quality constraints.
          439  +**
          440  +** This routine allocates a range of nEq+nExtraReg memory cells and returns
          441  +** the index of the first memory cell in that range. The code that
          442  +** calls this routine will use that memory range to store keys for
          443  +** start and termination conditions of the loop.
          444  +** key value of the loop.  If one or more IN operators appear, then
          445  +** this routine allocates an additional nEq memory cells for internal
          446  +** use.
          447  +**
          448  +** Before returning, *pzAff is set to point to a buffer containing a
          449  +** copy of the column affinity string of the index allocated using
          450  +** sqlite3DbMalloc(). Except, entries in the copy of the string associated
          451  +** with equality constraints that use BLOB or NONE affinity are set to
          452  +** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:
          453  +**
          454  +**   CREATE TABLE t1(a TEXT PRIMARY KEY, b);
          455  +**   SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
          456  +**
          457  +** In the example above, the index on t1(a) has TEXT affinity. But since
          458  +** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,
          459  +** no conversion should be attempted before using a t2.b value as part of
          460  +** a key to search the index. Hence the first byte in the returned affinity
          461  +** string in this example would be set to SQLITE_AFF_BLOB.
          462  +*/
          463  +static int codeAllEqualityTerms(
          464  +  Parse *pParse,        /* Parsing context */
          465  +  WhereLevel *pLevel,   /* Which nested loop of the FROM we are coding */
          466  +  int bRev,             /* Reverse the order of IN operators */
          467  +  int nExtraReg,        /* Number of extra registers to allocate */
          468  +  char **pzAff          /* OUT: Set to point to affinity string */
          469  +){
          470  +  u16 nEq;                      /* The number of == or IN constraints to code */
          471  +  u16 nSkip;                    /* Number of left-most columns to skip */
          472  +  Vdbe *v = pParse->pVdbe;      /* The vm under construction */
          473  +  Index *pIdx;                  /* The index being used for this loop */
          474  +  WhereTerm *pTerm;             /* A single constraint term */
          475  +  WhereLoop *pLoop;             /* The WhereLoop object */
          476  +  int j;                        /* Loop counter */
          477  +  int regBase;                  /* Base register */
          478  +  int nReg;                     /* Number of registers to allocate */
          479  +  char *zAff;                   /* Affinity string to return */
          480  +
          481  +  /* This module is only called on query plans that use an index. */
          482  +  pLoop = pLevel->pWLoop;
          483  +  assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );
          484  +  nEq = pLoop->u.btree.nEq;
          485  +  nSkip = pLoop->nSkip;
          486  +  pIdx = pLoop->u.btree.pIndex;
          487  +  assert( pIdx!=0 );
          488  +
          489  +  /* Figure out how many memory cells we will need then allocate them.
          490  +  */
          491  +  regBase = pParse->nMem + 1;
          492  +  nReg = pLoop->u.btree.nEq + nExtraReg;
          493  +  pParse->nMem += nReg;
          494  +
          495  +  zAff = sqlite3DbStrDup(pParse->db, sqlite3IndexAffinityStr(v, pIdx));
          496  +  if( !zAff ){
          497  +    pParse->db->mallocFailed = 1;
          498  +  }
          499  +
          500  +  if( nSkip ){
          501  +    int iIdxCur = pLevel->iIdxCur;
          502  +    sqlite3VdbeAddOp1(v, (bRev?OP_Last:OP_Rewind), iIdxCur);
          503  +    VdbeCoverageIf(v, bRev==0);
          504  +    VdbeCoverageIf(v, bRev!=0);
          505  +    VdbeComment((v, "begin skip-scan on %s", pIdx->zName));
          506  +    j = sqlite3VdbeAddOp0(v, OP_Goto);
          507  +    pLevel->addrSkip = sqlite3VdbeAddOp4Int(v, (bRev?OP_SeekLT:OP_SeekGT),
          508  +                            iIdxCur, 0, regBase, nSkip);
          509  +    VdbeCoverageIf(v, bRev==0);
          510  +    VdbeCoverageIf(v, bRev!=0);
          511  +    sqlite3VdbeJumpHere(v, j);
          512  +    for(j=0; j<nSkip; j++){
          513  +      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, j, regBase+j);
          514  +      assert( pIdx->aiColumn[j]>=0 );
          515  +      VdbeComment((v, "%s", pIdx->pTable->aCol[pIdx->aiColumn[j]].zName));
          516  +    }
          517  +  }    
          518  +
          519  +  /* Evaluate the equality constraints
          520  +  */
          521  +  assert( zAff==0 || (int)strlen(zAff)>=nEq );
          522  +  for(j=nSkip; j<nEq; j++){
          523  +    int r1;
          524  +    pTerm = pLoop->aLTerm[j];
          525  +    assert( pTerm!=0 );
          526  +    /* The following testcase is true for indices with redundant columns. 
          527  +    ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
          528  +    testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
          529  +    testcase( pTerm->wtFlags & TERM_VIRTUAL );
          530  +    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j);
          531  +    if( r1!=regBase+j ){
          532  +      if( nReg==1 ){
          533  +        sqlite3ReleaseTempReg(pParse, regBase);
          534  +        regBase = r1;
          535  +      }else{
          536  +        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
          537  +      }
          538  +    }
          539  +    testcase( pTerm->eOperator & WO_ISNULL );
          540  +    testcase( pTerm->eOperator & WO_IN );
          541  +    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
          542  +      Expr *pRight = pTerm->pExpr->pRight;
          543  +      if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
          544  +        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
          545  +        VdbeCoverage(v);
          546  +      }
          547  +      if( zAff ){
          548  +        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
          549  +          zAff[j] = SQLITE_AFF_BLOB;
          550  +        }
          551  +        if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
          552  +          zAff[j] = SQLITE_AFF_BLOB;
          553  +        }
          554  +      }
          555  +    }
          556  +  }
          557  +  *pzAff = zAff;
          558  +  return regBase;
          559  +}
          560  +
          561  +/*
          562  +** If the most recently coded instruction is a constant range contraint
          563  +** that originated from the LIKE optimization, then change the P3 to be
          564  +** pLoop->iLikeRepCntr and set P5.
          565  +**
          566  +** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
          567  +** expression: "x>='ABC' AND x<'abd'".  But this requires that the range
          568  +** scan loop run twice, once for strings and a second time for BLOBs.
          569  +** The OP_String opcodes on the second pass convert the upper and lower
          570  +** bound string contants to blobs.  This routine makes the necessary changes
          571  +** to the OP_String opcodes for that to happen.
          572  +*/
          573  +static void whereLikeOptimizationStringFixup(
          574  +  Vdbe *v,                /* prepared statement under construction */
          575  +  WhereLevel *pLevel,     /* The loop that contains the LIKE operator */
          576  +  WhereTerm *pTerm        /* The upper or lower bound just coded */
          577  +){
          578  +  if( pTerm->wtFlags & TERM_LIKEOPT ){
          579  +    VdbeOp *pOp;
          580  +    assert( pLevel->iLikeRepCntr>0 );
          581  +    pOp = sqlite3VdbeGetOp(v, -1);
          582  +    assert( pOp!=0 );
          583  +    assert( pOp->opcode==OP_String8 
          584  +            || pTerm->pWC->pWInfo->pParse->db->mallocFailed );
          585  +    pOp->p3 = pLevel->iLikeRepCntr;
          586  +    pOp->p5 = 1;
          587  +  }
          588  +}
          589  +
          590  +
          591  +/*
          592  +** Generate code for the start of the iLevel-th loop in the WHERE clause
          593  +** implementation described by pWInfo.
          594  +*/
          595  +Bitmask sqlite3WhereCodeOneLoopStart(
          596  +  WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
          597  +  int iLevel,          /* Which level of pWInfo->a[] should be coded */
          598  +  Bitmask notReady     /* Which tables are currently available */
          599  +){
          600  +  int j, k;            /* Loop counters */
          601  +  int iCur;            /* The VDBE cursor for the table */
          602  +  int addrNxt;         /* Where to jump to continue with the next IN case */
          603  +  int omitTable;       /* True if we use the index only */
          604  +  int bRev;            /* True if we need to scan in reverse order */
          605  +  WhereLevel *pLevel;  /* The where level to be coded */
          606  +  WhereLoop *pLoop;    /* The WhereLoop object being coded */
          607  +  WhereClause *pWC;    /* Decomposition of the entire WHERE clause */
          608  +  WhereTerm *pTerm;               /* A WHERE clause term */
          609  +  Parse *pParse;                  /* Parsing context */
          610  +  sqlite3 *db;                    /* Database connection */
          611  +  Vdbe *v;                        /* The prepared stmt under constructions */
          612  +  struct SrcList_item *pTabItem;  /* FROM clause term being coded */
          613  +  int addrBrk;                    /* Jump here to break out of the loop */
          614  +  int addrCont;                   /* Jump here to continue with next cycle */
          615  +  int iRowidReg = 0;        /* Rowid is stored in this register, if not zero */
          616  +  int iReleaseReg = 0;      /* Temp register to free before returning */
          617  +
          618  +  pParse = pWInfo->pParse;
          619  +  v = pParse->pVdbe;
          620  +  pWC = &pWInfo->sWC;
          621  +  db = pParse->db;
          622  +  pLevel = &pWInfo->a[iLevel];
          623  +  pLoop = pLevel->pWLoop;
          624  +  pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
          625  +  iCur = pTabItem->iCursor;
          626  +  pLevel->notReady = notReady & ~sqlite3WhereGetMask(&pWInfo->sMaskSet, iCur);
          627  +  bRev = (pWInfo->revMask>>iLevel)&1;
          628  +  omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0 
          629  +           && (pWInfo->wctrlFlags & WHERE_FORCE_TABLE)==0;
          630  +  VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName));
          631  +
          632  +  /* Create labels for the "break" and "continue" instructions
          633  +  ** for the current loop.  Jump to addrBrk to break out of a loop.
          634  +  ** Jump to cont to go immediately to the next iteration of the
          635  +  ** loop.
          636  +  **
          637  +  ** When there is an IN operator, we also have a "addrNxt" label that
          638  +  ** means to continue with the next IN value combination.  When
          639  +  ** there are no IN operators in the constraints, the "addrNxt" label
          640  +  ** is the same as "addrBrk".
          641  +  */
          642  +  addrBrk = pLevel->addrBrk = pLevel->addrNxt = sqlite3VdbeMakeLabel(v);
          643  +  addrCont = pLevel->addrCont = sqlite3VdbeMakeLabel(v);
          644  +
          645  +  /* If this is the right table of a LEFT OUTER JOIN, allocate and
          646  +  ** initialize a memory cell that records if this table matches any
          647  +  ** row of the left table of the join.
          648  +  */
          649  +  if( pLevel->iFrom>0 && (pTabItem[0].jointype & JT_LEFT)!=0 ){
          650  +    pLevel->iLeftJoin = ++pParse->nMem;
          651  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLeftJoin);
          652  +    VdbeComment((v, "init LEFT JOIN no-match flag"));
          653  +  }
          654  +
          655  +  /* Special case of a FROM clause subquery implemented as a co-routine */
          656  +  if( pTabItem->viaCoroutine ){
          657  +    int regYield = pTabItem->regReturn;
          658  +    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pTabItem->addrFillSub);
          659  +    pLevel->p2 =  sqlite3VdbeAddOp2(v, OP_Yield, regYield, addrBrk);
          660  +    VdbeCoverage(v);
          661  +    VdbeComment((v, "next row of \"%s\"", pTabItem->pTab->zName));
          662  +    pLevel->op = OP_Goto;
          663  +  }else
          664  +
          665  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          666  +  if(  (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
          667  +    /* Case 1:  The table is a virtual-table.  Use the VFilter and VNext
          668  +    **          to access the data.
          669  +    */
          670  +    int iReg;   /* P3 Value for OP_VFilter */
          671  +    int addrNotFound;
          672  +    int nConstraint = pLoop->nLTerm;
          673  +
          674  +    sqlite3ExprCachePush(pParse);
          675  +    iReg = sqlite3GetTempRange(pParse, nConstraint+2);
          676  +    addrNotFound = pLevel->addrBrk;
          677  +    for(j=0; j<nConstraint; j++){
          678  +      int iTarget = iReg+j+2;
          679  +      pTerm = pLoop->aLTerm[j];
          680  +      if( pTerm==0 ) continue;
          681  +      if( pTerm->eOperator & WO_IN ){
          682  +        codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, iTarget);
          683  +        addrNotFound = pLevel->addrNxt;
          684  +      }else{
          685  +        sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget);
          686  +      }
          687  +    }
          688  +    sqlite3VdbeAddOp2(v, OP_Integer, pLoop->u.vtab.idxNum, iReg);
          689  +    sqlite3VdbeAddOp2(v, OP_Integer, nConstraint, iReg+1);
          690  +    sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg,
          691  +                      pLoop->u.vtab.idxStr,
          692  +                      pLoop->u.vtab.needFree ? P4_MPRINTF : P4_STATIC);
          693  +    VdbeCoverage(v);
          694  +    pLoop->u.vtab.needFree = 0;
          695  +    for(j=0; j<nConstraint && j<16; j++){
          696  +      if( (pLoop->u.vtab.omitMask>>j)&1 ){
          697  +        disableTerm(pLevel, pLoop->aLTerm[j]);
          698  +      }
          699  +    }
          700  +    pLevel->op = OP_VNext;
          701  +    pLevel->p1 = iCur;
          702  +    pLevel->p2 = sqlite3VdbeCurrentAddr(v);
          703  +    sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
          704  +    sqlite3ExprCachePop(pParse);
          705  +  }else
          706  +#endif /* SQLITE_OMIT_VIRTUALTABLE */
          707  +
          708  +  if( (pLoop->wsFlags & WHERE_IPK)!=0
          709  +   && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0
          710  +  ){
          711  +    /* Case 2:  We can directly reference a single row using an
          712  +    **          equality comparison against the ROWID field.  Or
          713  +    **          we reference multiple rows using a "rowid IN (...)"
          714  +    **          construct.
          715  +    */
          716  +    assert( pLoop->u.btree.nEq==1 );
          717  +    pTerm = pLoop->aLTerm[0];
          718  +    assert( pTerm!=0 );
          719  +    assert( pTerm->pExpr!=0 );
          720  +    assert( omitTable==0 );
          721  +    testcase( pTerm->wtFlags & TERM_VIRTUAL );
          722  +    iReleaseReg = ++pParse->nMem;
          723  +    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
          724  +    if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
          725  +    addrNxt = pLevel->addrNxt;
          726  +    sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); VdbeCoverage(v);
          727  +    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
          728  +    VdbeCoverage(v);
          729  +    sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
          730  +    sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
          731  +    VdbeComment((v, "pk"));
          732  +    pLevel->op = OP_Noop;
          733  +  }else if( (pLoop->wsFlags & WHERE_IPK)!=0
          734  +         && (pLoop->wsFlags & WHERE_COLUMN_RANGE)!=0
          735  +  ){
          736  +    /* Case 3:  We have an inequality comparison against the ROWID field.
          737  +    */
          738  +    int testOp = OP_Noop;
          739  +    int start;
          740  +    int memEndValue = 0;
          741  +    WhereTerm *pStart, *pEnd;
          742  +
          743  +    assert( omitTable==0 );
          744  +    j = 0;
          745  +    pStart = pEnd = 0;
          746  +    if( pLoop->wsFlags & WHERE_BTM_LIMIT ) pStart = pLoop->aLTerm[j++];
          747  +    if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++];
          748  +    assert( pStart!=0 || pEnd!=0 );
          749  +    if( bRev ){
          750  +      pTerm = pStart;
          751  +      pStart = pEnd;
          752  +      pEnd = pTerm;
          753  +    }
          754  +    if( pStart ){
          755  +      Expr *pX;             /* The expression that defines the start bound */
          756  +      int r1, rTemp;        /* Registers for holding the start boundary */
          757  +
          758  +      /* The following constant maps TK_xx codes into corresponding 
          759  +      ** seek opcodes.  It depends on a particular ordering of TK_xx
          760  +      */
          761  +      const u8 aMoveOp[] = {
          762  +           /* TK_GT */  OP_SeekGT,
          763  +           /* TK_LE */  OP_SeekLE,
          764  +           /* TK_LT */  OP_SeekLT,
          765  +           /* TK_GE */  OP_SeekGE
          766  +      };
          767  +      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
          768  +      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
          769  +      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */
          770  +
          771  +      assert( (pStart->wtFlags & TERM_VNULL)==0 );
          772  +      testcase( pStart->wtFlags & TERM_VIRTUAL );
          773  +      pX = pStart->pExpr;
          774  +      assert( pX!=0 );
          775  +      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
          776  +      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
          777  +      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
          778  +      VdbeComment((v, "pk"));
          779  +      VdbeCoverageIf(v, pX->op==TK_GT);
          780  +      VdbeCoverageIf(v, pX->op==TK_LE);
          781  +      VdbeCoverageIf(v, pX->op==TK_LT);
          782  +      VdbeCoverageIf(v, pX->op==TK_GE);
          783  +      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
          784  +      sqlite3ReleaseTempReg(pParse, rTemp);
          785  +      disableTerm(pLevel, pStart);
          786  +    }else{
          787  +      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
          788  +      VdbeCoverageIf(v, bRev==0);
          789  +      VdbeCoverageIf(v, bRev!=0);
          790  +    }
          791  +    if( pEnd ){
          792  +      Expr *pX;
          793  +      pX = pEnd->pExpr;
          794  +      assert( pX!=0 );
          795  +      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
          796  +      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
          797  +      testcase( pEnd->wtFlags & TERM_VIRTUAL );
          798  +      memEndValue = ++pParse->nMem;
          799  +      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
          800  +      if( pX->op==TK_LT || pX->op==TK_GT ){
          801  +        testOp = bRev ? OP_Le : OP_Ge;
          802  +      }else{
          803  +        testOp = bRev ? OP_Lt : OP_Gt;
          804  +      }
          805  +      disableTerm(pLevel, pEnd);
          806  +    }
          807  +    start = sqlite3VdbeCurrentAddr(v);
          808  +    pLevel->op = bRev ? OP_Prev : OP_Next;
          809  +    pLevel->p1 = iCur;
          810  +    pLevel->p2 = start;
          811  +    assert( pLevel->p5==0 );
          812  +    if( testOp!=OP_Noop ){
          813  +      iRowidReg = ++pParse->nMem;
          814  +      sqlite3VdbeAddOp2(v, OP_Rowid, iCur, iRowidReg);
          815  +      sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
          816  +      sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, iRowidReg);
          817  +      VdbeCoverageIf(v, testOp==OP_Le);
          818  +      VdbeCoverageIf(v, testOp==OP_Lt);
          819  +      VdbeCoverageIf(v, testOp==OP_Ge);
          820  +      VdbeCoverageIf(v, testOp==OP_Gt);
          821  +      sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);
          822  +    }
          823  +  }else if( pLoop->wsFlags & WHERE_INDEXED ){
          824  +    /* Case 4: A scan using an index.
          825  +    **
          826  +    **         The WHERE clause may contain zero or more equality 
          827  +    **         terms ("==" or "IN" operators) that refer to the N
          828  +    **         left-most columns of the index. It may also contain
          829  +    **         inequality constraints (>, <, >= or <=) on the indexed
          830  +    **         column that immediately follows the N equalities. Only 
          831  +    **         the right-most column can be an inequality - the rest must
          832  +    **         use the "==" and "IN" operators. For example, if the 
          833  +    **         index is on (x,y,z), then the following clauses are all 
          834  +    **         optimized:
          835  +    **
          836  +    **            x=5
          837  +    **            x=5 AND y=10
          838  +    **            x=5 AND y<10
          839  +    **            x=5 AND y>5 AND y<10
          840  +    **            x=5 AND y=5 AND z<=10
          841  +    **
          842  +    **         The z<10 term of the following cannot be used, only
          843  +    **         the x=5 term:
          844  +    **
          845  +    **            x=5 AND z<10
          846  +    **
          847  +    **         N may be zero if there are inequality constraints.
          848  +    **         If there are no inequality constraints, then N is at
          849  +    **         least one.
          850  +    **
          851  +    **         This case is also used when there are no WHERE clause
          852  +    **         constraints but an index is selected anyway, in order
          853  +    **         to force the output order to conform to an ORDER BY.
          854  +    */  
          855  +    static const u8 aStartOp[] = {
          856  +      0,
          857  +      0,
          858  +      OP_Rewind,           /* 2: (!start_constraints && startEq &&  !bRev) */
          859  +      OP_Last,             /* 3: (!start_constraints && startEq &&   bRev) */
          860  +      OP_SeekGT,           /* 4: (start_constraints  && !startEq && !bRev) */
          861  +      OP_SeekLT,           /* 5: (start_constraints  && !startEq &&  bRev) */
          862  +      OP_SeekGE,           /* 6: (start_constraints  &&  startEq && !bRev) */
          863  +      OP_SeekLE            /* 7: (start_constraints  &&  startEq &&  bRev) */
          864  +    };
          865  +    static const u8 aEndOp[] = {
          866  +      OP_IdxGE,            /* 0: (end_constraints && !bRev && !endEq) */
          867  +      OP_IdxGT,            /* 1: (end_constraints && !bRev &&  endEq) */
          868  +      OP_IdxLE,            /* 2: (end_constraints &&  bRev && !endEq) */
          869  +      OP_IdxLT,            /* 3: (end_constraints &&  bRev &&  endEq) */
          870  +    };
          871  +    u16 nEq = pLoop->u.btree.nEq;     /* Number of == or IN terms */
          872  +    int regBase;                 /* Base register holding constraint values */
          873  +    WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
          874  +    WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
          875  +    int startEq;                 /* True if range start uses ==, >= or <= */
          876  +    int endEq;                   /* True if range end uses ==, >= or <= */
          877  +    int start_constraints;       /* Start of range is constrained */
          878  +    int nConstraint;             /* Number of constraint terms */
          879  +    Index *pIdx;                 /* The index we will be using */
          880  +    int iIdxCur;                 /* The VDBE cursor for the index */
          881  +    int nExtraReg = 0;           /* Number of extra registers needed */
          882  +    int op;                      /* Instruction opcode */
          883  +    char *zStartAff;             /* Affinity for start of range constraint */
          884  +    char cEndAff = 0;            /* Affinity for end of range constraint */
          885  +    u8 bSeekPastNull = 0;        /* True to seek past initial nulls */
          886  +    u8 bStopAtNull = 0;          /* Add condition to terminate at NULLs */
          887  +
          888  +    pIdx = pLoop->u.btree.pIndex;
          889  +    iIdxCur = pLevel->iIdxCur;
          890  +    assert( nEq>=pLoop->nSkip );
          891  +
          892  +    /* If this loop satisfies a sort order (pOrderBy) request that 
          893  +    ** was passed to this function to implement a "SELECT min(x) ..." 
          894  +    ** query, then the caller will only allow the loop to run for
          895  +    ** a single iteration. This means that the first row returned
          896  +    ** should not have a NULL value stored in 'x'. If column 'x' is
          897  +    ** the first one after the nEq equality constraints in the index,
          898  +    ** this requires some special handling.
          899  +    */
          900  +    assert( pWInfo->pOrderBy==0
          901  +         || pWInfo->pOrderBy->nExpr==1
          902  +         || (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 );
          903  +    if( (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)!=0
          904  +     && pWInfo->nOBSat>0
          905  +     && (pIdx->nKeyCol>nEq)
          906  +    ){
          907  +      assert( pLoop->nSkip==0 );
          908  +      bSeekPastNull = 1;
          909  +      nExtraReg = 1;
          910  +    }
          911  +
          912  +    /* Find any inequality constraint terms for the start and end 
          913  +    ** of the range. 
          914  +    */
          915  +    j = nEq;
          916  +    if( pLoop->wsFlags & WHERE_BTM_LIMIT ){
          917  +      pRangeStart = pLoop->aLTerm[j++];
          918  +      nExtraReg = 1;
          919  +      /* Like optimization range constraints always occur in pairs */
          920  +      assert( (pRangeStart->wtFlags & TERM_LIKEOPT)==0 || 
          921  +              (pLoop->wsFlags & WHERE_TOP_LIMIT)!=0 );
          922  +    }
          923  +    if( pLoop->wsFlags & WHERE_TOP_LIMIT ){
          924  +      pRangeEnd = pLoop->aLTerm[j++];
          925  +      nExtraReg = 1;
          926  +      if( (pRangeEnd->wtFlags & TERM_LIKEOPT)!=0 ){
          927  +        assert( pRangeStart!=0 );                     /* LIKE opt constraints */
          928  +        assert( pRangeStart->wtFlags & TERM_LIKEOPT );   /* occur in pairs */
          929  +        pLevel->iLikeRepCntr = ++pParse->nMem;
          930  +        testcase( bRev );
          931  +        testcase( pIdx->aSortOrder[nEq]==SQLITE_SO_DESC );
          932  +        sqlite3VdbeAddOp2(v, OP_Integer,
          933  +                          bRev ^ (pIdx->aSortOrder[nEq]==SQLITE_SO_DESC),
          934  +                          pLevel->iLikeRepCntr);
          935  +        VdbeComment((v, "LIKE loop counter"));
          936  +        pLevel->addrLikeRep = sqlite3VdbeCurrentAddr(v);
          937  +      }
          938  +      if( pRangeStart==0
          939  +       && (j = pIdx->aiColumn[nEq])>=0 
          940  +       && pIdx->pTable->aCol[j].notNull==0
          941  +      ){
          942  +        bSeekPastNull = 1;
          943  +      }
          944  +    }
          945  +    assert( pRangeEnd==0 || (pRangeEnd->wtFlags & TERM_VNULL)==0 );
          946  +
          947  +    /* Generate code to evaluate all constraint terms using == or IN
          948  +    ** and store the values of those terms in an array of registers
          949  +    ** starting at regBase.
          950  +    */
          951  +    regBase = codeAllEqualityTerms(pParse,pLevel,bRev,nExtraReg,&zStartAff);
          952  +    assert( zStartAff==0 || sqlite3Strlen30(zStartAff)>=nEq );
          953  +    if( zStartAff ) cEndAff = zStartAff[nEq];
          954  +    addrNxt = pLevel->addrNxt;
          955  +
          956  +    /* If we are doing a reverse order scan on an ascending index, or
          957  +    ** a forward order scan on a descending index, interchange the 
          958  +    ** start and end terms (pRangeStart and pRangeEnd).
          959  +    */
          960  +    if( (nEq<pIdx->nKeyCol && bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC))
          961  +     || (bRev && pIdx->nKeyCol==nEq)
          962  +    ){
          963  +      SWAP(WhereTerm *, pRangeEnd, pRangeStart);
          964  +      SWAP(u8, bSeekPastNull, bStopAtNull);
          965  +    }
          966  +
          967  +    testcase( pRangeStart && (pRangeStart->eOperator & WO_LE)!=0 );
          968  +    testcase( pRangeStart && (pRangeStart->eOperator & WO_GE)!=0 );
          969  +    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 );
          970  +    testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 );
          971  +    startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
          972  +    endEq =   !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE);
          973  +    start_constraints = pRangeStart || nEq>0;
          974  +
          975  +    /* Seek the index cursor to the start of the range. */
          976  +    nConstraint = nEq;
          977  +    if( pRangeStart ){
          978  +      Expr *pRight = pRangeStart->pExpr->pRight;
          979  +      sqlite3ExprCode(pParse, pRight, regBase+nEq);
          980  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
          981  +      if( (pRangeStart->wtFlags & TERM_VNULL)==0
          982  +       && sqlite3ExprCanBeNull(pRight)
          983  +      ){
          984  +        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
          985  +        VdbeCoverage(v);
          986  +      }
          987  +      if( zStartAff ){
          988  +        if( sqlite3CompareAffinity(pRight, zStartAff[nEq])==SQLITE_AFF_BLOB){
          989  +          /* Since the comparison is to be performed with no conversions
          990  +          ** applied to the operands, set the affinity to apply to pRight to 
          991  +          ** SQLITE_AFF_BLOB.  */
          992  +          zStartAff[nEq] = SQLITE_AFF_BLOB;
          993  +        }
          994  +        if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){
          995  +          zStartAff[nEq] = SQLITE_AFF_BLOB;
          996  +        }
          997  +      }  
          998  +      nConstraint++;
          999  +      testcase( pRangeStart->wtFlags & TERM_VIRTUAL );
         1000  +    }else if( bSeekPastNull ){
         1001  +      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
         1002  +      nConstraint++;
         1003  +      startEq = 0;
         1004  +      start_constraints = 1;
         1005  +    }
         1006  +    codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
         1007  +    op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
         1008  +    assert( op!=0 );
         1009  +    sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
         1010  +    VdbeCoverage(v);
         1011  +    VdbeCoverageIf(v, op==OP_Rewind);  testcase( op==OP_Rewind );
         1012  +    VdbeCoverageIf(v, op==OP_Last);    testcase( op==OP_Last );
         1013  +    VdbeCoverageIf(v, op==OP_SeekGT);  testcase( op==OP_SeekGT );
         1014  +    VdbeCoverageIf(v, op==OP_SeekGE);  testcase( op==OP_SeekGE );
         1015  +    VdbeCoverageIf(v, op==OP_SeekLE);  testcase( op==OP_SeekLE );
         1016  +    VdbeCoverageIf(v, op==OP_SeekLT);  testcase( op==OP_SeekLT );
         1017  +
         1018  +    /* Load the value for the inequality constraint at the end of the
         1019  +    ** range (if any).
         1020  +    */
         1021  +    nConstraint = nEq;
         1022  +    if( pRangeEnd ){
         1023  +      Expr *pRight = pRangeEnd->pExpr->pRight;
         1024  +      sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
         1025  +      sqlite3ExprCode(pParse, pRight, regBase+nEq);
         1026  +      whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
         1027  +      if( (pRangeEnd->wtFlags & TERM_VNULL)==0
         1028  +       && sqlite3ExprCanBeNull(pRight)
         1029  +      ){
         1030  +        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
         1031  +        VdbeCoverage(v);
         1032  +      }
         1033  +      if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_BLOB
         1034  +       && !sqlite3ExprNeedsNoAffinityChange(pRight, cEndAff)
         1035  +      ){
         1036  +        codeApplyAffinity(pParse, regBase+nEq, 1, &cEndAff);
         1037  +      }
         1038  +      nConstraint++;
         1039  +      testcase( pRangeEnd->wtFlags & TERM_VIRTUAL );
         1040  +    }else if( bStopAtNull ){
         1041  +      sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
         1042  +      endEq = 0;
         1043  +      nConstraint++;
         1044  +    }
         1045  +    sqlite3DbFree(db, zStartAff);
         1046  +
         1047  +    /* Top of the loop body */
         1048  +    pLevel->p2 = sqlite3VdbeCurrentAddr(v);
         1049  +
         1050  +    /* Check if the index cursor is past the end of the range. */
         1051  +    if( nConstraint ){
         1052  +      op = aEndOp[bRev*2 + endEq];
         1053  +      sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
         1054  +      testcase( op==OP_IdxGT );  VdbeCoverageIf(v, op==OP_IdxGT );
         1055  +      testcase( op==OP_IdxGE );  VdbeCoverageIf(v, op==OP_IdxGE );
         1056  +      testcase( op==OP_IdxLT );  VdbeCoverageIf(v, op==OP_IdxLT );
         1057  +      testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
         1058  +    }
         1059  +
         1060  +    /* Seek the table cursor, if required */
         1061  +    disableTerm(pLevel, pRangeStart);
         1062  +    disableTerm(pLevel, pRangeEnd);
         1063  +    if( omitTable ){
         1064  +      /* pIdx is a covering index.  No need to access the main table. */
         1065  +    }else if( HasRowid(pIdx->pTable) ){
         1066  +      iRowidReg = ++pParse->nMem;
         1067  +      sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
         1068  +      sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
         1069  +      sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg);  /* Deferred seek */
         1070  +    }else if( iCur!=iIdxCur ){
         1071  +      Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
         1072  +      iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol);
         1073  +      for(j=0; j<pPk->nKeyCol; j++){
         1074  +        k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
         1075  +        sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
         1076  +      }
         1077  +      sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
         1078  +                           iRowidReg, pPk->nKeyCol); VdbeCoverage(v);
         1079  +    }
         1080  +
         1081  +    /* Record the instruction used to terminate the loop. Disable 
         1082  +    ** WHERE clause terms made redundant by the index range scan.
         1083  +    */
         1084  +    if( pLoop->wsFlags & WHERE_ONEROW ){
         1085  +      pLevel->op = OP_Noop;
         1086  +    }else if( bRev ){
         1087  +      pLevel->op = OP_Prev;
         1088  +    }else{
         1089  +      pLevel->op = OP_Next;
         1090  +    }
         1091  +    pLevel->p1 = iIdxCur;
         1092  +    pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0;
         1093  +    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
         1094  +      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
         1095  +    }else{
         1096  +      assert( pLevel->p5==0 );
         1097  +    }
         1098  +  }else
         1099  +
         1100  +#ifndef SQLITE_OMIT_OR_OPTIMIZATION
         1101  +  if( pLoop->wsFlags & WHERE_MULTI_OR ){
         1102  +    /* Case 5:  Two or more separately indexed terms connected by OR
         1103  +    **
         1104  +    ** Example:
         1105  +    **
         1106  +    **   CREATE TABLE t1(a,b,c,d);
         1107  +    **   CREATE INDEX i1 ON t1(a);
         1108  +    **   CREATE INDEX i2 ON t1(b);
         1109  +    **   CREATE INDEX i3 ON t1(c);
         1110  +    **
         1111  +    **   SELECT * FROM t1 WHERE a=5 OR b=7 OR (c=11 AND d=13)
         1112  +    **
         1113  +    ** In the example, there are three indexed terms connected by OR.
         1114  +    ** The top of the loop looks like this:
         1115  +    **
         1116  +    **          Null       1                # Zero the rowset in reg 1
         1117  +    **
         1118  +    ** Then, for each indexed term, the following. The arguments to
         1119  +    ** RowSetTest are such that the rowid of the current row is inserted
         1120  +    ** into the RowSet. If it is already present, control skips the
         1121  +    ** Gosub opcode and jumps straight to the code generated by WhereEnd().
         1122  +    **
         1123  +    **        sqlite3WhereBegin(<term>)
         1124  +    **          RowSetTest                  # Insert rowid into rowset
         1125  +    **          Gosub      2 A
         1126  +    **        sqlite3WhereEnd()
         1127  +    **
         1128  +    ** Following the above, code to terminate the loop. Label A, the target
         1129  +    ** of the Gosub above, jumps to the instruction right after the Goto.
         1130  +    **
         1131  +    **          Null       1                # Zero the rowset in reg 1
         1132  +    **          Goto       B                # The loop is finished.
         1133  +    **
         1134  +    **       A: <loop body>                 # Return data, whatever.
         1135  +    **
         1136  +    **          Return     2                # Jump back to the Gosub
         1137  +    **
         1138  +    **       B: <after the loop>
         1139  +    **
         1140  +    ** Added 2014-05-26: If the table is a WITHOUT ROWID table, then
         1141  +    ** use an ephemeral index instead of a RowSet to record the primary
         1142  +    ** keys of the rows we have already seen.
         1143  +    **
         1144  +    */
         1145  +    WhereClause *pOrWc;    /* The OR-clause broken out into subterms */
         1146  +    SrcList *pOrTab;       /* Shortened table list or OR-clause generation */
         1147  +    Index *pCov = 0;             /* Potential covering index (or NULL) */
         1148  +    int iCovCur = pParse->nTab++;  /* Cursor used for index scans (if any) */
         1149  +
         1150  +    int regReturn = ++pParse->nMem;           /* Register used with OP_Gosub */
         1151  +    int regRowset = 0;                        /* Register for RowSet object */
         1152  +    int regRowid = 0;                         /* Register holding rowid */
         1153  +    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
         1154  +    int iRetInit;                             /* Address of regReturn init */
         1155  +    int untestedTerms = 0;             /* Some terms not completely tested */
         1156  +    int ii;                            /* Loop counter */
         1157  +    u16 wctrlFlags;                    /* Flags for sub-WHERE clause */
         1158  +    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
         1159  +    Table *pTab = pTabItem->pTab;
         1160  +   
         1161  +    pTerm = pLoop->aLTerm[0];
         1162  +    assert( pTerm!=0 );
         1163  +    assert( pTerm->eOperator & WO_OR );
         1164  +    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
         1165  +    pOrWc = &pTerm->u.pOrInfo->wc;
         1166  +    pLevel->op = OP_Return;
         1167  +    pLevel->p1 = regReturn;
         1168  +
         1169  +    /* Set up a new SrcList in pOrTab containing the table being scanned
         1170  +    ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
         1171  +    ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
         1172  +    */
         1173  +    if( pWInfo->nLevel>1 ){
         1174  +      int nNotReady;                 /* The number of notReady tables */
         1175  +      struct SrcList_item *origSrc;     /* Original list of tables */
         1176  +      nNotReady = pWInfo->nLevel - iLevel - 1;
         1177  +      pOrTab = sqlite3StackAllocRaw(db,
         1178  +                            sizeof(*pOrTab)+ nNotReady*sizeof(pOrTab->a[0]));
         1179  +      if( pOrTab==0 ) return notReady;
         1180  +      pOrTab->nAlloc = (u8)(nNotReady + 1);
         1181  +      pOrTab->nSrc = pOrTab->nAlloc;
         1182  +      memcpy(pOrTab->a, pTabItem, sizeof(*pTabItem));
         1183  +      origSrc = pWInfo->pTabList->a;
         1184  +      for(k=1; k<=nNotReady; k++){
         1185  +        memcpy(&pOrTab->a[k], &origSrc[pLevel[k].iFrom], sizeof(pOrTab->a[k]));
         1186  +      }
         1187  +    }else{
         1188  +      pOrTab = pWInfo->pTabList;
         1189  +    }
         1190  +
         1191  +    /* Initialize the rowset register to contain NULL. An SQL NULL is 
         1192  +    ** equivalent to an empty rowset.  Or, create an ephemeral index
         1193  +    ** capable of holding primary keys in the case of a WITHOUT ROWID.
         1194  +    **
         1195  +    ** Also initialize regReturn to contain the address of the instruction 
         1196  +    ** immediately following the OP_Return at the bottom of the loop. This
         1197  +    ** is required in a few obscure LEFT JOIN cases where control jumps
         1198  +    ** over the top of the loop into the body of it. In this case the 
         1199  +    ** correct response for the end-of-loop code (the OP_Return) is to 
         1200  +    ** fall through to the next instruction, just as an OP_Next does if
         1201  +    ** called on an uninitialized cursor.
         1202  +    */
         1203  +    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
         1204  +      if( HasRowid(pTab) ){
         1205  +        regRowset = ++pParse->nMem;
         1206  +        sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset);
         1207  +      }else{
         1208  +        Index *pPk = sqlite3PrimaryKeyIndex(pTab);
         1209  +        regRowset = pParse->nTab++;
         1210  +        sqlite3VdbeAddOp2(v, OP_OpenEphemeral, regRowset, pPk->nKeyCol);
         1211  +        sqlite3VdbeSetP4KeyInfo(pParse, pPk);
         1212  +      }
         1213  +      regRowid = ++pParse->nMem;
         1214  +    }
         1215  +    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);
         1216  +
         1217  +    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
         1218  +    ** Then for every term xN, evaluate as the subexpression: xN AND z
         1219  +    ** That way, terms in y that are factored into the disjunction will
         1220  +    ** be picked up by the recursive calls to sqlite3WhereBegin() below.
         1221  +    **
         1222  +    ** Actually, each subexpression is converted to "xN AND w" where w is
         1223  +    ** the "interesting" terms of z - terms that did not originate in the
         1224  +    ** ON or USING clause of a LEFT JOIN, and terms that are usable as 
         1225  +    ** indices.
         1226  +    **
         1227  +    ** This optimization also only applies if the (x1 OR x2 OR ...) term
         1228  +    ** is not contained in the ON clause of a LEFT JOIN.
         1229  +    ** See ticket http://www.sqlite.org/src/info/f2369304e4
         1230  +    */
         1231  +    if( pWC->nTerm>1 ){
         1232  +      int iTerm;
         1233  +      for(iTerm=0; iTerm<pWC->nTerm; iTerm++){
         1234  +        Expr *pExpr = pWC->a[iTerm].pExpr;
         1235  +        if( &pWC->a[iTerm] == pTerm ) continue;
         1236  +        if( ExprHasProperty(pExpr, EP_FromJoin) ) continue;
         1237  +        if( (pWC->a[iTerm].wtFlags & TERM_VIRTUAL)!=0 ) continue;
         1238  +        if( (pWC->a[iTerm].eOperator & WO_ALL)==0 ) continue;
         1239  +        testcase( pWC->a[iTerm].wtFlags & TERM_ORINFO );
         1240  +        pExpr = sqlite3ExprDup(db, pExpr, 0);
         1241  +        pAndExpr = sqlite3ExprAnd(db, pAndExpr, pExpr);
         1242  +      }
         1243  +      if( pAndExpr ){
         1244  +        pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
         1245  +      }
         1246  +    }
         1247  +
         1248  +    /* Run a separate WHERE clause for each term of the OR clause.  After
         1249  +    ** eliminating duplicates from other WHERE clauses, the action for each
         1250  +    ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
         1251  +    */
         1252  +    wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
         1253  +                | WHERE_FORCE_TABLE
         1254  +                | WHERE_ONETABLE_ONLY
         1255  +                | WHERE_NO_AUTOINDEX;
         1256  +    for(ii=0; ii<pOrWc->nTerm; ii++){
         1257  +      WhereTerm *pOrTerm = &pOrWc->a[ii];
         1258  +      if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
         1259  +        WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
         1260  +        Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
         1261  +        int j1 = 0;                     /* Address of jump operation */
         1262  +        if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){
         1263  +          pAndExpr->pLeft = pOrExpr;
         1264  +          pOrExpr = pAndExpr;
         1265  +        }
         1266  +        /* Loop through table entries that match term pOrTerm. */
         1267  +        WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
         1268  +        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
         1269  +                                      wctrlFlags, iCovCur);
         1270  +        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
         1271  +        if( pSubWInfo ){
         1272  +          WhereLoop *pSubLoop;
         1273  +          int addrExplain = sqlite3WhereExplainOneScan(
         1274  +              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
         1275  +          );
         1276  +          sqlite3WhereAddScanStatus(v, pOrTab, &pSubWInfo->a[0], addrExplain);
         1277  +
         1278  +          /* This is the sub-WHERE clause body.  First skip over
         1279  +          ** duplicate rows from prior sub-WHERE clauses, and record the
         1280  +          ** rowid (or PRIMARY KEY) for the current row so that the same
         1281  +          ** row will be skipped in subsequent sub-WHERE clauses.
         1282  +          */
         1283  +          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
         1284  +            int r;
         1285  +            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
         1286  +            if( HasRowid(pTab) ){
         1287  +              r = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, regRowid, 0);
         1288  +              j1 = sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, 0, r,iSet);
         1289  +              VdbeCoverage(v);
         1290  +            }else{
         1291  +              Index *pPk = sqlite3PrimaryKeyIndex(pTab);
         1292  +              int nPk = pPk->nKeyCol;
         1293  +              int iPk;
         1294  +
         1295  +              /* Read the PK into an array of temp registers. */
         1296  +              r = sqlite3GetTempRange(pParse, nPk);
         1297  +              for(iPk=0; iPk<nPk; iPk++){
         1298  +                int iCol = pPk->aiColumn[iPk];
         1299  +                sqlite3ExprCodeGetColumn(pParse, pTab, iCol, iCur, r+iPk, 0);
         1300  +              }
         1301  +
         1302  +              /* Check if the temp table already contains this key. If so,
         1303  +              ** the row has already been included in the result set and
         1304  +              ** can be ignored (by jumping past the Gosub below). Otherwise,
         1305  +              ** insert the key into the temp table and proceed with processing
         1306  +              ** the row.
         1307  +              **
         1308  +              ** Use some of the same optimizations as OP_RowSetTest: If iSet
         1309  +              ** is zero, assume that the key cannot already be present in
         1310  +              ** the temp table. And if iSet is -1, assume that there is no 
         1311  +              ** need to insert the key into the temp table, as it will never 
         1312  +              ** be tested for.  */ 
         1313  +              if( iSet ){
         1314  +                j1 = sqlite3VdbeAddOp4Int(v, OP_Found, regRowset, 0, r, nPk);
         1315  +                VdbeCoverage(v);
         1316  +              }
         1317  +              if( iSet>=0 ){
         1318  +                sqlite3VdbeAddOp3(v, OP_MakeRecord, r, nPk, regRowid);
         1319  +                sqlite3VdbeAddOp3(v, OP_IdxInsert, regRowset, regRowid, 0);
         1320  +                if( iSet ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
         1321  +              }
         1322  +
         1323  +              /* Release the array of temp registers */
         1324  +              sqlite3ReleaseTempRange(pParse, r, nPk);
         1325  +            }
         1326  +          }
         1327  +
         1328  +          /* Invoke the main loop body as a subroutine */
         1329  +          sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);
         1330  +
         1331  +          /* Jump here (skipping the main loop body subroutine) if the
         1332  +          ** current sub-WHERE row is a duplicate from prior sub-WHEREs. */
         1333  +          if( j1 ) sqlite3VdbeJumpHere(v, j1);
         1334  +
         1335  +          /* The pSubWInfo->untestedTerms flag means that this OR term
         1336  +          ** contained one or more AND term from a notReady table.  The
         1337  +          ** terms from the notReady table could not be tested and will
         1338  +          ** need to be tested later.
         1339  +          */
         1340  +          if( pSubWInfo->untestedTerms ) untestedTerms = 1;
         1341  +
         1342  +          /* If all of the OR-connected terms are optimized using the same
         1343  +          ** index, and the index is opened using the same cursor number
         1344  +          ** by each call to sqlite3WhereBegin() made by this loop, it may
         1345  +          ** be possible to use that index as a covering index.
         1346  +          **
         1347  +          ** If the call to sqlite3WhereBegin() above resulted in a scan that
         1348  +          ** uses an index, and this is either the first OR-connected term
         1349  +          ** processed or the index is the same as that used by all previous
         1350  +          ** terms, set pCov to the candidate covering index. Otherwise, set 
         1351  +          ** pCov to NULL to indicate that no candidate covering index will 
         1352  +          ** be available.
         1353  +          */
         1354  +          pSubLoop = pSubWInfo->a[0].pWLoop;
         1355  +          assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
         1356  +          if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
         1357  +           && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
         1358  +           && (HasRowid(pTab) || !IsPrimaryKeyIndex(pSubLoop->u.btree.pIndex))
         1359  +          ){
         1360  +            assert( pSubWInfo->a[0].iIdxCur==iCovCur );
         1361  +            pCov = pSubLoop->u.btree.pIndex;
         1362  +            wctrlFlags |= WHERE_REOPEN_IDX;
         1363  +          }else{
         1364  +            pCov = 0;
         1365  +          }
         1366  +
         1367  +          /* Finish the loop through table entries that match term pOrTerm. */
         1368  +          sqlite3WhereEnd(pSubWInfo);
         1369  +        }
         1370  +      }
         1371  +    }
         1372  +    pLevel->u.pCovidx = pCov;
         1373  +    if( pCov ) pLevel->iIdxCur = iCovCur;
         1374  +    if( pAndExpr ){
         1375  +      pAndExpr->pLeft = 0;
         1376  +      sqlite3ExprDelete(db, pAndExpr);
         1377  +    }
         1378  +    sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
         1379  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->addrBrk);
         1380  +    sqlite3VdbeResolveLabel(v, iLoopBody);
         1381  +
         1382  +    if( pWInfo->nLevel>1 ) sqlite3StackFree(db, pOrTab);
         1383  +    if( !untestedTerms ) disableTerm(pLevel, pTerm);
         1384  +  }else
         1385  +#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
         1386  +
         1387  +  {
         1388  +    /* Case 6:  There is no usable index.  We must do a complete
         1389  +    **          scan of the entire table.
         1390  +    */
         1391  +    static const u8 aStep[] = { OP_Next, OP_Prev };
         1392  +    static const u8 aStart[] = { OP_Rewind, OP_Last };
         1393  +    assert( bRev==0 || bRev==1 );
         1394  +    if( pTabItem->isRecursive ){
         1395  +      /* Tables marked isRecursive have only a single row that is stored in
         1396  +      ** a pseudo-cursor.  No need to Rewind or Next such cursors. */
         1397  +      pLevel->op = OP_Noop;
         1398  +    }else{
         1399  +      pLevel->op = aStep[bRev];
         1400  +      pLevel->p1 = iCur;
         1401  +      pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk);
         1402  +      VdbeCoverageIf(v, bRev==0);
         1403  +      VdbeCoverageIf(v, bRev!=0);
         1404  +      pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
         1405  +    }
         1406  +  }
         1407  +
         1408  +#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
         1409  +  pLevel->addrVisit = sqlite3VdbeCurrentAddr(v);
         1410  +#endif
         1411  +
         1412  +  /* Insert code to test every subexpression that can be completely
         1413  +  ** computed using the current set of tables.
         1414  +  */
         1415  +  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
         1416  +    Expr *pE;
         1417  +    int skipLikeAddr = 0;
         1418  +    testcase( pTerm->wtFlags & TERM_VIRTUAL );
         1419  +    testcase( pTerm->wtFlags & TERM_CODED );
         1420  +    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
         1421  +    if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
         1422  +      testcase( pWInfo->untestedTerms==0
         1423  +               && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 );
         1424  +      pWInfo->untestedTerms = 1;
         1425  +      continue;
         1426  +    }
         1427  +    pE = pTerm->pExpr;
         1428  +    assert( pE!=0 );
         1429  +    if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
         1430  +      continue;
         1431  +    }
         1432  +    if( pTerm->wtFlags & TERM_LIKECOND ){
         1433  +      assert( pLevel->iLikeRepCntr>0 );
         1434  +      skipLikeAddr = sqlite3VdbeAddOp1(v, OP_IfNot, pLevel->iLikeRepCntr);
         1435  +      VdbeCoverage(v);
         1436  +    }
         1437  +    sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
         1438  +    if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
         1439  +    pTerm->wtFlags |= TERM_CODED;
         1440  +  }
         1441  +
         1442  +  /* Insert code to test for implied constraints based on transitivity
         1443  +  ** of the "==" operator.
         1444  +  **
         1445  +  ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
         1446  +  ** and we are coding the t1 loop and the t2 loop has not yet coded,
         1447  +  ** then we cannot use the "t1.a=t2.b" constraint, but we can code
         1448  +  ** the implied "t1.a=123" constraint.
         1449  +  */
         1450  +  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
         1451  +    Expr *pE, *pEAlt;
         1452  +    WhereTerm *pAlt;
         1453  +    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
         1454  +    if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
         1455  +    if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
         1456  +    if( pTerm->leftCursor!=iCur ) continue;
         1457  +    if( pLevel->iLeftJoin ) continue;
         1458  +    pE = pTerm->pExpr;
         1459  +    assert( !ExprHasProperty(pE, EP_FromJoin) );
         1460  +    assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
         1461  +    pAlt = sqlite3WhereFindTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
         1462  +                    WO_EQ|WO_IN|WO_IS, 0);
         1463  +    if( pAlt==0 ) continue;
         1464  +    if( pAlt->wtFlags & (TERM_CODED) ) continue;
         1465  +    testcase( pAlt->eOperator & WO_EQ );
         1466  +    testcase( pAlt->eOperator & WO_IS );
         1467  +    testcase( pAlt->eOperator & WO_IN );
         1468  +    VdbeModuleComment((v, "begin transitive constraint"));
         1469  +    pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
         1470  +    if( pEAlt ){
         1471  +      *pEAlt = *pAlt->pExpr;
         1472  +      pEAlt->pLeft = pE->pLeft;
         1473  +      sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
         1474  +      sqlite3StackFree(db, pEAlt);
         1475  +    }
         1476  +  }
         1477  +
         1478  +  /* For a LEFT OUTER JOIN, generate code that will record the fact that
         1479  +  ** at least one row of the right table has matched the left table.  
         1480  +  */
         1481  +  if( pLevel->iLeftJoin ){
         1482  +    pLevel->addrFirst = sqlite3VdbeCurrentAddr(v);
         1483  +    sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
         1484  +    VdbeComment((v, "record LEFT JOIN hit"));
         1485  +    sqlite3ExprCacheClear(pParse);
         1486  +    for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){
         1487  +      testcase( pTerm->wtFlags & TERM_VIRTUAL );
         1488  +      testcase( pTerm->wtFlags & TERM_CODED );
         1489  +      if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
         1490  +      if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
         1491  +        assert( pWInfo->untestedTerms );
         1492  +        continue;
         1493  +      }
         1494  +      assert( pTerm->pExpr );
         1495  +      sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL);
         1496  +      pTerm->wtFlags |= TERM_CODED;
         1497  +    }
         1498  +  }
         1499  +
         1500  +  return pLevel->notReady;
         1501  +}

Changes to tool/mksqlite3c.tcl.

   339    339      prepare.c
   340    340      select.c
   341    341      table.c
   342    342      trigger.c
   343    343      update.c
   344    344      vacuum.c
   345    345      vtab.c
          346  +   wherecode.c
   346    347      where.c
   347    348   
   348    349      parse.c
   349    350   
   350    351      tokenize.c
   351    352      complete.c
   352    353