SQLite

** sqlite_stat4.sample.  The nEq, nLt, and nDLt entries of sqlite_stat3
** all contain just a single integer which is the same as the first
** integer in the equivalent columns in sqlite_stat4.
*/
#ifndef SQLITE_OMIT_ANALYZE
#include "sqliteInt.h"

#ifdef SQLITE_ENABLE_STAT4
# define IsStat3 0
#else
# define IsStat3 1
#endif

/*
** This routine generates code that opens the sqlite_stat1 table for
** writing with cursor iStatCur. If the library was built with the
** SQLITE_ENABLE_STAT4 macro defined, then the sqlite_stat4 table is
** opened for writing using cursor (iStatCur+1)
**
** If the sqlite_stat1 tables does not previously exist, it is created.

  static const struct {
    const char *zName;
    const char *zCols;
  } aTable[] = {
    { "sqlite_stat1", "tbl,idx,stat" },
#if defined(SQLITE_ENABLE_STAT4)
    { "sqlite_stat4", "tbl,idx,neq,nlt,ndlt,sample" },
    { "sqlite_stat3", 0 },
#elif defined(SQLITE_ENABLE_STAT3)
    { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
    { "sqlite_stat4", 0 },
#endif
  };

  int aRoot[] = {0, 0};
  u8 aCreateTbl[] = {0, 0};

  int i;

  /* Create new statistic tables if they do not exist, or clear them
  ** if they do already exist.
  */
  for(i=0; i<ArraySize(aTable); i++){
    const char *zTab = aTable[i].zName;
    Table *pStat;
    if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){
      if( aTable[i].zCols ){
        /* The sqlite_stat[12] table does not exist. Create it. Note that a 
        ** side-effect of the CREATE TABLE statement is to leave the rootpage 
        ** of the new table in register pParse->regRoot. This is important 
        ** because the OpenWrite opcode below will be needing it. */
        sqlite3NestedParse(pParse,
            "CREATE TABLE %Q.%s(%s)", pDb->zName, zTab, aTable[i].zCols
        );
        aRoot[i] = pParse->regRoot;
        aCreateTbl[i] = OPFLAG_P2ISREG;
      }
    }else{
      /* The table already exists. If zWhere is not NULL, delete all entries 
      ** associated with the table zWhere. If zWhere is NULL, delete the
      ** entire contents of the table. */
      aRoot[i] = pStat->tnum;
      sqlite3TableLock(pParse, iDb, aRoot[i], 1, zTab);
      if( zWhere ){
        sqlite3NestedParse(pParse,
           "DELETE FROM %Q.%s WHERE %s=%Q", pDb->zName, zTab, zWhereType, zWhere
        );
      }else{
        /* The sqlite_stat[134] table already exists.  Delete all rows. */
        sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
      }
    }
  }

  /* Open the sqlite_stat[14] tables for writing. */
  for(i=0; i<ArraySize(aRoot); i++){
    sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
    sqlite3VdbeChangeP5(v, aCreateTbl[i]);
  }
}

