SQLite

# define SQLITE_STAT4_SAMPLES 24
#endif

/*
** Three SQL functions - stat4_init(), stat4_push(), and stat4_pop() -
** share an instance of the following structure to hold their state
** information.
**
** bHaveP, bHaveNonP:
**   The stat4_push() user-defined-function may be invoked multiple
**   times with index keys that are identical except for the rowid 
**   field. An argument is passed to stat4_push() to indicate if this
**   is the case or not.
**
**   bHaveP is set to true if a periodic sample corresponding to the
**   current index key has already been added. bHaveNonP is true if a
**   non-periodic sample has been added.
*/
typedef struct Stat4Accum Stat4Accum;
struct Stat4Accum {
  tRowcnt nRow;             /* Number of rows in the entire table */
  tRowcnt nPSample;         /* How often to do a periodic sample */
  int iMin;                 /* Index of entry with minimum nEq and hash */
  int mxSample;             /* Maximum number of samples to accumulate */
  int nSample;              /* Current number of samples */
  int nCol;                 /* Number of columns in the index including rowid */
  u32 iPrn;                 /* Pseudo-random number used for sampling */
  int bHaveP;
  int bHaveNonP;
  struct Stat4Sample {
    i64 iRowid;                /* Rowid in main table of the key */
    tRowcnt *anEq;             /* sqlite_stat4.nEq */
    tRowcnt *anLt;             /* sqlite_stat4.nLt */
    tRowcnt *anDLt;            /* sqlite_stat4.nDLt */
    u8 isPSample;              /* True if a periodic sample */
    u32 iHash;                 /* Tiebreaker hash */

  int i;                          /* Used to iterate through p->aSample[] */

  /* Decode the three function arguments */
  UNUSED_PARAMETER(argc);
  nRow = (tRowcnt)sqlite3_value_int64(argv[0]);
  nCol = sqlite3_value_int(argv[1]);
  mxSample = sqlite3_value_int(argv[2]);
  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);
    return;

static void stat4Push(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  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;
  u8 isPSample = 0;
  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 = sqlite3_value_int64(aLt[p->nCol-1]);

  UNUSED_PARAMETER(context);
  UNUSED_PARAMETER(argc);

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


  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+1)/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 );
    }


  /* Or, if this is not a periodic sample, and there is already at least one
  ** periodic sample, return early. */
  }else if( p->bHaveP ){
    /* no-op */



  /* If there is already a non-periodic sample for the key, but this one
  ** has a higher hash score, replace the existing sample.  */
  }else if( p->bHaveNonP ){
    if( p->a[p->nSample-1].iHash<h ){
      p->nSample--;
      doInsert = 1;
    }

  /* 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=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;
      }
    }
    if( i<0 && h>p->a[iMin].iHash ){
      doInsert = 1;
    }
    p->bHaveNonP = doInsert;
  }
  if( doInsert==0 ) return;

  /* Fill in the new Stat4Sample object. */
  if( p->nSample==p->mxSample ){
    struct Stat4Sample *pMin = &p->a[iMin];
    tRowcnt *anEq = pMin->anEq;
    tRowcnt *anDLt = pMin->anDLt;
    tRowcnt *anLt = pMin->anLt;

  int regTemp = iMem++;        /* Temporary use register */
  int regNewRowid = iMem++;    /* Rowid for the inserted record */
  int regEof = iMem++;         /* True once cursors are all at EOF */
  int regCnt = iMem++;         /* Row counter */

  int regStat4 = iMem++;       /* Register to hold Stat4Accum object */
  int regRowid = iMem++;       /* Rowid argument passed to stat4_push() */
  int regKeychng = iMem++;     /* True if key has changed */

  pParse->nMem = MAX(pParse->nMem, regKeychng);
  v = sqlite3GetVdbe(pParse);
  if( v==0 || NEVER(pTab==0) ){
    return;
  }
  if( pTab->tnum==0 ){
    /* Do not gather statistics on views or virtual tables */
    return;

    int regEq;                    /* First in array of nEq registers */
    int endOfScan;                /* Label to jump to once scan is finished */

    if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
    if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0;
    VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
    nCol = pIdx->nColumn;
    aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*(nCol+1));
    if( aChngAddr==0 ) continue;
    pKey = sqlite3IndexKeyinfo(pParse, pIdx);

    /* Populate the register containing the index name. */
    sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);

    /*
    ** The following pseudo-code demonstrates the way the VM scans an index 
    ** to call stat4_push() and collect the values for the sqlite_stat1 
    ** entry. The code below is for an index with 2 columns. The actual
    ** VM code generated may be for any number of columns.
    **
    ** One cursor is opened for each column in the index and one for the
    ** rowid column (nCol+1 in total). All cursors scan concurrently the 
    ** index from start to end. All variables used in the pseudo-code are 
    ** initialized to zero.
    **
    **   Rewind csr(0)
    **   Rewind csr(1)
    **   Rewind csr(2)
    ** 
    **  next_0:
    **   regPrev(0) = csr(0)[0]
    **   regDLte(0) += 1
    **   regLt(0) += regEq(0)
    **   regEq(0) = 0
    **   do {
    **     regEq(0) += 1
    **     Next csr(0)
    **   }while ( csr(0)[0] == regPrev(0) )
    ** 
    **  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) )
    ** 
    **   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( csr(2)[0] != regPrev(0) ) goto next_0
    **   if( csr(2)[1] != regPrev(1) ) goto next_1
    **   goto next_row
    **
    **  endOfScan:
    **   // done!
    **
    ** The last two lines above modify the contents of the regDLte array
    ** so that each element contains the number of distinct key prefixes
    ** of the corresponding length. As required to calculate the contents
    ** of the sqlite_stat1 entry.
    **
    ** At this point, the last memory cell allocated (that with the largest 
    ** integer identifier) is regKeychng. Immediately following regKeychng
    ** we allocate the following:
    **
    **     regEq -    nCol registers
    **     regLt -    nCol+1 registers
    **     regDLte -  nCol+1 registers
    **     regPrev -  nCol+1 registers
    **


    ** can be passed to the stat4_push() function.
    **
    ** All of the above are initialized to contain integer value 0.
    */
    regEq = regKeychng+1;         /* First in array of nEq value registers */
    regLt = regEq+nCol+1;         /* First in array of nLt value registers */
    regDLte = regLt+nCol+1;       /* First in array of nDLt value registers */
    regPrev = regDLte+nCol+1;     /* First in array of prev. value registers */
    pParse->nMem = MAX(pParse->nMem, regPrev+nCol);

    /* Open a read-only cursor for each column of the index. And one for
    ** the rowid column. A total of (nCol+1) cursors.  */
    assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
    iIdxCur = iTab;
    pParse->nTab = MAX(pParse->nTab, iTab+nCol+1);
    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));
    }

