SQLite

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible 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".
**
** $Id: where.c,v 1.145 2005/07/16 13:33:21 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8-1)

/*
** Determine the number of elements in an array.
*/
#define ARRAYSIZE(X)  (sizeof(X)/sizeof(X[0]))


/*
** 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 an AND operator.
**
** The idxLeft and idxRight fields are the VDBE cursor numbers for the
** table that contains the column that appears on the left-hand and
** right-hand side of WhereTerm.p.  If either side of WhereTerm.p is
** something other than a simple column reference, then idxLeft or
** idxRight are -1.  
**
** It is the VDBE cursor number is the value stored in Expr.iTable
** when Expr.op==TK_COLUMN and the value stored in SrcList.a[].iCursor.
**
** prereqLeft, prereqRight, and prereqAll record sets of cursor numbers,
** but they do so indirectly.  A single ExprMaskSet 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 ExprMaskSet
** 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.
**
** prereqLeft tells us every VDBE cursor that is referenced on the
** left-hand side of WhereTerm.p.  prereqRight does the same for the
** right-hand side of the expression.  The following identity always
** holds:
**
**       prereqAll = prereqLeft | prereqRight
**
** The WhereTerm.indexable field is true if the WhereTerm.p expression
** is of a form that might control an index.  Indexable expressions
** look like this:
**
**              <column> <op> <expr>
**
** Where <column> is a simple column name and <op> is on of the operators
** that allowedOp() recognizes.  
*/
typedef struct WhereTerm WhereTerm;
struct WhereTerm {
  Expr *p;                /* Pointer to the subexpression */
  u16 flags;              /* Bit flags.  See below */
  u8 indexable;           /* True if this subexprssion is usable by an index */
  short int idxLeft;      /* p->pLeft is a column in this table number. -1 if
                          ** p->pLeft is not a column of any table */
  short int idxRight;     /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not a column of any table */
  Bitmask prereqLeft;     /* Bitmask of tables referenced by p->pLeft */
  Bitmask prereqRight;    /* Bitmask of tables referenced by p->pRight */
  Bitmask prereqAll;      /* Bitmask of tables referenced by p */
};

/*
** Allowed values of WhereTerm.flags
*/
#define TERM_DYNAMIC    0x0001   /* Need to call sqlite3ExprDelete(p) */
#define TERM_VIRTUAL    0x0002   /* Added by the optimizer.  Do not code */

/*
** An instance of the following structure holds all information about a
** WHERE clause.  Mostly this is a container for one or more WhereTerms.
*/
typedef struct WhereClause WhereClause;
struct WhereClause {
  int nTerm;               /* Number of terms */
  int nSlot;               /* Number of entries in a[] */
  WhereTerm *a;            /* Pointer to an array of terms */
  WhereTerm aStatic[10];   /* Initial static space for the terms */
};

/*
** 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

*/
typedef struct ExprMaskSet ExprMaskSet;
struct ExprMaskSet {
  int n;                        /* Number of assigned cursor values */
  int ix[sizeof(Bitmask)*8];    /* Cursor assigned to each bit */
};


/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(WhereClause *pWC){
  pWC->nTerm = 0;
  pWC->nSlot = ARRAYSIZE(pWC->aStatic);
  pWC->a = pWC->aStatic;
}

/*
** 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;
  for(i=pWC->nTerm-1, a=pWC->a; i>=0; i--, a++){
    if( a->flags & TERM_DYNAMIC ){
      sqlite3ExprDelete(a->p);
    }
  }
  if( pWC->a!=pWC->aStatic ){
    sqliteFree(pWC->a);
  }
}

/*
** Add a new entries to the WhereClause structure.  Increase the allocated
** space as necessary.
*/
static void whereClauseInsert(WhereClause *pWC, Expr *p, int flags){
  WhereTerm *pTerm;
  if( pWC->nTerm>=pWC->nSlot ){
    WhereTerm *pOld = pWC->a;
    pWC->a = sqliteMalloc( sizeof(pWC->a[0])*pWC->nSlot*2 );
    if( pWC->a==0 ) return;
    memcpy(pWC->a, pOld, sizeof(pWC->a[0])*pWC->nTerm);
    if( pOld!=pWC->aStatic ){
      sqliteFree(pOld);
    }
    pWC->nSlot *= 2;
  }
  pTerm = &pWC->a[pWC->nTerm++];
  pTerm->p = p;
  pTerm->flags = flags;
}