/*

    tRowcnt *anLt;             /* sqlite_stat4.nLt */
    tRowcnt *anDLt;            /* sqlite_stat4.nDLt */
    u8 isPSample;              /* True if a periodic sample */
    u32 iHash;                 /* Tiebreaker hash */
  } *a;                     /* An array of samples */
};

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
/*
** Implementation of the stat4_init(C,N,S) SQL function. The three parameters
** are the number of rows in the table or index (C), the number of columns
** in the index (N) and the number of samples to accumulate (S).
**
** This routine allocates the Stat4Accum object in heap memory. The return 
** value is a pointer to the the Stat4Accum object encoded as a blob (i.e. 

  int nCol;                       /* Number of columns in index being sampled */
  int n;                          /* Bytes of space to allocate */
  int i;                          /* Used to iterate through p->aSample[] */

  /* Decode the three function arguments */
  UNUSED_PARAMETER(argc);
  nRow = (tRowcnt)sqlite3_value_int64(argv[0]);
  mxSample = sqlite3_value_int(argv[2]);
  nCol = sqlite3_value_int(argv[1]);
  assert( nCol>1 );               /* >1 because it includes the rowid column */

  /* Allocate the space required for the Stat4Accum object */
  n = sizeof(*p) + (sizeof(p->a[0]) + 3*sizeof(tRowcnt)*nCol)*mxSample;
  p = sqlite3MallocZero( n );
  if( p==0 ){
    sqlite3_result_error_nomem(context);

  Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]);
  i64 rowid = sqlite3_value_int64(argv[1]);
  int bNewKey = sqlite3_value_int(argv[2]);
  struct Stat4Sample *pSample;
  u32 h;                          /* Hash value for this key */
  int iMin = p->iMin;
  int i;
  int nSampleCol;                 /* Number of fields in samples */
  u8 isPSample = 0;               /* True if this is a periodic sample */
  u8 doInsert = 0;

  sqlite3_value **aEq = &argv[3];
  sqlite3_value **aLt = &argv[3+p->nCol];
  sqlite3_value **aDLt = &argv[3+p->nCol+p->nCol];

  i64 nLt;
  i64 nEq;

  UNUSED_PARAMETER(context);
  UNUSED_PARAMETER(argc);
  assert( p->nCol>0 );
  assert( argc==(3 + 3*p->nCol) );
  assert( p->bHaveNonP==0 || p->bHaveP==0 );

  if( IsStat3 ){
    /* Stat3 builds ignore any call with bNewKey==0. And consider only
    ** the first column of the index keys. */
    if( bNewKey==0 ) return;
    nEq = sqlite3_value_int64(aEq[0]);
    nSampleCol = 1;
  }else{
    nEq = 1;
    nSampleCol = p->nCol;
  }
  nLt = sqlite3_value_int64(aLt[nSampleCol-1]);

  if( bNewKey ){
    p->bHaveP = 0;
    p->bHaveNonP = 0;
  }
  h = p->iPrn = p->iPrn*1103515245 + 12345;

  /* Check if this should be a periodic sample. If this is a periodic
  ** sample and there is already a non-periodic sample for this key,
  ** replace it.  */
  if( (nLt/p->nPSample) != (nLt+nEq)/p->nPSample ){
    doInsert = isPSample = 1;
    if( p->bHaveNonP ){
      p->nSample--;
      p->bHaveNonP = 0;
      p->bHaveP = 1;
      assert( p->nSample<p->mxSample );
      assert( p->a[p->nSample].isPSample==0 );

  /* Finally, check if this should be added as a non-periodic sample. */
  }else if( p->nSample<p->mxSample ){
    doInsert = 1;
    p->bHaveNonP = 1;
  }else{
    tRowcnt *aMinEq = p->a[iMin].anEq;
    for(i=(IsStat3 ? 0 : p->nCol-2); i>=0; i--){
      i64 nEq = sqlite3_value_int64(aEq[i]);
      if( nEq<aMinEq[i] ) break;
      if( nEq>aMinEq[i] ){
        doInsert = 1;
        break;
      }
    }

    pSample->anLt = anLt;
  }else{
    pSample = &p->a[p->nSample++];
  }
  pSample->iRowid = rowid;
  pSample->iHash = h;
  pSample->isPSample = isPSample;
  for(i=0; i<nSampleCol; i++){
    pSample->anEq[i] = sqlite3_value_int64(aEq[i]);
    pSample->anLt[i] = sqlite3_value_int64(aLt[i]);
    pSample->anDLt[i] = sqlite3_value_int64(aDLt[i])-1;
    assert( sqlite3_value_int64(aDLt[i])>0 );
  }

  /* Find the new minimum */
  if( p->nSample==p->mxSample ){
    iMin = -1;
    for(i=0; i<p->mxSample; i++){
      if( p->a[i].isPSample ) continue;
      if( iMin<0 ){
        iMin = i;
      }else{
        int j;
        for(j=nSampleCol-1; j>=0; j++){
          i64 iCmp = (p->a[iMin].anEq[j] - p->a[i].anEq[j]);
          if( iCmp<0 ){ iMin = i; }
          if( iCmp ) break;
        }
        if( j==0 && p->a[iMin].iHash<p->a[i].iHash ){
          iMin = i;
        }

        sqlite3_result_int64(context, p->a[n].iRowid);
        return;
      case 3:  aCnt = p->a[n].anEq; break;
      case 4:  aCnt = p->a[n].anLt; break;
      default: aCnt = p->a[n].anDLt; break;
    }

    if( IsStat3 ){
      sqlite3_result_int64(context, (i64)aCnt[0]);
    }else{
      zRet = sqlite3MallocZero(p->nCol * 25);
      if( zRet==0 ){
        sqlite3_result_error_nomem(context);
      }else{
        int i;
        char *z = zRet;
        for(i=0; i<p->nCol; i++){
          sqlite3_snprintf(24, z, "%lld ", aCnt[i]);
          z += sqlite3Strlen30(z);
        }
        assert( z[0]=='\0' && z>zRet );
        z[-1] = '\0';
        sqlite3_result_text(context, zRet, -1, sqlite3_free);
      }
    }
  }
}
static const FuncDef stat4GetFuncdef = {
  -1,               /* nArg */
  SQLITE_UTF8,      /* iPrefEnc */
  0,                /* flags */

  int i;                       /* Loop counter */
  int jZeroRows = -1;          /* Jump from here if number of rows is zero */
  int iDb;                     /* Index of database containing pTab */
  u8 needTableCnt = 1;         /* True to count the table */
  int regTabname = iMem++;     /* Register containing table name */
  int regIdxname = iMem++;     /* Register containing index name */
  int regStat1 = iMem++;       /* The stat column of sqlite_stat1 */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  int regNumEq = regStat1;     /* Number of instances.  Same as regStat1 */
  int regNumLt = iMem++;       /* Number of keys less than regSample */
  int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
  int regSample = iMem++;      /* The next sample value */
  int regLoop = iMem++;        /* Loop counter */
  int shortJump = 0;           /* Instruction address */
#endif

  sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);
  sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);

  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nCol;                     /* Number of columns indexed by pIdx */
    KeyInfo *pKey;                /* KeyInfo structure for pIdx */
    int *aChngAddr;               /* Array of jump instruction addresses */

    int regPrev;                  /* First in array of previous values */
    int regDLte;                  /* First in array of nDlt registers */
    int regLt;                    /* First in array of nLt registers */
    int regEq;                    /* First in array of nEq registers */
    int endOfScan;                /* Label to jump to once scan is finished */

    if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;

    **   regDLte(0) += 1
    **   regLt(0) += regEq(0)
    **   regEq(0) = 0
    **   do {
    **     regEq(0) += 1
    **     Next csr(0)
    **   }while ( csr(0)[0] == regPrev(0) )
    **   if( IsStat3 ) regKeychng = 1
    ** 
    **  next_1:
    **   regPrev(1) = csr(1)[1]
    **   regDLte(1) += 1
    **   regLt(1) += regEq(1)
    **   regEq(1) = 0
    **   do {
    **     regEq(1) += 1
    **     Next csr(1)
    **   }while ( csr(1)[0..1] == regPrev(0..1) )
    ** 
    **   if( IsStat3==0 ) regKeychng = 1
    **  next_row:
    **   regRowid = csr(2)[rowid]
    **   regEq(2) = 1
    **   regLt(2) = regCnt
    **   regCnt += 1
    **   regDLte(2) = regCnt
    **   stat4_push(regRowid, regKeychng, regEq, regLt, regDLte);

    for(i=0; i<(nCol+1); i++){
      int iMode = (i==0 ? P4_KEYINFO_HANDOFF : P4_KEYINFO);
      sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur+i, pIdx->tnum, iDb);
      sqlite3VdbeChangeP4(v, -1, (char*)pKey, iMode); 
      VdbeComment((v, "%s", pIdx->zName));
    }

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    /* Invoke the stat4_init() function. The arguments are:
    ** 
    **     * the number of rows in the index,
    **     * the number of columns in the index including the rowid,
    **     * the recommended number of samples for the stat4 table.
    **
    ** If this is a stat3 build, the number of columns in the index is
    ** set to 1 (as this is the number of index fields gathered).
    */
    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat4+1);
    sqlite3VdbeAddOp2(v, OP_Integer, nCol+1, regStat4+2);
    sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT4_SAMPLES, regStat4+3);
    sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4+1, regStat4);
    sqlite3VdbeChangeP4(v, -1, (char*)&stat4InitFuncdef, P4_FUNCDEF);
    sqlite3VdbeChangeP5(v, 3);

        sqlite3VdbeAddOp3(v, OP_Column, iCsr, j, regCol);
        sqlite3VdbeAddOp4(v, OP_Ne, regCol, iNe, regPrev+j, pColl, P4_COLLSEQ);
        sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
        VdbeComment((v, "if( regPrev(%d) != csr(%d)(%d) )", j, i, j));
      }
      sqlite3VdbeAddOp2(v, OP_Goto, 0, iDo);
      sqlite3VdbeResolveLabel(v, iNe);

      if( IsStat3 && i==0 ){
        sqlite3VdbeAddOp2(v, OP_Integer, 1, regKeychng);
      }
    }

    /* This stuff:
    ** 
    **   regKeychng = 1
    **  next_row:
    **   regRowid = csr(2)[rowid]
    **   regEq(2) = 1
    **   regLt(2) = regCnt
    **   regCnt += 1
    **   regDLte(2) = regCnt
    **   stat4_push(regRowid, regKeychng, regEq, regLt, regDLte);
    **   regKeychng = 0
    **   Next csr(2)
    **   if( eof( csr(2) ) ) goto endOfScan
    */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    if( 0==IsStat3 ){
      sqlite3VdbeAddOp2(v, OP_Integer, 1, regKeychng);
    }
    aChngAddr[nCol] =
    sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur+nCol, regRowid);
    sqlite3VdbeAddOp2(v, OP_Integer, 1, regEq+nCol);
    sqlite3VdbeAddOp2(v, OP_Copy, regCnt, regLt+nCol);
    sqlite3VdbeAddOp2(v, OP_AddImm, regCnt, 1);
    sqlite3VdbeAddOp2(v, OP_Copy, regCnt, regDLte+nCol);
    sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regTemp);

      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, aChngAddr[nCol]);
    sqlite3DbFree(db, aChngAddr);

    sqlite3VdbeResolveLabel(v, endOfScan);

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    /* Add rows to the sqlite_stat4 table */
    regLoop = regStat4+1;
    sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop);
    shortJump = sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1);
    sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4, regEq+nCol);
    sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF);
    sqlite3VdbeChangeP5(v, 2);
    sqlite3VdbeAddOp1(v, OP_IsNull, regEq+nCol);

    sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regEq+nCol);
    if( IsStat3==0 ){
      for(i=0; i<nCol; i++){
        int iCol = pIdx->aiColumn[i];
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regEq+i);
      }
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regEq, nCol+1, regSample);
      sqlite3VdbeChangeP4(v, -1, pIdx->zColAff, 0);
    }else{
      int iCol = pIdx->aiColumn[0];
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, iCol, regSample);
    }

    sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumEq);
    sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF);
    sqlite3VdbeChangeP5(v, 3);

    sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumLt);
    sqlite3VdbeChangeP4(v, -1, (char*)&stat4GetFuncdef, P4_FUNCDEF);

}