#ifdef SQLITE_ENABLE_STAT4
    /* 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.
    */
    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);
#endif /* SQLITE_ENABLE_STAT4 */

    /* Initialize all the memory registers allocated above to 0. */
    for(i=regEq; i<regDLte+nCol; i++){
      sqlite3VdbeAddOp2(v, OP_Integer, 0, i);
    }
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regCnt);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regEof);

    /* Rewind all cursors open on the index. If the table is entry, this
    ** will cause control to jump to address endOfScan immediately.  */
    endOfScan = sqlite3VdbeMakeLabel(v);
    for(i=0; i<(nCol+1); i++){
      sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur+i, endOfScan);
    }

    for(i=0; i<nCol; i++){
      char *pColl = (char*)sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
      int iCsr = iIdxCur+i;
      int iDo;
      int iNe;                    /* Jump here to exit do{...}while loop */
      int j;


      /* Implementation of the following pseudo-code:
      **
      **   regPrev(i) = csr(i)[i]
      **   regDLte(i) += 1
      **   regLt(i) += regEq(i)
      **   regEq(i) = 0
      **   regRowid = csr(i)[rowid]        // innermost cursor only
      */
      aChngAddr[i] = sqlite3VdbeAddOp3(v, OP_Column, iCsr, i, regPrev+i);
      VdbeComment((v, "regPrev(%d) = csr(%d)(%d)", i, i, i));
      sqlite3VdbeAddOp2(v, OP_AddImm, regDLte+i, 1);
      VdbeComment((v, "regDLte(%d) += 1", i));
      sqlite3VdbeAddOp3(v, OP_Add, regEq+i, regLt+i, regLt+i);
      VdbeComment((v, "regLt(%d) += regEq(%d)", i, i));
      sqlite3VdbeAddOp2(v, OP_Integer, 0, regEq+i);
      VdbeComment((v, "regEq(%d) = 0", i));


      /* This bit:
      **
      **   do {
      **     regEq(i) += 1

      **     Next csr(i)
      **     if( Eof csr(i) ){

      **       break
      **     }
      **   }while ( csr(i)[0..i] == regPrev(0..i) )
      */
      iDo = sqlite3VdbeAddOp2(v, OP_AddImm, regEq+i, 1);
      VdbeComment((v, "regEq(%d) += 1", i));




      sqlite3VdbeAddOp2(v, OP_Next, iCsr, sqlite3VdbeCurrentAddr(v)+2);

      iNe = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, iNe);
      for(j=0; j<=i; j++){
        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);
    }

    /* 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
    */
