**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.396 2005/07/21 18:23:20 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  Index *pIdx;          /* Index used.  NULL if no index */
  int iTabCur;          /* The VDBE cursor used to access the table */
  int iIdxCur;          /* The VDBE cursor used to acesss pIdx */
  int brk;              /* Jump here to break out of the loop */
  int cont;             /* Jump here to continue with the next loop cycle */
  int top;              /* First instruction of interior of the loop */
  int op, p1, p2;       /* Opcode used to terminate the loop */
  int inOp, inP1, inP2; /* Opcode used to implement an IN operator */

};

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed

**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.397 2005/07/22 00:31:40 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks
................................................................................
  Index *pIdx;          /* Index used.  NULL if no index */
  int iTabCur;          /* The VDBE cursor used to access the table */
  int iIdxCur;          /* The VDBE cursor used to acesss pIdx */
  int brk;              /* Jump here to break out of the loop */
  int cont;             /* Jump here to continue with the next loop cycle */
  int top;              /* First instruction of interior of the loop */
  int op, p1, p2;       /* Opcode used to terminate the loop */
  int nIn;              /* Number of IN operators constraining this loop */
  int *aInLoop;         /* Loop terminators for IN operators */
};

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
** this structure is returned by the first half and passed

** 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.150 2005/07/21 18:23:20 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
struct WhereClause {
  Parse *pParse;           /* The parser context */
  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 
................................................................................
** array will never overflow.
*/
static void createMask(ExprMaskSet *pMaskSet, int iCursor){
  assert( pMaskSet->n < ARRAYSIZE(pMaskSet->ix) );
  pMaskSet->ix[pMaskSet->n++] = iCursor;
}

/*
** Destroy an expression mask set
*/
#define freeMaskSet(P)   /* NO-OP */

/*
** 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 sqlite3ExprResolveNames() on the expression.  See
................................................................................
  }
  return 0;
}

/*
** Value for flags returned by bestIndex()
*/
#define WHERE_ROWID_EQ       0x001    /* rowid=EXPR or rowid IN (...) */
#define WHERE_ROWID_RANGE    0x002    /* rowid<EXPR and/or rowid>EXPR */
#define WHERE_COLUMN_EQ      0x004    /* x=EXPR or x IN (...) */
#define WHERE_COLUMN_RANGE   0x008    /* x<EXPR and/or x>EXPR */
#define WHERE_SCAN           0x010    /* Do a full table scan */
#define WHERE_REVERSE        0x020    /* Scan in reverse order */
#define WHERE_ORDERBY        0x040    /* Output will appear in correct order */
#define WHERE_IDX_ONLY       0x080    /* Use index only - omit table */
#define WHERE_TOP_LIMIT      0x100    /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT      0x200    /* x>EXPR or x>=EXPR constraint */



/*
** Find the best index for accessing a particular table.  Return the index,
** flags that describe how the index should be used, and the "score" for
** this index.
*/
static double bestIndex(
................................................................................
  if( pTerm ){
    *ppIndex = 0;
    if( pTerm->operator & WO_EQ ){
      *pFlags = WHERE_ROWID_EQ;
      if( pOrderBy ) *pFlags |= WHERE_ORDERBY;
      return 1.0e10;
    }else{
      *pFlags = WHERE_ROWID_EQ;
      return 1.0e9;
    }
  }

  /* Check for constraints on a range of rowids
  */
  pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
................................................................................
      }
    }
    nEq = i + usesIN;
    score = i*100.0 + usesIN*50.0;

    /* The optimization type is RANGE if there are no == or IN constraints
    */
    if( usesIN || nEq ){


      flags = WHERE_COLUMN_EQ;
    }else{
      flags = WHERE_COLUMN_RANGE;
    }









    /* Look for range constraints
    */
    if( !usesIN && nEq<pProbe->nColumn ){
      int j = pProbe->aiColumn[nEq];
      pTerm = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pProbe);
      if( pTerm ){
................................................................................

/*
** 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->pExpr;
  if( pX->op!=TK_IN ){
    assert( pX->op==TK_EQ );
    sqlite3ExprCode(pParse, pX->pRight);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int iTab;

    Vdbe *v = pParse->pVdbe;

    sqlite3CodeSubselect(pParse, pX);
    iTab = pX->iTable;
    sqlite3VdbeAddOp(v, OP_Rewind, iTab, brk);
    VdbeComment((v, "# %.*s", pX->span.n, pX->span.z));
    pLevel->inP2 = sqlite3VdbeAddOp(v, OP_Column, iTab, 0);




    pLevel->inOp = OP_Next;
    pLevel->inP1 = iTab;


#endif
  }
  disableTerm(pLevel, pTerm);
}

#ifdef SQLITE_TEST
/*
................................................................................
  Bitmask notReady;          /* Cursors that are not yet positioned */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
  ExprMaskSet maskSet;       /* The expression mask set */
  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 */
  int iFrom;                      /* First unused FROM clause element */


  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>BMS ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
    return 0;
................................................................................
  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.
................................................................................
  ** and work forward so that they added virtual terms are never processed.
  */
  for(i=0; i<pTabList->nSrc; i++){
    createMask(&maskSet, pTabList->a[i].iCursor);
  }
  for(i=wc.nTerm-1; i>=0; i--){
    exprAnalyze(pTabList, &maskSet, &wc.a[i]);




  }

  /* Chose the best index to use for each table in the FROM clause.
  **
  ** This loop fills in the pWInfo->a[].pIdx and pWInfo->a[].flags fields
  ** with information
  ** Reorder tables if necessary in order to choose a good ordering.
................................................................................
      }
    }
    if( bestFlags & WHERE_ORDERBY ){
      *ppOrderBy = 0;
    }
    pLevel->flags = bestFlags;
    pLevel->pIdx = pBest;


    if( pBest ){
      pLevel->iIdxCur = pParse->nTab++;
    }else{
      pLevel->iIdxCur = -1;
    }
    notReady &= ~getMask(&maskSet, pTabList->a[bestJ].iCursor);
    pLevel->iFrom = bestJ;
................................................................................
    int omitTable;     /* True if we use the index only */
    int bRev;          /* True if we need to scan in reverse order */

    pTabItem = &pTabList->a[pLevel->iFrom];
    iCur = pTabItem->iCursor;
    pIdx = pLevel->pIdx;
    iIdxCur = pLevel->iIdxCur;
    pLevel->inOp = OP_Noop;
    bRev = (pLevel->flags & WHERE_REVERSE)!=0;
    omitTable = (pLevel->flags & WHERE_IDX_ONLY)!=0;

    /* Create labels for the "break" and "continue" instructions
    ** for the current loop.  Jump to brk to break out of a loop.
    ** Jump to cont to go immediately to the next iteration of the
    ** loop.
................................................................................
  nQPlan = 0;
#endif /* SQLITE_TEST // Testing and debugging use only */

  /* Record the continuation address in the WhereInfo structure.  Then
  ** clean up and return.
  */
  pWInfo->iContinue = cont;
  freeMaskSet(&maskSet);
  whereClauseClear(&wc);

  return pWInfo;






}