/*
** If the Index.aSample variable is not NULL, delete the aSample[] array
** and its contents.
*/
void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  if( pIdx->aSample ){
    int j;
    for(j=0; j<pIdx->nSample; j++){
      IndexSample *p = &pIdx->aSample[j];
      sqlite3DbFree(db, p->p);
    }
    sqlite3DbFree(db, pIdx->aSample);
  }
  if( db && db->pnBytesFreed==0 ){
    pIdx->nSample = 0;
    pIdx->aSample = 0;
  }
#else
  UNUSED_PARAMETER(db);
  UNUSED_PARAMETER(pIdx);
#endif
}

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)























































/*
** Load the content from either the sqlite_stat4 or sqlite_stat3 table 
** into the relevant Index.aSample[] arrays.
**
** Arguments zSql1 and zSql2 must point to SQL statements that return
** data equivalent to the following (statements are different for stat3,
** see the caller of this function for details):

    /* Index.nSample is non-zero at this point if data has already been
    ** loaded from the stat4 table. In this case ignore stat3 data.  */
    if( pIdx==0 || pIdx->nSample ) continue;
    if( bStat3==0 ){
      nIdxCol = pIdx->nColumn+1;
      nAvgCol = pIdx->nColumn;
    }
    pIdx->nSampleCol = nIdxCol;
    pIdx->nSample = nSample;
    nByte = sizeof(IndexSample) * nSample;
    nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
    nByte += nAvgCol * sizeof(tRowcnt);     /* Space for Index.aAvgEq[] */

    pIdx->aSample = sqlite3DbMallocZero(db, nByte);
    if( pIdx->aSample==0 ){

  assert( db->aDb[iDb].pBt!=0 );

  /* Clear any prior statistics */
  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
    Index *pIdx = sqliteHashData(i);
    sqlite3DefaultRowEst(pIdx);
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    sqlite3DeleteIndexSamples(db, pIdx);
    pIdx->aSample = 0;
#endif
  }

  /* Check to make sure the sqlite_stat1 table exists */
  sInfo.db = db;

  }else{
    rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
    sqlite3DbFree(db, zSql);
  }


  /* Load the statistics from the sqlite_stat4 table. */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  if( rc==SQLITE_OK ){
    int lookasideEnabled = db->lookaside.bEnabled;
    db->lookaside.bEnabled = 0;
    rc = loadStat4(db, sInfo.zDatabase);
    db->lookaside.bEnabled = lookasideEnabled;
  }