/*
** This routine identifies subexpressions in the WHERE clause where
** each subexpression is separate by the AND operator.  aSlot is 
** filled with pointers to the 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 aSlot[] entries point to substructure within pExpr.
**
** aSlot[] is an array of subexpressions structures.  There are nSlot
** spaces left in this array.  This routine finds as many AND-separated
** subexpressions as it can and puts pointers to those subexpressions
** into aSlot[] entries.  The return value is the number of slots filled.
*/
static void whereSplit(WhereClause *pWC, Expr *pExpr){

  if( pExpr==0 ) return;
  if( pExpr->op!=TK_AND ){
    whereClauseInsert(pWC, pExpr, 0);





  }else{
    whereSplit(pWC, pExpr->pLeft);
    whereSplit(pWC, pExpr->pRight);
  }

}

/*
** Initialize an expression mask set
*/
#define initMaskSet(P)  memset(P, 0, sizeof(*P))

  for(i=0, pItem=pList->a; i<pList->nSrc; i++, pItem++){
    if( pItem->iCursor==iCur ) return i;
  }
  return -1;
}

/*
** The input to this routine is an WhereTerm structure with only the
** "p" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the WhereTerm
** structure.
*/
static void exprAnalyze(SrcList *pSrc, ExprMaskSet *pMaskSet, WhereTerm *pInfo){
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(pMaskSet, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
  pInfo->prereqAll = exprTableUsage(pMaskSet, pExpr);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;

/*
** Generate code for an equality term of the WHERE clause.  An equality
** term can be either X=expr  or X IN (...).   pTerm is the X.  
*/
static void codeEqualityTerm(
  Parse *pParse,      /* The parsing context */
  WhereTerm *pTerm,    /* The term of the WHERE clause to be coded */
  int brk,            /* Jump here to abandon the loop */
  WhereLevel *pLevel  /* When level of the FROM clause we are working on */
){
  Expr *pX = pTerm->p;
  if( pX->op!=TK_IN ){
    assert( pX->op==TK_EQ );
    sqlite3ExprCode(pParse, pX->pRight);

    pLevel->inOp = OP_Next;
    pLevel->inP1 = iTab;
#endif
  }
  disableTerm(pLevel, &pTerm->p);
}






#ifdef SQLITE_TEST
/*
** The following variable holds a text description of query plan generated
** by the most recent call to sqlite3WhereBegin().  Each call to WhereBegin
** overwrites the previous.  This information is used for testing and
** analysis only.
*/

  Expr *pWhere,         /* The WHERE clause */
  ExprList **ppOrderBy  /* An ORDER BY clause, or NULL */
){
  int i;                     /* Loop counter */
  WhereInfo *pWInfo;         /* Will become the return value of this function */
  Vdbe *v = pParse->pVdbe;   /* The virtual database engine */
  int brk, cont = 0;         /* Addresses used during code generation */

  Bitmask loopMask;          /* One bit set for each outer loop */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
  ExprMaskSet maskSet;       /* The expression mask set */
  int iDirectEq[BMS];        /* Term of the form ROWID==X for the N-th table */
  int iDirectLt[BMS];        /* Term of the form ROWID<X or ROWID<=X */
  int iDirectGt[BMS];        /* Term of the form ROWID>X or ROWID>=X */
  WhereClause wc;            /* The WHERE clause is divided into these terms */
  struct SrcList_item *pTabItem;  /* A single entry from pTabList */
  WhereLevel *pLevel;             /* A single level in the pWInfo list */

  /* The number of terms in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>sizeof(Bitmask)*8 ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join",
       sizeof(Bitmask)*8);
    return 0;
  }

  /* Split the WHERE clause into separate subexpressions where each
  ** subexpression is separated by an AND operator.  If the wc.a[]
  ** array fills up, the last entry might point to an expression which
  ** contains additional unfactored AND operators.
  */
  initMaskSet(&maskSet);
  whereClauseInit(&wc);
  whereSplit(&wc, pWhere);





    
  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
  */
  pWInfo = sqliteMalloc( sizeof(WhereInfo) + pTabList->nSrc*sizeof(WhereLevel));
  if( sqlite3_malloc_failed ){
    sqliteFree(pWInfo); /* Avoid leaking memory when malloc fails */
    whereClauseClear(&wc);
    return 0;
  }
  pWInfo->pParse = pParse;
  pWInfo->pTabList = pTabList;
  pWInfo->iBreak = sqlite3VdbeMakeLabel(v);

  /* Special case: a WHERE clause that is constant.  Evaluate the
  ** expression and either jump over all of the code or fall thru.
  */
  if( pWhere && (pTabList->nSrc==0 || sqlite3ExprIsConstant(pWhere)) ){
    sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, 1);
    pWhere = 0;
  }

  /* Analyze all of the subexpressions.
  */
  for(i=0; i<pTabList->nSrc; i++){
    createMask(&maskSet, pTabList->a[i].iCursor);
  }
  for(pTerm=wc.a, i=0; i<wc.nTerm; i++, pTerm++){
    exprAnalyze(pTabList, &maskSet, pTerm);
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make pWInfo->a[i].pIdx point to the index to use for the i-th nested
  ** loop where i==0 is the outer loop and i==pTabList->nSrc-1 is the inner
  ** loop. 

    **
    ** (Added:) Treat ROWID IN expr like ROWID=expr.
    */
    pLevel->iIdxCur = -1;
    iDirectEq[i] = -1;
    iDirectLt[i] = -1;
    iDirectGt[i] = -1;
    for(pTerm=wc.a, j=0; j<wc.nTerm; j++, pTerm++){
      Expr *pX = pTerm->p;
      if( pTerm->idxLeft==iCur && pX->pLeft->iColumn<0
            && (pTerm->prereqRight & loopMask)==pTerm->prereqRight ){
        switch( pX->op ){
          case TK_IN:
          case TK_EQ: iDirectEq[i] = j; break;
          case TK_LE:

      Bitmask inMask = 0;  /* Index columns covered by an x IN .. term */
      Bitmask m;
      int nEq, score, bRev = 0;

      if( pIdx->nColumn>sizeof(eqMask)*8 ){
        continue;  /* Ignore indices with too many columns to analyze */
      }
      for(pTerm=wc.a, j=0; j<wc.nTerm; j++, pTerm++){
        Expr *pX = pTerm->p;
        CollSeq *pColl = sqlite3ExprCollSeq(pParse, pX->pLeft);
        if( !pColl && pX->pRight ){
          pColl = sqlite3ExprCollSeq(pParse, pX->pRight);
        }
        if( !pColl ){
          pColl = pParse->db->pDfltColl;

    if( i<ARRAYSIZE(iDirectEq) && (k = iDirectEq[i])>=0 ){
      /* Case 1:  We can directly reference a single row using an
      **          equality comparison against the ROWID field.  Or
      **          we reference multiple rows using a "rowid IN (...)"
      **          construct.
      */
      assert( k<wc.nTerm );
      pTerm = &wc.a[k];
      assert( pTerm->p!=0 );
      assert( pTerm->idxLeft==iCur );
      assert( omitTable==0 );
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);
      codeEqualityTerm(pParse, pTerm, brk, pLevel);
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_MustBeInt, 1, brk);
      sqlite3VdbeAddOp(v, OP_NotExists, iCur, brk);
      VdbeComment((v, "pk"));
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score>3 && (pLevel->score&0x0c)==0 ){
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index using the "==" or "IN" operators.
      */
      int start;
      int nColumn = (pLevel->score+16)/32;
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);

      /* For each column of the index, find the term of the WHERE clause that
      ** constraints that column.  If the WHERE clause term is X=expr, then
      ** generate code to evaluate expr and leave the result on the stack */
      for(j=0; j<nColumn; j++){
        for(pTerm=wc.a, k=0; k<wc.nTerm; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
             && (pX->op==TK_EQ || pX->op==TK_IN)
          ){

        int t = iDirectGt[i];
        iDirectGt[i] = iDirectLt[i];
        iDirectLt[i] = t;
      }
      if( iDirectGt[i]>=0 ){
        Expr *pX;
        k = iDirectGt[i];
        assert( k<wc.nTerm );
        pTerm = &wc.a[k];
        pX = pTerm->p;
        assert( pX!=0 );
        assert( pTerm->idxLeft==iCur );
        sqlite3ExprCode(pParse, pX->pRight);
        sqlite3VdbeAddOp(v, OP_ForceInt, pX->op==TK_LE || pX->op==TK_GT, brk);
        sqlite3VdbeAddOp(v, bRev ? OP_MoveLt : OP_MoveGe, iCur, brk);
        VdbeComment((v, "pk"));
        disableTerm(pLevel, &pTerm->p);
      }else{
        sqlite3VdbeAddOp(v, bRev ? OP_Last : OP_Rewind, iCur, brk);
      }
      if( iDirectLt[i]>=0 ){
        Expr *pX;
        k = iDirectLt[i];
        assert( k<wc.nTerm );
        pTerm = &wc.a[k];
        pX = pTerm->p;
        assert( pX!=0 );
        assert( pTerm->idxLeft==iCur );
        sqlite3ExprCode(pParse, pX->pRight);
        pLevel->iMem = pParse->nMem++;
        sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
        if( pX->op==TK_LT || pX->op==TK_GT ){

      int leFlag=0, geFlag=0;
      int testOp;

      /* Evaluate the equality constraints
      */
      for(j=0; j<nEqColumn; j++){
        int iIdxCol = pIdx->aiColumn[j];
        for(pTerm=wc.a, k=0; k<wc.nTerm; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && pX->op==TK_EQ
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==iIdxCol
          ){

      ** will end the search.  There is no termination key if there
      ** are no equality terms and no "X<..." term.
      **
      ** 2002-Dec-04: On a reverse-order scan, the so-called "termination"
      ** key computed here really ends up being the start key.
      */
      if( (score & 4)!=0 ){
        for(pTerm=wc.a, k=0; k<wc.nTerm; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && (pX->op==TK_LT || pX->op==TK_LE)
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){

      ** that case, generate a "Rewind" instruction in place of the
      ** start key search.
      **
      ** 2002-Dec-04: In the case of a reverse-order search, the so-called
      ** "start" key really ends up being used as the termination key.
      */
      if( (score & 8)!=0 ){
        for(pTerm=wc.a, k=0; k<wc.nTerm; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && (pX->op==TK_GT || pX->op==TK_GE)
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){

      pLevel->p2 = start;
    }
    loopMask |= getMask(&maskSet, iCur);

    /* Insert code to test every subexpression that can be completely
    ** computed using the current set of tables.
    */
    for(pTerm=wc.a, j=0; j<wc.nTerm; j++, pTerm++){
      Expr *pE = pTerm->p;
      if( pE==0 || ExprHasProperty(pE, EP_OptOnly) ) continue;
      if( (pTerm->prereqAll & loopMask)!=pTerm->prereqAll ) continue;
      if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
        continue;
      }
      sqlite3ExprIfFalse(pParse, pE, cont, 1);
      pTerm->p = 0;
    }
    brk = cont;

    /* For a LEFT OUTER JOIN, generate code that will record the fact that
    ** at least one row of the right table has matched the left table.  
    */
    if( pLevel->iLeftJoin ){
      pLevel->top = sqlite3VdbeCurrentAddr(v);
      sqlite3VdbeAddOp(v, OP_Integer, 1, 0);
      sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iLeftJoin, 1);
      VdbeComment((v, "# record LEFT JOIN hit"));
      for(pTerm=wc.a, j=0; j<wc.nTerm; j++, pTerm++){
        Expr *pE = pTerm->p;
        if( pE==0 || ExprHasProperty(pE, EP_OptOnly) ) continue;
        if( (pTerm->prereqAll & loopMask)!=pTerm->prereqAll ) continue;
        sqlite3ExprIfFalse(pParse, pE, cont, 1);
        pTerm->p = 0;
      }
    }
  }
  pWInfo->iContinue = cont;
  freeMaskSet(&maskSet);
  whereClauseClear(&wc);
  return pWInfo;
}

/*
** Generate the end of the WHERE loop.  See comments on 
** sqlite3WhereBegin() for additional information.
*/

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.35 2005/07/16 13:33:21 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {

    SELECT * FROM t1;
  }
} {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}}

# A WHERE clause is not allowed to contain more than 99 terms.  Check to
# make sure this limit is enforced.
#
# 2005-07-16: There is no longer a limit on the number of terms in a
# WHERE clause.  But keep these tests just so that we have some tests
# that use a large number of terms in the WHERE clause.
#
do_test misc1-10.0 {
  execsql {SELECT count(*) FROM manycol}
} {9}
do_test misc1-10.1 {
  set ::where {WHERE x0>=0}
  for {set i 1} {$i<=99} {incr i} {
    append ::where " AND x$i<>0"
  }
  catchsql "SELECT count(*) FROM manycol $::where"
} {0 9}
do_test misc1-10.2 {
  catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
} {0 9}
do_test misc1-10.3 {
  regsub "x0>=0" $::where "x0=0" ::where
  catchsql "DELETE FROM manycol $::where"
} {0 {}}
do_test misc1-10.4 {
  execsql {SELECT count(*) FROM manycol}
} {8}
do_test misc1-10.5 {
  catchsql "DELETE FROM manycol $::where AND rowid>0"
} {0 {}}
do_test misc1-10.6 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {101}
do_test misc1-10.7 {
  regsub "x0=0" $::where "x0=100" ::where
  catchsql "UPDATE manycol SET x1=x1+1 $::where"
} {0 {}}
do_test misc1-10.8 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {102}
do_test misc1-10.9 {
  catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
} {0 {}}
do_test misc1-10.10 {
  execsql {SELECT x1 FROM manycol WHERE x0=100}
} {103}

# Make sure the initialization works even if a database is opened while
# another process has the database locked.
#
# Update for v3: The BEGIN doesn't lock the database so the schema is read
# and the SELECT returns successfully.
do_test misc1-11.1 {