sl@0: /*
sl@0: ** 2001 September 15
sl@0: **
sl@0: ** The author disclaims copyright to this source code.  In place of
sl@0: ** a legal notice, here is a blessing:
sl@0: **
sl@0: **    May you do good and not evil.
sl@0: **    May you find forgiveness for yourself and forgive others.
sl@0: **    May you share freely, never taking more than you give.
sl@0: **
sl@0: *************************************************************************
sl@0: ** This module contains C code that generates VDBE code used to process
sl@0: ** the WHERE clause of SQL statements.  This module is responsible for
sl@0: ** generating the code that loops through a table looking for applicable
sl@0: ** rows.  Indices are selected and used to speed the search when doing
sl@0: ** so is applicable.  Because this module is responsible for selecting
sl@0: ** indices, you might also think of this module as the "query optimizer".
sl@0: **
sl@0: ** $Id: where.c,v 1.326 2008/10/11 16:47:36 drh Exp $
sl@0: */
sl@0: #include "sqliteInt.h"
sl@0: 
sl@0: /*
sl@0: ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
sl@0: */
sl@0: #define BMS  (sizeof(Bitmask)*8)
sl@0: 
sl@0: /*
sl@0: ** Trace output macros
sl@0: */
sl@0: #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
sl@0: int sqlite3WhereTrace = 0;
sl@0: #endif
sl@0: #if 0
sl@0: # define WHERETRACE(X)  if(sqlite3WhereTrace) sqlite3DebugPrintf X
sl@0: #else
sl@0: # define WHERETRACE(X)
sl@0: #endif
sl@0: 
sl@0: /* Forward reference
sl@0: */
sl@0: typedef struct WhereClause WhereClause;
sl@0: typedef struct ExprMaskSet ExprMaskSet;
sl@0: 
sl@0: /*
sl@0: ** The query generator uses an array of instances of this structure to
sl@0: ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
sl@0: ** clause subexpression is separated from the others by an AND operator.
sl@0: **
sl@0: ** All WhereTerms are collected into a single WhereClause structure.  
sl@0: ** The following identity holds:
sl@0: **
sl@0: **        WhereTerm.pWC->a[WhereTerm.idx] == WhereTerm
sl@0: **
sl@0: ** When a term is of the form:
sl@0: **
sl@0: **              X <op> <expr>
sl@0: **
sl@0: ** where X is a column name and <op> is one of certain operators,
sl@0: ** then WhereTerm.leftCursor and WhereTerm.leftColumn record the
sl@0: ** cursor number and column number for X.  WhereTerm.operator records
sl@0: ** the <op> using a bitmask encoding defined by WO_xxx below.  The
sl@0: ** use of a bitmask encoding for the operator allows us to search
sl@0: ** quickly for terms that match any of several different operators.
sl@0: **
sl@0: ** prereqRight and prereqAll record sets of cursor numbers,
sl@0: ** but they do so indirectly.  A single ExprMaskSet structure translates
sl@0: ** cursor number into bits and the translated bit is stored in the prereq
sl@0: ** fields.  The translation is used in order to maximize the number of
sl@0: ** bits that will fit in a Bitmask.  The VDBE cursor numbers might be
sl@0: ** spread out over the non-negative integers.  For example, the cursor
sl@0: ** numbers might be 3, 8, 9, 10, 20, 23, 41, and 45.  The ExprMaskSet
sl@0: ** translates these sparse cursor numbers into consecutive integers
sl@0: ** beginning with 0 in order to make the best possible use of the available
sl@0: ** bits in the Bitmask.  So, in the example above, the cursor numbers
sl@0: ** would be mapped into integers 0 through 7.
sl@0: */
sl@0: typedef struct WhereTerm WhereTerm;
sl@0: struct WhereTerm {
sl@0:   Expr *pExpr;            /* Pointer to the subexpression */
sl@0:   i16 iParent;            /* Disable pWC->a[iParent] when this term disabled */
sl@0:   i16 leftCursor;         /* Cursor number of X in "X <op> <expr>" */
sl@0:   i16 leftColumn;         /* Column number of X in "X <op> <expr>" */
sl@0:   u16 eOperator;          /* A WO_xx value describing <op> */
sl@0:   u8 flags;               /* Bit flags.  See below */
sl@0:   u8 nChild;              /* Number of children that must disable us */
sl@0:   WhereClause *pWC;       /* The clause this term is part of */
sl@0:   Bitmask prereqRight;    /* Bitmask of tables used by pRight */
sl@0:   Bitmask prereqAll;      /* Bitmask of tables referenced by p */
sl@0: };
sl@0: 
sl@0: /*
sl@0: ** Allowed values of WhereTerm.flags
sl@0: */
sl@0: #define TERM_DYNAMIC    0x01   /* Need to call sqlite3ExprDelete(db, pExpr) */
sl@0: #define TERM_VIRTUAL    0x02   /* Added by the optimizer.  Do not code */
sl@0: #define TERM_CODED      0x04   /* This term is already coded */
sl@0: #define TERM_COPIED     0x08   /* Has a child */
sl@0: #define TERM_OR_OK      0x10   /* Used during OR-clause processing */
sl@0: 
sl@0: /*
sl@0: ** An instance of the following structure holds all information about a
sl@0: ** WHERE clause.  Mostly this is a container for one or more WhereTerms.
sl@0: */
sl@0: struct WhereClause {
sl@0:   Parse *pParse;           /* The parser context */
sl@0:   ExprMaskSet *pMaskSet;   /* Mapping of table indices to bitmasks */
sl@0:   int nTerm;               /* Number of terms */
sl@0:   int nSlot;               /* Number of entries in a[] */
sl@0:   WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
sl@0:   WhereTerm aStatic[10];   /* Initial static space for a[] */
sl@0: };
sl@0: 
sl@0: /*
sl@0: ** An instance of the following structure keeps track of a mapping
sl@0: ** between VDBE cursor numbers and bits of the bitmasks in WhereTerm.
sl@0: **
sl@0: ** The VDBE cursor numbers are small integers contained in 
sl@0: ** SrcList_item.iCursor and Expr.iTable fields.  For any given WHERE 
sl@0: ** clause, the cursor numbers might not begin with 0 and they might
sl@0: ** contain gaps in the numbering sequence.  But we want to make maximum
sl@0: ** use of the bits in our bitmasks.  This structure provides a mapping
sl@0: ** from the sparse cursor numbers into consecutive integers beginning
sl@0: ** with 0.
sl@0: **
sl@0: ** If ExprMaskSet.ix[A]==B it means that The A-th bit of a Bitmask
sl@0: ** corresponds VDBE cursor number B.  The A-th bit of a bitmask is 1<<A.
sl@0: **
sl@0: ** For example, if the WHERE clause expression used these VDBE
sl@0: ** cursors:  4, 5, 8, 29, 57, 73.  Then the  ExprMaskSet structure
sl@0: ** would map those cursor numbers into bits 0 through 5.
sl@0: **
sl@0: ** Note that the mapping is not necessarily ordered.  In the example
sl@0: ** above, the mapping might go like this:  4->3, 5->1, 8->2, 29->0,
sl@0: ** 57->5, 73->4.  Or one of 719 other combinations might be used. It
sl@0: ** does not really matter.  What is important is that sparse cursor
sl@0: ** numbers all get mapped into bit numbers that begin with 0 and contain
sl@0: ** no gaps.
sl@0: */
sl@0: struct ExprMaskSet {
sl@0:   int n;                        /* Number of assigned cursor values */
sl@0:   int ix[sizeof(Bitmask)*8];    /* Cursor assigned to each bit */
sl@0: };
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Bitmasks for the operators that indices are able to exploit.  An
sl@0: ** OR-ed combination of these values can be used when searching for
sl@0: ** terms in the where clause.
sl@0: */
sl@0: #define WO_IN     1
sl@0: #define WO_EQ     2
sl@0: #define WO_LT     (WO_EQ<<(TK_LT-TK_EQ))
sl@0: #define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
sl@0: #define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
sl@0: #define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
sl@0: #define WO_MATCH  64
sl@0: #define WO_ISNULL 128
sl@0: 
sl@0: /*
sl@0: ** Value for flags returned by bestIndex().  
sl@0: **
sl@0: ** The least significant byte is reserved as a mask for WO_ values above.
sl@0: ** The WhereLevel.flags field is usually set to WO_IN|WO_EQ|WO_ISNULL.
sl@0: ** But if the table is the right table of a left join, WhereLevel.flags
sl@0: ** is set to WO_IN|WO_EQ.  The WhereLevel.flags field can then be used as
sl@0: ** the "op" parameter to findTerm when we are resolving equality constraints.
sl@0: ** ISNULL constraints will then not be used on the right table of a left
sl@0: ** join.  Tickets #2177 and #2189.
sl@0: */
sl@0: #define WHERE_ROWID_EQ     0x000100   /* rowid=EXPR or rowid IN (...) */
sl@0: #define WHERE_ROWID_RANGE  0x000200   /* rowid<EXPR and/or rowid>EXPR */
sl@0: #define WHERE_COLUMN_EQ    0x001000   /* x=EXPR or x IN (...) */
sl@0: #define WHERE_COLUMN_RANGE 0x002000   /* x<EXPR and/or x>EXPR */
sl@0: #define WHERE_COLUMN_IN    0x004000   /* x IN (...) */
sl@0: #define WHERE_TOP_LIMIT    0x010000   /* x<EXPR or x<=EXPR constraint */
sl@0: #define WHERE_BTM_LIMIT    0x020000   /* x>EXPR or x>=EXPR constraint */
sl@0: #define WHERE_IDX_ONLY     0x080000   /* Use index only - omit table */
sl@0: #define WHERE_ORDERBY      0x100000   /* Output will appear in correct order */
sl@0: #define WHERE_REVERSE      0x200000   /* Scan in reverse order */
sl@0: #define WHERE_UNIQUE       0x400000   /* Selects no more than one row */
sl@0: #define WHERE_VIRTUALTABLE 0x800000   /* Use virtual-table processing */
sl@0: 
sl@0: /*
sl@0: ** Initialize a preallocated WhereClause structure.
sl@0: */
sl@0: static void whereClauseInit(
sl@0:   WhereClause *pWC,        /* The WhereClause to be initialized */
sl@0:   Parse *pParse,           /* The parsing context */
sl@0:   ExprMaskSet *pMaskSet    /* Mapping from table indices to bitmasks */
sl@0: ){
sl@0:   pWC->pParse = pParse;
sl@0:   pWC->pMaskSet = pMaskSet;
sl@0:   pWC->nTerm = 0;
sl@0:   pWC->nSlot = ArraySize(pWC->aStatic);
sl@0:   pWC->a = pWC->aStatic;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Deallocate a WhereClause structure.  The WhereClause structure
sl@0: ** itself is not freed.  This routine is the inverse of whereClauseInit().
sl@0: */
sl@0: static void whereClauseClear(WhereClause *pWC){
sl@0:   int i;
sl@0:   WhereTerm *a;
sl@0:   sqlite3 *db = pWC->pParse->db;
sl@0:   for(i=pWC->nTerm-1, a=pWC->a; i>=0; i--, a++){
sl@0:     if( a->flags & TERM_DYNAMIC ){
sl@0:       sqlite3ExprDelete(db, a->pExpr);
sl@0:     }
sl@0:   }
sl@0:   if( pWC->a!=pWC->aStatic ){
sl@0:     sqlite3DbFree(db, pWC->a);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Add a new entries to the WhereClause structure.  Increase the allocated
sl@0: ** space as necessary.
sl@0: **
sl@0: ** If the flags argument includes TERM_DYNAMIC, then responsibility
sl@0: ** for freeing the expression p is assumed by the WhereClause object.
sl@0: **
sl@0: ** WARNING:  This routine might reallocate the space used to store
sl@0: ** WhereTerms.  All pointers to WhereTerms should be invalidated after
sl@0: ** calling this routine.  Such pointers may be reinitialized by referencing
sl@0: ** the pWC->a[] array.
sl@0: */
sl@0: static int whereClauseInsert(WhereClause *pWC, Expr *p, int flags){
sl@0:   WhereTerm *pTerm;
sl@0:   int idx;
sl@0:   if( pWC->nTerm>=pWC->nSlot ){
sl@0:     WhereTerm *pOld = pWC->a;
sl@0:     sqlite3 *db = pWC->pParse->db;
sl@0:     pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
sl@0:     if( pWC->a==0 ){
sl@0:       if( flags & TERM_DYNAMIC ){
sl@0:         sqlite3ExprDelete(db, p);
sl@0:       }
sl@0:       pWC->a = pOld;
sl@0:       return 0;
sl@0:     }
sl@0:     memcpy(pWC->a, pOld, sizeof(pWC->a[0])*pWC->nTerm);
sl@0:     if( pOld!=pWC->aStatic ){
sl@0:       sqlite3DbFree(db, pOld);
sl@0:     }
sl@0:     pWC->nSlot *= 2;
sl@0:   }
sl@0:   pTerm = &pWC->a[idx = pWC->nTerm];
sl@0:   pWC->nTerm++;
sl@0:   pTerm->pExpr = p;
sl@0:   pTerm->flags = flags;
sl@0:   pTerm->pWC = pWC;
sl@0:   pTerm->iParent = -1;
sl@0:   return idx;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** This routine identifies subexpressions in the WHERE clause where
sl@0: ** each subexpression is separated by the AND operator or some other
sl@0: ** operator specified in the op parameter.  The WhereClause structure
sl@0: ** is filled with pointers to subexpressions.  For example:
sl@0: **
sl@0: **    WHERE  a=='hello' AND coalesce(b,11)<10 AND (c+12!=d OR c==22)
sl@0: **           \________/     \_______________/     \________________/
sl@0: **            slot[0]            slot[1]               slot[2]
sl@0: **
sl@0: ** The original WHERE clause in pExpr is unaltered.  All this routine
sl@0: ** does is make slot[] entries point to substructure within pExpr.
sl@0: **
sl@0: ** In the previous sentence and in the diagram, "slot[]" refers to
sl@0: ** the WhereClause.a[] array.  This array grows as needed to contain
sl@0: ** all terms of the WHERE clause.
sl@0: */
sl@0: static void whereSplit(WhereClause *pWC, Expr *pExpr, int op){
sl@0:   if( pExpr==0 ) return;
sl@0:   if( pExpr->op!=op ){
sl@0:     whereClauseInsert(pWC, pExpr, 0);
sl@0:   }else{
sl@0:     whereSplit(pWC, pExpr->pLeft, op);
sl@0:     whereSplit(pWC, pExpr->pRight, op);
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Initialize an expression mask set
sl@0: */
sl@0: #define initMaskSet(P)  memset(P, 0, sizeof(*P))
sl@0: 
sl@0: /*
sl@0: ** Return the bitmask for the given cursor number.  Return 0 if
sl@0: ** iCursor is not in the set.
sl@0: */
sl@0: static Bitmask getMask(ExprMaskSet *pMaskSet, int iCursor){
sl@0:   int i;
sl@0:   for(i=0; i<pMaskSet->n; i++){
sl@0:     if( pMaskSet->ix[i]==iCursor ){
sl@0:       return ((Bitmask)1)<<i;
sl@0:     }
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Create a new mask for cursor iCursor.
sl@0: **
sl@0: ** There is one cursor per table in the FROM clause.  The number of
sl@0: ** tables in the FROM clause is limited by a test early in the
sl@0: ** sqlite3WhereBegin() routine.  So we know that the pMaskSet->ix[]
sl@0: ** array will never overflow.
sl@0: */
sl@0: static void createMask(ExprMaskSet *pMaskSet, int iCursor){
sl@0:   assert( pMaskSet->n < ArraySize(pMaskSet->ix) );
sl@0:   pMaskSet->ix[pMaskSet->n++] = iCursor;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** This routine walks (recursively) an expression tree and generates
sl@0: ** a bitmask indicating which tables are used in that expression
sl@0: ** tree.
sl@0: **
sl@0: ** In order for this routine to work, the calling function must have
sl@0: ** previously invoked sqlite3ResolveExprNames() on the expression.  See
sl@0: ** the header comment on that routine for additional information.
sl@0: ** The sqlite3ResolveExprNames() routines looks for column names and
sl@0: ** sets their opcodes to TK_COLUMN and their Expr.iTable fields to
sl@0: ** the VDBE cursor number of the table.  This routine just has to
sl@0: ** translate the cursor numbers into bitmask values and OR all
sl@0: ** the bitmasks together.
sl@0: */
sl@0: static Bitmask exprListTableUsage(ExprMaskSet*, ExprList*);
sl@0: static Bitmask exprSelectTableUsage(ExprMaskSet*, Select*);
sl@0: static Bitmask exprTableUsage(ExprMaskSet *pMaskSet, Expr *p){
sl@0:   Bitmask mask = 0;
sl@0:   if( p==0 ) return 0;
sl@0:   if( p->op==TK_COLUMN ){
sl@0:     mask = getMask(pMaskSet, p->iTable);
sl@0:     return mask;
sl@0:   }
sl@0:   mask = exprTableUsage(pMaskSet, p->pRight);
sl@0:   mask |= exprTableUsage(pMaskSet, p->pLeft);
sl@0:   mask |= exprListTableUsage(pMaskSet, p->pList);
sl@0:   mask |= exprSelectTableUsage(pMaskSet, p->pSelect);
sl@0:   return mask;
sl@0: }
sl@0: static Bitmask exprListTableUsage(ExprMaskSet *pMaskSet, ExprList *pList){
sl@0:   int i;
sl@0:   Bitmask mask = 0;
sl@0:   if( pList ){
sl@0:     for(i=0; i<pList->nExpr; i++){
sl@0:       mask |= exprTableUsage(pMaskSet, pList->a[i].pExpr);
sl@0:     }
sl@0:   }
sl@0:   return mask;
sl@0: }
sl@0: static Bitmask exprSelectTableUsage(ExprMaskSet *pMaskSet, Select *pS){
sl@0:   Bitmask mask = 0;
sl@0:   while( pS ){
sl@0:     mask |= exprListTableUsage(pMaskSet, pS->pEList);
sl@0:     mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
sl@0:     mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
sl@0:     mask |= exprTableUsage(pMaskSet, pS->pWhere);
sl@0:     mask |= exprTableUsage(pMaskSet, pS->pHaving);
sl@0:     pS = pS->pPrior;
sl@0:   }
sl@0:   return mask;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Return TRUE if the given operator is one of the operators that is
sl@0: ** allowed for an indexable WHERE clause term.  The allowed operators are
sl@0: ** "=", "<", ">", "<=", ">=", and "IN".
sl@0: */
sl@0: static int allowedOp(int op){
sl@0:   assert( TK_GT>TK_EQ && TK_GT<TK_GE );
sl@0:   assert( TK_LT>TK_EQ && TK_LT<TK_GE );
sl@0:   assert( TK_LE>TK_EQ && TK_LE<TK_GE );
sl@0:   assert( TK_GE==TK_EQ+4 );
sl@0:   return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Swap two objects of type T.
sl@0: */
sl@0: #define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;}
sl@0: 
sl@0: /*
sl@0: ** Commute a comparison operator.  Expressions of the form "X op Y"
sl@0: ** are converted into "Y op X".
sl@0: **
sl@0: ** If a collation sequence is associated with either the left or right
sl@0: ** side of the comparison, it remains associated with the same side after
sl@0: ** the commutation. So "Y collate NOCASE op X" becomes 
sl@0: ** "X collate NOCASE op Y". This is because any collation sequence on
sl@0: ** the left hand side of a comparison overrides any collation sequence 
sl@0: ** attached to the right. For the same reason the EP_ExpCollate flag
sl@0: ** is not commuted.
sl@0: */
sl@0: static void exprCommute(Parse *pParse, Expr *pExpr){
sl@0:   u16 expRight = (pExpr->pRight->flags & EP_ExpCollate);
sl@0:   u16 expLeft = (pExpr->pLeft->flags & EP_ExpCollate);
sl@0:   assert( allowedOp(pExpr->op) && pExpr->op!=TK_IN );
sl@0:   pExpr->pRight->pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
sl@0:   pExpr->pLeft->pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
sl@0:   SWAP(CollSeq*,pExpr->pRight->pColl,pExpr->pLeft->pColl);
sl@0:   pExpr->pRight->flags = (pExpr->pRight->flags & ~EP_ExpCollate) | expLeft;
sl@0:   pExpr->pLeft->flags = (pExpr->pLeft->flags & ~EP_ExpCollate) | expRight;
sl@0:   SWAP(Expr*,pExpr->pRight,pExpr->pLeft);
sl@0:   if( pExpr->op>=TK_GT ){
sl@0:     assert( TK_LT==TK_GT+2 );
sl@0:     assert( TK_GE==TK_LE+2 );
sl@0:     assert( TK_GT>TK_EQ );
sl@0:     assert( TK_GT<TK_LE );
sl@0:     assert( pExpr->op>=TK_GT && pExpr->op<=TK_GE );
sl@0:     pExpr->op = ((pExpr->op-TK_GT)^2)+TK_GT;
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Translate from TK_xx operator to WO_xx bitmask.
sl@0: */
sl@0: static int operatorMask(int op){
sl@0:   int c;
sl@0:   assert( allowedOp(op) );
sl@0:   if( op==TK_IN ){
sl@0:     c = WO_IN;
sl@0:   }else if( op==TK_ISNULL ){
sl@0:     c = WO_ISNULL;
sl@0:   }else{
sl@0:     c = WO_EQ<<(op-TK_EQ);
sl@0:   }
sl@0:   assert( op!=TK_ISNULL || c==WO_ISNULL );
sl@0:   assert( op!=TK_IN || c==WO_IN );
sl@0:   assert( op!=TK_EQ || c==WO_EQ );
sl@0:   assert( op!=TK_LT || c==WO_LT );
sl@0:   assert( op!=TK_LE || c==WO_LE );
sl@0:   assert( op!=TK_GT || c==WO_GT );
sl@0:   assert( op!=TK_GE || c==WO_GE );
sl@0:   return c;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
sl@0: ** where X is a reference to the iColumn of table iCur and <op> is one of
sl@0: ** the WO_xx operator codes specified by the op parameter.
sl@0: ** Return a pointer to the term.  Return 0 if not found.
sl@0: */
sl@0: static WhereTerm *findTerm(
sl@0:   WhereClause *pWC,     /* The WHERE clause to be searched */
sl@0:   int iCur,             /* Cursor number of LHS */
sl@0:   int iColumn,          /* Column number of LHS */
sl@0:   Bitmask notReady,     /* RHS must not overlap with this mask */
sl@0:   u16 op,               /* Mask of WO_xx values describing operator */
sl@0:   Index *pIdx           /* Must be compatible with this index, if not NULL */
sl@0: ){
sl@0:   WhereTerm *pTerm;
sl@0:   int k;
sl@0:   assert( iCur>=0 );
sl@0:   for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
sl@0:     if( pTerm->leftCursor==iCur
sl@0:        && (pTerm->prereqRight & notReady)==0
sl@0:        && pTerm->leftColumn==iColumn
sl@0:        && (pTerm->eOperator & op)!=0
sl@0:     ){
sl@0:       if( pIdx && pTerm->eOperator!=WO_ISNULL ){
sl@0:         Expr *pX = pTerm->pExpr;
sl@0:         CollSeq *pColl;
sl@0:         char idxaff;
sl@0:         int j;
sl@0:         Parse *pParse = pWC->pParse;
sl@0: 
sl@0:         idxaff = pIdx->pTable->aCol[iColumn].affinity;
sl@0:         if( !sqlite3IndexAffinityOk(pX, idxaff) ) continue;
sl@0: 
sl@0:         /* Figure out the collation sequence required from an index for
sl@0:         ** it to be useful for optimising expression pX. Store this
sl@0:         ** value in variable pColl.
sl@0:         */
sl@0:         assert(pX->pLeft);
sl@0:         pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
sl@0:         if( !pColl ){
sl@0:           pColl = pParse->db->pDfltColl;
sl@0:         }
sl@0: 
sl@0:         for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
sl@0:           if( NEVER(j>=pIdx->nColumn) ) return 0;
sl@0:         }
sl@0:         if( sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
sl@0:       }
sl@0:       return pTerm;
sl@0:     }
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /* Forward reference */
sl@0: static void exprAnalyze(SrcList*, WhereClause*, int);
sl@0: 
sl@0: /*
sl@0: ** Call exprAnalyze on all terms in a WHERE clause.  
sl@0: **
sl@0: **
sl@0: */
sl@0: static void exprAnalyzeAll(
sl@0:   SrcList *pTabList,       /* the FROM clause */
sl@0:   WhereClause *pWC         /* the WHERE clause to be analyzed */
sl@0: ){
sl@0:   int i;
sl@0:   for(i=pWC->nTerm-1; i>=0; i--){
sl@0:     exprAnalyze(pTabList, pWC, i);
sl@0:   }
sl@0: }
sl@0: 
sl@0: #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
sl@0: /*
sl@0: ** Check to see if the given expression is a LIKE or GLOB operator that
sl@0: ** can be optimized using inequality constraints.  Return TRUE if it is
sl@0: ** so and false if not.
sl@0: **
sl@0: ** In order for the operator to be optimizible, the RHS must be a string
sl@0: ** literal that does not begin with a wildcard.  
sl@0: */
sl@0: static int isLikeOrGlob(
sl@0:   Parse *pParse,    /* Parsing and code generating context */
sl@0:   Expr *pExpr,      /* Test this expression */
sl@0:   int *pnPattern,   /* Number of non-wildcard prefix characters */
sl@0:   int *pisComplete, /* True if the only wildcard is % in the last character */
sl@0:   int *pnoCase      /* True if uppercase is equivalent to lowercase */
sl@0: ){
sl@0:   const char *z;
sl@0:   Expr *pRight, *pLeft;
sl@0:   ExprList *pList;
sl@0:   int c, cnt;
sl@0:   char wc[3];
sl@0:   CollSeq *pColl;
sl@0:   sqlite3 *db = pParse->db;
sl@0: 
sl@0:   if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){
sl@0:     return 0;
sl@0:   }
sl@0: #ifdef SQLITE_EBCDIC
sl@0:   if( *pnoCase ) return 0;
sl@0: #endif
sl@0:   pList = pExpr->pList;
sl@0:   pRight = pList->a[0].pExpr;
sl@0:   if( pRight->op!=TK_STRING
sl@0:    && (pRight->op!=TK_REGISTER || pRight->iColumn!=TK_STRING) ){
sl@0:     return 0;
sl@0:   }
sl@0:   pLeft = pList->a[1].pExpr;
sl@0:   if( pLeft->op!=TK_COLUMN ){
sl@0:     return 0;
sl@0:   }
sl@0:   pColl = sqlite3ExprCollSeq(pParse, pLeft);
sl@0:   assert( pColl!=0 || pLeft->iColumn==-1 );
sl@0:   if( pColl==0 ){
sl@0:     /* No collation is defined for the ROWID.  Use the default. */
sl@0:     pColl = db->pDfltColl;
sl@0:   }
sl@0:   if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) &&
sl@0:       (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){
sl@0:     return 0;
sl@0:   }
sl@0:   sqlite3DequoteExpr(db, pRight);
sl@0:   z = (char *)pRight->token.z;
sl@0:   cnt = 0;
sl@0:   if( z ){
sl@0:     while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; }
sl@0:   }
sl@0:   if( cnt==0 || 255==(u8)z[cnt] ){
sl@0:     return 0;
sl@0:   }
sl@0:   *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
sl@0:   *pnPattern = cnt;
sl@0:   return 1;
sl@0: }
sl@0: #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
sl@0: 
sl@0: 
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0: /*
sl@0: ** Check to see if the given expression is of the form
sl@0: **
sl@0: **         column MATCH expr
sl@0: **
sl@0: ** If it is then return TRUE.  If not, return FALSE.
sl@0: */
sl@0: static int isMatchOfColumn(
sl@0:   Expr *pExpr      /* Test this expression */
sl@0: ){
sl@0:   ExprList *pList;
sl@0: 
sl@0:   if( pExpr->op!=TK_FUNCTION ){
sl@0:     return 0;
sl@0:   }
sl@0:   if( pExpr->token.n!=5 ||
sl@0:        sqlite3StrNICmp((const char*)pExpr->token.z,"match",5)!=0 ){
sl@0:     return 0;
sl@0:   }
sl@0:   pList = pExpr->pList;
sl@0:   if( pList->nExpr!=2 ){
sl@0:     return 0;
sl@0:   }
sl@0:   if( pList->a[1].pExpr->op != TK_COLUMN ){
sl@0:     return 0;
sl@0:   }
sl@0:   return 1;
sl@0: }
sl@0: #endif /* SQLITE_OMIT_VIRTUALTABLE */
sl@0: 
sl@0: /*
sl@0: ** If the pBase expression originated in the ON or USING clause of
sl@0: ** a join, then transfer the appropriate markings over to derived.
sl@0: */
sl@0: static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
sl@0:   pDerived->flags |= pBase->flags & EP_FromJoin;
sl@0:   pDerived->iRightJoinTable = pBase->iRightJoinTable;
sl@0: }
sl@0: 
sl@0: #if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
sl@0: /*
sl@0: ** Return TRUE if the given term of an OR clause can be converted
sl@0: ** into an IN clause.  The iCursor and iColumn define the left-hand
sl@0: ** side of the IN clause.
sl@0: **
sl@0: ** The context is that we have multiple OR-connected equality terms
sl@0: ** like this:
sl@0: **
sl@0: **           a=<expr1> OR  a=<expr2> OR b=<expr3>  OR ...
sl@0: **
sl@0: ** The pOrTerm input to this routine corresponds to a single term of
sl@0: ** this OR clause.  In order for the term to be a candidate for
sl@0: ** conversion to an IN operator, the following must be true:
sl@0: **
sl@0: **     *  The left-hand side of the term must be the column which
sl@0: **        is identified by iCursor and iColumn.
sl@0: **
sl@0: **     *  If the right-hand side is also a column, then the affinities
sl@0: **        of both right and left sides must be such that no type
sl@0: **        conversions are required on the right.  (Ticket #2249)
sl@0: **
sl@0: ** If both of these conditions are true, then return true.  Otherwise
sl@0: ** return false.
sl@0: */
sl@0: static int orTermIsOptCandidate(WhereTerm *pOrTerm, int iCursor, int iColumn){
sl@0:   int affLeft, affRight;
sl@0:   assert( pOrTerm->eOperator==WO_EQ );
sl@0:   if( pOrTerm->leftCursor!=iCursor ){
sl@0:     return 0;
sl@0:   }
sl@0:   if( pOrTerm->leftColumn!=iColumn ){
sl@0:     return 0;
sl@0:   }
sl@0:   affRight = sqlite3ExprAffinity(pOrTerm->pExpr->pRight);
sl@0:   if( affRight==0 ){
sl@0:     return 1;
sl@0:   }
sl@0:   affLeft = sqlite3ExprAffinity(pOrTerm->pExpr->pLeft);
sl@0:   if( affRight!=affLeft ){
sl@0:     return 0;
sl@0:   }
sl@0:   return 1;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Return true if the given term of an OR clause can be ignored during
sl@0: ** a check to make sure all OR terms are candidates for optimization.
sl@0: ** In other words, return true if a call to the orTermIsOptCandidate()
sl@0: ** above returned false but it is not necessary to disqualify the
sl@0: ** optimization.
sl@0: **
sl@0: ** Suppose the original OR phrase was this:
sl@0: **
sl@0: **           a=4  OR  a=11  OR  a=b
sl@0: **
sl@0: ** During analysis, the third term gets flipped around and duplicate
sl@0: ** so that we are left with this:
sl@0: **
sl@0: **           a=4  OR  a=11  OR  a=b  OR  b=a
sl@0: **
sl@0: ** Since the last two terms are duplicates, only one of them
sl@0: ** has to qualify in order for the whole phrase to qualify.  When
sl@0: ** this routine is called, we know that pOrTerm did not qualify.
sl@0: ** This routine merely checks to see if pOrTerm has a duplicate that
sl@0: ** might qualify.  If there is a duplicate that has not yet been
sl@0: ** disqualified, then return true.  If there are no duplicates, or
sl@0: ** the duplicate has also been disqualified, return false.
sl@0: */
sl@0: static int orTermHasOkDuplicate(WhereClause *pOr, WhereTerm *pOrTerm){
sl@0:   if( pOrTerm->flags & TERM_COPIED ){
sl@0:     /* This is the original term.  The duplicate is to the left had
sl@0:     ** has not yet been analyzed and thus has not yet been disqualified. */
sl@0:     return 1;
sl@0:   }
sl@0:   if( (pOrTerm->flags & TERM_VIRTUAL)!=0
sl@0:      && (pOr->a[pOrTerm->iParent].flags & TERM_OR_OK)!=0 ){
sl@0:     /* This is a duplicate term.  The original qualified so this one
sl@0:     ** does not have to. */
sl@0:     return 1;
sl@0:   }
sl@0:   /* This is either a singleton term or else it is a duplicate for
sl@0:   ** which the original did not qualify.  Either way we are done for. */
sl@0:   return 0;
sl@0: }
sl@0: #endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */
sl@0: 
sl@0: /*
sl@0: ** The input to this routine is an WhereTerm structure with only the
sl@0: ** "pExpr" field filled in.  The job of this routine is to analyze the
sl@0: ** subexpression and populate all the other fields of the WhereTerm
sl@0: ** structure.
sl@0: **
sl@0: ** If the expression is of the form "<expr> <op> X" it gets commuted
sl@0: ** to the standard form of "X <op> <expr>".  If the expression is of
sl@0: ** the form "X <op> Y" where both X and Y are columns, then the original
sl@0: ** expression is unchanged and a new virtual expression of the form
sl@0: ** "Y <op> X" is added to the WHERE clause and analyzed separately.
sl@0: */
sl@0: static void exprAnalyze(
sl@0:   SrcList *pSrc,            /* the FROM clause */
sl@0:   WhereClause *pWC,         /* the WHERE clause */
sl@0:   int idxTerm               /* Index of the term to be analyzed */
sl@0: ){
sl@0:   WhereTerm *pTerm;
sl@0:   ExprMaskSet *pMaskSet;
sl@0:   Expr *pExpr;
sl@0:   Bitmask prereqLeft;
sl@0:   Bitmask prereqAll;
sl@0:   Bitmask extraRight = 0;
sl@0:   int nPattern;
sl@0:   int isComplete;
sl@0:   int noCase;
sl@0:   int op;
sl@0:   Parse *pParse = pWC->pParse;
sl@0:   sqlite3 *db = pParse->db;
sl@0: 
sl@0:   if( db->mallocFailed ){
sl@0:     return;
sl@0:   }
sl@0:   pTerm = &pWC->a[idxTerm];
sl@0:   pMaskSet = pWC->pMaskSet;
sl@0:   pExpr = pTerm->pExpr;
sl@0:   prereqLeft = exprTableUsage(pMaskSet, pExpr->pLeft);
sl@0:   op = pExpr->op;
sl@0:   if( op==TK_IN ){
sl@0:     assert( pExpr->pRight==0 );
sl@0:     pTerm->prereqRight = exprListTableUsage(pMaskSet, pExpr->pList)
sl@0:                           | exprSelectTableUsage(pMaskSet, pExpr->pSelect);
sl@0:   }else if( op==TK_ISNULL ){
sl@0:     pTerm->prereqRight = 0;
sl@0:   }else{
sl@0:     pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
sl@0:   }
sl@0:   prereqAll = exprTableUsage(pMaskSet, pExpr);
sl@0:   if( ExprHasProperty(pExpr, EP_FromJoin) ){
sl@0:     Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
sl@0:     prereqAll |= x;
sl@0:     extraRight = x-1;  /* ON clause terms may not be used with an index
sl@0:                        ** on left table of a LEFT JOIN.  Ticket #3015 */
sl@0:   }
sl@0:   pTerm->prereqAll = prereqAll;
sl@0:   pTerm->leftCursor = -1;
sl@0:   pTerm->iParent = -1;
sl@0:   pTerm->eOperator = 0;
sl@0:   if( allowedOp(op) && (pTerm->prereqRight & prereqLeft)==0 ){
sl@0:     Expr *pLeft = pExpr->pLeft;
sl@0:     Expr *pRight = pExpr->pRight;
sl@0:     if( pLeft->op==TK_COLUMN ){
sl@0:       pTerm->leftCursor = pLeft->iTable;
sl@0:       pTerm->leftColumn = pLeft->iColumn;
sl@0:       pTerm->eOperator = operatorMask(op);
sl@0:     }
sl@0:     if( pRight && pRight->op==TK_COLUMN ){
sl@0:       WhereTerm *pNew;
sl@0:       Expr *pDup;
sl@0:       if( pTerm->leftCursor>=0 ){
sl@0:         int idxNew;
sl@0:         pDup = sqlite3ExprDup(db, pExpr);
sl@0:         if( db->mallocFailed ){
sl@0:           sqlite3ExprDelete(db, pDup);
sl@0:           return;
sl@0:         }
sl@0:         idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:         if( idxNew==0 ) return;
sl@0:         pNew = &pWC->a[idxNew];
sl@0:         pNew->iParent = idxTerm;
sl@0:         pTerm = &pWC->a[idxTerm];
sl@0:         pTerm->nChild = 1;
sl@0:         pTerm->flags |= TERM_COPIED;
sl@0:       }else{
sl@0:         pDup = pExpr;
sl@0:         pNew = pTerm;
sl@0:       }
sl@0:       exprCommute(pParse, pDup);
sl@0:       pLeft = pDup->pLeft;
sl@0:       pNew->leftCursor = pLeft->iTable;
sl@0:       pNew->leftColumn = pLeft->iColumn;
sl@0:       pNew->prereqRight = prereqLeft;
sl@0:       pNew->prereqAll = prereqAll;
sl@0:       pNew->eOperator = operatorMask(pDup->op);
sl@0:     }
sl@0:   }
sl@0: 
sl@0: #ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION
sl@0:   /* If a term is the BETWEEN operator, create two new virtual terms
sl@0:   ** that define the range that the BETWEEN implements.
sl@0:   */
sl@0:   else if( pExpr->op==TK_BETWEEN ){
sl@0:     ExprList *pList = pExpr->pList;
sl@0:     int i;
sl@0:     static const u8 ops[] = {TK_GE, TK_LE};
sl@0:     assert( pList!=0 );
sl@0:     assert( pList->nExpr==2 );
sl@0:     for(i=0; i<2; i++){
sl@0:       Expr *pNewExpr;
sl@0:       int idxNew;
sl@0:       pNewExpr = sqlite3Expr(db, ops[i], sqlite3ExprDup(db, pExpr->pLeft),
sl@0:                              sqlite3ExprDup(db, pList->a[i].pExpr), 0);
sl@0:       idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:       exprAnalyze(pSrc, pWC, idxNew);
sl@0:       pTerm = &pWC->a[idxTerm];
sl@0:       pWC->a[idxNew].iParent = idxTerm;
sl@0:     }
sl@0:     pTerm->nChild = 2;
sl@0:   }
sl@0: #endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */
sl@0: 
sl@0: #if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
sl@0:   /* Attempt to convert OR-connected terms into an IN operator so that
sl@0:   ** they can make use of indices.  Example:
sl@0:   **
sl@0:   **      x = expr1  OR  expr2 = x  OR  x = expr3
sl@0:   **
sl@0:   ** is converted into
sl@0:   **
sl@0:   **      x IN (expr1,expr2,expr3)
sl@0:   **
sl@0:   ** This optimization must be omitted if OMIT_SUBQUERY is defined because
sl@0:   ** the compiler for the the IN operator is part of sub-queries.
sl@0:   */
sl@0:   else if( pExpr->op==TK_OR ){
sl@0:     int ok;
sl@0:     int i, j;
sl@0:     int iColumn, iCursor;
sl@0:     WhereClause sOr;
sl@0:     WhereTerm *pOrTerm;
sl@0: 
sl@0:     assert( (pTerm->flags & TERM_DYNAMIC)==0 );
sl@0:     whereClauseInit(&sOr, pWC->pParse, pMaskSet);
sl@0:     whereSplit(&sOr, pExpr, TK_OR);
sl@0:     exprAnalyzeAll(pSrc, &sOr);
sl@0:     assert( sOr.nTerm>=2 );
sl@0:     j = 0;
sl@0:     if( db->mallocFailed ) goto or_not_possible;
sl@0:     do{
sl@0:       assert( j<sOr.nTerm );
sl@0:       iColumn = sOr.a[j].leftColumn;
sl@0:       iCursor = sOr.a[j].leftCursor;
sl@0:       ok = iCursor>=0;
sl@0:       for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0 && ok; i--, pOrTerm++){
sl@0:         if( pOrTerm->eOperator!=WO_EQ ){
sl@0:           goto or_not_possible;
sl@0:         }
sl@0:         if( orTermIsOptCandidate(pOrTerm, iCursor, iColumn) ){
sl@0:           pOrTerm->flags |= TERM_OR_OK;
sl@0:         }else if( orTermHasOkDuplicate(&sOr, pOrTerm) ){
sl@0:           pOrTerm->flags &= ~TERM_OR_OK;
sl@0:         }else{
sl@0:           ok = 0;
sl@0:         }
sl@0:       }
sl@0:     }while( !ok && (sOr.a[j++].flags & TERM_COPIED)!=0 && j<2 );
sl@0:     if( ok ){
sl@0:       ExprList *pList = 0;
sl@0:       Expr *pNew, *pDup;
sl@0:       Expr *pLeft = 0;
sl@0:       for(i=sOr.nTerm-1, pOrTerm=sOr.a; i>=0; i--, pOrTerm++){
sl@0:         if( (pOrTerm->flags & TERM_OR_OK)==0 ) continue;
sl@0:         pDup = sqlite3ExprDup(db, pOrTerm->pExpr->pRight);
sl@0:         pList = sqlite3ExprListAppend(pWC->pParse, pList, pDup, 0);
sl@0:         pLeft = pOrTerm->pExpr->pLeft;
sl@0:       }
sl@0:       assert( pLeft!=0 );
sl@0:       pDup = sqlite3ExprDup(db, pLeft);
sl@0:       pNew = sqlite3Expr(db, TK_IN, pDup, 0, 0);
sl@0:       if( pNew ){
sl@0:         int idxNew;
sl@0:         transferJoinMarkings(pNew, pExpr);
sl@0:         pNew->pList = pList;
sl@0:         idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:         exprAnalyze(pSrc, pWC, idxNew);
sl@0:         pTerm = &pWC->a[idxTerm];
sl@0:         pWC->a[idxNew].iParent = idxTerm;
sl@0:         pTerm->nChild = 1;
sl@0:       }else{
sl@0:         sqlite3ExprListDelete(db, pList);
sl@0:       }
sl@0:     }
sl@0: or_not_possible:
sl@0:     whereClauseClear(&sOr);
sl@0:   }
sl@0: #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
sl@0: 
sl@0: #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
sl@0:   /* Add constraints to reduce the search space on a LIKE or GLOB
sl@0:   ** operator.
sl@0:   **
sl@0:   ** A like pattern of the form "x LIKE 'abc%'" is changed into constraints
sl@0:   **
sl@0:   **          x>='abc' AND x<'abd' AND x LIKE 'abc%'
sl@0:   **
sl@0:   ** The last character of the prefix "abc" is incremented to form the
sl@0:   ** termination condition "abd".
sl@0:   */
sl@0:   if( isLikeOrGlob(pParse, pExpr, &nPattern, &isComplete, &noCase) ){
sl@0:     Expr *pLeft, *pRight;
sl@0:     Expr *pStr1, *pStr2;
sl@0:     Expr *pNewExpr1, *pNewExpr2;
sl@0:     int idxNew1, idxNew2;
sl@0: 
sl@0:     pLeft = pExpr->pList->a[1].pExpr;
sl@0:     pRight = pExpr->pList->a[0].pExpr;
sl@0:     pStr1 = sqlite3PExpr(pParse, TK_STRING, 0, 0, 0);
sl@0:     if( pStr1 ){
sl@0:       sqlite3TokenCopy(db, &pStr1->token, &pRight->token);
sl@0:       pStr1->token.n = nPattern;
sl@0:       pStr1->flags = EP_Dequoted;
sl@0:     }
sl@0:     pStr2 = sqlite3ExprDup(db, pStr1);
sl@0:     if( !db->mallocFailed ){
sl@0:       u8 c, *pC;
sl@0:       assert( pStr2->token.dyn );
sl@0:       pC = (u8*)&pStr2->token.z[nPattern-1];
sl@0:       c = *pC;
sl@0:       if( noCase ){
sl@0:         if( c=='@' ) isComplete = 0;
sl@0:         c = sqlite3UpperToLower[c];
sl@0:       }
sl@0:       *pC = c + 1;
sl@0:     }
sl@0:     pNewExpr1 = sqlite3PExpr(pParse, TK_GE, sqlite3ExprDup(db,pLeft), pStr1, 0);
sl@0:     idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:     exprAnalyze(pSrc, pWC, idxNew1);
sl@0:     pNewExpr2 = sqlite3PExpr(pParse, TK_LT, sqlite3ExprDup(db,pLeft), pStr2, 0);
sl@0:     idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:     exprAnalyze(pSrc, pWC, idxNew2);
sl@0:     pTerm = &pWC->a[idxTerm];
sl@0:     if( isComplete ){
sl@0:       pWC->a[idxNew1].iParent = idxTerm;
sl@0:       pWC->a[idxNew2].iParent = idxTerm;
sl@0:       pTerm->nChild = 2;
sl@0:     }
sl@0:   }
sl@0: #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
sl@0: 
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0:   /* Add a WO_MATCH auxiliary term to the constraint set if the
sl@0:   ** current expression is of the form:  column MATCH expr.
sl@0:   ** This information is used by the xBestIndex methods of
sl@0:   ** virtual tables.  The native query optimizer does not attempt
sl@0:   ** to do anything with MATCH functions.
sl@0:   */
sl@0:   if( isMatchOfColumn(pExpr) ){
sl@0:     int idxNew;
sl@0:     Expr *pRight, *pLeft;
sl@0:     WhereTerm *pNewTerm;
sl@0:     Bitmask prereqColumn, prereqExpr;
sl@0: 
sl@0:     pRight = pExpr->pList->a[0].pExpr;
sl@0:     pLeft = pExpr->pList->a[1].pExpr;
sl@0:     prereqExpr = exprTableUsage(pMaskSet, pRight);
sl@0:     prereqColumn = exprTableUsage(pMaskSet, pLeft);
sl@0:     if( (prereqExpr & prereqColumn)==0 ){
sl@0:       Expr *pNewExpr;
sl@0:       pNewExpr = sqlite3Expr(db, TK_MATCH, 0, sqlite3ExprDup(db, pRight), 0);
sl@0:       idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
sl@0:       pNewTerm = &pWC->a[idxNew];
sl@0:       pNewTerm->prereqRight = prereqExpr;
sl@0:       pNewTerm->leftCursor = pLeft->iTable;
sl@0:       pNewTerm->leftColumn = pLeft->iColumn;
sl@0:       pNewTerm->eOperator = WO_MATCH;
sl@0:       pNewTerm->iParent = idxTerm;
sl@0:       pTerm = &pWC->a[idxTerm];
sl@0:       pTerm->nChild = 1;
sl@0:       pTerm->flags |= TERM_COPIED;
sl@0:       pNewTerm->prereqAll = pTerm->prereqAll;
sl@0:     }
sl@0:   }
sl@0: #endif /* SQLITE_OMIT_VIRTUALTABLE */
sl@0: 
sl@0:   /* Prevent ON clause terms of a LEFT JOIN from being used to drive
sl@0:   ** an index for tables to the left of the join.
sl@0:   */
sl@0:   pTerm->prereqRight |= extraRight;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Return TRUE if any of the expressions in pList->a[iFirst...] contain
sl@0: ** a reference to any table other than the iBase table.
sl@0: */
sl@0: static int referencesOtherTables(
sl@0:   ExprList *pList,          /* Search expressions in ths list */
sl@0:   ExprMaskSet *pMaskSet,    /* Mapping from tables to bitmaps */
sl@0:   int iFirst,               /* Be searching with the iFirst-th expression */
sl@0:   int iBase                 /* Ignore references to this table */
sl@0: ){
sl@0:   Bitmask allowed = ~getMask(pMaskSet, iBase);
sl@0:   while( iFirst<pList->nExpr ){
sl@0:     if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){
sl@0:       return 1;
sl@0:     }
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** This routine decides if pIdx can be used to satisfy the ORDER BY
sl@0: ** clause.  If it can, it returns 1.  If pIdx cannot satisfy the
sl@0: ** ORDER BY clause, this routine returns 0.
sl@0: **
sl@0: ** pOrderBy is an ORDER BY clause from a SELECT statement.  pTab is the
sl@0: ** left-most table in the FROM clause of that same SELECT statement and
sl@0: ** the table has a cursor number of "base".  pIdx is an index on pTab.
sl@0: **
sl@0: ** nEqCol is the number of columns of pIdx that are used as equality
sl@0: ** constraints.  Any of these columns may be missing from the ORDER BY
sl@0: ** clause and the match can still be a success.
sl@0: **
sl@0: ** All terms of the ORDER BY that match against the index must be either
sl@0: ** ASC or DESC.  (Terms of the ORDER BY clause past the end of a UNIQUE
sl@0: ** index do not need to satisfy this constraint.)  The *pbRev value is
sl@0: ** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if
sl@0: ** the ORDER BY clause is all ASC.
sl@0: */
sl@0: static int isSortingIndex(
sl@0:   Parse *pParse,          /* Parsing context */
sl@0:   ExprMaskSet *pMaskSet,  /* Mapping from table indices to bitmaps */
sl@0:   Index *pIdx,            /* The index we are testing */
sl@0:   int base,               /* Cursor number for the table to be sorted */
sl@0:   ExprList *pOrderBy,     /* The ORDER BY clause */
sl@0:   int nEqCol,             /* Number of index columns with == constraints */
sl@0:   int *pbRev              /* Set to 1 if ORDER BY is DESC */
sl@0: ){
sl@0:   int i, j;                       /* Loop counters */
sl@0:   int sortOrder = 0;              /* XOR of index and ORDER BY sort direction */
sl@0:   int nTerm;                      /* Number of ORDER BY terms */
sl@0:   struct ExprList_item *pTerm;    /* A term of the ORDER BY clause */
sl@0:   sqlite3 *db = pParse->db;
sl@0: 
sl@0:   assert( pOrderBy!=0 );
sl@0:   nTerm = pOrderBy->nExpr;
sl@0:   assert( nTerm>0 );
sl@0: 
sl@0:   /* Match terms of the ORDER BY clause against columns of
sl@0:   ** the index.
sl@0:   **
sl@0:   ** Note that indices have pIdx->nColumn regular columns plus
sl@0:   ** one additional column containing the rowid.  The rowid column
sl@0:   ** of the index is also allowed to match against the ORDER BY
sl@0:   ** clause.
sl@0:   */
sl@0:   for(i=j=0, pTerm=pOrderBy->a; j<nTerm && i<=pIdx->nColumn; i++){
sl@0:     Expr *pExpr;       /* The expression of the ORDER BY pTerm */
sl@0:     CollSeq *pColl;    /* The collating sequence of pExpr */
sl@0:     int termSortOrder; /* Sort order for this term */
sl@0:     int iColumn;       /* The i-th column of the index.  -1 for rowid */
sl@0:     int iSortOrder;    /* 1 for DESC, 0 for ASC on the i-th index term */
sl@0:     const char *zColl; /* Name of the collating sequence for i-th index term */
sl@0: 
sl@0:     pExpr = pTerm->pExpr;
sl@0:     if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){
sl@0:       /* Can not use an index sort on anything that is not a column in the
sl@0:       ** left-most table of the FROM clause */
sl@0:       break;
sl@0:     }
sl@0:     pColl = sqlite3ExprCollSeq(pParse, pExpr);
sl@0:     if( !pColl ){
sl@0:       pColl = db->pDfltColl;
sl@0:     }
sl@0:     if( i<pIdx->nColumn ){
sl@0:       iColumn = pIdx->aiColumn[i];
sl@0:       if( iColumn==pIdx->pTable->iPKey ){
sl@0:         iColumn = -1;
sl@0:       }
sl@0:       iSortOrder = pIdx->aSortOrder[i];
sl@0:       zColl = pIdx->azColl[i];
sl@0:     }else{
sl@0:       iColumn = -1;
sl@0:       iSortOrder = 0;
sl@0:       zColl = pColl->zName;
sl@0:     }
sl@0:     if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){
sl@0:       /* Term j of the ORDER BY clause does not match column i of the index */
sl@0:       if( i<nEqCol ){
sl@0:         /* If an index column that is constrained by == fails to match an
sl@0:         ** ORDER BY term, that is OK.  Just ignore that column of the index
sl@0:         */
sl@0:         continue;
sl@0:       }else if( i==pIdx->nColumn ){
sl@0:         /* Index column i is the rowid.  All other terms match. */
sl@0:         break;
sl@0:       }else{
sl@0:         /* If an index column fails to match and is not constrained by ==
sl@0:         ** then the index cannot satisfy the ORDER BY constraint.
sl@0:         */
sl@0:         return 0;
sl@0:       }
sl@0:     }
sl@0:     assert( pIdx->aSortOrder!=0 );
sl@0:     assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 );
sl@0:     assert( iSortOrder==0 || iSortOrder==1 );
sl@0:     termSortOrder = iSortOrder ^ pTerm->sortOrder;
sl@0:     if( i>nEqCol ){
sl@0:       if( termSortOrder!=sortOrder ){
sl@0:         /* Indices can only be used if all ORDER BY terms past the
sl@0:         ** equality constraints are all either DESC or ASC. */
sl@0:         return 0;
sl@0:       }
sl@0:     }else{
sl@0:       sortOrder = termSortOrder;
sl@0:     }
sl@0:     j++;
sl@0:     pTerm++;
sl@0:     if( iColumn<0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
sl@0:       /* If the indexed column is the primary key and everything matches
sl@0:       ** so far and none of the ORDER BY terms to the right reference other
sl@0:       ** tables in the join, then we are assured that the index can be used 
sl@0:       ** to sort because the primary key is unique and so none of the other
sl@0:       ** columns will make any difference
sl@0:       */
sl@0:       j = nTerm;
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   *pbRev = sortOrder!=0;
sl@0:   if( j>=nTerm ){
sl@0:     /* All terms of the ORDER BY clause are covered by this index so
sl@0:     ** this index can be used for sorting. */
sl@0:     return 1;
sl@0:   }
sl@0:   if( pIdx->onError!=OE_None && i==pIdx->nColumn
sl@0:       && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
sl@0:     /* All terms of this index match some prefix of the ORDER BY clause
sl@0:     ** and the index is UNIQUE and no terms on the tail of the ORDER BY
sl@0:     ** clause reference other tables in a join.  If this is all true then
sl@0:     ** the order by clause is superfluous. */
sl@0:     return 1;
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Check table to see if the ORDER BY clause in pOrderBy can be satisfied
sl@0: ** by sorting in order of ROWID.  Return true if so and set *pbRev to be
sl@0: ** true for reverse ROWID and false for forward ROWID order.
sl@0: */
sl@0: static int sortableByRowid(
sl@0:   int base,               /* Cursor number for table to be sorted */
sl@0:   ExprList *pOrderBy,     /* The ORDER BY clause */
sl@0:   ExprMaskSet *pMaskSet,  /* Mapping from tables to bitmaps */
sl@0:   int *pbRev              /* Set to 1 if ORDER BY is DESC */
sl@0: ){
sl@0:   Expr *p;
sl@0: 
sl@0:   assert( pOrderBy!=0 );
sl@0:   assert( pOrderBy->nExpr>0 );
sl@0:   p = pOrderBy->a[0].pExpr;
sl@0:   if( p->op==TK_COLUMN && p->iTable==base && p->iColumn==-1
sl@0:     && !referencesOtherTables(pOrderBy, pMaskSet, 1, base) ){
sl@0:     *pbRev = pOrderBy->a[0].sortOrder;
sl@0:     return 1;
sl@0:   }
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Prepare a crude estimate of the logarithm of the input value.
sl@0: ** The results need not be exact.  This is only used for estimating
sl@0: ** the total cost of performing operations with O(logN) or O(NlogN)
sl@0: ** complexity.  Because N is just a guess, it is no great tragedy if
sl@0: ** logN is a little off.
sl@0: */
sl@0: static double estLog(double N){
sl@0:   double logN = 1;
sl@0:   double x = 10;
sl@0:   while( N>x ){
sl@0:     logN += 1;
sl@0:     x *= 10;
sl@0:   }
sl@0:   return logN;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Two routines for printing the content of an sqlite3_index_info
sl@0: ** structure.  Used for testing and debugging only.  If neither
sl@0: ** SQLITE_TEST or SQLITE_DEBUG are defined, then these routines
sl@0: ** are no-ops.
sl@0: */
sl@0: #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_DEBUG)
sl@0: static void TRACE_IDX_INPUTS(sqlite3_index_info *p){
sl@0:   int i;
sl@0:   if( !sqlite3WhereTrace ) return;
sl@0:   for(i=0; i<p->nConstraint; i++){
sl@0:     sqlite3DebugPrintf("  constraint[%d]: col=%d termid=%d op=%d usabled=%d\n",
sl@0:        i,
sl@0:        p->aConstraint[i].iColumn,
sl@0:        p->aConstraint[i].iTermOffset,
sl@0:        p->aConstraint[i].op,
sl@0:        p->aConstraint[i].usable);
sl@0:   }
sl@0:   for(i=0; i<p->nOrderBy; i++){
sl@0:     sqlite3DebugPrintf("  orderby[%d]: col=%d desc=%d\n",
sl@0:        i,
sl@0:        p->aOrderBy[i].iColumn,
sl@0:        p->aOrderBy[i].desc);
sl@0:   }
sl@0: }
sl@0: static void TRACE_IDX_OUTPUTS(sqlite3_index_info *p){
sl@0:   int i;
sl@0:   if( !sqlite3WhereTrace ) return;
sl@0:   for(i=0; i<p->nConstraint; i++){
sl@0:     sqlite3DebugPrintf("  usage[%d]: argvIdx=%d omit=%d\n",
sl@0:        i,
sl@0:        p->aConstraintUsage[i].argvIndex,
sl@0:        p->aConstraintUsage[i].omit);
sl@0:   }
sl@0:   sqlite3DebugPrintf("  idxNum=%d\n", p->idxNum);
sl@0:   sqlite3DebugPrintf("  idxStr=%s\n", p->idxStr);
sl@0:   sqlite3DebugPrintf("  orderByConsumed=%d\n", p->orderByConsumed);
sl@0:   sqlite3DebugPrintf("  estimatedCost=%g\n", p->estimatedCost);
sl@0: }
sl@0: #else
sl@0: #define TRACE_IDX_INPUTS(A)
sl@0: #define TRACE_IDX_OUTPUTS(A)
sl@0: #endif
sl@0: 
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0: /*
sl@0: ** Compute the best index for a virtual table.
sl@0: **
sl@0: ** The best index is computed by the xBestIndex method of the virtual
sl@0: ** table module.  This routine is really just a wrapper that sets up
sl@0: ** the sqlite3_index_info structure that is used to communicate with
sl@0: ** xBestIndex.
sl@0: **
sl@0: ** In a join, this routine might be called multiple times for the
sl@0: ** same virtual table.  The sqlite3_index_info structure is created
sl@0: ** and initialized on the first invocation and reused on all subsequent
sl@0: ** invocations.  The sqlite3_index_info structure is also used when
sl@0: ** code is generated to access the virtual table.  The whereInfoDelete() 
sl@0: ** routine takes care of freeing the sqlite3_index_info structure after
sl@0: ** everybody has finished with it.
sl@0: */
sl@0: static double bestVirtualIndex(
sl@0:   Parse *pParse,                 /* The parsing context */
sl@0:   WhereClause *pWC,              /* The WHERE clause */
sl@0:   struct SrcList_item *pSrc,     /* The FROM clause term to search */
sl@0:   Bitmask notReady,              /* Mask of cursors that are not available */
sl@0:   ExprList *pOrderBy,            /* The order by clause */
sl@0:   int orderByUsable,             /* True if we can potential sort */
sl@0:   sqlite3_index_info **ppIdxInfo /* Index information passed to xBestIndex */
sl@0: ){
sl@0:   Table *pTab = pSrc->pTab;
sl@0:   sqlite3_vtab *pVtab = pTab->pVtab;
sl@0:   sqlite3_index_info *pIdxInfo;
sl@0:   struct sqlite3_index_constraint *pIdxCons;
sl@0:   struct sqlite3_index_orderby *pIdxOrderBy;
sl@0:   struct sqlite3_index_constraint_usage *pUsage;
sl@0:   WhereTerm *pTerm;
sl@0:   int i, j;
sl@0:   int nOrderBy;
sl@0:   int rc;
sl@0: 
sl@0:   /* If the sqlite3_index_info structure has not been previously
sl@0:   ** allocated and initialized for this virtual table, then allocate
sl@0:   ** and initialize it now
sl@0:   */
sl@0:   pIdxInfo = *ppIdxInfo;
sl@0:   if( pIdxInfo==0 ){
sl@0:     WhereTerm *pTerm;
sl@0:     int nTerm;
sl@0:     WHERETRACE(("Recomputing index info for %s...\n", pTab->zName));
sl@0: 
sl@0:     /* Count the number of possible WHERE clause constraints referring
sl@0:     ** to this virtual table */
sl@0:     for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
sl@0:       if( pTerm->leftCursor != pSrc->iCursor ) continue;
sl@0:       assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
sl@0:       testcase( pTerm->eOperator==WO_IN );
sl@0:       testcase( pTerm->eOperator==WO_ISNULL );
sl@0:       if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue;
sl@0:       nTerm++;
sl@0:     }
sl@0: 
sl@0:     /* If the ORDER BY clause contains only columns in the current 
sl@0:     ** virtual table then allocate space for the aOrderBy part of
sl@0:     ** the sqlite3_index_info structure.
sl@0:     */
sl@0:     nOrderBy = 0;
sl@0:     if( pOrderBy ){
sl@0:       for(i=0; i<pOrderBy->nExpr; i++){
sl@0:         Expr *pExpr = pOrderBy->a[i].pExpr;
sl@0:         if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break;
sl@0:       }
sl@0:       if( i==pOrderBy->nExpr ){
sl@0:         nOrderBy = pOrderBy->nExpr;
sl@0:       }
sl@0:     }
sl@0: 
sl@0:     /* Allocate the sqlite3_index_info structure
sl@0:     */
sl@0:     pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo)
sl@0:                              + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm
sl@0:                              + sizeof(*pIdxOrderBy)*nOrderBy );
sl@0:     if( pIdxInfo==0 ){
sl@0:       sqlite3ErrorMsg(pParse, "out of memory");
sl@0:       return 0.0;
sl@0:     }
sl@0:     *ppIdxInfo = pIdxInfo;
sl@0: 
sl@0:     /* Initialize the structure.  The sqlite3_index_info structure contains
sl@0:     ** many fields that are declared "const" to prevent xBestIndex from
sl@0:     ** changing them.  We have to do some funky casting in order to
sl@0:     ** initialize those fields.
sl@0:     */
sl@0:     pIdxCons = (struct sqlite3_index_constraint*)&pIdxInfo[1];
sl@0:     pIdxOrderBy = (struct sqlite3_index_orderby*)&pIdxCons[nTerm];
sl@0:     pUsage = (struct sqlite3_index_constraint_usage*)&pIdxOrderBy[nOrderBy];
sl@0:     *(int*)&pIdxInfo->nConstraint = nTerm;
sl@0:     *(int*)&pIdxInfo->nOrderBy = nOrderBy;
sl@0:     *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
sl@0:     *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
sl@0:     *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
sl@0:                                                                      pUsage;
sl@0: 
sl@0:     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
sl@0:       if( pTerm->leftCursor != pSrc->iCursor ) continue;
sl@0:       assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
sl@0:       testcase( pTerm->eOperator==WO_IN );
sl@0:       testcase( pTerm->eOperator==WO_ISNULL );
sl@0:       if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue;
sl@0:       pIdxCons[j].iColumn = pTerm->leftColumn;
sl@0:       pIdxCons[j].iTermOffset = i;
sl@0:       pIdxCons[j].op = pTerm->eOperator;
sl@0:       /* The direct assignment in the previous line is possible only because
sl@0:       ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
sl@0:       ** following asserts verify this fact. */
sl@0:       assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
sl@0:       assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
sl@0:       assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
sl@0:       assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
sl@0:       assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
sl@0:       assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
sl@0:       assert( pTerm->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );
sl@0:       j++;
sl@0:     }
sl@0:     for(i=0; i<nOrderBy; i++){
sl@0:       Expr *pExpr = pOrderBy->a[i].pExpr;
sl@0:       pIdxOrderBy[i].iColumn = pExpr->iColumn;
sl@0:       pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder;
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   /* At this point, the sqlite3_index_info structure that pIdxInfo points
sl@0:   ** to will have been initialized, either during the current invocation or
sl@0:   ** during some prior invocation.  Now we just have to customize the
sl@0:   ** details of pIdxInfo for the current invocation and pass it to
sl@0:   ** xBestIndex.
sl@0:   */
sl@0: 
sl@0:   /* The module name must be defined. Also, by this point there must
sl@0:   ** be a pointer to an sqlite3_vtab structure. Otherwise
sl@0:   ** sqlite3ViewGetColumnNames() would have picked up the error. 
sl@0:   */
sl@0:   assert( pTab->azModuleArg && pTab->azModuleArg[0] );
sl@0:   assert( pVtab );
sl@0: #if 0
sl@0:   if( pTab->pVtab==0 ){
sl@0:     sqlite3ErrorMsg(pParse, "undefined module %s for table %s",
sl@0:         pTab->azModuleArg[0], pTab->zName);
sl@0:     return 0.0;
sl@0:   }
sl@0: #endif
sl@0: 
sl@0:   /* Set the aConstraint[].usable fields and initialize all 
sl@0:   ** output variables to zero.
sl@0:   **
sl@0:   ** aConstraint[].usable is true for constraints where the right-hand
sl@0:   ** side contains only references to tables to the left of the current
sl@0:   ** table.  In other words, if the constraint is of the form:
sl@0:   **
sl@0:   **           column = expr
sl@0:   **
sl@0:   ** and we are evaluating a join, then the constraint on column is 
sl@0:   ** only valid if all tables referenced in expr occur to the left
sl@0:   ** of the table containing column.
sl@0:   **
sl@0:   ** The aConstraints[] array contains entries for all constraints
sl@0:   ** on the current table.  That way we only have to compute it once
sl@0:   ** even though we might try to pick the best index multiple times.
sl@0:   ** For each attempt at picking an index, the order of tables in the
sl@0:   ** join might be different so we have to recompute the usable flag
sl@0:   ** each time.
sl@0:   */
sl@0:   pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
sl@0:   pUsage = pIdxInfo->aConstraintUsage;
sl@0:   for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
sl@0:     j = pIdxCons->iTermOffset;
sl@0:     pTerm = &pWC->a[j];
sl@0:     pIdxCons->usable =  (pTerm->prereqRight & notReady)==0;
sl@0:   }
sl@0:   memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
sl@0:   if( pIdxInfo->needToFreeIdxStr ){
sl@0:     sqlite3_free(pIdxInfo->idxStr);
sl@0:   }
sl@0:   pIdxInfo->idxStr = 0;
sl@0:   pIdxInfo->idxNum = 0;
sl@0:   pIdxInfo->needToFreeIdxStr = 0;
sl@0:   pIdxInfo->orderByConsumed = 0;
sl@0:   pIdxInfo->estimatedCost = SQLITE_BIG_DBL / 2.0;
sl@0:   nOrderBy = pIdxInfo->nOrderBy;
sl@0:   if( pIdxInfo->nOrderBy && !orderByUsable ){
sl@0:     *(int*)&pIdxInfo->nOrderBy = 0;
sl@0:   }
sl@0: 
sl@0:   (void)sqlite3SafetyOff(pParse->db);
sl@0:   WHERETRACE(("xBestIndex for %s\n", pTab->zName));
sl@0:   TRACE_IDX_INPUTS(pIdxInfo);
sl@0:   rc = pVtab->pModule->xBestIndex(pVtab, pIdxInfo);
sl@0:   TRACE_IDX_OUTPUTS(pIdxInfo);
sl@0:   (void)sqlite3SafetyOn(pParse->db);
sl@0: 
sl@0:   if( rc!=SQLITE_OK ){
sl@0:     if( rc==SQLITE_NOMEM ){
sl@0:       pParse->db->mallocFailed = 1;
sl@0:     }else if( !pVtab->zErrMsg ){
sl@0:       sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
sl@0:     }else{
sl@0:       sqlite3ErrorMsg(pParse, "%s", pVtab->zErrMsg);
sl@0:     }
sl@0:   }
sl@0:   sqlite3DbFree(pParse->db, pVtab->zErrMsg);
sl@0:   pVtab->zErrMsg = 0;
sl@0: 
sl@0:   for(i=0; i<pIdxInfo->nConstraint; i++){
sl@0:     if( !pIdxInfo->aConstraint[i].usable && pUsage[i].argvIndex>0 ){
sl@0:       sqlite3ErrorMsg(pParse, 
sl@0:           "table %s: xBestIndex returned an invalid plan", pTab->zName);
sl@0:       return 0.0;
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   *(int*)&pIdxInfo->nOrderBy = nOrderBy;
sl@0:   return pIdxInfo->estimatedCost;
sl@0: }
sl@0: #endif /* SQLITE_OMIT_VIRTUALTABLE */
sl@0: 
sl@0: /*
sl@0: ** Find the best index for accessing a particular table.  Return a pointer
sl@0: ** to the index, flags that describe how the index should be used, the
sl@0: ** number of equality constraints, and the "cost" for this index.
sl@0: **
sl@0: ** The lowest cost index wins.  The cost is an estimate of the amount of
sl@0: ** CPU and disk I/O need to process the request using the selected index.
sl@0: ** Factors that influence cost include:
sl@0: **
sl@0: **    *  The estimated number of rows that will be retrieved.  (The
sl@0: **       fewer the better.)
sl@0: **
sl@0: **    *  Whether or not sorting must occur.
sl@0: **
sl@0: **    *  Whether or not there must be separate lookups in the
sl@0: **       index and in the main table.
sl@0: **
sl@0: ** If there was an INDEXED BY clause attached to the table in the SELECT
sl@0: ** statement, then this function only considers strategies using the 
sl@0: ** named index. If one cannot be found, then the returned cost is
sl@0: ** SQLITE_BIG_DBL. If a strategy can be found that uses the named index, 
sl@0: ** then the cost is calculated in the usual way.
sl@0: **
sl@0: ** If a NOT INDEXED clause was attached to the table in the SELECT 
sl@0: ** statement, then no indexes are considered. However, the selected 
sl@0: ** stategy may still take advantage of the tables built-in rowid
sl@0: ** index.
sl@0: */
sl@0: static double bestIndex(
sl@0:   Parse *pParse,              /* The parsing context */
sl@0:   WhereClause *pWC,           /* The WHERE clause */
sl@0:   struct SrcList_item *pSrc,  /* The FROM clause term to search */
sl@0:   Bitmask notReady,           /* Mask of cursors that are not available */
sl@0:   ExprList *pOrderBy,         /* The order by clause */
sl@0:   Index **ppIndex,            /* Make *ppIndex point to the best index */
sl@0:   int *pFlags,                /* Put flags describing this choice in *pFlags */
sl@0:   int *pnEq                   /* Put the number of == or IN constraints here */
sl@0: ){
sl@0:   WhereTerm *pTerm;
sl@0:   Index *bestIdx = 0;         /* Index that gives the lowest cost */
sl@0:   double lowestCost;          /* The cost of using bestIdx */
sl@0:   int bestFlags = 0;          /* Flags associated with bestIdx */
sl@0:   int bestNEq = 0;            /* Best value for nEq */
sl@0:   int iCur = pSrc->iCursor;   /* The cursor of the table to be accessed */
sl@0:   Index *pProbe;              /* An index we are evaluating */
sl@0:   int rev;                    /* True to scan in reverse order */
sl@0:   int flags;                  /* Flags associated with pProbe */
sl@0:   int nEq;                    /* Number of == or IN constraints */
sl@0:   int eqTermMask;             /* Mask of valid equality operators */
sl@0:   double cost;                /* Cost of using pProbe */
sl@0: 
sl@0:   WHERETRACE(("bestIndex: tbl=%s notReady=%llx\n", pSrc->pTab->zName, notReady));
sl@0:   lowestCost = SQLITE_BIG_DBL;
sl@0:   pProbe = pSrc->pTab->pIndex;
sl@0:   if( pSrc->notIndexed ){
sl@0:     pProbe = 0;
sl@0:   }
sl@0: 
sl@0:   /* If the table has no indices and there are no terms in the where
sl@0:   ** clause that refer to the ROWID, then we will never be able to do
sl@0:   ** anything other than a full table scan on this table.  We might as
sl@0:   ** well put it first in the join order.  That way, perhaps it can be
sl@0:   ** referenced by other tables in the join.
sl@0:   */
sl@0:   if( pProbe==0 &&
sl@0:      findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IN|WO_LT|WO_LE|WO_GT|WO_GE,0)==0 &&
sl@0:      (pOrderBy==0 || !sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)) ){
sl@0:     *pFlags = 0;
sl@0:     *ppIndex = 0;
sl@0:     *pnEq = 0;
sl@0:     return 0.0;
sl@0:   }
sl@0: 
sl@0:   /* Check for a rowid=EXPR or rowid IN (...) constraints. If there was
sl@0:   ** an INDEXED BY clause attached to this table, skip this step.
sl@0:   */
sl@0:   if( !pSrc->pIndex ){
sl@0:     pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
sl@0:     if( pTerm ){
sl@0:       Expr *pExpr;
sl@0:       *ppIndex = 0;
sl@0:       bestFlags = WHERE_ROWID_EQ;
sl@0:       if( pTerm->eOperator & WO_EQ ){
sl@0:         /* Rowid== is always the best pick.  Look no further.  Because only
sl@0:         ** a single row is generated, output is always in sorted order */
sl@0:         *pFlags = WHERE_ROWID_EQ | WHERE_UNIQUE;
sl@0:         *pnEq = 1;
sl@0:         WHERETRACE(("... best is rowid\n"));
sl@0:         return 0.0;
sl@0:       }else if( (pExpr = pTerm->pExpr)->pList!=0 ){
sl@0:         /* Rowid IN (LIST): cost is NlogN where N is the number of list
sl@0:         ** elements.  */
sl@0:         lowestCost = pExpr->pList->nExpr;
sl@0:         lowestCost *= estLog(lowestCost);
sl@0:       }else{
sl@0:         /* Rowid IN (SELECT): cost is NlogN where N is the number of rows
sl@0:         ** in the result of the inner select.  We have no way to estimate
sl@0:         ** that value so make a wild guess. */
sl@0:         lowestCost = 200;
sl@0:       }
sl@0:       WHERETRACE(("... rowid IN cost: %.9g\n", lowestCost));
sl@0:     }
sl@0:   
sl@0:     /* Estimate the cost of a table scan.  If we do not know how many
sl@0:     ** entries are in the table, use 1 million as a guess.
sl@0:     */
sl@0:     cost = pProbe ? pProbe->aiRowEst[0] : 1000000;
sl@0:     WHERETRACE(("... table scan base cost: %.9g\n", cost));
sl@0:     flags = WHERE_ROWID_RANGE;
sl@0:   
sl@0:     /* Check for constraints on a range of rowids in a table scan.
sl@0:     */
sl@0:     pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
sl@0:     if( pTerm ){
sl@0:       if( findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE, 0) ){
sl@0:         flags |= WHERE_TOP_LIMIT;
sl@0:         cost /= 3;  /* Guess that rowid<EXPR eliminates two-thirds or rows */
sl@0:       }
sl@0:       if( findTerm(pWC, iCur, -1, notReady, WO_GT|WO_GE, 0) ){
sl@0:         flags |= WHERE_BTM_LIMIT;
sl@0:         cost /= 3;  /* Guess that rowid>EXPR eliminates two-thirds of rows */
sl@0:       }
sl@0:       WHERETRACE(("... rowid range reduces cost to %.9g\n", cost));
sl@0:     }else{
sl@0:       flags = 0;
sl@0:     }
sl@0:   
sl@0:     /* If the table scan does not satisfy the ORDER BY clause, increase
sl@0:     ** the cost by NlogN to cover the expense of sorting. */
sl@0:     if( pOrderBy ){
sl@0:       if( sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) ){
sl@0:         flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE;
sl@0:         if( rev ){
sl@0:           flags |= WHERE_REVERSE;
sl@0:         }
sl@0:       }else{
sl@0:         cost += cost*estLog(cost);
sl@0:         WHERETRACE(("... sorting increases cost to %.9g\n", cost));
sl@0:       }
sl@0:     }
sl@0:     if( cost<lowestCost ){
sl@0:       lowestCost = cost;
sl@0:       bestFlags = flags;
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   /* If the pSrc table is the right table of a LEFT JOIN then we may not
sl@0:   ** use an index to satisfy IS NULL constraints on that table.  This is
sl@0:   ** because columns might end up being NULL if the table does not match -
sl@0:   ** a circumstance which the index cannot help us discover.  Ticket #2177.
sl@0:   */
sl@0:   if( (pSrc->jointype & JT_LEFT)!=0 ){
sl@0:     eqTermMask = WO_EQ|WO_IN;
sl@0:   }else{
sl@0:     eqTermMask = WO_EQ|WO_IN|WO_ISNULL;
sl@0:   }
sl@0: 
sl@0:   /* Look at each index.
sl@0:   */
sl@0:   if( pSrc->pIndex ){
sl@0:     pProbe = pSrc->pIndex;
sl@0:   }
sl@0:   for(; pProbe; pProbe=(pSrc->pIndex ? 0 : pProbe->pNext)){
sl@0:     int i;                       /* Loop counter */
sl@0:     double inMultiplier = 1;
sl@0: 
sl@0:     WHERETRACE(("... index %s:\n", pProbe->zName));
sl@0: 
sl@0:     /* Count the number of columns in the index that are satisfied
sl@0:     ** by x=EXPR constraints or x IN (...) constraints.
sl@0:     */
sl@0:     flags = 0;
sl@0:     for(i=0; i<pProbe->nColumn; i++){
sl@0:       int j = pProbe->aiColumn[i];
sl@0:       pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pProbe);
sl@0:       if( pTerm==0 ) break;
sl@0:       flags |= WHERE_COLUMN_EQ;
sl@0:       if( pTerm->eOperator & WO_IN ){
sl@0:         Expr *pExpr = pTerm->pExpr;
sl@0:         flags |= WHERE_COLUMN_IN;
sl@0:         if( pExpr->pSelect!=0 ){
sl@0:           inMultiplier *= 25;
sl@0:         }else if( ALWAYS(pExpr->pList) ){
sl@0:           inMultiplier *= pExpr->pList->nExpr + 1;
sl@0:         }
sl@0:       }
sl@0:     }
sl@0:     cost = pProbe->aiRowEst[i] * inMultiplier * estLog(inMultiplier);
sl@0:     nEq = i;
sl@0:     if( pProbe->onError!=OE_None && (flags & WHERE_COLUMN_IN)==0
sl@0:          && nEq==pProbe->nColumn ){
sl@0:       flags |= WHERE_UNIQUE;
sl@0:     }
sl@0:     WHERETRACE(("...... nEq=%d inMult=%.9g cost=%.9g\n",nEq,inMultiplier,cost));
sl@0: 
sl@0:     /* Look for range constraints
sl@0:     */
sl@0:     if( nEq<pProbe->nColumn ){
sl@0:       int j = pProbe->aiColumn[nEq];
sl@0:       pTerm = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pProbe);
sl@0:       if( pTerm ){
sl@0:         flags |= WHERE_COLUMN_RANGE;
sl@0:         if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pProbe) ){
sl@0:           flags |= WHERE_TOP_LIMIT;
sl@0:           cost /= 3;
sl@0:         }
sl@0:         if( findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pProbe) ){
sl@0:           flags |= WHERE_BTM_LIMIT;
sl@0:           cost /= 3;
sl@0:         }
sl@0:         WHERETRACE(("...... range reduces cost to %.9g\n", cost));
sl@0:       }
sl@0:     }
sl@0: 
sl@0:     /* Add the additional cost of sorting if that is a factor.
sl@0:     */
sl@0:     if( pOrderBy ){
sl@0:       if( (flags & WHERE_COLUMN_IN)==0 &&
sl@0:            isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev) ){
sl@0:         if( flags==0 ){
sl@0:           flags = WHERE_COLUMN_RANGE;
sl@0:         }
sl@0:         flags |= WHERE_ORDERBY;
sl@0:         if( rev ){
sl@0:           flags |= WHERE_REVERSE;
sl@0:         }
sl@0:       }else{
sl@0:         cost += cost*estLog(cost);
sl@0:         WHERETRACE(("...... orderby increases cost to %.9g\n", cost));
sl@0:       }
sl@0:     }
sl@0: 
sl@0:     /* Check to see if we can get away with using just the index without
sl@0:     ** ever reading the table.  If that is the case, then halve the
sl@0:     ** cost of this index.
sl@0:     */
sl@0:     if( flags && pSrc->colUsed < (((Bitmask)1)<<(BMS-1)) ){
sl@0:       Bitmask m = pSrc->colUsed;
sl@0:       int j;
sl@0:       for(j=0; j<pProbe->nColumn; j++){
sl@0:         int x = pProbe->aiColumn[j];
sl@0:         if( x<BMS-1 ){
sl@0:           m &= ~(((Bitmask)1)<<x);
sl@0:         }
sl@0:       }
sl@0:       if( m==0 ){
sl@0:         flags |= WHERE_IDX_ONLY;
sl@0:         cost /= 2;
sl@0:         WHERETRACE(("...... idx-only reduces cost to %.9g\n", cost));
sl@0:       }
sl@0:     }
sl@0: 
sl@0:     /* If this index has achieved the lowest cost so far, then use it.
sl@0:     */
sl@0:     if( flags && cost < lowestCost ){
sl@0:       bestIdx = pProbe;
sl@0:       lowestCost = cost;
sl@0:       bestFlags = flags;
sl@0:       bestNEq = nEq;
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   /* Report the best result
sl@0:   */
sl@0:   *ppIndex = bestIdx;
sl@0:   WHERETRACE(("best index is %s, cost=%.9g, flags=%x, nEq=%d\n",
sl@0:         bestIdx ? bestIdx->zName : "(none)", lowestCost, bestFlags, bestNEq));
sl@0:   *pFlags = bestFlags | eqTermMask;
sl@0:   *pnEq = bestNEq;
sl@0:   return lowestCost;
sl@0: }
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Disable a term in the WHERE clause.  Except, do not disable the term
sl@0: ** if it controls a LEFT OUTER JOIN and it did not originate in the ON
sl@0: ** or USING clause of that join.
sl@0: **
sl@0: ** Consider the term t2.z='ok' in the following queries:
sl@0: **
sl@0: **   (1)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
sl@0: **   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
sl@0: **   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
sl@0: **
sl@0: ** The t2.z='ok' is disabled in the in (2) because it originates
sl@0: ** in the ON clause.  The term is disabled in (3) because it is not part
sl@0: ** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
sl@0: **
sl@0: ** Disabling a term causes that term to not be tested in the inner loop
sl@0: ** of the join.  Disabling is an optimization.  When terms are satisfied
sl@0: ** by indices, we disable them to prevent redundant tests in the inner
sl@0: ** loop.  We would get the correct results if nothing were ever disabled,
sl@0: ** but joins might run a little slower.  The trick is to disable as much
sl@0: ** as we can without disabling too much.  If we disabled in (1), we'd get
sl@0: ** the wrong answer.  See ticket #813.
sl@0: */
sl@0: static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){
sl@0:   if( pTerm
sl@0:       && ALWAYS((pTerm->flags & TERM_CODED)==0)
sl@0:       && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin))
sl@0:   ){
sl@0:     pTerm->flags |= TERM_CODED;
sl@0:     if( pTerm->iParent>=0 ){
sl@0:       WhereTerm *pOther = &pTerm->pWC->a[pTerm->iParent];
sl@0:       if( (--pOther->nChild)==0 ){
sl@0:         disableTerm(pLevel, pOther);
sl@0:       }
sl@0:     }
sl@0:   }
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Apply the affinities associated with the first n columns of index
sl@0: ** pIdx to the values in the n registers starting at base.
sl@0: */
sl@0: static void codeApplyAffinity(Parse *pParse, int base, int n, Index *pIdx){
sl@0:   if( n>0 ){
sl@0:     Vdbe *v = pParse->pVdbe;
sl@0:     assert( v!=0 );
sl@0:     sqlite3VdbeAddOp2(v, OP_Affinity, base, n);
sl@0:     sqlite3IndexAffinityStr(v, pIdx);
sl@0:     sqlite3ExprCacheAffinityChange(pParse, base, n);
sl@0:   }
sl@0: }
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Generate code for a single equality term of the WHERE clause.  An equality
sl@0: ** term can be either X=expr or X IN (...).   pTerm is the term to be 
sl@0: ** coded.
sl@0: **
sl@0: ** The current value for the constraint is left in register iReg.
sl@0: **
sl@0: ** For a constraint of the form X=expr, the expression is evaluated and its
sl@0: ** result is left on the stack.  For constraints of the form X IN (...)
sl@0: ** this routine sets up a loop that will iterate over all values of X.
sl@0: */
sl@0: static int codeEqualityTerm(
sl@0:   Parse *pParse,      /* The parsing context */
sl@0:   WhereTerm *pTerm,   /* The term of the WHERE clause to be coded */
sl@0:   WhereLevel *pLevel, /* When level of the FROM clause we are working on */
sl@0:   int iTarget         /* Attempt to leave results in this register */
sl@0: ){
sl@0:   Expr *pX = pTerm->pExpr;
sl@0:   Vdbe *v = pParse->pVdbe;
sl@0:   int iReg;                  /* Register holding results */
sl@0: 
sl@0:   assert( iTarget>0 );
sl@0:   if( pX->op==TK_EQ ){
sl@0:     iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
sl@0:   }else if( pX->op==TK_ISNULL ){
sl@0:     iReg = iTarget;
sl@0:     sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
sl@0: #ifndef SQLITE_OMIT_SUBQUERY
sl@0:   }else{
sl@0:     int eType;
sl@0:     int iTab;
sl@0:     struct InLoop *pIn;
sl@0: 
sl@0:     assert( pX->op==TK_IN );
sl@0:     iReg = iTarget;
sl@0:     eType = sqlite3FindInIndex(pParse, pX, 0);
sl@0:     iTab = pX->iTable;
sl@0:     sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
sl@0:     VdbeComment((v, "%.*s", pX->span.n, pX->span.z));
sl@0:     if( pLevel->nIn==0 ){
sl@0:       pLevel->nxt = sqlite3VdbeMakeLabel(v);
sl@0:     }
sl@0:     pLevel->nIn++;
sl@0:     pLevel->aInLoop = sqlite3DbReallocOrFree(pParse->db, pLevel->aInLoop,
sl@0:                                     sizeof(pLevel->aInLoop[0])*pLevel->nIn);
sl@0:     pIn = pLevel->aInLoop;
sl@0:     if( pIn ){
sl@0:       pIn += pLevel->nIn - 1;
sl@0:       pIn->iCur = iTab;
sl@0:       if( eType==IN_INDEX_ROWID ){
sl@0:         pIn->topAddr = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iReg);
sl@0:       }else{
sl@0:         pIn->topAddr = sqlite3VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
sl@0:       }
sl@0:       sqlite3VdbeAddOp1(v, OP_IsNull, iReg);
sl@0:     }else{
sl@0:       pLevel->nIn = 0;
sl@0:     }
sl@0: #endif
sl@0:   }
sl@0:   disableTerm(pLevel, pTerm);
sl@0:   return iReg;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Generate code that will evaluate all == and IN constraints for an
sl@0: ** index.  The values for all constraints are left on the stack.
sl@0: **
sl@0: ** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
sl@0: ** Suppose the WHERE clause is this:  a==5 AND b IN (1,2,3) AND c>5 AND c<10
sl@0: ** The index has as many as three equality constraints, but in this
sl@0: ** example, the third "c" value is an inequality.  So only two 
sl@0: ** constraints are coded.  This routine will generate code to evaluate
sl@0: ** a==5 and b IN (1,2,3).  The current values for a and b will be left
sl@0: ** on the stack - a is the deepest and b the shallowest.
sl@0: **
sl@0: ** In the example above nEq==2.  But this subroutine works for any value
sl@0: ** of nEq including 0.  If nEq==0, this routine is nearly a no-op.
sl@0: ** The only thing it does is allocate the pLevel->iMem memory cell.
sl@0: **
sl@0: ** This routine always allocates at least one memory cell and puts
sl@0: ** the address of that memory cell in pLevel->iMem.  The code that
sl@0: ** calls this routine will use pLevel->iMem to store the termination
sl@0: ** key value of the loop.  If one or more IN operators appear, then
sl@0: ** this routine allocates an additional nEq memory cells for internal
sl@0: ** use.
sl@0: */
sl@0: static int codeAllEqualityTerms(
sl@0:   Parse *pParse,        /* Parsing context */
sl@0:   WhereLevel *pLevel,   /* Which nested loop of the FROM we are coding */
sl@0:   WhereClause *pWC,     /* The WHERE clause */
sl@0:   Bitmask notReady,     /* Which parts of FROM have not yet been coded */
sl@0:   int nExtraReg         /* Number of extra registers to allocate */
sl@0: ){
sl@0:   int nEq = pLevel->nEq;        /* The number of == or IN constraints to code */
sl@0:   Vdbe *v = pParse->pVdbe;      /* The virtual machine under construction */
sl@0:   Index *pIdx = pLevel->pIdx;   /* The index being used for this loop */
sl@0:   int iCur = pLevel->iTabCur;   /* The cursor of the table */
sl@0:   WhereTerm *pTerm;             /* A single constraint term */
sl@0:   int j;                        /* Loop counter */
sl@0:   int regBase;                  /* Base register */
sl@0: 
sl@0:   /* Figure out how many memory cells we will need then allocate them.
sl@0:   ** We always need at least one used to store the loop terminator
sl@0:   ** value.  If there are IN operators we'll need one for each == or
sl@0:   ** IN constraint.
sl@0:   */
sl@0:   pLevel->iMem = pParse->nMem + 1;
sl@0:   regBase = pParse->nMem + 2;
sl@0:   pParse->nMem += pLevel->nEq + 2 + nExtraReg;
sl@0: 
sl@0:   /* Evaluate the equality constraints
sl@0:   */
sl@0:   assert( pIdx->nColumn>=nEq );
sl@0:   for(j=0; j<nEq; j++){
sl@0:     int r1;
sl@0:     int k = pIdx->aiColumn[j];
sl@0:     pTerm = findTerm(pWC, iCur, k, notReady, pLevel->flags, pIdx);
sl@0:     if( NEVER(pTerm==0) ) break;
sl@0:     assert( (pTerm->flags & TERM_CODED)==0 );
sl@0:     r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
sl@0:     if( r1!=regBase+j ){
sl@0:       sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
sl@0:     }
sl@0:     testcase( pTerm->eOperator & WO_ISNULL );
sl@0:     testcase( pTerm->eOperator & WO_IN );
sl@0:     if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
sl@0:       sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->brk);
sl@0:     }
sl@0:   }
sl@0:   return regBase;
sl@0: }
sl@0: 
sl@0: #if defined(SQLITE_TEST)
sl@0: /*
sl@0: ** The following variable holds a text description of query plan generated
sl@0: ** by the most recent call to sqlite3WhereBegin().  Each call to WhereBegin
sl@0: ** overwrites the previous.  This information is used for testing and
sl@0: ** analysis only.
sl@0: */
sl@0: char sqlite3_query_plan[BMS*2*40];  /* Text of the join */
sl@0: static int nQPlan = 0;              /* Next free slow in _query_plan[] */
sl@0: 
sl@0: #endif /* SQLITE_TEST */
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Free a WhereInfo structure
sl@0: */
sl@0: static void whereInfoFree(sqlite3 *db, WhereInfo *pWInfo){
sl@0:   if( pWInfo ){
sl@0:     int i;
sl@0:     for(i=0; i<pWInfo->nLevel; i++){
sl@0:       sqlite3_index_info *pInfo = pWInfo->a[i].pIdxInfo;
sl@0:       if( pInfo ){
sl@0:         assert( pInfo->needToFreeIdxStr==0 );
sl@0:         sqlite3DbFree(db, pInfo);
sl@0:       }
sl@0:     }
sl@0:     sqlite3DbFree(db, pWInfo);
sl@0:   }
sl@0: }
sl@0: 
sl@0: 
sl@0: /*
sl@0: ** Generate the beginning of the loop used for WHERE clause processing.
sl@0: ** The return value is a pointer to an opaque structure that contains
sl@0: ** information needed to terminate the loop.  Later, the calling routine
sl@0: ** should invoke sqlite3WhereEnd() with the return value of this function
sl@0: ** in order to complete the WHERE clause processing.
sl@0: **
sl@0: ** If an error occurs, this routine returns NULL.
sl@0: **
sl@0: ** The basic idea is to do a nested loop, one loop for each table in
sl@0: ** the FROM clause of a select.  (INSERT and UPDATE statements are the
sl@0: ** same as a SELECT with only a single table in the FROM clause.)  For
sl@0: ** example, if the SQL is this:
sl@0: **
sl@0: **       SELECT * FROM t1, t2, t3 WHERE ...;
sl@0: **
sl@0: ** Then the code generated is conceptually like the following:
sl@0: **
sl@0: **      foreach row1 in t1 do       \    Code generated
sl@0: **        foreach row2 in t2 do      |-- by sqlite3WhereBegin()
sl@0: **          foreach row3 in t3 do   /
sl@0: **            ...
sl@0: **          end                     \    Code generated
sl@0: **        end                        |-- by sqlite3WhereEnd()
sl@0: **      end                         /
sl@0: **
sl@0: ** Note that the loops might not be nested in the order in which they
sl@0: ** appear in the FROM clause if a different order is better able to make
sl@0: ** use of indices.  Note also that when the IN operator appears in
sl@0: ** the WHERE clause, it might result in additional nested loops for
sl@0: ** scanning through all values on the right-hand side of the IN.
sl@0: **
sl@0: ** There are Btree cursors associated with each table.  t1 uses cursor
sl@0: ** number pTabList->a[0].iCursor.  t2 uses the cursor pTabList->a[1].iCursor.
sl@0: ** And so forth.  This routine generates code to open those VDBE cursors
sl@0: ** and sqlite3WhereEnd() generates the code to close them.
sl@0: **
sl@0: ** The code that sqlite3WhereBegin() generates leaves the cursors named
sl@0: ** in pTabList pointing at their appropriate entries.  The [...] code
sl@0: ** can use OP_Column and OP_Rowid opcodes on these cursors to extract
sl@0: ** data from the various tables of the loop.
sl@0: **
sl@0: ** If the WHERE clause is empty, the foreach loops must each scan their
sl@0: ** entire tables.  Thus a three-way join is an O(N^3) operation.  But if
sl@0: ** the tables have indices and there are terms in the WHERE clause that
sl@0: ** refer to those indices, a complete table scan can be avoided and the
sl@0: ** code will run much faster.  Most of the work of this routine is checking
sl@0: ** to see if there are indices that can be used to speed up the loop.
sl@0: **
sl@0: ** Terms of the WHERE clause are also used to limit which rows actually
sl@0: ** make it to the "..." in the middle of the loop.  After each "foreach",
sl@0: ** terms of the WHERE clause that use only terms in that loop and outer
sl@0: ** loops are evaluated and if false a jump is made around all subsequent
sl@0: ** inner loops (or around the "..." if the test occurs within the inner-
sl@0: ** most loop)
sl@0: **
sl@0: ** OUTER JOINS
sl@0: **
sl@0: ** An outer join of tables t1 and t2 is conceptally coded as follows:
sl@0: **
sl@0: **    foreach row1 in t1 do
sl@0: **      flag = 0
sl@0: **      foreach row2 in t2 do
sl@0: **        start:
sl@0: **          ...
sl@0: **          flag = 1
sl@0: **      end
sl@0: **      if flag==0 then
sl@0: **        move the row2 cursor to a null row
sl@0: **        goto start
sl@0: **      fi
sl@0: **    end
sl@0: **
sl@0: ** ORDER BY CLAUSE PROCESSING
sl@0: **
sl@0: ** *ppOrderBy is a pointer to the ORDER BY clause of a SELECT statement,
sl@0: ** if there is one.  If there is no ORDER BY clause or if this routine
sl@0: ** is called from an UPDATE or DELETE statement, then ppOrderBy is NULL.
sl@0: **
sl@0: ** If an index can be used so that the natural output order of the table
sl@0: ** scan is correct for the ORDER BY clause, then that index is used and
sl@0: ** *ppOrderBy is set to NULL.  This is an optimization that prevents an
sl@0: ** unnecessary sort of the result set if an index appropriate for the
sl@0: ** ORDER BY clause already exists.
sl@0: **
sl@0: ** If the where clause loops cannot be arranged to provide the correct
sl@0: ** output order, then the *ppOrderBy is unchanged.
sl@0: */
sl@0: WhereInfo *sqlite3WhereBegin(
sl@0:   Parse *pParse,        /* The parser context */
sl@0:   SrcList *pTabList,    /* A list of all tables to be scanned */
sl@0:   Expr *pWhere,         /* The WHERE clause */
sl@0:   ExprList **ppOrderBy, /* An ORDER BY clause, or NULL */
sl@0:   u8 wflags             /* One of the WHERE_* flags defined in sqliteInt.h */
sl@0: ){
sl@0:   int i;                     /* Loop counter */
sl@0:   WhereInfo *pWInfo;         /* Will become the return value of this function */
sl@0:   Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
sl@0:   int brk, cont = 0;         /* Addresses used during code generation */
sl@0:   Bitmask notReady;          /* Cursors that are not yet positioned */
sl@0:   WhereTerm *pTerm;          /* A single term in the WHERE clause */
sl@0:   ExprMaskSet maskSet;       /* The expression mask set */
sl@0:   WhereClause wc;            /* The WHERE clause is divided into these terms */
sl@0:   struct SrcList_item *pTabItem;  /* A single entry from pTabList */
sl@0:   WhereLevel *pLevel;             /* A single level in the pWInfo list */
sl@0:   int iFrom;                      /* First unused FROM clause element */
sl@0:   int andFlags;              /* AND-ed combination of all wc.a[].flags */
sl@0:   sqlite3 *db;               /* Database connection */
sl@0:   ExprList *pOrderBy = 0;
sl@0: 
sl@0:   /* The number of tables in the FROM clause is limited by the number of
sl@0:   ** bits in a Bitmask 
sl@0:   */
sl@0:   if( pTabList->nSrc>BMS ){
sl@0:     sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
sl@0:     return 0;
sl@0:   }
sl@0: 
sl@0:   if( ppOrderBy ){
sl@0:     pOrderBy = *ppOrderBy;
sl@0:   }
sl@0: 
sl@0:   /* Split the WHERE clause into separate subexpressions where each
sl@0:   ** subexpression is separated by an AND operator.
sl@0:   */
sl@0:   initMaskSet(&maskSet);
sl@0:   whereClauseInit(&wc, pParse, &maskSet);
sl@0:   sqlite3ExprCodeConstants(pParse, pWhere);
sl@0:   whereSplit(&wc, pWhere, TK_AND);
sl@0:     
sl@0:   /* Allocate and initialize the WhereInfo structure that will become the
sl@0:   ** return value.
sl@0:   */
sl@0:   db = pParse->db;
sl@0:   pWInfo = sqlite3DbMallocZero(db,  
sl@0:                       sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
sl@0:   if( db->mallocFailed ){
sl@0:     goto whereBeginError;
sl@0:   }
sl@0:   pWInfo->nLevel = pTabList->nSrc;
sl@0:   pWInfo->pParse = pParse;
sl@0:   pWInfo->pTabList = pTabList;
sl@0:   pWInfo->iBreak = sqlite3VdbeMakeLabel(v);
sl@0: 
sl@0:   /* Special case: a WHERE clause that is constant.  Evaluate the
sl@0:   ** expression and either jump over all of the code or fall thru.
sl@0:   */
sl@0:   if( pWhere && (pTabList->nSrc==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){
sl@0:     sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL);
sl@0:     pWhere = 0;
sl@0:   }
sl@0: 
sl@0:   /* Assign a bit from the bitmask to every term in the FROM clause.
sl@0:   **
sl@0:   ** When assigning bitmask values to FROM clause cursors, it must be
sl@0:   ** the case that if X is the bitmask for the N-th FROM clause term then
sl@0:   ** the bitmask for all FROM clause terms to the left of the N-th term
sl@0:   ** is (X-1).   An expression from the ON clause of a LEFT JOIN can use
sl@0:   ** its Expr.iRightJoinTable value to find the bitmask of the right table
sl@0:   ** of the join.  Subtracting one from the right table bitmask gives a
sl@0:   ** bitmask for all tables to the left of the join.  Knowing the bitmask
sl@0:   ** for all tables to the left of a left join is important.  Ticket #3015.
sl@0:   */
sl@0:   for(i=0; i<pTabList->nSrc; i++){
sl@0:     createMask(&maskSet, pTabList->a[i].iCursor);
sl@0:   }
sl@0: #ifndef NDEBUG
sl@0:   {
sl@0:     Bitmask toTheLeft = 0;
sl@0:     for(i=0; i<pTabList->nSrc; i++){
sl@0:       Bitmask m = getMask(&maskSet, pTabList->a[i].iCursor);
sl@0:       assert( (m-1)==toTheLeft );
sl@0:       toTheLeft |= m;
sl@0:     }
sl@0:   }
sl@0: #endif
sl@0: 
sl@0:   /* Analyze all of the subexpressions.  Note that exprAnalyze() might
sl@0:   ** add new virtual terms onto the end of the WHERE clause.  We do not
sl@0:   ** want to analyze these virtual terms, so start analyzing at the end
sl@0:   ** and work forward so that the added virtual terms are never processed.
sl@0:   */
sl@0:   exprAnalyzeAll(pTabList, &wc);
sl@0:   if( db->mallocFailed ){
sl@0:     goto whereBeginError;
sl@0:   }
sl@0: 
sl@0:   /* Chose the best index to use for each table in the FROM clause.
sl@0:   **
sl@0:   ** This loop fills in the following fields:
sl@0:   **
sl@0:   **   pWInfo->a[].pIdx      The index to use for this level of the loop.
sl@0:   **   pWInfo->a[].flags     WHERE_xxx flags associated with pIdx
sl@0:   **   pWInfo->a[].nEq       The number of == and IN constraints
sl@0:   **   pWInfo->a[].iFrom     Which term of the FROM clause is being coded
sl@0:   **   pWInfo->a[].iTabCur   The VDBE cursor for the database table
sl@0:   **   pWInfo->a[].iIdxCur   The VDBE cursor for the index
sl@0:   **
sl@0:   ** This loop also figures out the nesting order of tables in the FROM
sl@0:   ** clause.
sl@0:   */
sl@0:   notReady = ~(Bitmask)0;
sl@0:   pTabItem = pTabList->a;
sl@0:   pLevel = pWInfo->a;
sl@0:   andFlags = ~0;
sl@0:   WHERETRACE(("*** Optimizer Start ***\n"));
sl@0:   for(i=iFrom=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
sl@0:     Index *pIdx;                /* Index for FROM table at pTabItem */
sl@0:     int flags;                  /* Flags asssociated with pIdx */
sl@0:     int nEq;                    /* Number of == or IN constraints */
sl@0:     double cost;                /* The cost for pIdx */
sl@0:     int j;                      /* For looping over FROM tables */
sl@0:     Index *pBest = 0;           /* The best index seen so far */
sl@0:     int bestFlags = 0;          /* Flags associated with pBest */
sl@0:     int bestNEq = 0;            /* nEq associated with pBest */
sl@0:     double lowestCost;          /* Cost of the pBest */
sl@0:     int bestJ = 0;              /* The value of j */
sl@0:     Bitmask m;                  /* Bitmask value for j or bestJ */
sl@0:     int once = 0;               /* True when first table is seen */
sl@0:     sqlite3_index_info *pIndex; /* Current virtual index */
sl@0: 
sl@0:     lowestCost = SQLITE_BIG_DBL;
sl@0:     for(j=iFrom, pTabItem=&pTabList->a[j]; j<pTabList->nSrc; j++, pTabItem++){
sl@0:       int doNotReorder;  /* True if this table should not be reordered */
sl@0: 
sl@0:       doNotReorder =  (pTabItem->jointype & (JT_LEFT|JT_CROSS))!=0;
sl@0:       if( once && doNotReorder ) break;
sl@0:       m = getMask(&maskSet, pTabItem->iCursor);
sl@0:       if( (m & notReady)==0 ){
sl@0:         if( j==iFrom ) iFrom++;
sl@0:         continue;
sl@0:       }
sl@0:       assert( pTabItem->pTab );
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0:       if( IsVirtual(pTabItem->pTab) ){
sl@0:         sqlite3_index_info **ppIdxInfo = &pWInfo->a[j].pIdxInfo;
sl@0:         cost = bestVirtualIndex(pParse, &wc, pTabItem, notReady,
sl@0:                                 ppOrderBy ? *ppOrderBy : 0, i==0,
sl@0:                                 ppIdxInfo);
sl@0:         flags = WHERE_VIRTUALTABLE;
sl@0:         pIndex = *ppIdxInfo;
sl@0:         if( pIndex && pIndex->orderByConsumed ){
sl@0:           flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
sl@0:         }
sl@0:         pIdx = 0;
sl@0:         nEq = 0;
sl@0:         if( (SQLITE_BIG_DBL/2.0)<cost ){
sl@0:           /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
sl@0:           ** inital value of lowestCost in this loop. If it is, then
sl@0:           ** the (cost<lowestCost) test below will never be true and
sl@0:           ** pLevel->pBestIdx never set.
sl@0:           */ 
sl@0:           cost = (SQLITE_BIG_DBL/2.0);
sl@0:         }
sl@0:       }else 
sl@0: #endif
sl@0:       {
sl@0:         cost = bestIndex(pParse, &wc, pTabItem, notReady,
sl@0:                          (i==0 && ppOrderBy) ? *ppOrderBy : 0,
sl@0:                          &pIdx, &flags, &nEq);
sl@0:         pIndex = 0;
sl@0:       }
sl@0:       if( cost<lowestCost ){
sl@0:         once = 1;
sl@0:         lowestCost = cost;
sl@0:         pBest = pIdx;
sl@0:         bestFlags = flags;
sl@0:         bestNEq = nEq;
sl@0:         bestJ = j;
sl@0:         pLevel->pBestIdx = pIndex;
sl@0:       }
sl@0:       if( doNotReorder ) break;
sl@0:     }
sl@0:     WHERETRACE(("*** Optimizer selects table %d for loop %d\n", bestJ,
sl@0:            pLevel-pWInfo->a));
sl@0:     if( (bestFlags & WHERE_ORDERBY)!=0 ){
sl@0:       *ppOrderBy = 0;
sl@0:     }
sl@0:     andFlags &= bestFlags;
sl@0:     pLevel->flags = bestFlags;
sl@0:     pLevel->pIdx = pBest;
sl@0:     pLevel->nEq = bestNEq;
sl@0:     pLevel->aInLoop = 0;
sl@0:     pLevel->nIn = 0;
sl@0:     if( pBest ){
sl@0:       pLevel->iIdxCur = pParse->nTab++;
sl@0:     }else{
sl@0:       pLevel->iIdxCur = -1;
sl@0:     }
sl@0:     notReady &= ~getMask(&maskSet, pTabList->a[bestJ].iCursor);
sl@0:     pLevel->iFrom = bestJ;
sl@0: 
sl@0:     /* Check that if the table scanned by this loop iteration had an
sl@0:     ** INDEXED BY clause attached to it, that the named index is being
sl@0:     ** used for the scan. If not, then query compilation has failed.
sl@0:     ** Return an error.
sl@0:     */
sl@0:     pIdx = pTabList->a[bestJ].pIndex;
sl@0:     assert( !pIdx || !pBest || pIdx==pBest );
sl@0:     if( pIdx && pBest!=pIdx ){
sl@0:       sqlite3ErrorMsg(pParse, "cannot use index: %s", pIdx->zName);
sl@0:       goto whereBeginError;
sl@0:     }
sl@0:   }
sl@0:   WHERETRACE(("*** Optimizer Finished ***\n"));
sl@0: 
sl@0:   /* If the total query only selects a single row, then the ORDER BY
sl@0:   ** clause is irrelevant.
sl@0:   */
sl@0:   if( (andFlags & WHERE_UNIQUE)!=0 && ppOrderBy ){
sl@0:     *ppOrderBy = 0;
sl@0:   }
sl@0: 
sl@0:   /* If the caller is an UPDATE or DELETE statement that is requesting
sl@0:   ** to use a one-pass algorithm, determine if this is appropriate.
sl@0:   ** The one-pass algorithm only works if the WHERE clause constraints
sl@0:   ** the statement to update a single row.
sl@0:   */
sl@0:   assert( (wflags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
sl@0:   if( (wflags & WHERE_ONEPASS_DESIRED)!=0 && (andFlags & WHERE_UNIQUE)!=0 ){
sl@0:     pWInfo->okOnePass = 1;
sl@0:     pWInfo->a[0].flags &= ~WHERE_IDX_ONLY;
sl@0:   }
sl@0: 
sl@0:   /* Open all tables in the pTabList and any indices selected for
sl@0:   ** searching those tables.
sl@0:   */
sl@0:   sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
sl@0:   for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
sl@0:     Table *pTab;     /* Table to open */
sl@0:     Index *pIx;      /* Index used to access pTab (if any) */
sl@0:     int iDb;         /* Index of database containing table/index */
sl@0:     int iIdxCur = pLevel->iIdxCur;
sl@0: 
sl@0: #ifndef SQLITE_OMIT_EXPLAIN
sl@0:     if( pParse->explain==2 ){
sl@0:       char *zMsg;
sl@0:       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
sl@0:       zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
sl@0:       if( pItem->zAlias ){
sl@0:         zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
sl@0:       }
sl@0:       if( (pIx = pLevel->pIdx)!=0 ){
sl@0:         zMsg = sqlite3MAppendf(db, zMsg, "%s WITH INDEX %s", zMsg, pIx->zName);
sl@0:       }else if( pLevel->flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
sl@0:         zMsg = sqlite3MAppendf(db, zMsg, "%s USING PRIMARY KEY", zMsg);
sl@0:       }
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0:       else if( pLevel->pBestIdx ){
sl@0:         sqlite3_index_info *pBestIdx = pLevel->pBestIdx;
sl@0:         zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
sl@0:                     pBestIdx->idxNum, pBestIdx->idxStr);
sl@0:       }
sl@0: #endif
sl@0:       if( pLevel->flags & WHERE_ORDERBY ){
sl@0:         zMsg = sqlite3MAppendf(db, zMsg, "%s ORDER BY", zMsg);
sl@0:       }
sl@0:       sqlite3VdbeAddOp4(v, OP_Explain, i, pLevel->iFrom, 0, zMsg, P4_DYNAMIC);
sl@0:     }
sl@0: #endif /* SQLITE_OMIT_EXPLAIN */
sl@0:     pTabItem = &pTabList->a[pLevel->iFrom];
sl@0:     pTab = pTabItem->pTab;
sl@0:     iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
sl@0:     if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ) continue;
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0:     if( pLevel->pBestIdx ){
sl@0:       int iCur = pTabItem->iCursor;
sl@0:       sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0,
sl@0:                         (const char*)pTab->pVtab, P4_VTAB);
sl@0:     }else
sl@0: #endif
sl@0:     if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){
sl@0:       int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
sl@0:       sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
sl@0:       if( !pWInfo->okOnePass && pTab->nCol<(sizeof(Bitmask)*8) ){
sl@0:         Bitmask b = pTabItem->colUsed;
sl@0:         int n = 0;
sl@0:         for(; b; b=b>>1, n++){}
sl@0:         sqlite3VdbeChangeP2(v, sqlite3VdbeCurrentAddr(v)-2, n);
sl@0:         assert( n<=pTab->nCol );
sl@0:       }
sl@0:     }else{
sl@0:       sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
sl@0:     }
sl@0:     pLevel->iTabCur = pTabItem->iCursor;
sl@0:     if( (pIx = pLevel->pIdx)!=0 ){
sl@0:       KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
sl@0:       assert( pIx->pSchema==pTab->pSchema );
sl@0:       sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pIx->nColumn+1);
sl@0:       sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIx->tnum, iDb,
sl@0:                         (char*)pKey, P4_KEYINFO_HANDOFF);
sl@0:       VdbeComment((v, "%s", pIx->zName));
sl@0:     }
sl@0:     sqlite3CodeVerifySchema(pParse, iDb);
sl@0:   }
sl@0:   pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
sl@0: 
sl@0:   /* Generate the code to do the search.  Each iteration of the for
sl@0:   ** loop below generates code for a single nested loop of the VM
sl@0:   ** program.
sl@0:   */
sl@0:   notReady = ~(Bitmask)0;
sl@0:   for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
sl@0:     int j, k;
sl@0:     int iCur = pTabItem->iCursor;  /* The VDBE cursor for the table */
sl@0:     Index *pIdx;       /* The index we will be using */
sl@0:     int nxt;           /* Where to jump to continue with the next IN case */
sl@0:     int iIdxCur;       /* The VDBE cursor for the index */
sl@0:     int omitTable;     /* True if we use the index only */
sl@0:     int bRev;          /* True if we need to scan in reverse order */
sl@0: 
sl@0:     pTabItem = &pTabList->a[pLevel->iFrom];
sl@0:     iCur = pTabItem->iCursor;
sl@0:     pIdx = pLevel->pIdx;
sl@0:     iIdxCur = pLevel->iIdxCur;
sl@0:     bRev = (pLevel->flags & WHERE_REVERSE)!=0;
sl@0:     omitTable = (pLevel->flags & WHERE_IDX_ONLY)!=0;
sl@0: 
sl@0:     /* Create labels for the "break" and "continue" instructions
sl@0:     ** for the current loop.  Jump to brk to break out of a loop.
sl@0:     ** Jump to cont to go immediately to the next iteration of the
sl@0:     ** loop.
sl@0:     **
sl@0:     ** When there is an IN operator, we also have a "nxt" label that
sl@0:     ** means to continue with the next IN value combination.  When
sl@0:     ** there are no IN operators in the constraints, the "nxt" label
sl@0:     ** is the same as "brk".
sl@0:     */
sl@0:     brk = pLevel->brk = pLevel->nxt = sqlite3VdbeMakeLabel(v);
sl@0:     cont = pLevel->cont = sqlite3VdbeMakeLabel(v);
sl@0: 
sl@0:     /* If this is the right table of a LEFT OUTER JOIN, allocate and
sl@0:     ** initialize a memory cell that records if this table matches any
sl@0:     ** row of the left table of the join.
sl@0:     */
sl@0:     if( pLevel->iFrom>0 && (pTabItem[0].jointype & JT_LEFT)!=0 ){
sl@0:       pLevel->iLeftJoin = ++pParse->nMem;
sl@0:       sqlite3VdbeAddOp2(v, OP_Integer, 0, pLevel->iLeftJoin);
sl@0:       VdbeComment((v, "init LEFT JOIN no-match flag"));
sl@0:     }
sl@0: 
sl@0: #ifndef SQLITE_OMIT_VIRTUALTABLE
sl@0:     if( pLevel->pBestIdx ){
sl@0:       /* Case 0:  The table is a virtual-table.  Use the VFilter and VNext
sl@0:       **          to access the data.
sl@0:       */
sl@0:       int j;
sl@0:       int iReg;   /* P3 Value for OP_VFilter */
sl@0:       sqlite3_index_info *pBestIdx = pLevel->pBestIdx;
sl@0:       int nConstraint = pBestIdx->nConstraint;
sl@0:       struct sqlite3_index_constraint_usage *aUsage =
sl@0:                                                   pBestIdx->aConstraintUsage;
sl@0:       const struct sqlite3_index_constraint *aConstraint =
sl@0:                                                   pBestIdx->aConstraint;
sl@0: 
sl@0:       iReg = sqlite3GetTempRange(pParse, nConstraint+2);
sl@0:       pParse->disableColCache++;
sl@0:       for(j=1; j<=nConstraint; j++){
sl@0:         int k;
sl@0:         for(k=0; k<nConstraint; k++){
sl@0:           if( aUsage[k].argvIndex==j ){
sl@0:             int iTerm = aConstraint[k].iTermOffset;
sl@0:             assert( pParse->disableColCache );
sl@0:             sqlite3ExprCode(pParse, wc.a[iTerm].pExpr->pRight, iReg+j+1);
sl@0:             break;
sl@0:           }
sl@0:         }
sl@0:         if( k==nConstraint ) break;
sl@0:       }
sl@0:       assert( pParse->disableColCache );
sl@0:       pParse->disableColCache--;
sl@0:       sqlite3VdbeAddOp2(v, OP_Integer, pBestIdx->idxNum, iReg);
sl@0:       sqlite3VdbeAddOp2(v, OP_Integer, j-1, iReg+1);
sl@0:       sqlite3VdbeAddOp4(v, OP_VFilter, iCur, brk, iReg, pBestIdx->idxStr,
sl@0:                         pBestIdx->needToFreeIdxStr ? P4_MPRINTF : P4_STATIC);
sl@0:       sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
sl@0:       pBestIdx->needToFreeIdxStr = 0;
sl@0:       for(j=0; j<nConstraint; j++){
sl@0:         if( aUsage[j].omit ){
sl@0:           int iTerm = aConstraint[j].iTermOffset;
sl@0:           disableTerm(pLevel, &wc.a[iTerm]);
sl@0:         }
sl@0:       }
sl@0:       pLevel->op = OP_VNext;
sl@0:       pLevel->p1 = iCur;
sl@0:       pLevel->p2 = sqlite3VdbeCurrentAddr(v);
sl@0:     }else
sl@0: #endif /* SQLITE_OMIT_VIRTUALTABLE */
sl@0: 
sl@0:     if( pLevel->flags & WHERE_ROWID_EQ ){
sl@0:       /* Case 1:  We can directly reference a single row using an
sl@0:       **          equality comparison against the ROWID field.  Or
sl@0:       **          we reference multiple rows using a "rowid IN (...)"
sl@0:       **          construct.
sl@0:       */
sl@0:       int r1;
sl@0:       int rtmp = sqlite3GetTempReg(pParse);
sl@0:       pTerm = findTerm(&wc, iCur, -1, notReady, WO_EQ|WO_IN, 0);
sl@0:       assert( pTerm!=0 );
sl@0:       assert( pTerm->pExpr!=0 );
sl@0:       assert( pTerm->leftCursor==iCur );
sl@0:       assert( omitTable==0 );
sl@0:       r1 = codeEqualityTerm(pParse, pTerm, pLevel, rtmp);
sl@0:       nxt = pLevel->nxt;
sl@0:       sqlite3VdbeAddOp2(v, OP_MustBeInt, r1, nxt);
sl@0:       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, nxt, r1);
sl@0:       sqlite3ReleaseTempReg(pParse, rtmp);
sl@0:       VdbeComment((v, "pk"));
sl@0:       pLevel->op = OP_Noop;
sl@0:     }else if( pLevel->flags & WHERE_ROWID_RANGE ){
sl@0:       /* Case 2:  We have an inequality comparison against the ROWID field.
sl@0:       */
sl@0:       int testOp = OP_Noop;
sl@0:       int start;
sl@0:       WhereTerm *pStart, *pEnd;
sl@0: 
sl@0:       assert( omitTable==0 );
sl@0:       pStart = findTerm(&wc, iCur, -1, notReady, WO_GT|WO_GE, 0);
sl@0:       pEnd = findTerm(&wc, iCur, -1, notReady, WO_LT|WO_LE, 0);
sl@0:       if( bRev ){
sl@0:         pTerm = pStart;
sl@0:         pStart = pEnd;
sl@0:         pEnd = pTerm;
sl@0:       }
sl@0:       if( pStart ){
sl@0:         Expr *pX;
sl@0:         int r1;
sl@0:         pX = pStart->pExpr;
sl@0:         assert( pX!=0 );
sl@0:         assert( pStart->leftCursor==iCur );
sl@0: 
sl@0:         /* The ForceInt instruction may modify the register that it operates
sl@0:         ** on. For example it may replace a real value with an integer one,
sl@0:         ** or if p3 is true it may increment the register value. For this
sl@0:         ** reason we need to make sure that register r1 is really a newly
sl@0:         ** allocated temporary register, and not part of the column-cache.
sl@0:         ** For this reason we cannot use sqlite3ExprCodeTemp() here.
sl@0:         */
sl@0:         r1 = sqlite3GetTempReg(pParse);
sl@0:         sqlite3ExprCode(pParse, pX->pRight, r1);
sl@0: 
sl@0:         sqlite3VdbeAddOp3(v, OP_ForceInt, r1, brk, 
sl@0:                              pX->op==TK_LE || pX->op==TK_GT);
sl@0:         sqlite3VdbeAddOp3(v, bRev ? OP_MoveLt : OP_MoveGe, iCur, brk, r1);
sl@0:         VdbeComment((v, "pk"));
sl@0:         sqlite3ReleaseTempReg(pParse, r1);
sl@0:         disableTerm(pLevel, pStart);
sl@0:       }else{
sl@0:         sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, brk);
sl@0:       }
sl@0:       if( pEnd ){
sl@0:         Expr *pX;
sl@0:         pX = pEnd->pExpr;
sl@0:         assert( pX!=0 );
sl@0:         assert( pEnd->leftCursor==iCur );
sl@0:         pLevel->iMem = ++pParse->nMem;
sl@0:         sqlite3ExprCode(pParse, pX->pRight, pLevel->iMem);
sl@0:         if( pX->op==TK_LT || pX->op==TK_GT ){
sl@0:           testOp = bRev ? OP_Le : OP_Ge;
sl@0:         }else{
sl@0:           testOp = bRev ? OP_Lt : OP_Gt;
sl@0:         }
sl@0:         disableTerm(pLevel, pEnd);
sl@0:       }
sl@0:       start = sqlite3VdbeCurrentAddr(v);
sl@0:       pLevel->op = bRev ? OP_Prev : OP_Next;
sl@0:       pLevel->p1 = iCur;
sl@0:       pLevel->p2 = start;
sl@0:       if( testOp!=OP_Noop ){
sl@0:         int r1 = sqlite3GetTempReg(pParse);
sl@0:         sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1);
sl@0:         /* sqlite3VdbeAddOp2(v, OP_SCopy, pLevel->iMem, 0); */
sl@0:         sqlite3VdbeAddOp3(v, testOp, pLevel->iMem, brk, r1);
sl@0:         sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);
sl@0:         sqlite3ReleaseTempReg(pParse, r1);
sl@0:       }
sl@0:     }else if( pLevel->flags & (WHERE_COLUMN_RANGE|WHERE_COLUMN_EQ) ){
sl@0:       /* Case 3: A scan using an index.
sl@0:       **
sl@0:       **         The WHERE clause may contain zero or more equality 
sl@0:       **         terms ("==" or "IN" operators) that refer to the N
sl@0:       **         left-most columns of the index. It may also contain
sl@0:       **         inequality constraints (>, <, >= or <=) on the indexed
sl@0:       **         column that immediately follows the N equalities. Only 
sl@0:       **         the right-most column can be an inequality - the rest must
sl@0:       **         use the "==" and "IN" operators. For example, if the 
sl@0:       **         index is on (x,y,z), then the following clauses are all 
sl@0:       **         optimized:
sl@0:       **
sl@0:       **            x=5
sl@0:       **            x=5 AND y=10
sl@0:       **            x=5 AND y<10
sl@0:       **            x=5 AND y>5 AND y<10
sl@0:       **            x=5 AND y=5 AND z<=10
sl@0:       **
sl@0:       **         The z<10 term of the following cannot be used, only
sl@0:       **         the x=5 term:
sl@0:       **
sl@0:       **            x=5 AND z<10
sl@0:       **
sl@0:       **         N may be zero if there are inequality constraints.
sl@0:       **         If there are no inequality constraints, then N is at
sl@0:       **         least one.
sl@0:       **
sl@0:       **         This case is also used when there are no WHERE clause
sl@0:       **         constraints but an index is selected anyway, in order
sl@0:       **         to force the output order to conform to an ORDER BY.
sl@0:       */  
sl@0:       int aStartOp[] = {
sl@0:         0,
sl@0:         0,
sl@0:         OP_Rewind,           /* 2: (!start_constraints && startEq &&  !bRev) */
sl@0:         OP_Last,             /* 3: (!start_constraints && startEq &&   bRev) */
sl@0:         OP_MoveGt,           /* 4: (start_constraints  && !startEq && !bRev) */
sl@0:         OP_MoveLt,           /* 5: (start_constraints  && !startEq &&  bRev) */
sl@0:         OP_MoveGe,           /* 6: (start_constraints  &&  startEq && !bRev) */
sl@0:         OP_MoveLe            /* 7: (start_constraints  &&  startEq &&  bRev) */
sl@0:       };
sl@0:       int aEndOp[] = {
sl@0:         OP_Noop,             /* 0: (!end_constraints) */
sl@0:         OP_IdxGE,            /* 1: (end_constraints && !bRev) */
sl@0:         OP_IdxLT             /* 2: (end_constraints && bRev) */
sl@0:       };
sl@0:       int nEq = pLevel->nEq;
sl@0:       int isMinQuery = 0;          /* If this is an optimized SELECT min(x).. */
sl@0:       int regBase;                 /* Base register holding constraint values */
sl@0:       int r1;                      /* Temp register */
sl@0:       WhereTerm *pRangeStart = 0;  /* Inequality constraint at range start */
sl@0:       WhereTerm *pRangeEnd = 0;    /* Inequality constraint at range end */
sl@0:       int startEq;                 /* True if range start uses ==, >= or <= */
sl@0:       int endEq;                   /* True if range end uses ==, >= or <= */
sl@0:       int start_constraints;       /* Start of range is constrained */
sl@0:       int k = pIdx->aiColumn[nEq]; /* Column for inequality constraints */
sl@0:       int nConstraint;             /* Number of constraint terms */
sl@0:       int op;
sl@0: 
sl@0:       /* Generate code to evaluate all constraint terms using == or IN
sl@0:       ** and store the values of those terms in an array of registers
sl@0:       ** starting at regBase.
sl@0:       */
sl@0:       regBase = codeAllEqualityTerms(pParse, pLevel, &wc, notReady, 2);
sl@0:       nxt = pLevel->nxt;
sl@0: 
sl@0:       /* If this loop satisfies a sort order (pOrderBy) request that 
sl@0:       ** was passed to this function to implement a "SELECT min(x) ..." 
sl@0:       ** query, then the caller will only allow the loop to run for
sl@0:       ** a single iteration. This means that the first row returned
sl@0:       ** should not have a NULL value stored in 'x'. If column 'x' is
sl@0:       ** the first one after the nEq equality constraints in the index,
sl@0:       ** this requires some special handling.
sl@0:       */
sl@0:       if( (wflags&WHERE_ORDERBY_MIN)!=0
sl@0:        && (pLevel->flags&WHERE_ORDERBY)
sl@0:        && (pIdx->nColumn>nEq)
sl@0:       ){
sl@0:         assert( pOrderBy->nExpr==1 );
sl@0:         assert( pOrderBy->a[0].pExpr->iColumn==pIdx->aiColumn[nEq] );
sl@0:         isMinQuery = 1;
sl@0:       }
sl@0: 
sl@0:       /* Find any inequality constraint terms for the start and end 
sl@0:       ** of the range. 
sl@0:       */
sl@0:       if( pLevel->flags & WHERE_TOP_LIMIT ){
sl@0:         pRangeEnd = findTerm(&wc, iCur, k, notReady, (WO_LT|WO_LE), pIdx);
sl@0:       }
sl@0:       if( pLevel->flags & WHERE_BTM_LIMIT ){
sl@0:         pRangeStart = findTerm(&wc, iCur, k, notReady, (WO_GT|WO_GE), pIdx);
sl@0:       }
sl@0: 
sl@0:       /* If we are doing a reverse order scan on an ascending index, or
sl@0:       ** a forward order scan on a descending index, interchange the 
sl@0:       ** start and end terms (pRangeStart and pRangeEnd).
sl@0:       */
sl@0:       if( bRev==(pIdx->aSortOrder[nEq]==SQLITE_SO_ASC) ){
sl@0:         SWAP(WhereTerm *, pRangeEnd, pRangeStart);
sl@0:       }
sl@0: 
sl@0:       testcase( pRangeStart && pRangeStart->eOperator & WO_LE );
sl@0:       testcase( pRangeStart && pRangeStart->eOperator & WO_GE );
sl@0:       testcase( pRangeEnd && pRangeEnd->eOperator & WO_LE );
sl@0:       testcase( pRangeEnd && pRangeEnd->eOperator & WO_GE );
sl@0:       startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE);
sl@0:       endEq =   !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE);
sl@0:       start_constraints = pRangeStart || nEq>0;
sl@0: 
sl@0:       /* Seek the index cursor to the start of the range. */
sl@0:       nConstraint = nEq;
sl@0:       if( pRangeStart ){
sl@0:         int dcc = pParse->disableColCache;
sl@0:         if( pRangeEnd ){
sl@0:           pParse->disableColCache++;
sl@0:         }
sl@0:         sqlite3ExprCode(pParse, pRangeStart->pExpr->pRight, regBase+nEq);
sl@0:         pParse->disableColCache = dcc;
sl@0:         sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, nxt);
sl@0:         nConstraint++;
sl@0:       }else if( isMinQuery ){
sl@0:         sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq);
sl@0:         nConstraint++;
sl@0:         startEq = 0;
sl@0:         start_constraints = 1;
sl@0:       }
sl@0:       codeApplyAffinity(pParse, regBase, nConstraint, pIdx);
sl@0:       op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
sl@0:       assert( op!=0 );
sl@0:       testcase( op==OP_Rewind );
sl@0:       testcase( op==OP_Last );
sl@0:       testcase( op==OP_MoveGt );
sl@0:       testcase( op==OP_MoveGe );
sl@0:       testcase( op==OP_MoveLe );
sl@0:       testcase( op==OP_MoveLt );
sl@0:       sqlite3VdbeAddOp4(v, op, iIdxCur, nxt, regBase, 
sl@0:                         SQLITE_INT_TO_PTR(nConstraint), P4_INT32);
sl@0: 
sl@0:       /* Load the value for the inequality constraint at the end of the
sl@0:       ** range (if any).
sl@0:       */
sl@0:       nConstraint = nEq;
sl@0:       if( pRangeEnd ){
sl@0:         sqlite3ExprCode(pParse, pRangeEnd->pExpr->pRight, regBase+nEq);
sl@0:         sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, nxt);
sl@0:         codeApplyAffinity(pParse, regBase, nEq+1, pIdx);
sl@0:         nConstraint++;
sl@0:       }
sl@0: 
sl@0:       /* Top of the loop body */
sl@0:       pLevel->p2 = sqlite3VdbeCurrentAddr(v);
sl@0: 
sl@0:       /* Check if the index cursor is past the end of the range. */
sl@0:       op = aEndOp[(pRangeEnd || nEq) * (1 + bRev)];
sl@0:       testcase( op==OP_Noop );
sl@0:       testcase( op==OP_IdxGE );
sl@0:       testcase( op==OP_IdxLT );
sl@0:       sqlite3VdbeAddOp4(v, op, iIdxCur, nxt, regBase,
sl@0:                         SQLITE_INT_TO_PTR(nConstraint), P4_INT32);
sl@0:       sqlite3VdbeChangeP5(v, endEq!=bRev);
sl@0: 
sl@0:       /* If there are inequality constraints, check that the value
sl@0:       ** of the table column that the inequality contrains is not NULL.
sl@0:       ** If it is, jump to the next iteration of the loop.
sl@0:       */
sl@0:       r1 = sqlite3GetTempReg(pParse);
sl@0:       testcase( pLevel->flags & WHERE_BTM_LIMIT );
sl@0:       testcase( pLevel->flags & WHERE_TOP_LIMIT );
sl@0:       if( pLevel->flags & (WHERE_BTM_LIMIT|WHERE_TOP_LIMIT) ){
sl@0:         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, nEq, r1);
sl@0:         sqlite3VdbeAddOp2(v, OP_IsNull, r1, cont);
sl@0:       }
sl@0: 
sl@0:       /* Seek the table cursor, if required */
sl@0:       if( !omitTable ){
sl@0:         sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, r1);
sl@0:         sqlite3VdbeAddOp3(v, OP_MoveGe, iCur, 0, r1);  /* Deferred seek */
sl@0:       }
sl@0:       sqlite3ReleaseTempReg(pParse, r1);
sl@0: 
sl@0:       /* Record the instruction used to terminate the loop. Disable 
sl@0:       ** WHERE clause terms made redundant by the index range scan.
sl@0:       */
sl@0:       pLevel->op = bRev ? OP_Prev : OP_Next;
sl@0:       pLevel->p1 = iIdxCur;
sl@0:       disableTerm(pLevel, pRangeStart);
sl@0:       disableTerm(pLevel, pRangeEnd);
sl@0:     }else{
sl@0:       /* Case 4:  There is no usable index.  We must do a complete
sl@0:       **          scan of the entire table.
sl@0:       */
sl@0:       assert( omitTable==0 );
sl@0:       assert( bRev==0 );
sl@0:       pLevel->op = OP_Next;
sl@0:       pLevel->p1 = iCur;
sl@0:       pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, OP_Rewind, iCur, brk);
sl@0:       pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
sl@0:     }
sl@0:     notReady &= ~getMask(&maskSet, iCur);
sl@0: 
sl@0:     /* Insert code to test every subexpression that can be completely
sl@0:     ** computed using the current set of tables.
sl@0:     */
sl@0:     k = 0;
sl@0:     for(pTerm=wc.a, j=wc.nTerm; j>0; j--, pTerm++){
sl@0:       Expr *pE;
sl@0:       testcase( pTerm->flags & TERM_VIRTUAL );
sl@0:       testcase( pTerm->flags & TERM_CODED );
sl@0:       if( pTerm->flags & (TERM_VIRTUAL|TERM_CODED) ) continue;
sl@0:       if( (pTerm->prereqAll & notReady)!=0 ) continue;
sl@0:       pE = pTerm->pExpr;
sl@0:       assert( pE!=0 );
sl@0:       if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
sl@0:         continue;
sl@0:       }
sl@0:       pParse->disableColCache += k;
sl@0:       sqlite3ExprIfFalse(pParse, pE, cont, SQLITE_JUMPIFNULL);
sl@0:       pParse->disableColCache -= k;
sl@0:       k = 1;
sl@0:       pTerm->flags |= TERM_CODED;
sl@0:     }
sl@0: 
sl@0:     /* For a LEFT OUTER JOIN, generate code that will record the fact that
sl@0:     ** at least one row of the right table has matched the left table.  
sl@0:     */
sl@0:     if( pLevel->iLeftJoin ){
sl@0:       pLevel->top = sqlite3VdbeCurrentAddr(v);
sl@0:       sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin);
sl@0:       VdbeComment((v, "record LEFT JOIN hit"));
sl@0:       sqlite3ExprClearColumnCache(pParse, pLevel->iTabCur);
sl@0:       sqlite3ExprClearColumnCache(pParse, pLevel->iIdxCur);
sl@0:       for(pTerm=wc.a, j=0; j<wc.nTerm; j++, pTerm++){
sl@0:         testcase( pTerm->flags & TERM_VIRTUAL );
sl@0:         testcase( pTerm->flags & TERM_CODED );
sl@0:         if( pTerm->flags & (TERM_VIRTUAL|TERM_CODED) ) continue;
sl@0:         if( (pTerm->prereqAll & notReady)!=0 ) continue;
sl@0:         assert( pTerm->pExpr );
sl@0:         sqlite3ExprIfFalse(pParse, pTerm->pExpr, cont, SQLITE_JUMPIFNULL);
sl@0:         pTerm->flags |= TERM_CODED;
sl@0:       }
sl@0:     }
sl@0:   }
sl@0: 
sl@0: #ifdef SQLITE_TEST  /* For testing and debugging use only */
sl@0:   /* Record in the query plan information about the current table
sl@0:   ** and the index used to access it (if any).  If the table itself
sl@0:   ** is not used, its name is just '{}'.  If no index is used
sl@0:   ** the index is listed as "{}".  If the primary key is used the
sl@0:   ** index name is '*'.
sl@0:   */
sl@0:   for(i=0; i<pTabList->nSrc; i++){
sl@0:     char *z;
sl@0:     int n;
sl@0:     pLevel = &pWInfo->a[i];
sl@0:     pTabItem = &pTabList->a[pLevel->iFrom];
sl@0:     z = pTabItem->zAlias;
sl@0:     if( z==0 ) z = pTabItem->pTab->zName;
sl@0:     n = strlen(z);
sl@0:     if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
sl@0:       if( pLevel->flags & WHERE_IDX_ONLY ){
sl@0:         memcpy(&sqlite3_query_plan[nQPlan], "{}", 2);
sl@0:         nQPlan += 2;
sl@0:       }else{
sl@0:         memcpy(&sqlite3_query_plan[nQPlan], z, n);
sl@0:         nQPlan += n;
sl@0:       }
sl@0:       sqlite3_query_plan[nQPlan++] = ' ';
sl@0:     }
sl@0:     testcase( pLevel->flags & WHERE_ROWID_EQ );
sl@0:     testcase( pLevel->flags & WHERE_ROWID_RANGE );
sl@0:     if( pLevel->flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
sl@0:       memcpy(&sqlite3_query_plan[nQPlan], "* ", 2);
sl@0:       nQPlan += 2;
sl@0:     }else if( pLevel->pIdx==0 ){
sl@0:       memcpy(&sqlite3_query_plan[nQPlan], "{} ", 3);
sl@0:       nQPlan += 3;
sl@0:     }else{
sl@0:       n = strlen(pLevel->pIdx->zName);
sl@0:       if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
sl@0:         memcpy(&sqlite3_query_plan[nQPlan], pLevel->pIdx->zName, n);
sl@0:         nQPlan += n;
sl@0:         sqlite3_query_plan[nQPlan++] = ' ';
sl@0:       }
sl@0:     }
sl@0:   }
sl@0:   while( nQPlan>0 && sqlite3_query_plan[nQPlan-1]==' ' ){
sl@0:     sqlite3_query_plan[--nQPlan] = 0;
sl@0:   }
sl@0:   sqlite3_query_plan[nQPlan] = 0;
sl@0:   nQPlan = 0;
sl@0: #endif /* SQLITE_TEST // Testing and debugging use only */
sl@0: 
sl@0:   /* Record the continuation address in the WhereInfo structure.  Then
sl@0:   ** clean up and return.
sl@0:   */
sl@0:   pWInfo->iContinue = cont;
sl@0:   whereClauseClear(&wc);
sl@0:   return pWInfo;
sl@0: 
sl@0:   /* Jump here if malloc fails */
sl@0: whereBeginError:
sl@0:   whereClauseClear(&wc);
sl@0:   whereInfoFree(db, pWInfo);
sl@0:   return 0;
sl@0: }
sl@0: 
sl@0: /*
sl@0: ** Generate the end of the WHERE loop.  See comments on 
sl@0: ** sqlite3WhereBegin() for additional information.
sl@0: */
sl@0: void sqlite3WhereEnd(WhereInfo *pWInfo){
sl@0:   Parse *pParse = pWInfo->pParse;
sl@0:   Vdbe *v = pParse->pVdbe;
sl@0:   int i;
sl@0:   WhereLevel *pLevel;
sl@0:   SrcList *pTabList = pWInfo->pTabList;
sl@0:   sqlite3 *db = pParse->db;
sl@0: 
sl@0:   /* Generate loop termination code.
sl@0:   */
sl@0:   sqlite3ExprClearColumnCache(pParse, -1);
sl@0:   for(i=pTabList->nSrc-1; i>=0; i--){
sl@0:     pLevel = &pWInfo->a[i];
sl@0:     sqlite3VdbeResolveLabel(v, pLevel->cont);
sl@0:     if( pLevel->op!=OP_Noop ){
sl@0:       sqlite3VdbeAddOp2(v, pLevel->op, pLevel->p1, pLevel->p2);
sl@0:       sqlite3VdbeChangeP5(v, pLevel->p5);
sl@0:     }
sl@0:     if( pLevel->nIn ){
sl@0:       struct InLoop *pIn;
sl@0:       int j;
sl@0:       sqlite3VdbeResolveLabel(v, pLevel->nxt);
sl@0:       for(j=pLevel->nIn, pIn=&pLevel->aInLoop[j-1]; j>0; j--, pIn--){
sl@0:         sqlite3VdbeJumpHere(v, pIn->topAddr+1);
sl@0:         sqlite3VdbeAddOp2(v, OP_Next, pIn->iCur, pIn->topAddr);
sl@0:         sqlite3VdbeJumpHere(v, pIn->topAddr-1);
sl@0:       }
sl@0:       sqlite3DbFree(db, pLevel->aInLoop);
sl@0:     }
sl@0:     sqlite3VdbeResolveLabel(v, pLevel->brk);
sl@0:     if( pLevel->iLeftJoin ){
sl@0:       int addr;
sl@0:       addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin);
sl@0:       sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
sl@0:       if( pLevel->iIdxCur>=0 ){
sl@0:         sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
sl@0:       }
sl@0:       sqlite3VdbeAddOp2(v, OP_Goto, 0, pLevel->top);
sl@0:       sqlite3VdbeJumpHere(v, addr);
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   /* The "break" point is here, just past the end of the outer loop.
sl@0:   ** Set it.
sl@0:   */
sl@0:   sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
sl@0: 
sl@0:   /* Close all of the cursors that were opened by sqlite3WhereBegin.
sl@0:   */
sl@0:   for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
sl@0:     struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
sl@0:     Table *pTab = pTabItem->pTab;
sl@0:     assert( pTab!=0 );
sl@0:     if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ) continue;
sl@0:     if( !pWInfo->okOnePass && (pLevel->flags & WHERE_IDX_ONLY)==0 ){
sl@0:       sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
sl@0:     }
sl@0:     if( pLevel->pIdx!=0 ){
sl@0:       sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
sl@0:     }
sl@0: 
sl@0:     /* If this scan uses an index, make code substitutions to read data
sl@0:     ** from the index in preference to the table. Sometimes, this means
sl@0:     ** the table need never be read from. This is a performance boost,
sl@0:     ** as the vdbe level waits until the table is read before actually
sl@0:     ** seeking the table cursor to the record corresponding to the current
sl@0:     ** position in the index.
sl@0:     ** 
sl@0:     ** Calls to the code generator in between sqlite3WhereBegin and
sl@0:     ** sqlite3WhereEnd will have created code that references the table
sl@0:     ** directly.  This loop scans all that code looking for opcodes
sl@0:     ** that reference the table and converts them into opcodes that
sl@0:     ** reference the index.
sl@0:     */
sl@0:     if( pLevel->pIdx ){
sl@0:       int k, j, last;
sl@0:       VdbeOp *pOp;
sl@0:       Index *pIdx = pLevel->pIdx;
sl@0:       int useIndexOnly = pLevel->flags & WHERE_IDX_ONLY;
sl@0: 
sl@0:       assert( pIdx!=0 );
sl@0:       pOp = sqlite3VdbeGetOp(v, pWInfo->iTop);
sl@0:       last = sqlite3VdbeCurrentAddr(v);
sl@0:       for(k=pWInfo->iTop; k<last; k++, pOp++){
sl@0:         if( pOp->p1!=pLevel->iTabCur ) continue;
sl@0:         if( pOp->opcode==OP_Column ){
sl@0:           for(j=0; j<pIdx->nColumn; j++){
sl@0:             if( pOp->p2==pIdx->aiColumn[j] ){
sl@0:               pOp->p2 = j;
sl@0:               pOp->p1 = pLevel->iIdxCur;
sl@0:               break;
sl@0:             }
sl@0:           }
sl@0:           assert(!useIndexOnly || j<pIdx->nColumn);
sl@0:         }else if( pOp->opcode==OP_Rowid ){
sl@0:           pOp->p1 = pLevel->iIdxCur;
sl@0:           pOp->opcode = OP_IdxRowid;
sl@0:         }else if( pOp->opcode==OP_NullRow && useIndexOnly ){
sl@0:           pOp->opcode = OP_Noop;
sl@0:         }
sl@0:       }
sl@0:     }
sl@0:   }
sl@0: 
sl@0:   /* Final cleanup
sl@0:   */
sl@0:   whereInfoFree(db, pWInfo);
sl@0:   return;
sl@0: }