#endif

#endif
#ifdef SQLITE_ENABLE_OVERSIZE_CELL_CHECK
  "ENABLE_OVERSIZE_CELL_CHECK",
#endif
#ifdef SQLITE_ENABLE_RTREE
  "ENABLE_RTREE",
#endif
#if defined(SQLITE_ENABLE_STAT4)
  "ENABLE_STAT4",
#elif defined(SQLITE_ENABLE_STAT3)
  "ENABLE_STAT3",
#endif
#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
  "ENABLE_UNLOCK_NOTIFY",
#endif
#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
  "ENABLE_UPDATE_DELETE_LIMIT",
#endif

  Expr *pPartIdxWhere;     /* WHERE clause for partial indices */
  int tnum;                /* DB Page containing root of this index */
  u16 nColumn;             /* Number of columns in table used by this index */
  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned autoIndex:2;    /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  IndexSample *aSample;    /* Samples of the left-most key */
#endif
};

/*
** Each sample stored in the sqlite_stat3 table is represented in memory 

#endif

#ifdef SQLITE_ENABLE_STAT4
  Tcl_SetVar2(interp, "sqlite_options", "stat4", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat4", "0", TCL_GLOBAL_ONLY);
#endif
#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
#endif

#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#    define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#    define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif

  }
  op = pExpr->op;

  /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT4.
  ** The ifdef here is to enable us to achieve 100% branch test coverage even
  ** when SQLITE_ENABLE_STAT4 is omitted.
  */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  if( op==TK_REGISTER ) op = pExpr->op2;
