blob: cf30d94d671bb81ef361cb6ac5b7a1aae392f395 [file] [log] [blame]
/*
** 2001 September 15
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements. This module is responsible for
** generating the code that loops through a table looking for applicable
** rows. Indices are selected and used to speed the search when doing
** so is applicable. Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
*/
#include "sqliteInt.h"
/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
int sqlite3WhereTrace = 0;
#endif
#if defined(SQLITE_TEST) && defined(SQLITE_DEBUG)
# define WHERETRACE(X) if(sqlite3WhereTrace) sqlite3DebugPrintf X
#else
# define WHERETRACE(X)
#endif
/* Forward reference
*/
typedef struct WhereClause WhereClause;
typedef struct WhereMaskSet WhereMaskSet;
typedef struct WhereOrInfo WhereOrInfo;
typedef struct WhereAndInfo WhereAndInfo;
typedef struct WhereCost WhereCost;
/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause. Each WHERE
** clause subexpression is separated from the others by AND operators,
** usually, or sometimes subexpressions separated by OR.
**
** All WhereTerms are collected into a single WhereClause structure.
** The following identity holds:
**
** WhereTerm.pWC->a[WhereTerm.idx] == WhereTerm
**
** When a term is of the form:
**
** X <op> <expr>
**
** where X is a column name and <op> is one of certain operators,
** then WhereTerm.leftCursor and WhereTerm.u.leftColumn record the
** cursor number and column number for X. WhereTerm.eOperator records
** the <op> using a bitmask encoding defined by WO_xxx below. The
** use of a bitmask encoding for the operator allows us to search
** quickly for terms that match any of several different operators.
**
** A WhereTerm might also be two or more subterms connected by OR:
**
** (t1.X <op> <expr>) OR (t1.Y <op> <expr>) OR ....
**
** In this second case, wtFlag as the TERM_ORINFO set and eOperator==WO_OR
** and the WhereTerm.u.pOrInfo field points to auxiliary information that
** is collected about the
**
** If a term in the WHERE clause does not match either of the two previous
** categories, then eOperator==0. The WhereTerm.pExpr field is still set
** to the original subexpression content and wtFlags is set up appropriately
** but no other fields in the WhereTerm object are meaningful.
**
** When eOperator!=0, prereqRight and prereqAll record sets of cursor numbers,
** but they do so indirectly. A single WhereMaskSet structure translates
** cursor number into bits and the translated bit is stored in the prereq
** fields. The translation is used in order to maximize the number of
** bits that will fit in a Bitmask. The VDBE cursor numbers might be
** spread out over the non-negative integers. For example, the cursor
** numbers might be 3, 8, 9, 10, 20, 23, 41, and 45. The WhereMaskSet
** translates these sparse cursor numbers into consecutive integers
** beginning with 0 in order to make the best possible use of the available
** bits in the Bitmask. So, in the example above, the cursor numbers
** would be mapped into integers 0 through 7.
**
** The number of terms in a join is limited by the number of bits
** in prereqRight and prereqAll. The default is 64 bits, hence SQLite
** is only able to process joins with 64 or fewer tables.
*/
typedef struct WhereTerm WhereTerm;
struct WhereTerm {
Expr *pExpr; /* Pointer to the subexpression that is this term */
int iParent; /* Disable pWC->a[iParent] when this term disabled */
int leftCursor; /* Cursor number of X in "X <op> <expr>" */
union {
int leftColumn; /* Column number of X in "X <op> <expr>" */
WhereOrInfo *pOrInfo; /* Extra information if eOperator==WO_OR */
WhereAndInfo *pAndInfo; /* Extra information if eOperator==WO_AND */
} u;
u16 eOperator; /* A WO_xx value describing <op> */
u8 wtFlags; /* TERM_xxx bit flags. See below */
u8 nChild; /* Number of children that must disable us */
WhereClause *pWC; /* The clause this term is part of */
Bitmask prereqRight; /* Bitmask of tables used by pExpr->pRight */
Bitmask prereqAll; /* Bitmask of tables referenced by pExpr */
};
/*
** Allowed values of WhereTerm.wtFlags
*/
#define TERM_DYNAMIC 0x01 /* Need to call sqlite3ExprDelete(db, pExpr) */
#define TERM_VIRTUAL 0x02 /* Added by the optimizer. Do not code */
#define TERM_CODED 0x04 /* This term is already coded */
#define TERM_COPIED 0x08 /* Has a child */
#define TERM_ORINFO 0x10 /* Need to free the WhereTerm.u.pOrInfo object */
#define TERM_ANDINFO 0x20 /* Need to free the WhereTerm.u.pAndInfo obj */
#define TERM_OR_OK 0x40 /* Used during OR-clause processing */
#ifdef SQLITE_ENABLE_STAT2
# define TERM_VNULL 0x80 /* Manufactured x>NULL or x<=NULL term */
#else
# define TERM_VNULL 0x00 /* Disabled if not using stat2 */
#endif
/*
** An instance of the following structure holds all information about a
** WHERE clause. Mostly this is a container for one or more WhereTerms.
*/
struct WhereClause {
Parse *pParse; /* The parser context */
WhereMaskSet *pMaskSet; /* Mapping of table cursor numbers to bitmasks */
Bitmask vmask; /* Bitmask identifying virtual table cursors */
u8 op; /* Split operator. TK_AND or TK_OR */
int nTerm; /* Number of terms */
int nSlot; /* Number of entries in a[] */
WhereTerm *a; /* Each a[] describes a term of the WHERE cluase */
#if defined(SQLITE_SMALL_STACK)
WhereTerm aStatic[1]; /* Initial static space for a[] */
#else
WhereTerm aStatic[8]; /* Initial static space for a[] */
#endif
};
/*
** A WhereTerm with eOperator==WO_OR has its u.pOrInfo pointer set to
** a dynamically allocated instance of the following structure.
*/
struct WhereOrInfo {
WhereClause wc; /* Decomposition into subterms */
Bitmask indexable; /* Bitmask of all indexable tables in the clause */
};
/*
** A WhereTerm with eOperator==WO_AND has its u.pAndInfo pointer set to
** a dynamically allocated instance of the following structure.
*/
struct WhereAndInfo {
WhereClause wc; /* The subexpression broken out */
};
/*
** An instance of the following structure keeps track of a mapping
** between VDBE cursor numbers and bits of the bitmasks in WhereTerm.
**
** The VDBE cursor numbers are small integers contained in
** SrcList_item.iCursor and Expr.iTable fields. For any given WHERE
** clause, the cursor numbers might not begin with 0 and they might
** contain gaps in the numbering sequence. But we want to make maximum
** use of the bits in our bitmasks. This structure provides a mapping
** from the sparse cursor numbers into consecutive integers beginning
** with 0.
**
** If WhereMaskSet.ix[A]==B it means that The A-th bit of a Bitmask
** corresponds VDBE cursor number B. The A-th bit of a bitmask is 1<<A.
**
** For example, if the WHERE clause expression used these VDBE
** cursors: 4, 5, 8, 29, 57, 73. Then the WhereMaskSet structure
** would map those cursor numbers into bits 0 through 5.
**
** Note that the mapping is not necessarily ordered. In the example
** above, the mapping might go like this: 4->3, 5->1, 8->2, 29->0,
** 57->5, 73->4. Or one of 719 other combinations might be used. It
** does not really matter. What is important is that sparse cursor
** numbers all get mapped into bit numbers that begin with 0 and contain
** no gaps.
*/
struct WhereMaskSet {
int n; /* Number of assigned cursor values */
int ix[BMS]; /* Cursor assigned to each bit */
};
/*
** A WhereCost object records a lookup strategy and the estimated
** cost of pursuing that strategy.
*/
struct WhereCost {
WherePlan plan; /* The lookup strategy */
double rCost; /* Overall cost of pursuing this search strategy */
Bitmask used; /* Bitmask of cursors used by this plan */
};
/*
** Bitmasks for the operators that indices are able to exploit. An
** OR-ed combination of these values can be used when searching for
** terms in the where clause.
*/
#define WO_IN 0x001
#define WO_EQ 0x002
#define WO_LT (WO_EQ<<(TK_LT-TK_EQ))
#define WO_LE (WO_EQ<<(TK_LE-TK_EQ))
#define WO_GT (WO_EQ<<(TK_GT-TK_EQ))
#define WO_GE (WO_EQ<<(TK_GE-TK_EQ))
#define WO_MATCH 0x040
#define WO_ISNULL 0x080
#define WO_OR 0x100 /* Two or more OR-connected terms */
#define WO_AND 0x200 /* Two or more AND-connected terms */
#define WO_NOOP 0x800 /* This term does not restrict search space */
#define WO_ALL 0xfff /* Mask of all possible WO_* values */
#define WO_SINGLE 0x0ff /* Mask of all non-compound WO_* values */
/*
** Value for wsFlags returned by bestIndex() and stored in
** WhereLevel.wsFlags. These flags determine which search
** strategies are appropriate.
**
** The least significant 12 bits is reserved as a mask for WO_ values above.
** The WhereLevel.wsFlags field is usually set to WO_IN|WO_EQ|WO_ISNULL.
** But if the table is the right table of a left join, WhereLevel.wsFlags
** is set to WO_IN|WO_EQ. The WhereLevel.wsFlags field can then be used as
** the "op" parameter to findTerm when we are resolving equality constraints.
** ISNULL constraints will then not be used on the right table of a left
** join. Tickets #2177 and #2189.
*/
#define WHERE_ROWID_EQ 0x00001000 /* rowid=EXPR or rowid IN (...) */
#define WHERE_ROWID_RANGE 0x00002000 /* rowid<EXPR and/or rowid>EXPR */
#define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */
#define WHERE_COLUMN_RANGE 0x00020000 /* x<EXPR and/or x>EXPR */
#define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */
#define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */
#define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */
#define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */
#define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */
#define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */
#define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */
#define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */
#define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */
#define WHERE_REVERSE 0x02000000 /* Scan in reverse order */
#define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */
#define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */
#define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */
#define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */
/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(
WhereClause *pWC, /* The WhereClause to be initialized */
Parse *pParse, /* The parsing context */
WhereMaskSet *pMaskSet /* Mapping from table cursor numbers to bitmasks */
){
pWC->pParse = pParse;
pWC->pMaskSet = pMaskSet;
pWC->nTerm = 0;
pWC->nSlot = ArraySize(pWC->aStatic);
pWC->a = pWC->aStatic;
pWC->vmask = 0;
}
/* Forward reference */
static void whereClauseClear(WhereClause*);
/*
** Deallocate all memory associated with a WhereOrInfo object.
*/
static void whereOrInfoDelete(sqlite3 *db, WhereOrInfo *p){
whereClauseClear(&p->wc);
sqlite3DbFree(db, p);
}
/*
** Deallocate all memory associated with a WhereAndInfo object.
*/
static void whereAndInfoDelete(sqlite3 *db, WhereAndInfo *p){
whereClauseClear(&p->wc);
sqlite3DbFree(db, p);
}
/*
** Deallocate a WhereClause structure. The WhereClause structure
** itself is not freed. This routine is the inverse of whereClauseInit().
*/
static void whereClauseClear(WhereClause *pWC){
int i;
WhereTerm *a;
sqlite3 *db = pWC->pParse->db;
for(i=pWC->nTerm-1, a=pWC->a; i>=0; i--, a++){
if( a->wtFlags & TERM_DYNAMIC ){
sqlite3ExprDelete(db, a->pExpr);
}
if( a->wtFlags & TERM_ORINFO ){
whereOrInfoDelete(db, a->u.pOrInfo);
}else if( a->wtFlags & TERM_ANDINFO ){
whereAndInfoDelete(db, a->u.pAndInfo);
}
}
if( pWC->a!=pWC->aStatic ){
sqlite3DbFree(db, pWC->a);
}
}
/*
** Add a single new WhereTerm entry to the WhereClause object pWC.
** The new WhereTerm object is constructed from Expr p and with wtFlags.
** The index in pWC->a[] of the new WhereTerm is returned on success.
** 0 is returned if the new WhereTerm could not be added due to a memory
** allocation error. The memory allocation failure will be recorded in
** the db->mallocFailed flag so that higher-level functions can detect it.
**
** This routine will increase the size of the pWC->a[] array as necessary.
**
** If the wtFlags argument includes TERM_DYNAMIC, then responsibility
** for freeing the expression p is assumed by the WhereClause object pWC.
** This is true even if this routine fails to allocate a new WhereTerm.
**
** WARNING: This routine might reallocate the space used to store
** WhereTerms. All pointers to WhereTerms should be invalidated after
** calling this routine. Such pointers may be reinitialized by referencing
** the pWC->a[] array.
*/
static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){
WhereTerm *pTerm;
int idx;
testcase( wtFlags & TERM_VIRTUAL ); /* EV: R-00211-15100 */
if( pWC->nTerm>=pWC->nSlot ){
WhereTerm *pOld = pWC->a;
sqlite3 *db = pWC->pParse->db;
pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 );
if( pWC->a==0 ){
if( wtFlags & TERM_DYNAMIC ){
sqlite3ExprDelete(db, p);
}
pWC->a = pOld;
return 0;
}
memcpy(pWC->a, pOld, sizeof(pWC->a[0])*pWC->nTerm);
if( pOld!=pWC->aStatic ){
sqlite3DbFree(db, pOld);
}
pWC->nSlot = sqlite3DbMallocSize(db, pWC->a)/sizeof(pWC->a[0]);
}
pTerm = &pWC->a[idx = pWC->nTerm++];
pTerm->pExpr = p;
pTerm->wtFlags = wtFlags;
pTerm->pWC = pWC;
pTerm->iParent = -1;
return idx;
}
/*
** This routine identifies subexpressions in the WHERE clause where
** each subexpression is separated by the AND operator or some other
** operator specified in the op parameter. The WhereClause structure
** is filled with pointers to subexpressions. For example:
**
** WHERE a=='hello' AND coalesce(b,11)<10 AND (c+12!=d OR c==22)
** \________/ \_______________/ \________________/
** slot[0] slot[1] slot[2]
**
** The original WHERE clause in pExpr is unaltered. All this routine
** does is make slot[] entries point to substructure within pExpr.
**
** In the previous sentence and in the diagram, "slot[]" refers to
** the WhereClause.a[] array. The slot[] array grows as needed to contain
** all terms of the WHERE clause.
*/
static void whereSplit(WhereClause *pWC, Expr *pExpr, int op){
pWC->op = (u8)op;
if( pExpr==0 ) return;
if( pExpr->op!=op ){
whereClauseInsert(pWC, pExpr, 0);
}else{
whereSplit(pWC, pExpr->pLeft, op);
whereSplit(pWC, pExpr->pRight, op);
}
}
/*
** Initialize an expression mask set (a WhereMaskSet object)
*/
#define initMaskSet(P) memset(P, 0, sizeof(*P))
/*
** Return the bitmask for the given cursor number. Return 0 if
** iCursor is not in the set.
*/
static Bitmask getMask(WhereMaskSet *pMaskSet, int iCursor){
int i;
assert( pMaskSet->n<=(int)sizeof(Bitmask)*8 );
for(i=0; i<pMaskSet->n; i++){
if( pMaskSet->ix[i]==iCursor ){
return ((Bitmask)1)<<i;
}
}
return 0;
}
/*
** Create a new mask for cursor iCursor.
**
** There is one cursor per table in the FROM clause. The number of
** tables in the FROM clause is limited by a test early in the
** sqlite3WhereBegin() routine. So we know that the pMaskSet->ix[]
** array will never overflow.
*/
static void createMask(WhereMaskSet *pMaskSet, int iCursor){
assert( pMaskSet->n < ArraySize(pMaskSet->ix) );
pMaskSet->ix[pMaskSet->n++] = iCursor;
}
/*
** This routine walks (recursively) an expression tree and generates
** a bitmask indicating which tables are used in that expression
** tree.
**
** In order for this routine to work, the calling function must have
** previously invoked sqlite3ResolveExprNames() on the expression. See
** the header comment on that routine for additional information.
** The sqlite3ResolveExprNames() routines looks for column names and
** sets their opcodes to TK_COLUMN and their Expr.iTable fields to
** the VDBE cursor number of the table. This routine just has to
** translate the cursor numbers into bitmask values and OR all
** the bitmasks together.
*/
static Bitmask exprListTableUsage(WhereMaskSet*, ExprList*);
static Bitmask exprSelectTableUsage(WhereMaskSet*, Select*);
static Bitmask exprTableUsage(WhereMaskSet *pMaskSet, Expr *p){
Bitmask mask = 0;
if( p==0 ) return 0;
if( p->op==TK_COLUMN ){
mask = getMask(pMaskSet, p->iTable);
return mask;
}
mask = exprTableUsage(pMaskSet, p->pRight);
mask |= exprTableUsage(pMaskSet, p->pLeft);
if( ExprHasProperty(p, EP_xIsSelect) ){
mask |= exprSelectTableUsage(pMaskSet, p->x.pSelect);
}else{
mask |= exprListTableUsage(pMaskSet, p->x.pList);
}
return mask;
}
static Bitmask exprListTableUsage(WhereMaskSet *pMaskSet, ExprList *pList){
int i;
Bitmask mask = 0;
if( pList ){
for(i=0; i<pList->nExpr; i++){
mask |= exprTableUsage(pMaskSet, pList->a[i].pExpr);
}
}
return mask;
}
static Bitmask exprSelectTableUsage(WhereMaskSet *pMaskSet, Select *pS){
Bitmask mask = 0;
while( pS ){
mask |= exprListTableUsage(pMaskSet, pS->pEList);
mask |= exprListTableUsage(pMaskSet, pS->pGroupBy);
mask |= exprListTableUsage(pMaskSet, pS->pOrderBy);
mask |= exprTableUsage(pMaskSet, pS->pWhere);
mask |= exprTableUsage(pMaskSet, pS->pHaving);
pS = pS->pPrior;
}
return mask;
}
/*
** Return TRUE if the given operator is one of the operators that is
** allowed for an indexable WHERE clause term. The allowed operators are
** "=", "<", ">", "<=", ">=", and "IN".
**
** IMPLEMENTATION-OF: R-59926-26393 To be usable by an index a term must be
** of one of the following forms: column = expression column > expression
** column >= expression column < expression column <= expression
** expression = column expression > column expression >= column
** expression < column expression <= column column IN
** (expression-list) column IN (subquery) column IS NULL
*/
static int allowedOp(int op){
assert( TK_GT>TK_EQ && TK_GT<TK_GE );
assert( TK_LT>TK_EQ && TK_LT<TK_GE );
assert( TK_LE>TK_EQ && TK_LE<TK_GE );
assert( TK_GE==TK_EQ+4 );
return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL;
}
/*
** Swap two objects of type TYPE.
*/
#define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;}
/*
** Commute a comparison operator. Expressions of the form "X op Y"
** are converted into "Y op X".
**
** If a collation sequence is associated with either the left or right
** side of the comparison, it remains associated with the same side after
** the commutation. So "Y collate NOCASE op X" becomes
** "X collate NOCASE op Y". This is because any collation sequence on
** the left hand side of a comparison overrides any collation sequence
** attached to the right. For the same reason the EP_ExpCollate flag
** is not commuted.
*/
static void exprCommute(Parse *pParse, Expr *pExpr){
u16 expRight = (pExpr->pRight->flags & EP_ExpCollate);
u16 expLeft = (pExpr->pLeft->flags & EP_ExpCollate);
assert( allowedOp(pExpr->op) && pExpr->op!=TK_IN );
pExpr->pRight->pColl = sqlite3ExprCollSeq(pParse, pExpr->pRight);
pExpr->pLeft->pColl = sqlite3ExprCollSeq(pParse, pExpr->pLeft);
SWAP(CollSeq*,pExpr->pRight->pColl,pExpr->pLeft->pColl);
pExpr->pRight->flags = (pExpr->pRight->flags & ~EP_ExpCollate) | expLeft;
pExpr->pLeft->flags = (pExpr->pLeft->flags & ~EP_ExpCollate) | expRight;
SWAP(Expr*,pExpr->pRight,pExpr->pLeft);
if( pExpr->op>=TK_GT ){
assert( TK_LT==TK_GT+2 );
assert( TK_GE==TK_LE+2 );
assert( TK_GT>TK_EQ );
assert( TK_GT<TK_LE );
assert( pExpr->op>=TK_GT && pExpr->op<=TK_GE );
pExpr->op = ((pExpr->op-TK_GT)^2)+TK_GT;
}
}
/*
** Translate from TK_xx operator to WO_xx bitmask.
*/
static u16 operatorMask(int op){
u16 c;
assert( allowedOp(op) );
if( op==TK_IN ){
c = WO_IN;
}else if( op==TK_ISNULL ){
c = WO_ISNULL;
}else{
assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
c = (u16)(WO_EQ<<(op-TK_EQ));
}
assert( op!=TK_ISNULL || c==WO_ISNULL );
assert( op!=TK_IN || c==WO_IN );
assert( op!=TK_EQ || c==WO_EQ );
assert( op!=TK_LT || c==WO_LT );
assert( op!=TK_LE || c==WO_LE );
assert( op!=TK_GT || c==WO_GT );
assert( op!=TK_GE || c==WO_GE );
return c;
}
/*
** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
** where X is a reference to the iColumn of table iCur and <op> is one of
** the WO_xx operator codes specified by the op parameter.
** Return a pointer to the term. Return 0 if not found.
*/
static WhereTerm *findTerm(
WhereClause *pWC, /* The WHERE clause to be searched */
int iCur, /* Cursor number of LHS */
int iColumn, /* Column number of LHS */
Bitmask notReady, /* RHS must not overlap with this mask */
u32 op, /* Mask of WO_xx values describing operator */
Index *pIdx /* Must be compatible with this index, if not NULL */
){
WhereTerm *pTerm;
int k;
assert( iCur>=0 );
op &= WO_ALL;
for(pTerm=pWC->a, k=pWC->nTerm; k; k--, pTerm++){
if( pTerm->leftCursor==iCur
&& (pTerm->prereqRight & notReady)==0
&& pTerm->u.leftColumn==iColumn
&& (pTerm->eOperator & op)!=0
){
if( pIdx && pTerm->eOperator!=WO_ISNULL ){
Expr *pX = pTerm->pExpr;
CollSeq *pColl;
char idxaff;
int j;
Parse *pParse = pWC->pParse;
idxaff = pIdx->pTable->aCol[iColumn].affinity;
if( !sqlite3IndexAffinityOk(pX, idxaff) ) continue;
/* Figure out the collation sequence required from an index for
** it to be useful for optimising expression pX. Store this
** value in variable pColl.
*/
assert(pX->pLeft);
pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
assert(pColl || pParse->nErr);
for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
if( NEVER(j>=pIdx->nColumn) ) return 0;
}
if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
}
return pTerm;
}
}
return 0;
}
/* Forward reference */
static void exprAnalyze(SrcList*, WhereClause*, int);
/*
** Call exprAnalyze on all terms in a WHERE clause.
**
**
*/
static void exprAnalyzeAll(
SrcList *pTabList, /* the FROM clause */
WhereClause *pWC /* the WHERE clause to be analyzed */
){
int i;
for(i=pWC->nTerm-1; i>=0; i--){
exprAnalyze(pTabList, pWC, i);
}
}
#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
/*
** Check to see if the given expression is a LIKE or GLOB operator that
** can be optimized using inequality constraints. Return TRUE if it is
** so and false if not.
**
** In order for the operator to be optimizible, the RHS must be a string
** literal that does not begin with a wildcard.
*/
static int isLikeOrGlob(
Parse *pParse, /* Parsing and code generating context */
Expr *pExpr, /* Test this expression */
Expr **ppPrefix, /* Pointer to TK_STRING expression with pattern prefix */
int *pisComplete, /* True if the only wildcard is % in the last character */
int *pnoCase /* True if uppercase is equivalent to lowercase */
){
const char *z = 0; /* String on RHS of LIKE operator */
Expr *pRight, *pLeft; /* Right and left size of LIKE operator */
ExprList *pList; /* List of operands to the LIKE operator */
int c; /* One character in z[] */
int cnt; /* Number of non-wildcard prefix characters */
char wc[3]; /* Wildcard characters */
sqlite3 *db = pParse->db; /* Database connection */
sqlite3_value *pVal = 0;
int op; /* Opcode of pRight */
if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){
return 0;
}
#ifdef SQLITE_EBCDIC
if( *pnoCase ) return 0;
#endif
pList = pExpr->x.pList;
pLeft = pList->a[1].pExpr;
if( pLeft->op!=TK_COLUMN || sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT ){
/* IMP: R-02065-49465 The left-hand side of the LIKE or GLOB operator must
** be the name of an indexed column with TEXT affinity. */
return 0;
}
assert( pLeft->iColumn!=(-1) ); /* Because IPK never has AFF_TEXT */
pRight = pList->a[0].pExpr;
op = pRight->op;
if( op==TK_REGISTER ){
op = pRight->op2;
}
if( op==TK_VARIABLE ){
Vdbe *pReprepare = pParse->pReprepare;
int iCol = pRight->iColumn;
pVal = sqlite3VdbeGetValue(pReprepare, iCol, SQLITE_AFF_NONE);
if( pVal && sqlite3_value_type(pVal)==SQLITE_TEXT ){
z = (char *)sqlite3_value_text(pVal);
}
sqlite3VdbeSetVarmask(pParse->pVdbe, iCol); /* IMP: R-23257-02778 */
assert( pRight->op==TK_VARIABLE || pRight->op==TK_REGISTER );
}else if( op==TK_STRING ){
z = pRight->u.zToken;
}
if( z ){
cnt = 0;
while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
cnt++;
}
if( cnt!=0 && 255!=(u8)z[cnt-1] ){
Expr *pPrefix;
*pisComplete = c==wc[0] && z[cnt+1]==0;
pPrefix = sqlite3Expr(db, TK_STRING, z);
if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
*ppPrefix = pPrefix;
if( op==TK_VARIABLE ){
Vdbe *v = pParse->pVdbe;
sqlite3VdbeSetVarmask(v, pRight->iColumn); /* IMP: R-23257-02778 */
if( *pisComplete && pRight->u.zToken[1] ){
/* If the rhs of the LIKE expression is a variable, and the current
** value of the variable means there is no need to invoke the LIKE
** function, then no OP_Variable will be added to the program.
** This causes problems for the sqlite3_bind_parameter_name()
** API. To workaround them, add a dummy OP_Variable here.
*/
int r1 = sqlite3GetTempReg(pParse);
sqlite3ExprCodeTarget(pParse, pRight, r1);
sqlite3VdbeChangeP3(v, sqlite3VdbeCurrentAddr(v)-1, 0);
sqlite3ReleaseTempReg(pParse, r1);
}
}
}else{
z = 0;
}
}
sqlite3ValueFree(pVal);
return (z!=0);
}
#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Check to see if the given expression is of the form
**
** column MATCH expr
**
** If it is then return TRUE. If not, return FALSE.
*/
static int isMatchOfColumn(
Expr *pExpr /* Test this expression */
){
ExprList *pList;
if( pExpr->op!=TK_FUNCTION ){
return 0;
}
if( sqlite3StrICmp(pExpr->u.zToken,"match")!=0 ){
return 0;
}
pList = pExpr->x.pList;
if( pList->nExpr!=2 ){
return 0;
}
if( pList->a[1].pExpr->op != TK_COLUMN ){
return 0;
}
return 1;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */
/*
** If the pBase expression originated in the ON or USING clause of
** a join, then transfer the appropriate markings over to derived.
*/
static void transferJoinMarkings(Expr *pDerived, Expr *pBase){
pDerived->flags |= pBase->flags & EP_FromJoin;
pDerived->iRightJoinTable = pBase->iRightJoinTable;
}
#if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
/*
** Analyze a term that consists of two or more OR-connected
** subterms. So in:
**
** ... WHERE (a=5) AND (b=7 OR c=9 OR d=13) AND (d=13)
** ^^^^^^^^^^^^^^^^^^^^
**
** This routine analyzes terms such as the middle term in the above example.
** A WhereOrTerm object is computed and attached to the term under
** analysis, regardless of the outcome of the analysis. Hence:
**
** WhereTerm.wtFlags |= TERM_ORINFO
** WhereTerm.u.pOrInfo = a dynamically allocated WhereOrTerm object
**
** The term being analyzed must have two or more of OR-connected subterms.
** A single subterm might be a set of AND-connected sub-subterms.
** Examples of terms under analysis:
**
** (A) t1.x=t2.y OR t1.x=t2.z OR t1.y=15 OR t1.z=t3.a+5
** (B) x=expr1 OR expr2=x OR x=expr3
** (C) t1.x=t2.y OR (t1.x=t2.z AND t1.y=15)
** (D) x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*')
** (E) (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6)
**
** CASE 1:
**
** If all subterms are of the form T.C=expr for some single column of C
** a single table T (as shown in example B above) then create a new virtual
** term that is an equivalent IN expression. In other words, if the term
** being analyzed is:
**
** x = expr1 OR expr2 = x OR x = expr3
**
** then create a new virtual term like this:
**
** x IN (expr1,expr2,expr3)
**
** CASE 2:
**
** If all subterms are indexable by a single table T, then set
**
** WhereTerm.eOperator = WO_OR
** WhereTerm.u.pOrInfo->indexable |= the cursor number for table T
**
** A subterm is "indexable" if it is of the form
** "T.C <op> <expr>" where C is any column of table T and
** <op> is one of "=", "<", "<=", ">", ">=", "IS NULL", or "IN".
** A subterm is also indexable if it is an AND of two or more
** subsubterms at least one of which is indexable. Indexable AND
** subterms have their eOperator set to WO_AND and they have
** u.pAndInfo set to a dynamically allocated WhereAndTerm object.
**
** From another point of view, "indexable" means that the subterm could
** potentially be used with an index if an appropriate index exists.
** This analysis does not consider whether or not the index exists; that
** is something the bestIndex() routine will determine. This analysis
** only looks at whether subterms appropriate for indexing exist.
**
** All examples A through E above all satisfy case 2. But if a term
** also statisfies case 1 (such as B) we know that the optimizer will
** always prefer case 1, so in that case we pretend that case 2 is not
** satisfied.
**
** It might be the case that multiple tables are indexable. For example,
** (E) above is indexable on tables P, Q, and R.
**
** Terms that satisfy case 2 are candidates for lookup by using
** separate indices to find rowids for each subterm and composing
** the union of all rowids using a RowSet object. This is similar
** to "bitmap indices" in other database engines.
**
** OTHERWISE:
**
** If neither case 1 nor case 2 apply, then leave the eOperator set to
** zero. This term is not useful for search.
*/
static void exprAnalyzeOrTerm(
SrcList *pSrc, /* the FROM clause */
WhereClause *pWC, /* the complete WHERE clause */
int idxTerm /* Index of the OR-term to be analyzed */
){
Parse *pParse = pWC->pParse; /* Parser context */
sqlite3 *db = pParse->db; /* Database connection */
WhereTerm *pTerm = &pWC->a[idxTerm]; /* The term to be analyzed */
Expr *pExpr = pTerm->pExpr; /* The expression of the term */
WhereMaskSet *pMaskSet = pWC->pMaskSet; /* Table use masks */
int i; /* Loop counters */
WhereClause *pOrWc; /* Breakup of pTerm into subterms */
WhereTerm *pOrTerm; /* A Sub-term within the pOrWc */
WhereOrInfo *pOrInfo; /* Additional information associated with pTerm */
Bitmask chngToIN; /* Tables that might satisfy case 1 */
Bitmask indexable; /* Tables that are indexable, satisfying case 2 */
/*
** Break the OR clause into its separate subterms. The subterms are
** stored in a WhereClause structure containing within the WhereOrInfo
** object that is attached to the original OR clause term.
*/
assert( (pTerm->wtFlags & (TERM_DYNAMIC|TERM_ORINFO|TERM_ANDINFO))==0 );
assert( pExpr->op==TK_OR );
pTerm->u.pOrInfo = pOrInfo = sqlite3DbMallocZero(db, sizeof(*pOrInfo));
if( pOrInfo==0 ) return;
pTerm->wtFlags |= TERM_ORINFO;
pOrWc = &pOrInfo->wc;
whereClauseInit(pOrWc, pWC->pParse, pMaskSet);
whereSplit(pOrWc, pExpr, TK_OR);
exprAnalyzeAll(pSrc, pOrWc);
if( db->mallocFailed ) return;
assert( pOrWc->nTerm>=2 );
/*
** Compute the set of tables that might satisfy cases 1 or 2.
*/
indexable = ~(Bitmask)0;
chngToIN = ~(pWC->vmask);
for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0 && indexable; i--, pOrTerm++){
if( (pOrTerm->eOperator & WO_SINGLE)==0 ){
WhereAndInfo *pAndInfo;
assert( pOrTerm->eOperator==0 );
assert( (pOrTerm->wtFlags & (TERM_ANDINFO|TERM_ORINFO))==0 );
chngToIN = 0;
pAndInfo = sqlite3DbMallocRaw(db, sizeof(*pAndInfo));
if( pAndInfo ){
WhereClause *pAndWC;
WhereTerm *pAndTerm;
int j;
Bitmask b = 0;
pOrTerm->u.pAndInfo = pAndInfo;
pOrTerm->wtFlags |= TERM_ANDINFO;
pOrTerm->eOperator = WO_AND;
pAndWC = &pAndInfo->wc;
whereClauseInit(pAndWC, pWC->pParse, pMaskSet);
whereSplit(pAndWC, pOrTerm->pExpr, TK_AND);
exprAnalyzeAll(pSrc, pAndWC);
testcase( db->mallocFailed );
if( !db->mallocFailed ){
for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){
assert( pAndTerm->pExpr );
if( allowedOp(pAndTerm->pExpr->op) ){
b |= getMask(pMaskSet, pAndTerm->leftCursor);
}
}
}
indexable &= b;
}
}else if( pOrTerm->wtFlags & TERM_COPIED ){
/* Skip this term for now. We revisit it when we process the
** corresponding TERM_VIRTUAL term */
}else{
Bitmask b;
b = getMask(pMaskSet, pOrTerm->leftCursor);
if( pOrTerm->wtFlags & TERM_VIRTUAL ){
WhereTerm *pOther = &pOrWc->a[pOrTerm->iParent];
b |= getMask(pMaskSet, pOther->leftCursor);
}
indexable &= b;
if( pOrTerm->eOperator!=WO_EQ ){
chngToIN = 0;
}else{
chngToIN &= b;
}
}
}
/*
** Record the set of tables that satisfy case 2. The set might be
** empty.
*/
pOrInfo->indexable = indexable;
pTerm->eOperator = indexable==0 ? 0 : WO_OR;
/*
** chngToIN holds a set of tables that *might* satisfy case 1. But
** we have to do some additional checking to see if case 1 really
** is satisfied.
**
** chngToIN will hold either 0, 1, or 2 bits. The 0-bit case means
** that there is no possibility of transforming the OR clause into an
** IN operator because one or more terms in the OR clause contain
** something other than == on a column in the single table. The 1-bit
** case means that every term of the OR clause is of the form
** "table.column=expr" for some single table. The one bit that is set
** will correspond to the common table. We still need to check to make
** sure the same column is used on all terms. The 2-bit case is when
** the all terms are of the form "table1.column=table2.column". It
** might be possible to form an IN operator with either table1.column
** or table2.column as the LHS if either is common to every term of
** the OR clause.
**
** Note that terms of the form "table.column1=table.column2" (the
** same table on both sizes of the ==) cannot be optimized.
*/
if( chngToIN ){
int okToChngToIN = 0; /* True if the conversion to IN is valid */
int iColumn = -1; /* Column index on lhs of IN operator */
int iCursor = -1; /* Table cursor common to all terms */
int j = 0; /* Loop counter */
/* Search for a table and column that appears on one side or the
** other of the == operator in every subterm. That table and column
** will be recorded in iCursor and iColumn. There might not be any
** such table and column. Set okToChngToIN if an appropriate table
** and column is found but leave okToChngToIN false if not found.
*/
for(j=0; j<2 && !okToChngToIN; j++){
pOrTerm = pOrWc->a;
for(i=pOrWc->nTerm-1; i>=0; i--, pOrTerm++){
assert( pOrTerm->eOperator==WO_EQ );
pOrTerm->wtFlags &= ~TERM_OR_OK;
if( pOrTerm->leftCursor==iCursor ){
/* This is the 2-bit case and we are on the second iteration and
** current term is from the first iteration. So skip this term. */
assert( j==1 );
continue;
}
if( (chngToIN & getMask(pMaskSet, pOrTerm->leftCursor))==0 ){
/* This term must be of the form t1.a==t2.b where t2 is in the
** chngToIN set but t1 is not. This term will be either preceeded
** or follwed by an inverted copy (t2.b==t1.a). Skip this term
** and use its inversion. */
testcase( pOrTerm->wtFlags & TERM_COPIED );
testcase( pOrTerm->wtFlags & TERM_VIRTUAL );
assert( pOrTerm->wtFlags & (TERM_COPIED|TERM_VIRTUAL) );
continue;
}
iColumn = pOrTerm->u.leftColumn;
iCursor = pOrTerm->leftCursor;
break;
}
if( i<0 ){
/* No candidate table+column was found. This can only occur
** on the second iteration */
assert( j==1 );
assert( (chngToIN&(chngToIN-1))==0 );
assert( chngToIN==getMask(pMaskSet, iCursor) );
break;
}
testcase( j==1 );
/* We have found a candidate table and column. Check to see if that
** table and column is common to every term in the OR clause */
okToChngToIN = 1;
for(; i>=0 && okToChngToIN; i--, pOrTerm++){
assert( pOrTerm->eOperator==WO_EQ );
if( pOrTerm->leftCursor!=iCursor ){
pOrTerm->wtFlags &= ~TERM_OR_OK;
}else if( pOrTerm->u.leftColumn!=iColumn ){
okToChngToIN = 0;
}else{
int affLeft, affRight;
/* If the right-hand side is also a column, then the affinities
** of both right and left sides must be such that no type
** conversions are required on the right. (Ticket #2249)
*/
affRight = sqlite3ExprAffinity(pOrTerm->pExpr->pRight);
affLeft = sqlite3ExprAffinity(pOrTerm->pExpr->pLeft);
if( affRight!=0 && affRight!=affLeft ){
okToChngToIN = 0;
}else{
pOrTerm->wtFlags |= TERM_OR_OK;
}
}
}
}
/* At this point, okToChngToIN is true if original pTerm satisfies
** case 1. In that case, construct a new virtual term that is
** pTerm converted into an IN operator.
**
** EV: R-00211-15100
*/
if( okToChngToIN ){
Expr *pDup; /* A transient duplicate expression */
ExprList *pList = 0; /* The RHS of the IN operator */
Expr *pLeft = 0; /* The LHS of the IN operator */
Expr *pNew; /* The complete IN operator */
for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0; i--, pOrTerm++){
if( (pOrTerm->wtFlags & TERM_OR_OK)==0 ) continue;
assert( pOrTerm->eOperator==WO_EQ );
assert( pOrTerm->leftCursor==iCursor );
assert( pOrTerm->u.leftColumn==iColumn );
pDup = sqlite3ExprDup(db, pOrTerm->pExpr->pRight, 0);
pList = sqlite3ExprListAppend(pWC->pParse, pList, pDup);
pLeft = pOrTerm->pExpr->pLeft;
}
assert( pLeft!=0 );
pDup = sqlite3ExprDup(db, pLeft, 0);
pNew = sqlite3PExpr(pParse, TK_IN, pDup, 0, 0);
if( pNew ){
int idxNew;
transferJoinMarkings(pNew, pExpr);
assert( !ExprHasProperty(pNew, EP_xIsSelect) );
pNew->x.pList = pList;
idxNew = whereClauseInsert(pWC, pNew, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew==0 );
exprAnalyze(pSrc, pWC, idxNew);
pTerm = &pWC->a[idxTerm];
pWC->a[idxNew].iParent = idxTerm;
pTerm->nChild = 1;
}else{
sqlite3ExprListDelete(db, pList);
}
pTerm->eOperator = WO_NOOP; /* case 1 trumps case 2 */
}
}
}
#endif /* !SQLITE_OMIT_OR_OPTIMIZATION && !SQLITE_OMIT_SUBQUERY */
/*
** The input to this routine is an WhereTerm structure with only the
** "pExpr" field filled in. The job of this routine is to analyze the
** subexpression and populate all the other fields of the WhereTerm
** structure.
**
** If the expression is of the form "<expr> <op> X" it gets commuted
** to the standard form of "X <op> <expr>".
**
** If the expression is of the form "X <op> Y" where both X and Y are
** columns, then the original expression is unchanged and a new virtual
** term of the form "Y <op> X" is added to the WHERE clause and
** analyzed separately. The original term is marked with TERM_COPIED
** and the new term is marked with TERM_DYNAMIC (because it's pExpr
** needs to be freed with the WhereClause) and TERM_VIRTUAL (because it
** is a commuted copy of a prior term.) The original term has nChild=1
** and the copy has idxParent set to the index of the original term.
*/
static void exprAnalyze(
SrcList *pSrc, /* the FROM clause */
WhereClause *pWC, /* the WHERE clause */
int idxTerm /* Index of the term to be analyzed */
){
WhereTerm *pTerm; /* The term to be analyzed */
WhereMaskSet *pMaskSet; /* Set of table index masks */
Expr *pExpr; /* The expression to be analyzed */
Bitmask prereqLeft; /* Prerequesites of the pExpr->pLeft */
Bitmask prereqAll; /* Prerequesites of pExpr */
Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */
Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */
int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */
int noCase = 0; /* LIKE/GLOB distinguishes case */
int op; /* Top-level operator. pExpr->op */
Parse *pParse = pWC->pParse; /* Parsing context */
sqlite3 *db = pParse->db; /* Database connection */
if( db->mallocFailed ){
return;
}
pTerm = &pWC->a[idxTerm];
pMaskSet = pWC->pMaskSet;
pExpr = pTerm->pExpr;
prereqLeft = exprTableUsage(pMaskSet, pExpr->pLeft);
op = pExpr->op;
if( op==TK_IN ){
assert( pExpr->pRight==0 );
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
pTerm->prereqRight = exprSelectTableUsage(pMaskSet, pExpr->x.pSelect);
}else{
pTerm->prereqRight = exprListTableUsage(pMaskSet, pExpr->x.pList);
}
}else if( op==TK_ISNULL ){
pTerm->prereqRight = 0;
}else{
pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
}
prereqAll = exprTableUsage(pMaskSet, pExpr);
if( ExprHasProperty(pExpr, EP_FromJoin) ){
Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
prereqAll |= x;
extraRight = x-1; /* ON clause terms may not be used with an index
** on left table of a LEFT JOIN. Ticket #3015 */
}
pTerm->prereqAll = prereqAll;
pTerm->leftCursor = -1;
pTerm->iParent = -1;
pTerm->eOperator = 0;
if( allowedOp(op) && (pTerm->prereqRight & prereqLeft)==0 ){
Expr *pLeft = pExpr->pLeft;
Expr *pRight = pExpr->pRight;
if( pLeft->op==TK_COLUMN ){
pTerm->leftCursor = pLeft->iTable;
pTerm->u.leftColumn = pLeft->iColumn;
pTerm->eOperator = operatorMask(op);
}
if( pRight && pRight->op==TK_COLUMN ){
WhereTerm *pNew;
Expr *pDup;
if( pTerm->leftCursor>=0 ){
int idxNew;
pDup = sqlite3ExprDup(db, pExpr, 0);
if( db->mallocFailed ){
sqlite3ExprDelete(db, pDup);
return;
}
idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
if( idxNew==0 ) return;
pNew = &pWC->a[idxNew];
pNew->iParent = idxTerm;
pTerm = &pWC->a[idxTerm];
pTerm->nChild = 1;
pTerm->wtFlags |= TERM_COPIED;
}else{
pDup = pExpr;
pNew = pTerm;
}
exprCommute(pParse, pDup);
pLeft = pDup->pLeft;
pNew->leftCursor = pLeft->iTable;
pNew->u.leftColumn = pLeft->iColumn;
testcase( (prereqLeft | extraRight) != prereqLeft );
pNew->prereqRight = prereqLeft | extraRight;
pNew->prereqAll = prereqAll;
pNew->eOperator = operatorMask(pDup->op);
}
}
#ifndef SQLITE_OMIT_BETWEEN_OPTIMIZATION
/* If a term is the BETWEEN operator, create two new virtual terms
** that define the range that the BETWEEN implements. For example:
**
** a BETWEEN b AND c
**
** is converted into:
**
** (a BETWEEN b AND c) AND (a>=b) AND (a<=c)
**
** The two new terms are added onto the end of the WhereClause object.
** The new terms are "dynamic" and are children of the original BETWEEN
** term. That means that if the BETWEEN term is coded, the children are
** skipped. Or, if the children are satisfied by an index, the original
** BETWEEN term is skipped.
*/
else if( pExpr->op==TK_BETWEEN && pWC->op==TK_AND ){
ExprList *pList = pExpr->x.pList;
int i;
static const u8 ops[] = {TK_GE, TK_LE};
assert( pList!=0 );
assert( pList->nExpr==2 );
for(i=0; i<2; i++){
Expr *pNewExpr;
int idxNew;
pNewExpr = sqlite3PExpr(pParse, ops[i],
sqlite3ExprDup(db, pExpr->pLeft, 0),
sqlite3ExprDup(db, pList->a[i].pExpr, 0), 0);
idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew==0 );
exprAnalyze(pSrc, pWC, idxNew);
pTerm = &pWC->a[idxTerm];
pWC->a[idxNew].iParent = idxTerm;
}
pTerm->nChild = 2;
}
#endif /* SQLITE_OMIT_BETWEEN_OPTIMIZATION */
#if !defined(SQLITE_OMIT_OR_OPTIMIZATION) && !defined(SQLITE_OMIT_SUBQUERY)
/* Analyze a term that is composed of two or more subterms connected by
** an OR operator.
*/
else if( pExpr->op==TK_OR ){
assert( pWC->op==TK_AND );
exprAnalyzeOrTerm(pSrc, pWC, idxTerm);
pTerm = &pWC->a[idxTerm];
}
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
#ifndef SQLITE_OMIT_LIKE_OPTIMIZATION
/* Add constraints to reduce the search space on a LIKE or GLOB
** operator.
**
** A like pattern of the form "x LIKE 'abc%'" is changed into constraints
**
** x>='abc' AND x<'abd' AND x LIKE 'abc%'
**
** The last character of the prefix "abc" is incremented to form the
** termination condition "abd".
*/
if( pWC->op==TK_AND
&& isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase)
){
Expr *pLeft; /* LHS of LIKE/GLOB operator */
Expr *pStr2; /* Copy of pStr1 - RHS of LIKE/GLOB operator */
Expr *pNewExpr1;
Expr *pNewExpr2;
int idxNew1;
int idxNew2;
CollSeq *pColl; /* Collating sequence to use */
pLeft = pExpr->x.pList->a[1].pExpr;
pStr2 = sqlite3ExprDup(db, pStr1, 0);
if( !db->mallocFailed ){
u8 c, *pC; /* Last character before the first wildcard */
pC = (u8*)&pStr2->u.zToken[sqlite3Strlen30(pStr2->u.zToken)-1];
c = *pC;
if( noCase ){
/* The point is to increment the last character before the first
** wildcard. But if we increment '@', that will push it into the
** alphabetic range where case conversions will mess up the
** inequality. To avoid this, make sure to also run the full
** LIKE on all candidate expressions by clearing the isComplete flag
*/
if( c=='A'-1 ) isComplete = 0; /* EV: R-64339-08207 */
c = sqlite3UpperToLower[c];
}
*pC = c + 1;
}
pColl = sqlite3FindCollSeq(db, SQLITE_UTF8, noCase ? "NOCASE" : "BINARY",0);
pNewExpr1 = sqlite3PExpr(pParse, TK_GE,
sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl),
pStr1, 0);
idxNew1 = whereClauseInsert(pWC, pNewExpr1, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew1==0 );
exprAnalyze(pSrc, pWC, idxNew1);
pNewExpr2 = sqlite3PExpr(pParse, TK_LT,
sqlite3ExprSetColl(sqlite3ExprDup(db,pLeft,0), pColl),
pStr2, 0);
idxNew2 = whereClauseInsert(pWC, pNewExpr2, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew2==0 );
exprAnalyze(pSrc, pWC, idxNew2);
pTerm = &pWC->a[idxTerm];
if( isComplete ){
pWC->a[idxNew1].iParent = idxTerm;
pWC->a[idxNew2].iParent = idxTerm;
pTerm->nChild = 2;
}
}
#endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */
#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Add a WO_MATCH auxiliary term to the constraint set if the
** current expression is of the form: column MATCH expr.
** This information is used by the xBestIndex methods of
** virtual tables. The native query optimizer does not attempt
** to do anything with MATCH functions.
*/
if( isMatchOfColumn(pExpr) ){
int idxNew;
Expr *pRight, *pLeft;
WhereTerm *pNewTerm;
Bitmask prereqColumn, prereqExpr;
pRight = pExpr->x.pList->a[0].pExpr;
pLeft = pExpr->x.pList->a[1].pExpr;
prereqExpr = exprTableUsage(pMaskSet, pRight);
prereqColumn = exprTableUsage(pMaskSet, pLeft);
if( (prereqExpr & prereqColumn)==0 ){
Expr *pNewExpr;
pNewExpr = sqlite3PExpr(pParse, TK_MATCH,
0, sqlite3ExprDup(db, pRight, 0), 0);
idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC);
testcase( idxNew==0 );
pNewTerm = &pWC->a[idxNew];
pNewTerm->prereqRight = prereqExpr;
pNewTerm->leftCursor = pLeft->iTable;
pNewTerm->u.leftColumn = pLeft->iColumn;
pNewTerm->eOperator = WO_MATCH;
pNewTerm->iParent = idxTerm;
pTerm = &pWC->a[idxTerm];
pTerm->nChild = 1;
pTerm->wtFlags |= TERM_COPIED;
pNewTerm->prereqAll = pTerm->prereqAll;
}
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */
#ifdef SQLITE_ENABLE_STAT2
/* When sqlite_stat2 histogram data is available an operator of the
** form "x IS NOT NULL" can sometimes be evaluated more efficiently
** as "x>NULL" if x is not an INTEGER PRIMARY KEY. So construct a
** virtual term of that form.
**
** Note that the virtual term must be tagged with TERM_VNULL. This
** TERM_VNULL tag will suppress the not-null check at the beginning
** of the loop. Without the TERM_VNULL flag, the not-null check at
** the start of the loop will prevent any results from being returned.
*/
if( pExpr->op==TK_NOTNULL
&& pExpr->pLeft->op==TK_COLUMN
&& pExpr->pLeft->iColumn>=0
){
Expr *pNewExpr;
Expr *pLeft = pExpr->pLeft;
int idxNew;
WhereTerm *pNewTerm;
pNewExpr = sqlite3PExpr(pParse, TK_GT,
sqlite3ExprDup(db, pLeft, 0),
sqlite3PExpr(pParse, TK_NULL, 0, 0, 0), 0);
idxNew = whereClauseInsert(pWC, pNewExpr,
TERM_VIRTUAL|TERM_DYNAMIC|TERM_VNULL);
if( idxNew ){
pNewTerm = &pWC->a[idxNew];
pNewTerm->prereqRight = 0;
pNewTerm->leftCursor = pLeft->iTable;
pNewTerm->u.leftColumn = pLeft->iColumn;
pNewTerm->eOperator = WO_GT;
pNewTerm->iParent = idxTerm;
pTerm = &pWC->a[idxTerm];
pTerm->nChild = 1;
pTerm->wtFlags |= TERM_COPIED;
pNewTerm->prereqAll = pTerm->prereqAll;
}
}
#endif /* SQLITE_ENABLE_STAT2 */
/* Prevent ON clause terms of a LEFT JOIN from being used to drive
** an index for tables to the left of the join.
*/
pTerm->prereqRight |= extraRight;
}
/*
** Return TRUE if any of the expressions in pList->a[iFirst...] contain
** a reference to any table other than the iBase table.
*/
static int referencesOtherTables(
ExprList *pList, /* Search expressions in ths list */
WhereMaskSet *pMaskSet, /* Mapping from tables to bitmaps */
int iFirst, /* Be searching with the iFirst-th expression */
int iBase /* Ignore references to this table */
){
Bitmask allowed = ~getMask(pMaskSet, iBase);
while( iFirst<pList->nExpr ){
if( (exprTableUsage(pMaskSet, pList->a[iFirst++].pExpr)&allowed)!=0 ){
return 1;
}
}
return 0;
}
/*
** This routine decides if pIdx can be used to satisfy the ORDER BY
** clause. If it can, it returns 1. If pIdx cannot satisfy the
** ORDER BY clause, this routine returns 0.
**
** pOrderBy is an ORDER BY clause from a SELECT statement. pTab is the
** left-most table in the FROM clause of that same SELECT statement and
** the table has a cursor number of "base". pIdx is an index on pTab.
**
** nEqCol is the number of columns of pIdx that are used as equality
** constraints. Any of these columns may be missing from the ORDER BY
** clause and the match can still be a success.
**
** All terms of the ORDER BY that match against the index must be either
** ASC or DESC. (Terms of the ORDER BY clause past the end of a UNIQUE
** index do not need to satisfy this constraint.) The *pbRev value is
** set to 1 if the ORDER BY clause is all DESC and it is set to 0 if
** the ORDER BY clause is all ASC.
*/
static int isSortingIndex(
Parse *pParse, /* Parsing context */
WhereMaskSet *pMaskSet, /* Mapping from table cursor numbers to bitmaps */
Index *pIdx, /* The index we are testing */
int base, /* Cursor number for the table to be sorted */
ExprList *pOrderBy, /* The ORDER BY clause */
int nEqCol, /* Number of index columns with == constraints */
int wsFlags, /* Index usages flags */
int *pbRev /* Set to 1 if ORDER BY is DESC */
){
int i, j; /* Loop counters */
int sortOrder = 0; /* XOR of index and ORDER BY sort direction */
int nTerm; /* Number of ORDER BY terms */
struct ExprList_item *pTerm; /* A term of the ORDER BY clause */
sqlite3 *db = pParse->db;
assert( pOrderBy!=0 );
nTerm = pOrderBy->nExpr;
assert( nTerm>0 );
/* Argument pIdx must either point to a 'real' named index structure,
** or an index structure allocated on the stack by bestBtreeIndex() to
** represent the rowid index that is part of every table. */
assert( pIdx->zName || (pIdx->nColumn==1 && pIdx->aiColumn[0]==-1) );
/* Match terms of the ORDER BY clause against columns of
** the index.
**
** Note that indices have pIdx->nColumn regular columns plus
** one additional column containing the rowid. The rowid column
** of the index is also allowed to match against the ORDER BY
** clause.
*/
for(i=j=0, pTerm=pOrderBy->a; j<nTerm && i<=pIdx->nColumn; i++){
Expr *pExpr; /* The expression of the ORDER BY pTerm */
CollSeq *pColl; /* The collating sequence of pExpr */
int termSortOrder; /* Sort order for this term */
int iColumn; /* The i-th column of the index. -1 for rowid */
int iSortOrder; /* 1 for DESC, 0 for ASC on the i-th index term */
const char *zColl; /* Name of the collating sequence for i-th index term */
pExpr = pTerm->pExpr;
if( pExpr->op!=TK_COLUMN || pExpr->iTable!=base ){
/* Can not use an index sort on anything that is not a column in the
** left-most table of the FROM clause */
break;
}
pColl = sqlite3ExprCollSeq(pParse, pExpr);
if( !pColl ){
pColl = db->pDfltColl;
}
if( pIdx->zName && i<pIdx->nColumn ){
iColumn = pIdx->aiColumn[i];
if( iColumn==pIdx->pTable->iPKey ){
iColumn = -1;
}
iSortOrder = pIdx->aSortOrder[i];
zColl = pIdx->azColl[i];
}else{
iColumn = -1;
iSortOrder = 0;
zColl = pColl->zName;
}
if( pExpr->iColumn!=iColumn || sqlite3StrICmp(pColl->zName, zColl) ){
/* Term j of the ORDER BY clause does not match column i of the index */
if( i<nEqCol ){
/* If an index column that is constrained by == fails to match an
** ORDER BY term, that is OK. Just ignore that column of the index
*/
continue;
}else if( i==pIdx->nColumn ){
/* Index column i is the rowid. All other terms match. */
break;
}else{
/* If an index column fails to match and is not constrained by ==
** then the index cannot satisfy the ORDER BY constraint.
*/
return 0;
}
}
assert( pIdx->aSortOrder!=0 || iColumn==-1 );
assert( pTerm->sortOrder==0 || pTerm->sortOrder==1 );
assert( iSortOrder==0 || iSortOrder==1 );
termSortOrder = iSortOrder ^ pTerm->sortOrder;
if( i>nEqCol ){
if( termSortOrder!=sortOrder ){
/* Indices can only be used if all ORDER BY terms past the
** equality constraints are all either DESC or ASC. */
return 0;
}
}else{
sortOrder = termSortOrder;
}
j++;
pTerm++;
if( iColumn<0 && !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
/* If the indexed column is the primary key and everything matches
** so far and none of the ORDER BY terms to the right reference other
** tables in the join, then we are assured that the index can be used
** to sort because the primary key is unique and so none of the other
** columns will make any difference
*/
j = nTerm;
}
}
*pbRev = sortOrder!=0;
if( j>=nTerm ){
/* All terms of the ORDER BY clause are covered by this index so
** this index can be used for sorting. */
return 1;
}
if( pIdx->onError!=OE_None && i==pIdx->nColumn
&& (wsFlags & WHERE_COLUMN_NULL)==0
&& !referencesOtherTables(pOrderBy, pMaskSet, j, base) ){
/* All terms of this index match some prefix of the ORDER BY clause
** and the index is UNIQUE and no terms on the tail of the ORDER BY
** clause reference other tables in a join. If this is all true then
** the order by clause is superfluous. Not that if the matching
** condition is IS NULL then the result is not necessarily unique
** even on a UNIQUE index, so disallow those cases. */
return 1;
}
return 0;
}
/*
** Prepare a crude estimate of the logarithm of the input value.
** The results need not be exact. This is only used for estimating
** the total cost of performing operations with O(logN) or O(NlogN)
** complexity. Because N is just a guess, it is no great tragedy if
** logN is a little off.
*/
static double estLog(double N){
double logN = 1;
double x = 10;
while( N>x ){
logN += 1;
x *= 10;
}
return logN;
}
/*
** Two routines for printing the content of an sqlite3_index_info
** structure. Used for testing and debugging only. If neither
** SQLITE_TEST or SQLITE_DEBUG are defined, then these routines
** are no-ops.
*/
#if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_DEBUG)
static void TRACE_IDX_INPUTS(sqlite3_index_info *p){
int i;
if( !sqlite3WhereTrace ) return;
for(i=0; i<p->nConstraint; i++){
sqlite3DebugPrintf(" constraint[%d]: col=%d termid=%d op=%d usabled=%d\n",
i,
p->aConstraint[i].iColumn,
p->aConstraint[i].iTermOffset,
p->aConstraint[i].op,
p->aConstraint[i].usable);
}
for(i=0; i<p->nOrderBy; i++){
sqlite3DebugPrintf(" orderby[%d]: col=%d desc=%d\n",
i,
p->aOrderBy[i].iColumn,
p->aOrderBy[i].desc);
}
}
static void TRACE_IDX_OUTPUTS(sqlite3_index_info *p){
int i;
if( !sqlite3WhereTrace ) return;
for(i=0; i<p->nConstraint; i++){
sqlite3DebugPrintf(" usage[%d]: argvIdx=%d omit=%d\n",
i,
p->aConstraintUsage[i].argvIndex,
p->aConstraintUsage[i].omit);
}
sqlite3DebugPrintf(" idxNum=%d\n", p->idxNum);
sqlite3DebugPrintf(" idxStr=%s\n", p->idxStr);
sqlite3DebugPrintf(" orderByConsumed=%d\n", p->orderByConsumed);
sqlite3DebugPrintf(" estimatedCost=%g\n", p->estimatedCost);
}
#else
#define TRACE_IDX_INPUTS(A)
#define TRACE_IDX_OUTPUTS(A)
#endif
/*
** Required because bestIndex() is called by bestOrClauseIndex()
*/
static void bestIndex(
Parse*, WhereClause*, struct SrcList_item*,
Bitmask, Bitmask, ExprList*, WhereCost*);
/*
** This routine attempts to find an scanning strategy that can be used
** to optimize an 'OR' expression that is part of a WHERE clause.
**
** The table associated with FROM clause term pSrc may be either a
** regular B-Tree table or a virtual table.
*/
static void bestOrClauseIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors not available for indexing */
Bitmask notValid, /* Cursors not available for any purpose */
ExprList *pOrderBy, /* The ORDER BY clause */
WhereCost *pCost /* Lowest cost query plan */
){
#ifndef SQLITE_OMIT_OR_OPTIMIZATION
const int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
const Bitmask maskSrc = getMask(pWC->pMaskSet, iCur); /* Bitmask for pSrc */
WhereTerm * const pWCEnd = &pWC->a[pWC->nTerm]; /* End of pWC->a[] */
WhereTerm *pTerm; /* A single term of the WHERE clause */
/* No OR-clause optimization allowed if the INDEXED BY or NOT INDEXED clauses
** are used */
if( pSrc->notIndexed || pSrc->pIndex!=0 ){
return;
}
/* Search the WHERE clause terms for a usable WO_OR term. */
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( pTerm->eOperator==WO_OR
&& ((pTerm->prereqAll & ~maskSrc) & notReady)==0
&& (pTerm->u.pOrInfo->indexable & maskSrc)!=0
){
WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm];
WhereTerm *pOrTerm;
int flags = WHERE_MULTI_OR;
double rTotal = 0;
double nRow = 0;
Bitmask used = 0;
for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){
WhereCost sTermCost;
WHERETRACE(("... Multi-index OR testing for term %d of %d....\n",
(pOrTerm - pOrWC->a), (pTerm - pWC->a)
));
if( pOrTerm->eOperator==WO_AND ){
WhereClause *pAndWC = &pOrTerm->u.pAndInfo->wc;
bestIndex(pParse, pAndWC, pSrc, notReady, notValid, 0, &sTermCost);
}else if( pOrTerm->leftCursor==iCur ){
WhereClause tempWC;
tempWC.pParse = pWC->pParse;
tempWC.pMaskSet = pWC->pMaskSet;
tempWC.op = TK_AND;
tempWC.a = pOrTerm;
tempWC.nTerm = 1;
bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost);
}else{
continue;
}
rTotal += sTermCost.rCost;
nRow += sTermCost.plan.nRow;
used |= sTermCost.used;
if( rTotal>=pCost->rCost ) break;
}
/* If there is an ORDER BY clause, increase the scan cost to account
** for the cost of the sort. */
if( pOrderBy!=0 ){
WHERETRACE(("... sorting increases OR cost %.9g to %.9g\n",
rTotal, rTotal+nRow*estLog(nRow)));
rTotal += nRow*estLog(nRow);
}
/* If the cost of scanning using this OR term for optimization is
** less than the current cost stored in pCost, replace the contents
** of pCost. */
WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow));
if( rTotal<pCost->rCost ){
pCost->rCost = rTotal;
pCost->used = used;
pCost->plan.nRow = nRow;
pCost->plan.wsFlags = flags;
pCost->plan.u.pTerm = pTerm;
}
}
}
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
}
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** Return TRUE if the WHERE clause term pTerm is of a form where it
** could be used with an index to access pSrc, assuming an appropriate
** index existed.
*/
static int termCanDriveIndex(
WhereTerm *pTerm, /* WHERE clause term to check */
struct SrcList_item *pSrc, /* Table we are trying to access */
Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( pTerm->eOperator!=WO_EQ ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
return 1;
}
#endif
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** If the query plan for pSrc specified in pCost is a full table scan
** and indexing is allows (if there is no NOT INDEXED clause) and it
** possible to construct a transient index that would perform better
** than a full table scan even when the cost of constructing the index
** is taken into account, then alter the query plan to use the
** transient index.
*/
static void bestAutomaticIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors that are not available */
WhereCost *pCost /* Lowest cost query plan */
){
double nTableRow; /* Rows in the input table */
double logN; /* log(nTableRow) */
double costTempIdx; /* per-query cost of the transient index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
WhereTerm *pWCEnd; /* End of pWC->a[] */
Table *pTable; /* Table tht might be indexed */
if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){
/* Automatic indices are disabled at run-time */
return;
}
if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
/* We already have some kind of index in use for this query. */
return;
}
if( pSrc->notIndexed ){
/* The NOT INDEXED clause appears in the SQL. */
return;
}
assert( pParse->nQueryLoop >= (double)1 );
pTable = pSrc->pTab;
nTableRow = pTable->nRowEst;
logN = estLog(nTableRow);
costTempIdx = 2*logN*(nTableRow/pParse->nQueryLoop + 1);
if( costTempIdx>=pCost->rCost ){
/* The cost of creating the transient table would be greater than
** doing the full table scan */
return;
}
/* Search for any equality comparison term */
pWCEnd = &pWC->a[pWC->nTerm];
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
WHERETRACE(("auto-index reduces cost from %.1f to %.1f\n",
pCost->rCost, costTempIdx));
pCost->rCost = costTempIdx;
pCost->plan.nRow = logN + 1;
pCost->plan.wsFlags = WHERE_TEMP_INDEX;
pCost->used = pTerm->prereqRight;
break;
}
}
}
#else
# define bestAutomaticIndex(A,B,C,D,E) /* no-op */
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** Generate code to construct the Index object for an automatic index
** and to set up the WhereLevel object pLevel so that the code generator
** makes use of the automatic index.
*/
static void constructAutomaticIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to get the next index */
Bitmask notReady, /* Mask of cursors that are not available */
WhereLevel *pLevel /* Write new index here */
){
int nColumn; /* Number of columns in the constructed index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
WhereTerm *pWCEnd; /* End of pWC->a[] */
int nByte; /* Byte of memory needed for pIdx */
Index *pIdx; /* Object describing the transient index */
Vdbe *v; /* Prepared statement under construction */
int regIsInit; /* Register set by initialization */
int addrInit; /* Address of the initialization bypass jump */
Table *pTable; /* The table being indexed */
KeyInfo *pKeyinfo; /* Key information for the index */
int addrTop; /* Top of the index fill loop */
int regRecord; /* Register holding an index record */
int n; /* Column counter */
int i; /* Loop counter */
int mxBitCol; /* Maximum column in pSrc->colUsed */
CollSeq *pColl; /* Collating sequence to on a column */
Bitmask idxCols; /* Bitmap of columns used for indexing */
Bitmask extraCols; /* Bitmap of additional columns */
/* Generate code to skip over the creation and initialization of the
** transient index on 2nd and subsequent iterations of the loop. */
v = pParse->pVdbe;
assert( v!=0 );
regIsInit = ++pParse->nMem;
addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit);
sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit);
/* Count the number of columns that will be added to the index
** and used to match WHERE clause constraints */
nColumn = 0;
pTable = pSrc->pTab;
pWCEnd = &pWC->a[pWC->nTerm];
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
testcase( iCol==BMS );
testcase( iCol==BMS-1 );
if( (idxCols & cMask)==0 ){
nColumn++;
idxCols |= cMask;
}
}
}
assert( nColumn>0 );
pLevel->plan.nEq = nColumn;
/* Count the number of additional columns needed to create a
** covering index. A "covering index" is an index that contains all
** columns that are needed by the query. With a covering index, the
** original table never needs to be accessed. Automatic indices must
** be a covering index because the index will not be updated if the
** original table changes and the index and table cannot both be used
** if they go out of sync.
*/
extraCols = pSrc->colUsed & (~idxCols | (((Bitmask)1)<<(BMS-1)));
mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol;
testcase( pTable->nCol==BMS-1 );
testcase( pTable->nCol==BMS-2 );
for(i=0; i<mxBitCol; i++){
if( extraCols & (((Bitmask)1)<<i) ) nColumn++;
}
if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
nColumn += pTable->nCol - BMS + 1;
}
pLevel->plan.wsFlags |= WHERE_COLUMN_EQ | WHERE_IDX_ONLY | WO_EQ;
/* Construct the Index object to describe this index */
nByte = sizeof(Index);
nByte += nColumn*sizeof(int); /* Index.aiColumn */
nByte += nColumn*sizeof(char*); /* Index.azColl */
nByte += nColumn; /* Index.aSortOrder */
pIdx = sqlite3DbMallocZero(pParse->db, nByte);
if( pIdx==0 ) return;
pLevel->plan.u.pIdx = pIdx;
pIdx->azColl = (char**)&pIdx[1];
pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
pIdx->zName = "auto-index";
pIdx->nColumn = nColumn;
pIdx->pTable = pTable;
n = 0;
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? ((Bitmask)1)<<(BMS-1) : ((Bitmask)1)<<iCol;
if( (idxCols & cMask)==0 ){
Expr *pX = pTerm->pExpr;
idxCols |= cMask;
pIdx->aiColumn[n] = pTerm->u.leftColumn;
pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
pIdx->azColl[n] = ALWAYS(pColl) ? pColl->zName : "BINARY";
n++;
}
}
}
assert( (u32)n==pLevel->plan.nEq );
/* Add additional columns needed to make the automatic index into
** a covering index */
for(i=0; i<mxBitCol; i++){
if( extraCols & (((Bitmask)1)<<i) ){
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){
for(i=BMS-1; i<pTable->nCol; i++){
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
assert( n==nColumn );
/* Create the automatic index */
pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
assert( pLevel->iIdxCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
(char*)pKeyinfo, P4_KEYINFO_HANDOFF);
VdbeComment((v, "for %s", pTable->zName));
/* Fill the automatic index with content */
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
regRecord = sqlite3GetTempReg(pParse);
sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
sqlite3VdbeJumpHere(v, addrTop);
sqlite3ReleaseTempReg(pParse, regRecord);
/* Jump here when skipping the initialization */
sqlite3VdbeJumpHere(v, addrInit);
}
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Allocate and populate an sqlite3_index_info structure. It is the
** responsibility of the caller to eventually release the structure
** by passing the pointer returned by this function to sqlite3_free().
*/
static sqlite3_index_info *allocateIndexInfo(
Parse *pParse,
WhereClause *pWC,
struct SrcList_item *pSrc,
ExprList *pOrderBy
){
int i, j;
int nTerm;
struct sqlite3_index_constraint *pIdxCons;
struct sqlite3_index_orderby *pIdxOrderBy;
struct sqlite3_index_constraint_usage *pUsage;
WhereTerm *pTerm;
int nOrderBy;
sqlite3_index_info *pIdxInfo;
WHERETRACE(("Recomputing index info for %s...\n", pSrc->pTab->zName));
/* Count the number of possible WHERE clause constraints referring
** to this virtual table */
for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
if( pTerm->leftCursor != pSrc->iCursor ) continue;
assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
testcase( pTerm->eOperator==WO_IN );
testcase( pTerm->eOperator==WO_ISNULL );
if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue;
nTerm++;
}
/* If the ORDER BY clause contains only columns in the current
** virtual table then allocate space for the aOrderBy part of
** the sqlite3_index_info structure.
*/
nOrderBy = 0;
if( pOrderBy ){
for(i=0; i<pOrderBy->nExpr; i++){
Expr *pExpr = pOrderBy->a[i].pExpr;
if( pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor ) break;
}
if( i==pOrderBy->nExpr ){
nOrderBy = pOrderBy->nExpr;
}
}
/* Allocate the sqlite3_index_info structure
*/
pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo)
+ (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm
+ sizeof(*pIdxOrderBy)*nOrderBy );
if( pIdxInfo==0 ){
sqlite3ErrorMsg(pParse, "out of memory");
/* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
return 0;
}
/* Initialize the structure. The sqlite3_index_info structure contains
** many fields that are declared "const" to prevent xBestIndex from
** changing them. We have to do some funky casting in order to
** initialize those fields.
*/
pIdxCons = (struct sqlite3_index_constraint*)&pIdxInfo[1];
pIdxOrderBy = (struct sqlite3_index_orderby*)&pIdxCons[nTerm];
pUsage = (struct sqlite3_index_constraint_usage*)&pIdxOrderBy[nOrderBy];
*(int*)&pIdxInfo->nConstraint = nTerm;
*(int*)&pIdxInfo->nOrderBy = nOrderBy;
*(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
*(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
*(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
pUsage;
for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
if( pTerm->leftCursor != pSrc->iCursor ) continue;
assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 );
testcase( pTerm->eOperator==WO_IN );
testcase( pTerm->eOperator==WO_ISNULL );
if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue;
pIdxCons[j].iColumn = pTerm->u.leftColumn;
pIdxCons[j].iTermOffset = i;
pIdxCons[j].op = (u8)pTerm->eOperator;
/* The direct assignment in the previous line is possible only because
** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The
** following asserts verify this fact. */
assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH );
assert( pTerm->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) );
j++;
}
for(i=0; i<nOrderBy; i++){
Expr *pExpr = pOrderBy->a[i].pExpr;
pIdxOrderBy[i].iColumn = pExpr->iColumn;
pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder;
}
return pIdxInfo;
}
/*
** The table object reference passed as the second argument to this function
** must represent a virtual table. This function invokes the xBestIndex()
** method of the virtual table with the sqlite3_index_info pointer passed
** as the argument.
**
** If an error occurs, pParse is populated with an error message and a
** non-zero value is returned. Otherwise, 0 is returned and the output
** part of the sqlite3_index_info structure is left populated.
**
** Whether or not an error is returned, it is the responsibility of the
** caller to eventually free p->idxStr if p->needToFreeIdxStr indicates
** that this is required.
*/
static int vtabBestIndex(Parse *pParse, Table *pTab, sqlite3_index_info *p){
sqlite3_vtab *pVtab = sqlite3GetVTable(pParse->db, pTab)->pVtab;
int i;
int rc;
WHERETRACE(("xBestIndex for %s\n", pTab->zName));
TRACE_IDX_INPUTS(p);
rc = pVtab->pModule->xBestIndex(pVtab, p);
TRACE_IDX_OUTPUTS(p);
if( rc!=SQLITE_OK ){
if( rc==SQLITE_NOMEM ){
pParse->db->mallocFailed = 1;
}else if( !pVtab->zErrMsg ){
sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
}else{
sqlite3ErrorMsg(pParse, "%s", pVtab->zErrMsg);
}
}
sqlite3_free(pVtab->zErrMsg);
pVtab->zErrMsg = 0;
for(i=0; i<p->nConstraint; i++){
if( !p->aConstraint[i].usable && p->aConstraintUsage[i].argvIndex>0 ){
sqlite3ErrorMsg(pParse,
"table %s: xBestIndex returned an invalid plan", pTab->zName);
}
}
return pParse->nErr;
}
/*
** Compute the best index for a virtual table.
**
** The best index is computed by the xBestIndex method of the virtual
** table module. This routine is really just a wrapper that sets up
** the sqlite3_index_info structure that is used to communicate with
** xBestIndex.
**
** In a join, this routine might be called multiple times for the
** same virtual table. The sqlite3_index_info structure is created
** and initialized on the first invocation and reused on all subsequent
** invocations. The sqlite3_index_info structure is also used when
** code is generated to access the virtual table. The whereInfoDelete()
** routine takes care of freeing the sqlite3_index_info structure after
** everybody has finished with it.
*/
static void bestVirtualIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors not available for index */
Bitmask notValid, /* Cursors not valid for any purpose */
ExprList *pOrderBy, /* The order by clause */
WhereCost *pCost, /* Lowest cost query plan */
sqlite3_index_info **ppIdxInfo /* Index information passed to xBestIndex */
){
Table *pTab = pSrc->pTab;
sqlite3_index_info *pIdxInfo;
struct sqlite3_index_constraint *pIdxCons;
struct sqlite3_index_constraint_usage *pUsage;
WhereTerm *pTerm;
int i, j;
int nOrderBy;
double rCost;
/* Make sure wsFlags is initialized to some sane value. Otherwise, if the
** malloc in allocateIndexInfo() fails and this function returns leaving
** wsFlags in an uninitialized state, the caller may behave unpredictably.
*/
memset(pCost, 0, sizeof(*pCost));
pCost->plan.wsFlags = WHERE_VIRTUALTABLE;
/* If the sqlite3_index_info structure has not been previously
** allocated and initialized, then allocate and initialize it now.
*/
pIdxInfo = *ppIdxInfo;
if( pIdxInfo==0 ){
*ppIdxInfo = pIdxInfo = allocateIndexInfo(pParse, pWC, pSrc, pOrderBy);
}
if( pIdxInfo==0 ){
return;
}
/* At this point, the sqlite3_index_info structure that pIdxInfo points
** to will have been initialized, either during the current invocation or
** during some prior invocation. Now we just have to customize the
** details of pIdxInfo for the current invocation and pass it to
** xBestIndex.
*/
/* The module name must be defined. Also, by this point there must
** be a pointer to an sqlite3_vtab structure. Otherwise
** sqlite3ViewGetColumnNames() would have picked up the error.
*/
assert( pTab->azModuleArg && pTab->azModuleArg[0] );
assert( sqlite3GetVTable(pParse->db, pTab) );
/* Set the aConstraint[].usable fields and initialize all
** output variables to zero.
**
** aConstraint[].usable is true for constraints where the right-hand
** side contains only references to tables to the left of the current
** table. In other words, if the constraint is of the form:
**
** column = expr
**
** and we are evaluating a join, then the constraint on column is
** only valid if all tables referenced in expr occur to the left
** of the table containing column.
**
** The aConstraints[] array contains entries for all constraints
** on the current table. That way we only have to compute it once
** even though we might try to pick the best index multiple times.
** For each attempt at picking an index, the order of tables in the
** join might be different so we have to recompute the usable flag
** each time.
*/
pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
pUsage = pIdxInfo->aConstraintUsage;
for(i=0; i<pIdxInfo->nConstraint; i++, pIdxCons++){
j = pIdxCons->iTermOffset;
pTerm = &pWC->a[j];
pIdxCons->usable = (pTerm->prereqRight&notReady) ? 0 : 1;
}
memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
if( pIdxInfo->needToFreeIdxStr ){
sqlite3_free(pIdxInfo->idxStr);
}
pIdxInfo->idxStr = 0;
pIdxInfo->idxNum = 0;
pIdxInfo->needToFreeIdxStr = 0;
pIdxInfo->orderByConsumed = 0;
/* ((double)2) In case of SQLITE_OMIT_FLOATING_POINT... */
pIdxInfo->estimatedCost = SQLITE_BIG_DBL / ((double)2);
nOrderBy = pIdxInfo->nOrderBy;
if( !pOrderBy ){
pIdxInfo->nOrderBy = 0;
}
if( vtabBestIndex(pParse, pTab, pIdxInfo) ){
return;
}
pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++){
if( pUsage[i].argvIndex>0 ){
pCost->used |= pWC->a[pIdxCons[i].iTermOffset].prereqRight;
}
}
/* If there is an ORDER BY clause, and the selected virtual table index
** does not satisfy it, increase the cost of the scan accordingly. This
** matches the processing for non-virtual tables in bestBtreeIndex().
*/
rCost = pIdxInfo->estimatedCost;
if( pOrderBy && pIdxInfo->orderByConsumed==0 ){
rCost += estLog(rCost)*rCost;
}
/* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
** inital value of lowestCost in this loop. If it is, then the
** (cost<lowestCost) test below will never be true.
**
** Use "(double)2" instead of "2.0" in case OMIT_FLOATING_POINT
** is defined.
*/
if( (SQLITE_BIG_DBL/((double)2))<rCost ){
pCost->rCost = (SQLITE_BIG_DBL/((double)2));
}else{
pCost->rCost = rCost;
}
pCost->plan.u.pVtabIdx = pIdxInfo;
if( pIdxInfo->orderByConsumed ){
pCost->plan.wsFlags |= WHERE_ORDERBY;
}
pCost->plan.nEq = 0;
pIdxInfo->nOrderBy = nOrderBy;
/* Try to find a more efficient access pattern by using multiple indexes
** to optimize an OR expression within the WHERE clause.
*/
bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost);
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */
/*
** Argument pIdx is a pointer to an index structure that has an array of
** SQLITE_INDEX_SAMPLES evenly spaced samples of the first indexed column
** stored in Index.aSample. These samples divide the domain of values stored
** the index into (SQLITE_INDEX_SAMPLES+1) regions.
** Region 0 contains all values less than the first sample value. Region
** 1 contains values between the first and second samples. Region 2 contains
** values between samples 2 and 3. And so on. Region SQLITE_INDEX_SAMPLES
** contains values larger than the last sample.
**
** If the index contains many duplicates of a single value, then it is
** possible that two or more adjacent samples can hold the same value.
** When that is the case, the smallest possible region code is returned
** when roundUp is false and the largest possible region code is returned
** when roundUp is true.
**
** If successful, this function determines which of the regions value
** pVal lies in, sets *piRegion to the region index (a value between 0
** and SQLITE_INDEX_SAMPLES+1, inclusive) and returns SQLITE_OK.
** Or, if an OOM occurs while converting text values between encodings,
** SQLITE_NOMEM is returned and *piRegion is undefined.
*/
#ifdef SQLITE_ENABLE_STAT2
static int whereRangeRegion(
Parse *pParse, /* Database connection */
Index *pIdx, /* Index to consider domain of */
sqlite3_value *pVal, /* Value to consider */
int roundUp, /* Return largest valid region if true */
int *piRegion /* OUT: Region of domain in which value lies */
){
assert( roundUp==0 || roundUp==1 );
if( ALWAYS(pVal) ){
IndexSample *aSample = pIdx->aSample;
int i = 0;
int eType = sqlite3_value_type(pVal);
if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
double r = sqlite3_value_double(pVal);
for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
if( aSample[i].eType==SQLITE_NULL ) continue;
if( aSample[i].eType>=SQLITE_TEXT ) break;
if( roundUp ){
if( aSample[i].u.r>r ) break;
}else{
if( aSample[i].u.r>=r ) break;
}
}
}else if( eType==SQLITE_NULL ){
i = 0;
if( roundUp ){
while( i<SQLITE_INDEX_SAMPLES && aSample[i].eType==SQLITE_NULL ) i++;
}
}else{
sqlite3 *db = pParse->db;
CollSeq *pColl;
const u8 *z;
int n;
/* pVal comes from sqlite3ValueFromExpr() so the type cannot be NULL */
assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB );
if( eType==SQLITE_BLOB ){
z = (const u8 *)sqlite3_value_blob(pVal);
pColl = db->pDfltColl;
assert( pColl->enc==SQLITE_UTF8 );
}else{
pColl = sqlite3GetCollSeq(db, SQLITE_UTF8, 0, *pIdx->azColl);
if( pColl==0 ){
sqlite3ErrorMsg(pParse, "no such collation sequence: %s",
*pIdx->azColl);
return SQLITE_ERROR;
}
z = (const u8 *)sqlite3ValueText(pVal, pColl->enc);
if( !z ){
return SQLITE_NOMEM;
}
assert( z && pColl && pColl->xCmp );
}
n = sqlite3ValueBytes(pVal, pColl->enc);
for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
int c;
int eSampletype = aSample[i].eType;
if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue;
if( (eSampletype!=eType) ) break;
#ifndef SQLITE_OMIT_UTF16
if( pColl->enc!=SQLITE_UTF8 ){
int nSample;
char *zSample = sqlite3Utf8to16(
db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample
);
if( !zSample ){
assert( db->mallocFailed );
return SQLITE_NOMEM;
}
c = pColl->xCmp(pColl->pUser, nSample, zSample, n, z);
sqlite3DbFree(db, zSample);
}else
#endif
{
c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
}
if( c-roundUp>=0 ) break;
}
}
assert( i>=0 && i<=SQLITE_INDEX_SAMPLES );
*piRegion = i;
}
return SQLITE_OK;
}
#endif /* #ifdef SQLITE_ENABLE_STAT2 */
/*
** If expression pExpr represents a literal value, set *pp to point to
** an sqlite3_value structure containing the same value, with affinity
** aff applied to it, before returning. It is the responsibility of the
** caller to eventually release this structure by passing it to
** sqlite3ValueFree().
**
** If the current parse is a recompile (sqlite3Reprepare()) and pExpr
** is an SQL variable that currently has a non-NULL value bound to it,
** create an sqlite3_value structure containing this value, again with
** affinity aff applied to it, instead.
**
** If neither of the above apply, set *pp to NULL.
**
** If an error occurs, return an error code. Otherwise, SQLITE_OK.
*/
#ifdef SQLITE_ENABLE_STAT2
static int valueFromExpr(
Parse *pParse,
Expr *pExpr,
u8 aff,
sqlite3_value **pp
){
if( pExpr->op==TK_VARIABLE
|| (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE)
){
int iVar = pExpr->iColumn;
sqlite3VdbeSetVarmask(pParse->pVdbe, iVar); /* IMP: R-23257-02778 */
*pp = sqlite3VdbeGetValue(pParse->pReprepare, iVar, aff);
return SQLITE_OK;
}
return sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, aff, pp);
}
#endif
/*
** This function is used to estimate the number of rows that will be visited
** by scanning an index for a range of values. The range may have an upper
** bound, a lower bound, or both. The WHERE clause terms that set the upper
** and lower bounds are represented by pLower and pUpper respectively. For
** example, assuming that index p is on t1(a):
**
** ... FROM t1 WHERE a > ? AND a < ? ...
** |_____| |_____|
** | |
** pLower pUpper
**
** If either of the upper or lower bound is not present, then NULL is passed in
** place of the corresponding WhereTerm.
**
** The nEq parameter is passed the index of the index column subject to the
** range constraint. Or, equivalently, the number of equality constraints
** optimized by the proposed index scan. For example, assuming index p is
** on t1(a, b), and the SQL query is:
**
** ... FROM t1 WHERE a = ? AND b > ? AND b < ? ...
**
** then nEq should be passed the value 1 (as the range restricted column,
** b, is the second left-most column of the index). Or, if the query is:
**
** ... FROM t1 WHERE a > ? AND a < ? ...
**
** then nEq should be passed 0.
**
** The returned value is an integer between 1 and 100, inclusive. A return
** value of 1 indicates that the proposed range scan is expected to visit
** approximately 1/100th (1%) of the rows selected by the nEq equality
** constraints (if any). A return value of 100 indicates that it is expected
** that the range scan will visit every row (100%) selected by the equality
** constraints.
**
** In the absence of sqlite_stat2 ANALYZE data, each range inequality
** reduces the search space by 3/4ths. Hence a single constraint (x>?)
** results in a return of 25 and a range constraint (x>? AND x<?) results
** in a return of 6.
*/
static int whereRangeScanEst(
Parse *pParse, /* Parsing & code generating context */
Index *p, /* The index containing the range-compared column; "x" */
int nEq, /* index into p->aCol[] of the range-compared column */
WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */
WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */
int *piEst /* OUT: Return value */
){
int rc = SQLITE_OK;
#ifdef SQLITE_ENABLE_STAT2
if( nEq==0 && p->aSample ){
sqlite3_value *pLowerVal = 0;
sqlite3_value *pUpperVal = 0;
int iEst;
int iLower = 0;
int iUpper = SQLITE_INDEX_SAMPLES;
int roundUpUpper = 0;
int roundUpLower = 0;
u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
if( pLower ){
Expr *pExpr = pLower->pExpr->pRight;
rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal);
assert( pLower->eOperator==WO_GT || pLower->eOperator==WO_GE );
roundUpLower = (pLower->eOperator==WO_GT) ?1:0;
}
if( rc==SQLITE_OK && pUpper ){
Expr *pExpr = pUpper->pExpr->pRight;
rc = valueFromExpr(pParse, pExpr, aff, &pUpperVal);
assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE );
roundUpUpper = (pUpper->eOperator==WO_LE) ?1:0;
}
if( rc!=SQLITE_OK || (pLowerVal==0 && pUpperVal==0) ){
sqlite3ValueFree(pLowerVal);
sqlite3ValueFree(pUpperVal);
goto range_est_fallback;
}else if( pLowerVal==0 ){
rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
if( pLower ) iLower = iUpper/2;
}else if( pUpperVal==0 ){
rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
if( pUpper ) iUpper = (iLower + SQLITE_INDEX_SAMPLES + 1)/2;
}else{
rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
if( rc==SQLITE_OK ){
rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
}
}
WHERETRACE(("range scan regions: %d..%d\n", iLower, iUpper));
iEst = iUpper - iLower;
testcase( iEst==SQLITE_INDEX_SAMPLES );
assert( iEst<=SQLITE_INDEX_SAMPLES );
if( iEst<1 ){
*piEst = 50/SQLITE_INDEX_SAMPLES;
}else{
*piEst = (iEst*100)/SQLITE_INDEX_SAMPLES;
}
sqlite3ValueFree(pLowerVal);
sqlite3ValueFree(pUpperVal);
return rc;
}
range_est_fallback:
#else
UNUSED_PARAMETER(pParse);
UNUSED_PARAMETER(p);
UNUSED_PARAMETER(nEq);
#endif
assert( pLower || pUpper );
*piEst = 100;
if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *piEst /= 4;
if( pUpper ) *piEst /= 4;
return rc;
}
#ifdef SQLITE_ENABLE_STAT2
/*
** Estimate the number of rows that will be returned based on
** an equality constraint x=VALUE and where that VALUE occurs in
** the histogram data. This only works when x is the left-most
** column of an index and sqlite_stat2 histogram data is available
** for that index. When pExpr==NULL that means the constraint is
** "x IS NULL" instead of "x=VALUE".
**
** Write the estimated row count into *pnRow and return SQLITE_OK.
** If unable to make an estimate, leave *pnRow unchanged and return
** non-zero.
**
** This routine can fail if it is unable to load a collating sequence
** required for string comparison, or if unable to allocate memory
** for a UTF conversion required for comparison. The error is stored
** in the pParse structure.
*/
static int whereEqualScanEst(
Parse *pParse, /* Parsing & code generating context */
Index *p, /* The index whose left-most column is pTerm */
Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */
double *pnRow /* Write the revised row estimate here */
){
sqlite3_value *pRhs = 0; /* VALUE on right-hand side of pTerm */
int iLower, iUpper; /* Range of histogram regions containing pRhs */
u8 aff; /* Column affinity */
int rc; /* Subfunction return code */
double nRowEst; /* New estimate of the number of rows */
assert( p->aSample!=0 );
aff = p->pTable->aCol[p->aiColumn[0]].affinity;
if( pExpr ){
rc = valueFromExpr(pParse, pExpr, aff, &pRhs);
if( rc ) goto whereEqualScanEst_cancel;
}else{
pRhs = sqlite3ValueNew(pParse->db);
}
if( pRhs==0 ) return SQLITE_NOTFOUND;
rc = whereRangeRegion(pParse, p, pRhs, 0, &iLower);
if( rc ) goto whereEqualScanEst_cancel;
rc = whereRangeRegion(pParse, p, pRhs, 1, &iUpper);
if( rc ) goto whereEqualScanEst_cancel;
WHERETRACE(("equality scan regions: %d..%d\n", iLower, iUpper));
if( iLower>=iUpper ){
nRowEst = p->aiRowEst[0]/(SQLITE_INDEX_SAMPLES*2);
if( nRowEst<*pnRow ) *pnRow = nRowEst;
}else{
nRowEst = (iUpper-iLower)*p->aiRowEst[0]/SQLITE_INDEX_SAMPLES;
*pnRow = nRowEst;
}
whereEqualScanEst_cancel:
sqlite3ValueFree(pRhs);
return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT2) */
#ifdef SQLITE_ENABLE_STAT2
/*
** Estimate the number of rows that will be returned based on
** an IN constraint where the right-hand side of the IN operator
** is a list of values. Example:
**
** WHERE x IN (1,2,3,4)
**
** Write the estimated row count into *pnRow and return SQLITE_OK.
** If unable to make an estimate, leave *pnRow unchanged and return
** non-zero.
**
** This routine can fail if it is unable to load a collating sequence
** required for string comparison, or if unable to allocate memory
** for a UTF conversion required for comparison. The error is stored
** in the pParse structure.
*/
static int whereInScanEst(
Parse *pParse, /* Parsing & code generating context */
Index *p, /* The index whose left-most column is pTerm */
ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */
double *pnRow /* Write the revised row estimate here */
){
sqlite3_value *pVal = 0; /* One value from list */
int iLower, iUpper; /* Range of histogram regions containing pRhs */
u8 aff; /* Column affinity */
int rc = SQLITE_OK; /* Subfunction return code */
double nRowEst; /* New estimate of the number of rows */
int nSpan = 0; /* Number of histogram regions spanned */
int nSingle = 0; /* Histogram regions hit by a single value */
int nNotFound = 0; /* Count of values that are not constants */
int i; /* Loop counter */
u8 aSpan[SQLITE_INDEX_SAMPLES+1]; /* Histogram regions that are spanned */
u8 aSingle[SQLITE_INDEX_SAMPLES+1]; /* Histogram regions hit once */
assert( p->aSample!=0 );
aff = p->pTable->aCol[p->aiColumn[0]].affinity;
memset(aSpan, 0, sizeof(aSpan));
memset(aSingle, 0, sizeof(aSingle));
for(i=0; i<pList->nExpr; i++){
sqlite3ValueFree(pVal);
rc = valueFromExpr(pParse, pList->a[i].pExpr, aff, &pVal);
if( rc ) break;
if( pVal==0 || sqlite3_value_type(pVal)==SQLITE_NULL ){
nNotFound++;
continue;
}
rc = whereRangeRegion(pParse, p, pVal, 0, &iLower);
if( rc ) break;
rc = whereRangeRegion(pParse, p, pVal, 1, &iUpper);
if( rc ) break;
if( iLower>=iUpper ){
aSingle[iLower] = 1;
}else{
assert( iLower>=0 && iUpper<=SQLITE_INDEX_SAMPLES );
while( iLower<iUpper ) aSpan[iLower++] = 1;
}
}
if( rc==SQLITE_OK ){
for(i=nSpan=0; i<=SQLITE_INDEX_SAMPLES; i++){
if( aSpan[i] ){
nSpan++;
}else if( aSingle[i] ){
nSingle++;
}
}
nRowEst = (nSpan*2+nSingle)*p->aiRowEst[0]/(2*SQLITE_INDEX_SAMPLES)
+ nNotFound*p->aiRowEst[1];
if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
*pnRow = nRowEst;
WHERETRACE(("IN row estimate: nSpan=%d, nSingle=%d, nNotFound=%d, est=%g\n",
nSpan, nSingle, nNotFound, nRowEst));
}
sqlite3ValueFree(pVal);
return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT2) */
/*
** Find the best query plan for accessing a particular table. Write the
** best query plan and its cost into the WhereCost object supplied as the
** last parameter.
**
** The lowest cost plan wins. The cost is an estimate of the amount of
** CPU and disk I/O needed to process the requested result.
** Factors that influence cost include:
**
** * The estimated number of rows that will be retrieved. (The
** fewer the better.)
**
** * Whether or not sorting must occur.
**
** * Whether or not there must be separate lookups in the
** index and in the main table.
**
** If there was an INDEXED BY clause (pSrc->pIndex) attached to the table in
** the SQL statement, then this function only considers plans using the
** named index. If no such plan is found, then the returned cost is
** SQLITE_BIG_DBL. If a plan is found that uses the named index,
** then the cost is calculated in the usual way.
**
** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table
** in the SELECT statement, then no indexes are considered. However, the
** selected plan may still take advantage of the built-in rowid primary key
** index.
*/
static void bestBtreeIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors not available for indexing */
Bitmask notValid, /* Cursors not available for any purpose */
ExprList *pOrderBy, /* The ORDER BY clause */
WhereCost *pCost /* Lowest cost query plan */
){
int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
Index *pProbe; /* An index we are evaluating */
Index *pIdx; /* Copy of pProbe, or zero for IPK index */
int eqTermMask; /* Current mask of valid equality operators */
int idxEqTermMask; /* Index mask of valid equality operators */
Index sPk; /* A fake index object for the primary key */
unsigned int aiRowEstPk[2]; /* The aiRowEst[] value for the sPk index */
int aiColumnPk = -1; /* The aColumn[] value for the sPk index */
int wsFlagMask; /* Allowed flags in pCost->plan.wsFlag */
/* Initialize the cost to a worst-case value */
memset(pCost, 0, sizeof(*pCost));
pCost->rCost = SQLITE_BIG_DBL;
/* If the pSrc table is the right table of a LEFT JOIN then we may not
** use an index to satisfy IS NULL constraints on that table. This is
** because columns might end up being NULL if the table does not match -
** a circumstance which the index cannot help us discover. Ticket #2177.
*/
if( pSrc->jointype & JT_LEFT ){
idxEqTermMask = WO_EQ|WO_IN;
}else{
idxEqTermMask = WO_EQ|WO_IN|WO_ISNULL;
}
if( pSrc->pIndex ){
/* An INDEXED BY clause specifies a particular index to use */
pIdx = pProbe = pSrc->pIndex;
wsFlagMask = ~(WHERE_ROWID_EQ|WHERE_ROWID_RANGE);
eqTermMask = idxEqTermMask;
}else{
/* There is no INDEXED BY clause. Create a fake Index object in local
** variable sPk to represent the rowid primary key index. Make this
** fake index the first in a chain of Index objects with all of the real
** indices to follow */
Index *pFirst; /* First of real indices on the table */
memset(&sPk, 0, sizeof(Index));
sPk.nColumn = 1;
sPk.aiColumn = &aiColumnPk;
sPk.aiRowEst = aiRowEstPk;
sPk.onError = OE_Replace;
sPk.pTable = pSrc->pTab;
aiRowEstPk[0] = pSrc->pTab->nRowEst;
aiRowEstPk[1] = 1;
pFirst = pSrc->pTab->pIndex;
if( pSrc->notIndexed==0 ){
/* The real indices of the table are only considered if the
** NOT INDEXED qualifier is omitted from the FROM clause */
sPk.pNext = pFirst;
}
pProbe = &sPk;
wsFlagMask = ~(
WHERE_COLUMN_IN|WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_RANGE
);
eqTermMask = WO_EQ|WO_IN;
pIdx = 0;
}
/* Loop over all indices looking for the best one to use
*/
for(; pProbe; pIdx=pProbe=pProbe->pNext){
const unsigned int * const aiRowEst = pProbe->aiRowEst;
double cost; /* Cost of using pProbe */
double nRow; /* Estimated number of rows in result set */
double log10N; /* base-10 logarithm of nRow (inexact) */
int rev; /* True to scan in reverse order */
int wsFlags = 0;
Bitmask used = 0;
/* The following variables are populated based on the properties of
** index being evaluated. They are then used to determine the expected
** cost and number of rows returned.
**
** nEq:
** Number of equality terms that can be implemented using the index.
** In other words, the number of initial fields in the index that
** are used in == or IN or NOT NULL constraints of the WHERE clause.
**
** nInMul:
** The "in-multiplier". This is an estimate of how many seek operations
** SQLite must perform on the index in question. For example, if the
** WHERE clause is:
**
** WHERE a IN (1, 2, 3) AND b IN (4, 5, 6)
**
** SQLite must perform 9 lookups on an index on (a, b), so nInMul is
** set to 9. Given the same schema and either of the following WHERE
** clauses:
**
** WHERE a = 1
** WHERE a >= 2
**
** nInMul is set to 1.
**
** If there exists a WHERE term of the form "x IN (SELECT ...)", then
** the sub-select is assumed to return 25 rows for the purposes of
** determining nInMul.
**
** bInEst:
** Set to true if there was at least one "x IN (SELECT ...)" term used
** in determining the value of nInMul. Note that the RHS of the
** IN operator must be a SELECT, not a value list, for this variable
** to be true.
**
** estBound:
** An estimate on the amount of the table that must be searched. A
** value of 100 means the entire table is searched. Range constraints
** might reduce this to a value less than 100 to indicate that only
** a fraction of the table needs searching. In the absence of
** sqlite_stat2 ANALYZE data, a single inequality reduces the search
** space to 1/4rd its original size. So an x>? constraint reduces
** estBound to 25. Two constraints (x>? AND x<?) reduce estBound to 6.
**
** bSort:
** Boolean. True if there is an ORDER BY clause that will require an
** external sort (i.e. scanning the index being evaluated will not
** correctly order records).
**
** bLookup:
** Boolean. True if a table lookup is required for each index entry
** visited. In other words, true if this is not a covering index.
** This is always false for the rowid primary key index of a table.
** For other indexes, it is true unless all the columns of the table
** used by the SELECT statement are present in the index (such an
** index is sometimes described as a covering index).
** For example, given the index on (a, b), the second of the following
** two queries requires table b-tree lookups in order to find the value
** of column c, but the first does not because columns a and b are
** both available in the index.
**
** SELECT a, b FROM tbl WHERE a = 1;
** SELECT a, b, c FROM tbl WHERE a = 1;
*/
int nEq; /* Number of == or IN terms matching index */
int bInEst = 0; /* True if "x IN (SELECT...)" seen */
int nInMul = 1; /* Number of distinct equalities to lookup */
int estBound = 100; /* Estimated reduction in search space */
int nBound = 0; /* Number of range constraints seen */
int bSort = 0; /* True if external sort required */
int bLookup = 0; /* True if not a covering index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
#ifdef SQLITE_ENABLE_STAT2
WhereTerm *pFirstTerm = 0; /* First term matching the index */
#endif
/* Determine the values of nEq and nInMul */
for(nEq=0; nEq<pProbe->nColumn; nEq++){
int j = pProbe->aiColumn[nEq];
pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
if( pTerm==0 ) break;
wsFlags |= (WHERE_COLUMN_EQ|WHERE_ROWID_EQ);
if( pTerm->eOperator & WO_IN ){
Expr *pExpr = pTerm->pExpr;
wsFlags |= WHERE_COLUMN_IN;
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
/* "x IN (SELECT ...)": Assume the SELECT returns 25 rows */
nInMul *= 25;
bInEst = 1;
}else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
/* "x IN (value, value, ...)" */
nInMul *= pExpr->x.pList->nExpr;
}
}else if( pTerm->eOperator & WO_ISNULL ){
wsFlags |= WHERE_COLUMN_NULL;
}
#ifdef SQLITE_ENABLE_STAT2
if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm;
#endif
used |= pTerm->prereqRight;
}
/* Determine the value of estBound. */
if( nEq<pProbe->nColumn && pProbe->bUnordered==0 ){
int j = pProbe->aiColumn[nEq];
if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){
WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx);
WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx);
whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &estBound);
if( pTop ){
nBound = 1;
wsFlags |= WHERE_TOP_LIMIT;
used |= pTop->prereqRight;
}
if( pBtm ){
nBound++;
wsFlags |= WHERE_BTM_LIMIT;
used |= pBtm->prereqRight;
}
wsFlags |= (WHERE_COLUMN_RANGE|WHERE_ROWID_RANGE);
}
}else if( pProbe->onError!=OE_None ){
testcase( wsFlags & WHERE_COLUMN_IN );
testcase( wsFlags & WHERE_COLUMN_NULL );
if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){
wsFlags |= WHERE_UNIQUE;
}
}
/* If there is an ORDER BY clause and the index being considered will
** naturally scan rows in the required order, set the appropriate flags
** in wsFlags. Otherwise, if there is an ORDER BY clause but the index
** will scan rows in a different order, set the bSort variable. */
if( pOrderBy ){
if( (wsFlags & WHERE_COLUMN_IN)==0
&& pProbe->bUnordered==0
&& isSortingIndex(pParse, pWC->pMaskSet, pProbe, iCur, pOrderBy,
nEq, wsFlags, &rev)
){
wsFlags |= WHERE_ROWID_RANGE|WHERE_COLUMN_RANGE|WHERE_ORDERBY;
wsFlags |= (rev ? WHERE_REVERSE : 0);
}else{
bSort = 1;
}
}
/* If currently calculating the cost of using an index (not the IPK
** index), determine if all required column data may be obtained without
** using the main table (i.e. if the index is a covering
** index for this query). If it is, set the WHERE_IDX_ONLY flag in
** wsFlags. Otherwise, set the bLookup variable to true. */
if( pIdx && wsFlags ){
Bitmask m = pSrc->colUsed;
int j;
for(j=0; j<pIdx->nColumn; j++){
int x = pIdx->aiColumn[j];
if( x<BMS-1 ){
m &= ~(((Bitmask)1)<<x);
}
}
if( m==0 ){
wsFlags |= WHERE_IDX_ONLY;
}else{
bLookup = 1;
}
}
/*
** Estimate the number of rows of output. For an "x IN (SELECT...)"
** constraint, do not let the estimate exceed half the rows in the table.
*/
nRow = (double)(aiRowEst[nEq] * nInMul);
if( bInEst && nRow*2>aiRowEst[0] ){
nRow = aiRowEst[0]/2;
nInMul = (int)(nRow / aiRowEst[nEq]);
}
#ifdef SQLITE_ENABLE_STAT2
/* If the constraint is of the form x=VALUE and histogram
** data is available for column x, then it might be possible
** to get a better estimate on the number of rows based on
** VALUE and how common that value is according to the histogram.
*/
if( nRow>(double)1 && nEq==1 && pFirstTerm!=0 ){
if( pFirstTerm->eOperator & (WO_EQ|WO_ISNULL) ){
testcase( pFirstTerm->eOperator==WO_EQ );
testcase( pFirstTerm->eOperator==WO_ISNULL );
whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight, &nRow);
}else if( pFirstTerm->eOperator==WO_IN && bInEst==0 ){
whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList, &nRow);
}
}
#endif /* SQLITE_ENABLE_STAT2 */
/* Adjust the number of output rows and downward to reflect rows
** that are excluded by range constraints.
*/
nRow = (nRow * (double)estBound) / (double)100;
if( nRow<1 ) nRow = 1;
/* Experiments run on real SQLite databases show that the time needed
<