/*
** Generate the end of the WHERE loop.  See comments on 
** sqlite3WhereBegin() for additional information.
*/
void sqlite3WhereEnd(WhereInfo *pWInfo){
................................................................................
  for(i=pTabList->nSrc-1; i>=0; i--){
    pLevel = &pWInfo->a[i];
    sqlite3VdbeResolveLabel(v, pLevel->cont);
    if( pLevel->op!=OP_Noop ){
      sqlite3VdbeAddOp(v, pLevel->op, pLevel->p1, pLevel->p2);
    }
    sqlite3VdbeResolveLabel(v, pLevel->brk);
    if( pLevel->inOp!=OP_Noop ){



      sqlite3VdbeAddOp(v, pLevel->inOp, pLevel->inP1, pLevel->inP2);


    }
    if( pLevel->iLeftJoin ){
      int addr;
      addr = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iLeftJoin, 0);
      sqlite3VdbeAddOp(v, OP_NotNull, 1, addr+4 + (pLevel->iIdxCur>=0));
      sqlite3VdbeAddOp(v, OP_NullRow, pTabList->a[i].iCursor, 0);
      if( pLevel->iIdxCur>=0 ){

** 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.151 2005/07/22 00:31:40 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
struct WhereClause {
  Parse *pParse;           /* The parser context */
  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 */
};

/*
** When WhereTerms are used to select elements from an index, we
** call those terms "constraints".  For example, consider the following
** SQL:
**
**       CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
**       CREATE INDEX t1i1 ON t1(b,c);
**
**       SELECT * FROM t1 WHERE d=5 AND b=7 AND c>11;
**
** In the SELECT statement, the "b=7" and "c>11" terms are constraints
** because they can be used to choose rows out of the t1i1 index.  But
** the "d=5" term is not a constraint because it is not indexed.
**
** When generating code to access an index, we have to keep track of
** all of the constraints associated with that index.  This is done
** using an array of instanaces of the following structure.  There is
** one instance of this structure for each constraint on the index.
**
** Actually, we allocate the array of this structure based on the total
** number of terms in the entire WHERE clause (because the number of
** constraints can never be more than that) and reuse it when coding
** each index.
*/
typedef struct WhereConstraint WhereConstraint;
struct WhereConstraint {
  int iMem;            /* Mem cell used to hold <expr> part of constraint */
};

/*
** 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 
................................................................................
** array will never overflow.
*/
static void createMask(ExprMaskSet *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 sqlite3ExprResolveNames() on the expression.  See
................................................................................
  }
  return 0;
}

/*
** Value for flags returned by bestIndex()
*/
#define WHERE_ROWID_EQ       0x0001   /* rowid=EXPR or rowid IN (...) */
#define WHERE_ROWID_RANGE    0x0002   /* rowid<EXPR and/or rowid>EXPR */
#define WHERE_COLUMN_EQ      0x0004   /* x=EXPR or x IN (...) */
#define WHERE_COLUMN_RANGE   0x0008   /* x<EXPR and/or x>EXPR */
#define WHERE_SCAN           0x0010   /* Do a full table scan */
#define WHERE_REVERSE        0x0020   /* Scan in reverse order */
#define WHERE_ORDERBY        0x0040   /* Output will appear in correct order */
#define WHERE_IDX_ONLY       0x0080   /* Use index only - omit table */
#define WHERE_TOP_LIMIT      0x0100   /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT      0x0200   /* x>EXPR or x>=EXPR constraint */
#define WHERE_USES_IN        0x0400   /* True if the IN operator is used */
#define WHERE_UNIQUE         0x0800   /* True if fully specifies a unique idx */

/*
** Find the best index for accessing a particular table.  Return the index,
** flags that describe how the index should be used, and the "score" for
** this index.
*/
static double bestIndex(
................................................................................
  if( pTerm ){
    *ppIndex = 0;
    if( pTerm->operator & WO_EQ ){
      *pFlags = WHERE_ROWID_EQ;
      if( pOrderBy ) *pFlags |= WHERE_ORDERBY;
      return 1.0e10;
    }else{
      *pFlags = WHERE_ROWID_EQ | WHERE_USES_IN;
      return 1.0e9;
    }
  }

  /* Check for constraints on a range of rowids
  */
  pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
................................................................................
      }
    }
    nEq = i + usesIN;
    score = i*100.0 + usesIN*50.0;

    /* The optimization type is RANGE if there are no == or IN constraints
    */
    if( usesIN ){
      flags = WHERE_COLUMN_EQ | WHERE_USES_IN;
    }else if( nEq ){
      flags = WHERE_COLUMN_EQ;
    }else{
      flags = WHERE_COLUMN_RANGE;
    }

    /* Check for a uniquely specified row
    */
#if 0
    if( nEq==pProbe->nColumn && pProbe->isUnique ){
      flags |= WHERE_UNIQUE;
    }
#endif

    /* Look for range constraints
    */
    if( !usesIN && nEq<pProbe->nColumn ){
      int j = pProbe->aiColumn[nEq];
      pTerm = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pProbe);
      if( pTerm ){
................................................................................

/*
** 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->pExpr;
  if( pX->op!=TK_IN ){
    assert( pX->op==TK_EQ );
    sqlite3ExprCode(pParse, pX->pRight);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int iTab;
    int *aIn;
    Vdbe *v = pParse->pVdbe;

    sqlite3CodeSubselect(pParse, pX);
    iTab = pX->iTable;
    sqlite3VdbeAddOp(v, OP_Rewind, iTab, brk);
    VdbeComment((v, "# %.*s", pX->span.n, pX->span.z));
    pLevel->nIn++;
    pLevel->aInLoop = aIn = sqliteRealloc(pLevel->aInLoop,
                                 sizeof(pLevel->aInLoop[0])*3*pLevel->nIn);
    if( aIn ){
      aIn += pLevel->nIn*3 - 3;
      aIn[0] = OP_Next;
      aIn[1] = iTab;
      aIn[2] = sqlite3VdbeAddOp(v, OP_Column, iTab, 0);
    }
#endif
  }
  disableTerm(pLevel, pTerm);
}

#ifdef SQLITE_TEST
/*
................................................................................
  Bitmask notReady;          /* Cursors that are not yet positioned */
  WhereTerm *pTerm;          /* A single term in the WHERE clause */
  ExprMaskSet maskSet;       /* The expression mask set */
  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 */
  int iFrom;                      /* First unused FROM clause element */
  WhereConstraint *aConstraint;   /* Information on constraints */

  /* The number of tables in the FROM clause is limited by the number of
  ** bits in a Bitmask 
  */
  if( pTabList->nSrc>BMS ){
    sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
    return 0;
................................................................................
  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 ){
    goto whereBeginNoMem;


  }
  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.
................................................................................
  ** and work forward so that they added virtual terms are never processed.
  */
  for(i=0; i<pTabList->nSrc; i++){
    createMask(&maskSet, pTabList->a[i].iCursor);
  }
  for(i=wc.nTerm-1; i>=0; i--){
    exprAnalyze(pTabList, &maskSet, &wc.a[i]);
  }
  aConstraint = sqliteMalloc( wc.nTerm*sizeof(aConstraint[0]) );
  if( aConstraint==0 && wc.nTerm>0 ){
    goto whereBeginNoMem;
  }

  /* Chose the best index to use for each table in the FROM clause.
  **
  ** This loop fills in the pWInfo->a[].pIdx and pWInfo->a[].flags fields
  ** with information
  ** Reorder tables if necessary in order to choose a good ordering.
................................................................................
      }
    }
    if( bestFlags & WHERE_ORDERBY ){
      *ppOrderBy = 0;
    }
    pLevel->flags = bestFlags;
    pLevel->pIdx = pBest;
    pLevel->aInLoop = 0;
    pLevel->nIn = 0;
    if( pBest ){
      pLevel->iIdxCur = pParse->nTab++;
    }else{
      pLevel->iIdxCur = -1;
    }
    notReady &= ~getMask(&maskSet, pTabList->a[bestJ].iCursor);
    pLevel->iFrom = bestJ;
................................................................................
    int omitTable;     /* True if we use the index only */
    int bRev;          /* True if we need to scan in reverse order */

    pTabItem = &pTabList->a[pLevel->iFrom];
    iCur = pTabItem->iCursor;
    pIdx = pLevel->pIdx;
    iIdxCur = pLevel->iIdxCur;

    bRev = (pLevel->flags & WHERE_REVERSE)!=0;
    omitTable = (pLevel->flags & WHERE_IDX_ONLY)!=0;

    /* Create labels for the "break" and "continue" instructions
    ** for the current loop.  Jump to brk to break out of a loop.
    ** Jump to cont to go immediately to the next iteration of the
    ** loop.
................................................................................
  nQPlan = 0;
#endif /* SQLITE_TEST // Testing and debugging use only */

  /* Record the continuation address in the WhereInfo structure.  Then
  ** clean up and return.
  */
  pWInfo->iContinue = cont;

  whereClauseClear(&wc);
  sqliteFree(aConstraint);
  return pWInfo;

  /* Jump here if malloc fails */
whereBeginNoMem:
  whereClauseClear(&wc);
  sqliteFree(pWInfo);
  return 0;
}

/*
** Generate the end of the WHERE loop.  See comments on 
** sqlite3WhereBegin() for additional information.
*/
void sqlite3WhereEnd(WhereInfo *pWInfo){
................................................................................
  for(i=pTabList->nSrc-1; i>=0; i--){
    pLevel = &pWInfo->a[i];
    sqlite3VdbeResolveLabel(v, pLevel->cont);
    if( pLevel->op!=OP_Noop ){
      sqlite3VdbeAddOp(v, pLevel->op, pLevel->p1, pLevel->p2);
    }
    sqlite3VdbeResolveLabel(v, pLevel->brk);
    if( pLevel->nIn ){
      int *a;
      int j;
      for(j=pLevel->nIn, a=&pLevel->aInLoop[j*3-3]; j>0; j--, a-=3){
        sqlite3VdbeAddOp(v, a[0], a[1], a[2]);
      }
      sqliteFree(pLevel->aInLoop);
    }
    if( pLevel->iLeftJoin ){
      int addr;
      addr = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iLeftJoin, 0);
      sqlite3VdbeAddOp(v, OP_NotNull, 1, addr+4 + (pLevel->iIdxCur>=0));
      sqlite3VdbeAddOp(v, OP_NullRow, pTabList->a[i].iCursor, 0);
      if( pLevel->iIdxCur>=0 ){