#else
  if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;
#endif

  /* Handle negative integers in a single step.  This is needed in the
  ** case when the value is -9223372036854775808.

  u8 enc,                   /* Encoding to use */
  u8 affinity,              /* Affinity to use */
  sqlite3_value **ppVal     /* Write the new value here */
){
  return valueFromExpr(db, pExpr, enc, affinity, ppVal, valueNew, (void*)db);
}

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
/*
** The implementation of the sqlite_record() function. This function accepts
** a single argument of any type. The return value is a formatted database 
** record (a blob) containing the argument value.
**
** This is used to convert the value stored in the 'sample' column of the
** sqlite_stat3 table to the record format SQLite uses internally.
*/
static void recordFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const int file_format = 1;
  int iSerial;                    /* Serial type */
  int nSerial;                    /* Bytes of space for iSerial as varint */
  int nVal;                       /* Bytes of space required for argv[0] */
  int nRet;
  sqlite3 *db;
  u8 *aRet;

  iSerial = sqlite3VdbeSerialType(argv[0], file_format);
  nSerial = sqlite3VarintLen(iSerial);
  nVal = sqlite3VdbeSerialTypeLen(iSerial);
  db = sqlite3_context_db_handle(context);

  nRet = 1 + nSerial + nVal;
  aRet = sqlite3DbMallocRaw(db, nRet);
  if( aRet==0 ){
    sqlite3_result_error_nomem(context);
  }else{
    aRet[0] = nSerial+1;
    sqlite3PutVarint(&aRet[1], iSerial);
    sqlite3VdbeSerialPut(&aRet[1+nSerial], nVal, argv[0], file_format);
    sqlite3_result_blob(context, aRet, nRet, SQLITE_TRANSIENT);
    sqlite3DbFree(db, aRet);
  }
}

/*
** Register built-in functions used to help read ANALYZE data.
*/
void sqlite3AnalyzeFunctions(void){
  static SQLITE_WSD FuncDef aAnalyzeTableFuncs[] = {
    FUNCTION(sqlite_record,   1, 0, 0, recordFunc),
  };
  int i;
  FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
  FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aAnalyzeTableFuncs);
  for(i=0; i<ArraySize(aAnalyzeTableFuncs); i++){
    sqlite3FuncDefInsert(pHash, &aFunc[i]);
  }
}

/*
** A pointer to an instance of this object is passed as the context 
** pointer to valueNewStat4() (see below.
*/
struct ValueNewStat4Ctx {
  Parse *pParse;
  Index *pIdx;

#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 */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
#  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif

/*
** An instance of the WhereScan object is used as an iterator for locating

*/
struct WhereLoopBuilder {
  WhereInfo *pWInfo;        /* Information about this WHERE */
  WhereClause *pWC;         /* WHERE clause terms */
  ExprList *pOrderBy;       /* ORDER BY clause */
  WhereLoop *pNew;          /* Template WhereLoop */
  WhereOrSet *pOrSet;       /* Record best loops here, if not NULL */
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  UnpackedRecord *pRec;     /* Probe for stat4 (if required) */
  int nRecValid;            /* Number of valid fields currently in pRec */
#endif
};

/*
** The WHERE clause processing routine has two halves.  The

      pTerm->nChild = 1;
      pTerm->wtFlags |= TERM_COPIED;
      pNewTerm->prereqAll = pTerm->prereqAll;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  /* When sqlite_stat3 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

  }

  return pParse->nErr;
}
#endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */


#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
/*
** Estimate the location of a particular key among all keys in an
** index.  Store the results in aStat as follows:
**
**    aStat[0]      Est. number of rows less than pVal
**    aStat[1]      Est. number of rows equal to pVal
**

  int iCol = pRec->nField-1;  /* Index of required stats in anEq[] etc. */
  int iMin = 0;               /* Smallest sample not yet tested */
  int i = pIdx->nSample;      /* Smallest sample larger than or equal to pRec */
  int iTest;                  /* Next sample to test */
  int res;                    /* Result of comparison operation */