#ifdef SQLITE_ENABLE_STAT4
    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);
    sqlite3VdbeChangeP4(v, -1, (char*)&stat4PushFuncdef, P4_FUNCDEF);
    sqlite3VdbeChangeP5(v, 3 + 3*(nCol+1));
    sqlite3VdbeAddOp2(v, OP_Integer, 0, regKeychng);
    sqlite3VdbeAddOp2(v, OP_Next, iIdxCur+nCol, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfScan);
#endif

    sqlite3VdbeAddOp2(v, OP_If, regEof, endOfScan);
    for(i=0; i<nCol; i++){
      char *pColl = (char*)sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
      sqlite3VdbeAddOp3(v, OP_Column, iIdxCur+nCol, i, regCol);
      sqlite3VdbeAddOp3(v, OP_Ne, regCol, aChngAddr[i], regPrev+i);
      sqlite3VdbeChangeP4(v, -1, pColl, P4_COLLSEQ);
      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, aChngAddr[nCol]);
    sqlite3DbFree(db, aChngAddr);

    sqlite3VdbeResolveLabel(v, endOfScan);

#ifdef SQLITE_ENABLE_STAT4
    /* 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);
    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);

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

    sqlite3VdbeAddOp3(v, OP_Function, 1, regStat4, regNumLt);

  UnpackedRecord *pRec = p->ppRec[0];

  if( pRec==0 ){
    sqlite3 *db = p->pParse->db;  /* Database handle */
    Index *pIdx = p->pIdx;        /* Index being probed */
    int nByte;                    /* Bytes of space to allocate */
    int i;                        /* Counter variable */
    int nCol = pIdx->nColumn+1;   /* Number of index columns including rowid */

    nByte = sizeof(Mem) * nCol + sizeof(UnpackedRecord);
    pRec = (UnpackedRecord*)sqlite3DbMallocZero(db, nByte);
    if( pRec ){
      pRec->pKeyInfo = sqlite3IndexKeyinfo(p->pParse, pIdx);
      if( pRec->pKeyInfo ){
        assert( pRec->pKeyInfo->nField+1==nCol );
        pRec->pKeyInfo->enc = ENC(db);
        pRec->flags = UNPACKED_PREFIX_MATCH;
        pRec->aMem = (Mem *)&pRec[1];
        for(i=0; i<nCol; i++){
          pRec->aMem[i].flags = MEM_Null;
          pRec->aMem[i].type = SQLITE_NULL;
          pRec->aMem[i].db = db;
        }
      }else{
        sqlite3DbFree(db, pRec);
        pRec = 0;

** Unless it is NULL, the argument must be an UnpackedRecord object returned
** by an earlier call to sqlite3Stat4ProbeSetValue(). This call deletes
** the object.
*/
void sqlite3Stat4ProbeFree(UnpackedRecord *pRec){
  if( pRec ){
    int i;
    int nCol = pRec->pKeyInfo->nField+1;
    Mem *aMem = pRec->aMem;
    sqlite3 *db = aMem[0].db;
    for(i=0; i<nCol; i++){
      sqlite3DbFree(db, aMem[i].zMalloc);
    }
    sqlite3DbFree(db, pRec->pKeyInfo);
    sqlite3DbFree(db, pRec);
  }
}
#endif /* ifdef SQLITE_ENABLE_STAT4 */

  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->nColumn );
  do{
    iTest = (iMin+i)/2;
    res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec);
    if( res<0 ){
      iMin = iTest+1;
    }else{
      i = iTest;

  /* If values are not available for all fields of the index to the left
  ** of this one, no estimate can be made. Return SQLITE_NOTFOUND. */
  if( pBuilder->nRecValid<(nEq-1) ){
    return SQLITE_NOTFOUND;
  }

  /* This is an optimization only. The call to sqlite3Stat4ProbeSetValue()
  ** below would return the same value.  */
  if( nEq>p->nColumn ){
    *pnRow = 1;
    return SQLITE_OK;
  }

  aff = p->pTable->aCol[p->aiColumn[nEq-1]].affinity;
  rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq-1, &bOk);

  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i+100, $i) }
  }
  execsql {
    COMMIT;
    ANALYZE;
  }
  execsql {
    SELECT count(*)>0 FROM sqlite_stat4;
  }
} {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 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?)}}

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

do_test analyze3-3.2.1 {
  set S [sqlite3_prepare_v2 db "SELECT * FROM t1 WHERE b>?" -1 dummy]
  sqlite3_expired $S
} {0}
do_test analyze3-3.2.2 {
  sqlite3_bind_text $S 1 "abc" 3
  sqlite3_expired $S

  set ret ""
  foreach c [split $blob {}] {
    if {[string is alpha $c]} {append ret $c}
  }
  return $ret
}
db func alpha alpha