  assert( pIdx->nSample>0 );
  assert( pRec->nField>0 && iCol<pIdx->nSampleCol );
  do{
    iTest = (iMin+i)/2;
    res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec);
    if( res<0 ){
      iMin = iTest+1;
    }else{
      i = iTest;

  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 */
  WhereCost *pnOut     /* IN/OUT: Number of rows visited */
){
  int rc = SQLITE_OK;
  int nOut = (int)*pnOut;

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  Index *p = pBuilder->pNew->u.btree.pIndex;
  int nEq = pBuilder->pNew->u.btree.nEq;

  if( nEq==pBuilder->nRecValid
   && nEq<p->nSampleCol
   && p->nSample 
   && OptimizationEnabled(pParse->db, SQLITE_Stat3) 
  ){
    UnpackedRecord *pRec = pBuilder->pRec;
    tRowcnt a[2];
    u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;

    nOut -= 20;        assert( 20==whereCost(4) );
  }
  if( nOut<10 ) nOut = 10;
  *pnOut = (WhereCost)nOut;
  return rc;
}

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
/*
** 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_stat3 histogram data is available
** for that index.  When pExpr==NULL that means the constraint is
** "x IS NULL" instead of "x=VALUE".

  WHERETRACE(0x100,("equality scan regions: %d\n", (int)a[1]));
  *pnRow = a[1];
  
  return rc;
}
#endif /* defined(SQLITE_ENABLE_STAT4) */

#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
/*
** 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)
**

  saved_wsFlags = pNew->wsFlags;
  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 0;
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    int nRecValid = pBuilder->nRecValid;
    assert( pNew->nOut==saved_nOut );
    if( (pTerm->wtFlags & TERM_VNULL)!=0 && pSrc->pTab->aCol[iCol].notNull ){
      continue; /* skip IS NOT NULL constraints on a NOT NULL column */
    }
#endif
    if( pTerm->prereqRight & pNew->maskSelf ) continue;

                     pNew->aLTerm[pNew->nLTerm-2] : 0;
    }
    if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
      /* Adjust nOut and rRun for STAT3 range values */
      assert( pNew->nOut==saved_nOut );
      whereRangeScanEst(pParse, pBuilder, pBtm, pTop, &pNew->nOut);
    }
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    if( nInMul==0 
     && pProbe->nSample 
     && pNew->u.btree.nEq<=pProbe->nSampleCol
     && OptimizationEnabled(db, SQLITE_Stat3) 
    ){
      Expr *pExpr = pTerm->pExpr;
      tRowcnt nOut = 0;
      if( (pTerm->eOperator & (WO_EQ|WO_ISNULL))!=0 ){
        testcase( pTerm->eOperator & WO_EQ );
        testcase( pTerm->eOperator & WO_ISNULL );
        rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
      }else if( (pTerm->eOperator & WO_IN)

    rc = whereLoopInsert(pBuilder, pNew);
    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<(pProbe->nColumn + (pProbe->zName!=0))
    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
    }
    pNew->nOut = saved_nOut;
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    pBuilder->nRecValid = nRecValid;
#endif
  }
  pNew->prereq = saved_prereq;
  pNew->u.btree.nEq = saved_nEq;
  pNew->wsFlags = saved_wsFlags;
  pNew->nOut = saved_nOut;

        }
        rc = whereLoopInsert(pBuilder, pNew);
        if( rc ) break;
      }
    }

    rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);
#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
    sqlite3Stat4ProbeFree(pBuilder->pRec);
    pBuilder->nRecValid = 0;
    pBuilder->pRec = 0;
#endif

    /* If there was an INDEXED BY clause, then only that one index is
    ** considered. */

  sqlite3 db test.db
  execsql {
    SELECT * FROM t4 WHERE x=1234;
  }
} {}

# Verify that DROP TABLE and DROP INDEX remove entries from the 
# sqlite_stat1, sqlite_stat3 and sqlite_stat4 tables.
#
do_test analyze-5.0 {
  execsql {
    DELETE FROM t3;
    DELETE FROM t4;
    INSERT INTO t3 VALUES(1,2,3,4);
    INSERT INTO t3 VALUES(5,6,7,8);
    INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3;
    INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3;
    INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3;
    INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3;
    INSERT INTO t4 SELECT a, b, c FROM t3;
    ANALYZE;
    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
  }
} {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
ifcapable stat4||stat3 {
  ifcapable stat4 {set stat sqlite_stat4} else {set stat sqlite_stat3}
  do_test analyze-5.1 {
    execsql "
      SELECT DISTINCT idx FROM $stat ORDER BY 1;
      SELECT DISTINCT tbl FROM $stat ORDER BY 1;

    "
  } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
}
do_test analyze-5.2 {
  execsql {
    DROP INDEX t3i2;
    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
  }
} {t3i1 t3i3 t4i1 t4i2 t3 t4}
ifcapable stat4||stat3 {
  do_test analyze-5.3 {
    execsql "
      SELECT DISTINCT idx FROM $stat ORDER BY 1;
      SELECT DISTINCT tbl FROM $stat ORDER BY 1;

    "
  } {t3i1 t3i3 t4i1 t4i2 t3 t4}
}
do_test analyze-5.4 {
  execsql {
    DROP TABLE t3;
    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
  }
} {t4i1 t4i2 t4}
ifcapable stat4||stat3 {
  do_test analyze-5.5 {
    execsql "
      SELECT DISTINCT idx FROM $stat ORDER BY 1;
      SELECT DISTINCT tbl FROM $stat ORDER BY 1;

    "
  } {t4i1 t4i2 t4}
}

# This test corrupts the database file so it must be the last test
# in the series.
#
do_test analyze-99.1 {

# implements tests for range and LIKE constraints that use bound variables
# instead of literal constant arguments.
#

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

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

#----------------------------------------------------------------------
# Test Organization:
#

  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i+100, $i) }
  }
  execsql {
    COMMIT;
    ANALYZE;
  }

  ifcapable stat4 {
    execsql { SELECT count(*)>0 FROM sqlite_stat4; }
  } else {
    execsql { SELECT count(*)>0 FROM sqlite_stat3; }
  }
} {1}

do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}
do_eqp_test analyze3-1.1.3 {

# in this file is the use of the sqlite_stat4 histogram data on tables
# with many repeated values and only a few distinct values.
#

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

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

set testprefix analyze5

proc eqp {sql {db db}} {

    CREATE INDEX t1u ON t1(u);  -- text
    CREATE INDEX t1v ON t1(v);  -- mixed case text
    CREATE INDEX t1w ON t1(w);  -- integers 0, 1, 2 and a few NULLs
    CREATE INDEX t1x ON t1(x);  -- integers 1, 2, 3 and many NULLs
    CREATE INDEX t1y ON t1(y);  -- integers 0 and very few 1s
    CREATE INDEX t1z ON t1(z);  -- integers 0, 1, 2, and 3
    ANALYZE;
  }
  ifcapable stat4 {
    db eval {
      SELECT DISTINCT lindex(test_decode(sample),0) 
        FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt;
    }
  } else {
    db eval {
      SELECT sample FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt;
    }
  }
} {alpha bravo charlie delta}

do_test analyze5-1.1 {
  ifcapable stat4 {
    db eval {
      SELECT DISTINCT lower(lindex(test_decode(sample), 0)) 
        FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1
    }
  } else {
    db eval {
      SELECT lower(sample) FROM sqlite_stat3 WHERE idx='t1v' ORDER BY 1
    }
  }
} {alpha bravo charlie delta}
ifcapable stat4 {
  do_test analyze5-1.2 {
    db eval {SELECT idx, count(*) FROM sqlite_stat4 GROUP BY 1 ORDER BY 1}
  } {t1t 8 t1u 8 t1v 8 t1w 8 t1x 8 t1y 9 t1z 8}
} else {
  do_test analyze5-1.2 {
    db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1}
  } {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4}
}

# Verify that range queries generate the correct row count estimates
#
foreach {testid where index rows} {
    1  {z>=0 AND z<=0}       t1z  400
    2  {z>=1 AND z<=1}       t1z  300
    3  {z>=2 AND z<=2}       t1z  175

# in this file a corner-case query planner optimization involving the
# join order of two tables of different sizes.
#

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

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

set testprefix analyze6

proc eqp {sql {db db}} {

} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test analyze7-3.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test analyze7-3.2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
ifcapable stat4||stat3 {
  # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated
  # row count for (c=2) than it does for (c=?).
  do_test analyze7-3.2.2 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
} else {
  # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the
  # same as that for (c=?).
  do_test analyze7-3.2.3 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
}
do_test analyze7-3.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

ifcapable {!stat4 && !stat3} {
  do_test analyze7-3.4 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  do_test analyze7-3.5 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
}
do_test analyze7-3.6 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}}

finish_test

# This file implements tests for SQLite library.  The focus of the tests
# in this file is testing the capabilities of sqlite_stat3.
#

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

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

set testprefix analyze8