db func lindex lindex

unset -nocomplain i t u v w x y z
do_test analyze5-1.0 {
  db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)}
  for {set i 0} {$i < 1000} {incr i} {
    set y [expr {$i>=25 && $i<=50}]
    set z [expr {($i>=400) + ($i>=700) + ($i>=875)}]

    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;
    SELECT DISTINCT lindex(test_decode(sample),0) 
    FROM sqlite_stat4 WHERE idx='t1u' ORDER BY nlt;
  }
} {alpha bravo charlie delta}

do_test analyze5-1.1 {
  db eval {
    SELECT DISTINCT lower(lindex(test_decode(sample), 0)) 
    FROM sqlite_stat4 WHERE idx='t1v' ORDER BY 1
  }
} {alpha bravo charlie delta}
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}

# 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

      append ret .
    }
  }
  return $ret
}
db function s s

do_execsql_test 1.0 {
  CREATE TABLE t1(a TEXT, b TEXT); 
  INSERT INTO t1 VALUES('(0)', '(0)');
  INSERT INTO t1 VALUES('(1)', '(1)');

  INSERT INTO t1 VALUES('(2)', '(2)');
  INSERT INTO t1 VALUES('(3)', '(3)');
  INSERT INTO t1 VALUES('(4)', '(4)');
  CREATE INDEX i1 ON t1(a, b);
} {}


do_execsql_test 1.1 {
  ANALYZE;
} {}

do_execsql_test 1.3 {
  SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4;
} {
  t1 i1 {1 1 1} {0 0 0} {0 0 0} {(0) (0) 1}
  t1 i1 {1 1 1} {1 1 1} {1 1 1} {(1) (1) 2}
  t1 i1 {1 1 1} {2 2 2} {2 2 2} {(2) (2) 3}
  t1 i1 {1 1 1} {3 3 3} {3 3 3} {(3) (3) 4}
  t1 i1 {1 1 1} {4 4 4} {4 4 4} {(4) (4) 5}
}

do_execsql_test 1.2 {
  SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4;
} {
  t1 i1 {1 1 1} {0 0 0} {0 0 0} ....(0)(0)
  t1 i1 {1 1 1} {1 1 1} {1 1 1} ....(1)(1).
  t1 i1 {1 1 1} {2 2 2} {2 2 2} ....(2)(2).
  t1 i1 {1 1 1} {3 3 3} {3 3 3} ....(3)(3).
  t1 i1 {1 1 1} {4 4 4} {4 4 4} ....(4)(4).
}


#-------------------------------------------------------------------------
# This is really just to test SQL user function "test_decode".
#
reset_db
do_execsql_test 2.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('some text', 14, NULL);
  INSERT INTO t1 VALUES(22.0, NULL, x'656667');
  CREATE INDEX i1 ON t1(a, b, c);
  ANALYZE;
  SELECT test_decode(sample) FROM sqlite_stat4;
} {
  {22.0 NULL x'656667' 2} 
  {{some text} 14 NULL 1}
}

#-------------------------------------------------------------------------
# 
reset_db
do_execsql_test 3.1 {
  CREATE TABLE t2(a, b);

# The first element in the "nEq" list of all samples should therefore be 10.
#
do_execsql_test 3.3.2 {
  ANALYZE;
  SELECT lindex(nEq, 0) FROM sqlite_stat4;
} [lrange [string repeat "10 " 100] 0 23]

#-------------------------------------------------------------------------
# 
do_execsql_test 3.4 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 1, 'one-a');
  INSERT INTO t1 VALUES(11, 1, 'one-b');
  INSERT INTO t1 VALUES(21, 1, 'one-c');
  INSERT INTO t1 VALUES(31, 1, 'one-d');
  INSERT INTO t1 VALUES(41, 1, 'one-e');
  INSERT INTO t1 VALUES(51, 1, 'one-f');
  INSERT INTO t1 VALUES(61, 1, 'one-g');
  INSERT INTO t1 VALUES(71, 1, 'one-h');
  INSERT INTO t1 VALUES(81, 1, 'one-i');
  INSERT INTO t1 VALUES(91, 1, 'one-j');
  INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
  INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
  INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';	
  CREATE INDEX t1b ON t1(b);
  ANALYZE;
  SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
} {three-d three-e three-f}

finish_test

do_test tkt-cbd05-1.3 {
  execsql { 
    SELECT tbl,idx,group_concat(s(sample),' ') 
    FROM sqlite_stat4 
    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'');

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

finish_test