proc eqp {sql {db db}} {

  #
  execsql {
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2
  db2 close
}

# Populate the stat4 table according to the current contents of the db
#
proc populate_stat4 {{bDropTable 1}} {
  sqlite3 db2 test.db
  execsql { ANALYZE }

  ifcapable stat3 {
    execsql {
      PRAGMA writable_schema = on;
      CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
      INSERT INTO sqlite_stat4 
      SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) 
      FROM sqlite_stat3;
    } db2
    if {$bDropTable} { execsql {DROP TABLE sqlite_stat3} db2 }
    execsql { PRAGMA writable_schema = off }
  }
 
  # Modify the database schema cookie to ensure that the other connection
  # reloads the schema.
  #
  execsql {
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2
  db2 close
}

# Populate the stat4 table according to the current contents of the db.
# Leave deceptive data in the stat3 table. This data should be ignored
# in favour of that from the stat4 table.
#
proc populate_both {} {
  ifcapable stat4 { populate_stat3 0 }
  ifcapable stat3 { populate_stat4 0 }

  sqlite3 db2 test.db
  execsql {
    PRAGMA writable_schema = on;
    UPDATE sqlite_stat3 SET idx = 
      CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b'
    END;
    PRAGMA writable_schema = off;
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2

  db2 close
}





















foreach {tn analyze_cmd} {
  1 populate_stat4 
  2 populate_stat3
  3 populate_both
} {
  reset_db
  do_test 1.$tn.1 {

    SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}

  do_eqp_test 1.$tn.3.6 {
    SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
}


finish_test

        DROP TABLE v1chng;
      }
    }
  }
  ifcapable stat4 {
    set stat4 "sqlite_stat4 "
  } else {
    ifcapable stat3 {
      set stat4 "sqlite_stat3 "
    } else {
      set stat4 ""
    }
  }
  do_test auth-5.2 {
    execsql {
      SELECT name FROM (
        SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
      WHERE type='table'
      ORDER BY name

proc lookaside {db} {
  expr { $::lookaside_buffer_size *
    [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1]
  }
}

ifcapable stat4||stat3 {
  set STAT3 1
} else {
  set STAT3 0
}

ifcapable malloc_usable_size {
  finish_test

} {800}
do_test index6-2.2 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t2 WHERE a=5;
  }
} {/.* TABLE t2 USING INDEX t2a1 .*/}
ifcapable stat4||stat3 {
  do_test index6-2.3stat4 {
    execsql {
      EXPLAIN QUERY PLAN
      SELECT * FROM t2 WHERE a IS NOT NULL;
    }
  } {/.* TABLE t2 USING INDEX t2a1 .*/}
} else {

# Dropping sqlite_statN tables is OK.
#
do_test table-5.2.1 {
  db eval {
    ANALYZE;
    DROP TABLE IF EXISTS sqlite_stat1;
    DROP TABLE IF EXISTS sqlite_stat2;
    DROP TABLE IF EXISTS sqlite_stat3;
    DROP TABLE IF EXISTS sqlite_stat4;
    SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*';
  }
} {}

# Make sure an EXPLAIN does not really create a new table
#

# This file implements tests to verify that ticket [cbd054fa6b] has been
# fixed.  
#

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

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

proc s {blob} {
  set ret ""
  binary scan $blob c* bytes

    INSERT INTO t1 VALUES (NULL, 'G');
    INSERT INTO t1 VALUES (NULL, 'H');
    INSERT INTO t1 VALUES (NULL, 'I');
    SELECT count(*) FROM t1;
  }
} {10}
do_test tkt-cbd05-1.2 {
  db eval { ANALYZE; }
  ifcapable stat4 {
    db eval {

      PRAGMA writable_schema = 1;
      CREATE VIEW vvv AS 
      SELECT tbl,idx,neq,nlt,ndlt,test_extract(sample,0) AS sample
      FROM sqlite_stat4;
      PRAGMA writable_schema = 0;
    }
  } else {
    db eval {
      CREATE VIEW vvv AS 
      SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3;
    }
  }
} {}
do_test tkt-cbd05-1.3 {
  execsql { 
    SELECT tbl,idx,group_concat(s(sample),' ') 
    FROM vvv 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

do_test tkt-cbd05-2.1 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB UNIQUE NOT NULL);
    CREATE INDEX t1_x ON t1(b);
    INSERT INTO t1 VALUES(NULL, X'');

  db eval {
    ANALYZE;
  }
} {}
do_test tkt-cbd05-2.3 {
  execsql { 
    SELECT tbl,idx,group_concat(s(sample),' ') 
    FROM vvv 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

finish_test

  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {no query solution}}
ifcapable stat4||stat3 {
  # When STAT3 is enabled, the "b NOT NULL" terms get translated
  # into b>NULL, which can be satified by the index t1b.  It is a very
  # expensive way to do the query, but it works, and so a solution is possible.
  do_test where9-6.8.3-stat4 {
    catchsql {
      UPDATE t1 INDEXED BY t1b SET a=a+100
       WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)

    CREATE INDEX t5yd ON t5(y, d);
    CREATE INDEX t5ye ON t5(y, e);
    CREATE INDEX t5yf ON t5(y, f);
    CREATE INDEX t5yg ON t5(y, g);
    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
    INSERT INTO t6 SELECT * FROM t5;
    ANALYZE t5;
  }
  ifcapable stat3 {
    sqlite3 db2 test.db
    db2 eval { DROP TABLE IF EXISTS sqlite_stat3 }
    db2 close
  }
} {}
do_test where9-7.1.1 {
  count_steps {
    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83 scan 0 sort 1}