/ Check-in [0df04f92]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Merge branches branch-3.7.2 and stat3-enhancement into a new branch for testing purposes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat3-3.7.2
Files: files | file ages | folders
SHA1:0df04f920b3e6cfe8db7687cc9c392f65c11d9ce
User & Date: drh 2011-08-26 13:52:23
Original Comment: Merge branches branch-3.7.2 and stat3-enhancement into a new branch for testing purposes.
Context
2011-08-26
15:51
Get stat3 working after import from trunk-based the stat3-enhancement branch. There is still a problem in the wal2 test. check-in: 01cc4ff6 user: drh tags: stat3-3.7.2
13:52
Merge branches branch-3.7.2 and stat3-enhancement into a new branch for testing purposes. check-in: 0df04f92 user: drh tags: stat3-3.7.2
2011-08-18
13:45
Fix the stat3 analysis loader to be compatible with sqlite3_db_status(). Also fix some OOM issues with the stat3 analysis loader. Closed-Leaf check-in: eaf447ea user: drh tags: stat3-enhancement
2011-07-13
18:53
Cherrypicked from trunk: Do not try to use STAT2 for row estimates if the index is unique or nearly so. check-in: d55b64ef user: drh tags: branch-3.7.2
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

     6      6   **
     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code associated with the ANALYZE command.
           13  +**
           14  +** The ANALYZE command gather statistics about the content of tables
           15  +** and indices.  These statistics are made available to the query planner
           16  +** to help it make better decisions about how to perform queries.
           17  +**
           18  +** The following system tables are or have been supported:
           19  +**
           20  +**    CREATE TABLE sqlite_stat1(tbl, idx, stat);
           21  +**    CREATE TABLE sqlite_stat2(tbl, idx, sampleno, sample);
           22  +**    CREATE TABLE sqlite_stat3(tbl, idx, nEq, nLt, nDLt, sample);
           23  +**
           24  +** Additional tables might be added in future releases of SQLite.
           25  +** The sqlite_stat2 table is not created or used unless the SQLite version
           26  +** is between 3.6.18 and 3.7.7, inclusive, and unless SQLite is compiled
           27  +** with SQLITE_ENABLE_STAT2.  The sqlite_stat2 table is deprecated.
           28  +** The sqlite_stat2 table is superceded by sqlite_stat3, which is only
           29  +** created and used by SQLite versions after 2011-08-09 with
           30  +** SQLITE_ENABLE_STAT3 defined.  The fucntionality of sqlite_stat3
           31  +** is a superset of sqlite_stat2.  
           32  +**
           33  +** Format of sqlite_stat1:
           34  +**
           35  +** There is normally one row per index, with the index identified by the
           36  +** name in the idx column.  The tbl column is the name of the table to
           37  +** which the index belongs.  In each such row, the stat column will be
           38  +** a string consisting of a list of integers.  The first integer in this
           39  +** list is the number of rows in the index and in the table.  The second
           40  +** integer is the average number of rows in the index that have the same
           41  +** value in the first column of the index.  The third integer is the average
           42  +** number of rows in the index that have the same value for the first two
           43  +** columns.  The N-th integer (for N>1) is the average number of rows in 
           44  +** the index which have the same value for the first N-1 columns.  For
           45  +** a K-column index, there will be K+1 integers in the stat column.  If
           46  +** the index is unique, then the last integer will be 1.
           47  +**
           48  +** The list of integers in the stat column can optionally be followed
           49  +** by the keyword "unordered".  The "unordered" keyword, if it is present,
           50  +** must be separated from the last integer by a single space.  If the
           51  +** "unordered" keyword is present, then the query planner assumes that
           52  +** the index is unordered and will not use the index for a range query.
           53  +** 
           54  +** If the sqlite_stat1.idx column is NULL, then the sqlite_stat1.stat
           55  +** column contains a single integer which is the (estimated) number of
           56  +** rows in the table identified by sqlite_stat1.tbl.
           57  +**
           58  +** Format of sqlite_stat2:
           59  +**
           60  +** The sqlite_stat2 is only created and is only used if SQLite is compiled
           61  +** with SQLITE_ENABLE_STAT2 and if the SQLite version number is between
           62  +** 3.6.18 and 3.7.7.  The "stat2" table contains additional information
           63  +** about the distribution of keys within an index.  The index is identified by
           64  +** the "idx" column and the "tbl" column is the name of the table to which
           65  +** the index belongs.  There are usually 10 rows in the sqlite_stat2
           66  +** table for each index.
           67  +**
           68  +** The sqlite_stat2 entries for an index that have sampleno between 0 and 9
           69  +** inclusive are samples of the left-most key value in the index taken at
           70  +** evenly spaced points along the index.  Let the number of samples be S
           71  +** (10 in the standard build) and let C be the number of rows in the index.
           72  +** Then the sampled rows are given by:
           73  +**
           74  +**     rownumber = (i*C*2 + C)/(S*2)
           75  +**
           76  +** For i between 0 and S-1.  Conceptually, the index space is divided into
           77  +** S uniform buckets and the samples are the middle row from each bucket.
           78  +**
           79  +** The format for sqlite_stat2 is recorded here for legacy reference.  This
           80  +** version of SQLite does not support sqlite_stat2.  It neither reads nor
           81  +** writes the sqlite_stat2 table.  This version of SQLite only supports
           82  +** sqlite_stat3.
           83  +**
           84  +** Format for sqlite_stat3:
           85  +**
           86  +** The sqlite_stat3 is an enhancement to sqlite_stat2.  A new name is
           87  +** used to avoid compatibility problems.  
           88  +**
           89  +** The format of the sqlite_stat3 table is similar to the format for
           90  +** the sqlite_stat2 table, with the following changes:  (1)
           91  +** The sampleno column is removed.  (2) Every sample has nEq, nLt, and nDLt
           92  +** columns which hold the approximate number of rows in the table that
           93  +** exactly match the sample, the approximate number of rows with values
           94  +** less than the sample, and the approximate number of distinct key values
           95  +** less than the sample, respectively.  (3) The number of samples can vary 
           96  +** from one table to the next; the sample count does not have to be 
           97  +** exactly 10 as it is with sqlite_stat2.
           98  +**
           99  +** The ANALYZE command will typically generate sqlite_stat3 tables
          100  +** that contain between 10 and 40 samples which are distributed across
          101  +** the key space, though not uniformly, and which include samples with
          102  +** largest possible nEq values.
    13    103   */
    14    104   #ifndef SQLITE_OMIT_ANALYZE
    15    105   #include "sqliteInt.h"
    16    106   
    17    107   /*
    18    108   ** This routine generates code that opens the sqlite_stat1 table for
    19    109   ** writing with cursor iStatCur. If the library was built with the
................................................................................
    37    127     const char *zWhere      /* Delete entries associated with this table */
    38    128   ){
    39    129     static const struct {
    40    130       const char *zName;
    41    131       const char *zCols;
    42    132     } aTable[] = {
    43    133       { "sqlite_stat1", "tbl,idx,stat" },
    44         -#ifdef SQLITE_ENABLE_STAT2
    45         -    { "sqlite_stat2", "tbl,idx,sampleno,sample" },
          134  +#ifdef SQLITE_ENABLE_STAT3
          135  +    { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
          136  +#endif
          137  +  };
          138  +  static const char *azToDrop[] = { 
          139  +    "sqlite_stat2",
          140  +#ifndef SQLITE_ENABLE_STAT3
          141  +    "sqlite_stat3",
    46    142   #endif
    47    143     };
    48    144   
    49    145     int aRoot[] = {0, 0};
    50    146     u8 aCreateTbl[] = {0, 0};
    51    147   
    52    148     int i;
................................................................................
    54    150     Db *pDb;
    55    151     Vdbe *v = sqlite3GetVdbe(pParse);
    56    152     if( v==0 ) return;
    57    153     assert( sqlite3BtreeHoldsAllMutexes(db) );
    58    154     assert( sqlite3VdbeDb(v)==db );
    59    155     pDb = &db->aDb[iDb];
    60    156   
          157  +  /* Drop all statistics tables that this version of SQLite does not
          158  +  ** understand.
          159  +  */
          160  +  for(i=0; i<ArraySize(azToDrop); i++){
          161  +    Table *pTab = sqlite3FindTable(db, azToDrop[i], pDb->zName);
          162  +    if( pTab ) sqlite3CodeDropTable(pParse, pTab, iDb, 0);
          163  +  }
          164  +
          165  +  /* Create new statistic tables if they do not exist, or clear them
          166  +  ** if they do already exist.
          167  +  */
    61    168     for(i=0; i<ArraySize(aTable); i++){
    62    169       const char *zTab = aTable[i].zName;
    63    170       Table *pStat;
    64    171       if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){
    65    172         /* The sqlite_stat[12] table does not exist. Create it. Note that a 
    66    173         ** side-effect of the CREATE TABLE statement is to leave the rootpage 
    67    174         ** of the new table in register pParse->regRoot. This is important 
................................................................................
    84    191         }else{
    85    192           /* The sqlite_stat[12] table already exists.  Delete all rows. */
    86    193           sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
    87    194         }
    88    195       }
    89    196     }
    90    197   
    91         -  /* Open the sqlite_stat[12] tables for writing. */
          198  +  /* Open the sqlite_stat[13] tables for writing. */
    92    199     for(i=0; i<ArraySize(aTable); i++){
    93    200       sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    94    201       sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
    95    202       sqlite3VdbeChangeP5(v, aCreateTbl[i]);
    96    203     }
    97    204   }
          205  +
          206  +/*
          207  +** Recommended number of samples for sqlite_stat3
          208  +*/
          209  +#ifndef SQLITE_STAT3_SAMPLES
          210  +# define SQLITE_STAT3_SAMPLES 24
          211  +#endif
          212  +
          213  +/*
          214  +** Three SQL functions - stat3_init(), stat3_push(), and stat3_pop() -
          215  +** share an instance of the following structure to hold their state
          216  +** information.
          217  +*/
          218  +typedef struct Stat3Accum Stat3Accum;
          219  +struct Stat3Accum {
          220  +  tRowcnt nRow;             /* Number of rows in the entire table */
          221  +  tRowcnt nPSample;         /* How often to do a periodic sample */
          222  +  int iMin;                 /* Index of entry with minimum nEq and hash */
          223  +  int mxSample;             /* Maximum number of samples to accumulate */
          224  +  int nSample;              /* Current number of samples */
          225  +  u32 iPrn;                 /* Pseudo-random number used for sampling */
          226  +  struct Stat3Sample {
          227  +    i64 iRowid;                /* Rowid in main table of the key */
          228  +    tRowcnt nEq;               /* sqlite_stat3.nEq */
          229  +    tRowcnt nLt;               /* sqlite_stat3.nLt */
          230  +    tRowcnt nDLt;              /* sqlite_stat3.nDLt */
          231  +    u8 isPSample;              /* True if a periodic sample */
          232  +    u32 iHash;                 /* Tiebreaker hash */
          233  +  } *a;                     /* An array of samples */
          234  +};
          235  +
          236  +#ifdef SQLITE_ENABLE_STAT3
          237  +/*
          238  +** Implementation of the stat3_init(C,S) SQL function.  The two parameters
          239  +** are the number of rows in the table or index (C) and the number of samples
          240  +** to accumulate (S).
          241  +**
          242  +** This routine allocates the Stat3Accum object.
          243  +**
          244  +** The return value is the Stat3Accum object (P).
          245  +*/
          246  +static void stat3Init(
          247  +  sqlite3_context *context,
          248  +  int argc,
          249  +  sqlite3_value **argv
          250  +){
          251  +  Stat3Accum *p;
          252  +  tRowcnt nRow;
          253  +  int mxSample;
          254  +  int n;
          255  +
          256  +  UNUSED_PARAMETER(argc);
          257  +  nRow = (tRowcnt)sqlite3_value_int64(argv[0]);
          258  +  mxSample = sqlite3_value_int(argv[1]);
          259  +  n = sizeof(*p) + sizeof(p->a[0])*mxSample;
          260  +  p = sqlite3_malloc( n );
          261  +  if( p==0 ){
          262  +    sqlite3_result_error_nomem(context);
          263  +    return;
          264  +  }
          265  +  memset(p, 0, n);
          266  +  p->a = (struct Stat3Sample*)&p[1];
          267  +  p->nRow = nRow;
          268  +  p->mxSample = mxSample;
          269  +  p->nPSample = p->nRow/(mxSample/3+1) + 1;
          270  +  sqlite3_randomness(sizeof(p->iPrn), &p->iPrn);
          271  +  sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
          272  +}
          273  +static const FuncDef stat3InitFuncdef = {
          274  +  2,                /* nArg */
          275  +  SQLITE_UTF8,      /* iPrefEnc */
          276  +  0,                /* flags */
          277  +  0,                /* pUserData */
          278  +  0,                /* pNext */
          279  +  stat3Init,        /* xFunc */
          280  +  0,                /* xStep */
          281  +  0,                /* xFinalize */
          282  +  "stat3_init",     /* zName */
          283  +  0                 /* pHash */
          284  +};
          285  +
          286  +
          287  +/*
          288  +** Implementation of the stat3_push(nEq,nLt,nDLt,rowid,P) SQL function.  The
          289  +** arguments describe a single key instance.  This routine makes the 
          290  +** decision about whether or not to retain this key for the sqlite_stat3
          291  +** table.
          292  +**
          293  +** The return value is NULL.
          294  +*/
          295  +static void stat3Push(
          296  +  sqlite3_context *context,
          297  +  int argc,
          298  +  sqlite3_value **argv
          299  +){
          300  +  Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[4]);
          301  +  tRowcnt nEq = sqlite3_value_int64(argv[0]);
          302  +  tRowcnt nLt = sqlite3_value_int64(argv[1]);
          303  +  tRowcnt nDLt = sqlite3_value_int64(argv[2]);
          304  +  i64 rowid = sqlite3_value_int64(argv[3]);
          305  +  u8 isPSample = 0;
          306  +  u8 doInsert = 0;
          307  +  int iMin = p->iMin;
          308  +  struct Stat3Sample *pSample;
          309  +  int i;
          310  +  u32 h;
          311  +
          312  +  UNUSED_PARAMETER(context);
          313  +  UNUSED_PARAMETER(argc);
          314  +  if( nEq==0 ) return;
          315  +  h = p->iPrn = p->iPrn*1103515245 + 12345;
          316  +  if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){
          317  +    doInsert = isPSample = 1;
          318  +  }else if( p->nSample<p->mxSample ){
          319  +    doInsert = 1;
          320  +  }else{
          321  +    if( nEq>p->a[iMin].nEq || (nEq==p->a[iMin].nEq && h>p->a[iMin].iHash) ){
          322  +      doInsert = 1;
          323  +    }
          324  +  }
          325  +  if( !doInsert ) return;
          326  +  if( p->nSample==p->mxSample ){
          327  +    if( iMin<p->nSample ){
          328  +      memcpy(&p->a[iMin], &p->a[iMin+1], sizeof(p->a[0])*(p->nSample-iMin));
          329  +    }
          330  +    pSample = &p->a[p->nSample-1];
          331  +  }else{
          332  +    pSample = &p->a[p->nSample++];
          333  +  }
          334  +  pSample->iRowid = rowid;
          335  +  pSample->nEq = nEq;
          336  +  pSample->nLt = nLt;
          337  +  pSample->nDLt = nDLt;
          338  +  pSample->iHash = h;
          339  +  pSample->isPSample = isPSample;
          340  +
          341  +  /* Find the new minimum */
          342  +  if( p->nSample==p->mxSample ){
          343  +    pSample = p->a;
          344  +    i = 0;
          345  +    while( pSample->isPSample ){
          346  +      i++;
          347  +      pSample++;
          348  +      assert( i<p->nSample );
          349  +    }
          350  +    nEq = pSample->nEq;
          351  +    h = pSample->iHash;
          352  +    iMin = i;
          353  +    for(i++, pSample++; i<p->nSample; i++, pSample++){
          354  +      if( pSample->isPSample ) continue;
          355  +      if( pSample->nEq<nEq
          356  +       || (pSample->nEq==nEq && pSample->iHash<h)
          357  +      ){
          358  +        iMin = i;
          359  +        nEq = pSample->nEq;
          360  +        h = pSample->iHash;
          361  +      }
          362  +    }
          363  +    p->iMin = iMin;
          364  +  }
          365  +}
          366  +static const FuncDef stat3PushFuncdef = {
          367  +  5,                /* nArg */
          368  +  SQLITE_UTF8,      /* iPrefEnc */
          369  +  0,                /* flags */
          370  +  0,                /* pUserData */
          371  +  0,                /* pNext */
          372  +  stat3Push,        /* xFunc */
          373  +  0,                /* xStep */
          374  +  0,                /* xFinalize */
          375  +  "stat3_push",     /* zName */
          376  +  0                 /* pHash */
          377  +};
          378  +
          379  +/*
          380  +** Implementation of the stat3_get(P,N,...) SQL function.  This routine is
          381  +** used to query the results.  Content is returned for the Nth sqlite_stat3
          382  +** row where N is between 0 and S-1 and S is the number of samples.  The
          383  +** value returned depends on the number of arguments.
          384  +**
          385  +**   argc==2    result:  rowid
          386  +**   argc==3    result:  nEq
          387  +**   argc==4    result:  nLt
          388  +**   argc==5    result:  nDLt
          389  +*/
          390  +static void stat3Get(
          391  +  sqlite3_context *context,
          392  +  int argc,
          393  +  sqlite3_value **argv
          394  +){
          395  +  int n = sqlite3_value_int(argv[1]);
          396  +  Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[0]);
          397  +
          398  +  assert( p!=0 );
          399  +  if( p->nSample<=n ) return;
          400  +  switch( argc ){
          401  +    case 2: sqlite3_result_int64(context, p->a[n].iRowid); break;
          402  +    case 3: sqlite3_result_int64(context, p->a[n].nEq);    break;
          403  +    case 4: sqlite3_result_int64(context, p->a[n].nLt);    break;
          404  +    case 5: sqlite3_result_int64(context, p->a[n].nDLt);   break;
          405  +  }
          406  +}
          407  +static const FuncDef stat3GetFuncdef = {
          408  +  -1,               /* nArg */
          409  +  SQLITE_UTF8,      /* iPrefEnc */
          410  +  0,                /* flags */
          411  +  0,                /* pUserData */
          412  +  0,                /* pNext */
          413  +  stat3Get,         /* xFunc */
          414  +  0,                /* xStep */
          415  +  0,                /* xFinalize */
          416  +  "stat3_get",      /* zName */
          417  +  0                 /* pHash */
          418  +};
          419  +#endif /* SQLITE_ENABLE_STAT3 */
          420  +
          421  +
          422  +
    98    423   
    99    424   /*
   100    425   ** Generate code to do an analysis of all indices associated with
   101    426   ** a single table.
   102    427   */
   103    428   static void analyzeOneTable(
   104    429     Parse *pParse,   /* Parser context */
................................................................................
   114    439     int topOfLoop;               /* The top of the loop */
   115    440     int endOfLoop;               /* The end of the loop */
   116    441     int addr = 0;                /* The address of an instruction */
   117    442     int jZeroRows = 0;           /* Jump from here if number of rows is zero */
   118    443     int iDb;                     /* Index of database containing pTab */
   119    444     int regTabname = iMem++;     /* Register containing table name */
   120    445     int regIdxname = iMem++;     /* Register containing index name */
   121         -  int regSampleno = iMem++;    /* Register containing next sample number */
   122         -  int regCol = iMem++;         /* Content of a column analyzed table */
          446  +  int regStat1 = iMem++;       /* The stat column of sqlite_stat1 */
          447  +#ifdef SQLITE_ENABLE_STAT3
          448  +  int regNumEq = regStat1;     /* Number of instances.  Same as regStat1 */
          449  +  int regNumLt = iMem++;       /* Number of keys less than regSample */
          450  +  int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
          451  +  int regSample = iMem++;      /* The next sample value */
          452  +  int regRowid = regSample;    /* Rowid of a sample */
          453  +  int regAccum = iMem++;       /* Register to hold Stat3Accum object */
          454  +  int regLoop = iMem++;        /* Loop counter */
          455  +  int regCount = iMem++;       /* Number of rows in the table or index */
          456  +  int regTemp1 = iMem++;       /* Intermediate register */
          457  +  int regTemp2 = iMem++;       /* Intermediate register */
          458  +  int once = 1;                /* One-time initialization */
          459  +  int shortJump = 0;           /* Instruction address */
          460  +  int iTabCur = pParse->nTab++; /* Table cursor */
          461  +#endif
          462  +  int regCol = iMem++;         /* Content of a column in analyzed table */
   123    463     int regRec = iMem++;         /* Register holding completed record */
   124    464     int regTemp = iMem++;        /* Temporary use register */
   125         -  int regRowid = iMem++;       /* Rowid for the inserted record */
   126         -
   127         -#ifdef SQLITE_ENABLE_STAT2
   128         -  int regTemp2 = iMem++;       /* Temporary use register */
   129         -  int regSamplerecno = iMem++; /* Index of next sample to record */
   130         -  int regRecno = iMem++;       /* Current sample index */
   131         -  int regLast = iMem++;        /* Index of last sample to record */
   132         -  int regFirst = iMem++;       /* Index of first sample to record */
   133         -#endif
          465  +  int regNewRowid = iMem++;    /* Rowid for the inserted record */
   134    466   
   135    467     v = sqlite3GetVdbe(pParse);
   136    468     if( v==0 || NEVER(pTab==0) ){
   137    469       return;
   138    470     }
   139    471     if( pTab->tnum==0 ){
   140    472       /* Do not gather statistics on views or virtual tables */
................................................................................
   158    490     sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
   159    491   
   160    492     iIdxCur = pParse->nTab++;
   161    493     sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
   162    494     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   163    495       int nCol = pIdx->nColumn;
   164    496       KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
          497  +    int addrIfNot = 0;           /* address of OP_IfNot */
          498  +    int *aChngAddr;              /* Array of jump instruction addresses */
   165    499   
          500  +    VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
          501  +    nCol = pIdx->nColumn;
          502  +    aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol);
          503  +    if( aChngAddr==0 ) continue;
          504  +    pKey = sqlite3IndexKeyinfo(pParse, pIdx);
   166    505       if( iMem+1+(nCol*2)>pParse->nMem ){
   167    506         pParse->nMem = iMem+1+(nCol*2);
   168    507       }
   169    508   
   170    509       /* Open a cursor to the index to be analyzed. */
   171    510       assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
   172    511       sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
   173    512           (char *)pKey, P4_KEYINFO_HANDOFF);
   174    513       VdbeComment((v, "%s", pIdx->zName));
   175    514   
   176    515       /* Populate the register containing the index name. */
   177    516       sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);
   178    517   
   179         -#ifdef SQLITE_ENABLE_STAT2
   180         -
   181         -    /* If this iteration of the loop is generating code to analyze the
   182         -    ** first index in the pTab->pIndex list, then register regLast has
   183         -    ** not been populated. In this case populate it now.  */
   184         -    if( pTab->pIndex==pIdx ){
   185         -      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regSamplerecno);
   186         -      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2-1, regTemp);
   187         -      sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES*2, regTemp2);
   188         -
   189         -      sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regLast);
   190         -      sqlite3VdbeAddOp2(v, OP_Null, 0, regFirst);
   191         -      addr = sqlite3VdbeAddOp3(v, OP_Lt, regSamplerecno, 0, regLast);
   192         -      sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regLast, regFirst);
   193         -      sqlite3VdbeAddOp3(v, OP_Multiply, regLast, regTemp, regLast);
   194         -      sqlite3VdbeAddOp2(v, OP_AddImm, regLast, SQLITE_INDEX_SAMPLES*2-2);
   195         -      sqlite3VdbeAddOp3(v, OP_Divide,  regTemp2, regLast, regLast);
   196         -      sqlite3VdbeJumpHere(v, addr);
          518  +#ifdef SQLITE_ENABLE_STAT3
          519  +    if( once ){
          520  +      once = 0;
          521  +      sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);
   197    522       }
   198         -
   199         -    /* Zero the regSampleno and regRecno registers. */
   200         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, regSampleno);
   201         -    sqlite3VdbeAddOp2(v, OP_Integer, 0, regRecno);
   202         -    sqlite3VdbeAddOp2(v, OP_Copy, regFirst, regSamplerecno);
   203         -#endif
          523  +    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
          524  +    sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT3_SAMPLES, regTemp1);
          525  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumEq);
          526  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumLt);
          527  +    sqlite3VdbeAddOp2(v, OP_Integer, -1, regNumDLt);
          528  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regCount, regAccum,
          529  +                      (char*)&stat3InitFuncdef, P4_FUNCDEF);
          530  +    sqlite3VdbeChangeP5(v, 2);
          531  +#endif /* SQLITE_ENABLE_STAT3 */
   204    532   
   205    533       /* The block of memory cells initialized here is used as follows.
   206    534       **
   207    535       **    iMem:                
   208    536       **        The total number of rows in the table.
   209    537       **
   210    538       **    iMem+1 .. iMem+nCol: 
................................................................................
   226    554       }
   227    555   
   228    556       /* Start the analysis loop. This loop runs through all the entries in
   229    557       ** the index b-tree.  */
   230    558       endOfLoop = sqlite3VdbeMakeLabel(v);
   231    559       sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
   232    560       topOfLoop = sqlite3VdbeCurrentAddr(v);
   233         -    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);
          561  +    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);  /* Increment row counter */
   234    562   
   235    563       for(i=0; i<nCol; i++){
   236    564         CollSeq *pColl;
   237    565         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   238    566         if( i==0 ){
   239         -#ifdef SQLITE_ENABLE_STAT2
   240         -        /* Check if the record that cursor iIdxCur points to contains a
   241         -        ** value that should be stored in the sqlite_stat2 table. If so,
   242         -        ** store it.  */
   243         -        int ne = sqlite3VdbeAddOp3(v, OP_Ne, regRecno, 0, regSamplerecno);
   244         -        assert( regTabname+1==regIdxname 
   245         -             && regTabname+2==regSampleno
   246         -             && regTabname+3==regCol
   247         -        );
   248         -        sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
   249         -        sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 4, regRec, "aaab", 0);
   250         -        sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regRowid);
   251         -        sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regRowid);
   252         -
   253         -        /* Calculate new values for regSamplerecno and regSampleno.
   254         -        **
   255         -        **   sampleno = sampleno + 1
   256         -        **   samplerecno = samplerecno+(remaining records)/(remaining samples)
   257         -        */
   258         -        sqlite3VdbeAddOp2(v, OP_AddImm, regSampleno, 1);
   259         -        sqlite3VdbeAddOp3(v, OP_Subtract, regRecno, regLast, regTemp);
   260         -        sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
   261         -        sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_INDEX_SAMPLES, regTemp2);
   262         -        sqlite3VdbeAddOp3(v, OP_Subtract, regSampleno, regTemp2, regTemp2);
   263         -        sqlite3VdbeAddOp3(v, OP_Divide, regTemp2, regTemp, regTemp);
   264         -        sqlite3VdbeAddOp3(v, OP_Add, regSamplerecno, regTemp, regSamplerecno);
   265         -
   266         -        sqlite3VdbeJumpHere(v, ne);
   267         -        sqlite3VdbeAddOp2(v, OP_AddImm, regRecno, 1);
   268         -#endif
   269         -
   270    567           /* Always record the very first row */
   271         -        sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
          568  +        addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
   272    569         }
   273    570         assert( pIdx->azColl!=0 );
   274    571         assert( pIdx->azColl[i]!=0 );
   275    572         pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
   276         -      sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
   277         -                       (char*)pColl, P4_COLLSEQ);
          573  +      aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
          574  +                                      (char*)pColl, P4_COLLSEQ);
   278    575         sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
   279         -    }
   280         -    if( db->mallocFailed ){
   281         -      /* If a malloc failure has occurred, then the result of the expression 
   282         -      ** passed as the second argument to the call to sqlite3VdbeJumpHere() 
   283         -      ** below may be negative. Which causes an assert() to fail (or an
   284         -      ** out-of-bounds write if SQLITE_DEBUG is not defined).  */
   285         -      return;
          576  +      VdbeComment((v, "jump if column %d changed", i));
          577  +#ifdef SQLITE_ENABLE_STAT3
          578  +      if( i==0 ){
          579  +        sqlite3VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
          580  +        VdbeComment((v, "incr repeat count"));
          581  +      }
          582  +#endif
   286    583       }
   287    584       sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
   288    585       for(i=0; i<nCol; i++){
   289         -      int addr2 = sqlite3VdbeCurrentAddr(v) - (nCol*2);
          586  +      sqlite3VdbeJumpHere(v, aChngAddr[i]);  /* Set jump dest for the OP_Ne */
   290    587         if( i==0 ){
   291         -        sqlite3VdbeJumpHere(v, addr2-1);  /* Set jump dest for the OP_IfNot */
          588  +        sqlite3VdbeJumpHere(v, addrIfNot);   /* Jump dest for OP_IfNot */
          589  +#ifdef SQLITE_ENABLE_STAT3
          590  +        sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
          591  +                          (char*)&stat3PushFuncdef, P4_FUNCDEF);
          592  +        sqlite3VdbeChangeP5(v, 5);
          593  +        sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, pIdx->nColumn, regRowid);
          594  +        sqlite3VdbeAddOp3(v, OP_Add, regNumEq, regNumLt, regNumLt);
          595  +        sqlite3VdbeAddOp2(v, OP_AddImm, regNumDLt, 1);
          596  +        sqlite3VdbeAddOp2(v, OP_Integer, 1, regNumEq);
          597  +#endif        
   292    598         }
   293         -      sqlite3VdbeJumpHere(v, addr2);      /* Set jump dest for the OP_Ne */
   294    599         sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
   295    600         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
   296    601       }
          602  +    sqlite3DbFree(db, aChngAddr);
   297    603   
   298         -    /* End of the analysis loop. */
          604  +    /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
   299    605       sqlite3VdbeResolveLabel(v, endOfLoop);
          606  +
   300    607       sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
   301    608       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
          609  +#ifdef SQLITE_ENABLE_STAT3
          610  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
          611  +                      (char*)&stat3PushFuncdef, P4_FUNCDEF);
          612  +    sqlite3VdbeChangeP5(v, 5);
          613  +    sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop);
          614  +    shortJump = 
          615  +    sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1);
          616  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regTemp1,
          617  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          618  +    sqlite3VdbeChangeP5(v, 2);
          619  +    sqlite3VdbeAddOp1(v, OP_IsNull, regTemp1);
          620  +    sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regTemp1);
          621  +    sqlite3VdbeAddOp3(v, OP_Column, iTabCur, pIdx->aiColumn[0], regSample);
          622  +    sqlite3ColumnDefault(v, pTab, pIdx->aiColumn[0], regSample);
          623  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumEq,
          624  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          625  +    sqlite3VdbeChangeP5(v, 3);
          626  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumLt,
          627  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          628  +    sqlite3VdbeChangeP5(v, 4);
          629  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumDLt,
          630  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          631  +    sqlite3VdbeChangeP5(v, 5);
          632  +    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 6, regRec, "bbbbbb", 0);
          633  +    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
          634  +    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regNewRowid);
          635  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, shortJump);
          636  +    sqlite3VdbeJumpHere(v, shortJump+2);
          637  +#endif        
   302    638   
   303    639       /* Store the results in sqlite_stat1.
   304    640       **
   305    641       ** The result is a single row of the sqlite_stat1 table.  The first
   306    642       ** two columns are the names of the table and index.  The third column
   307    643       ** is a string composed of a list of integer statistics about the
   308    644       ** index.  The first integer in the list is the total number of entries
................................................................................
   314    650       **
   315    651       **        I = (K+D-1)/D
   316    652       **
   317    653       ** If K==0 then no entry is made into the sqlite_stat1 table.  
   318    654       ** If K>0 then it is always the case the D>0 so division by zero
   319    655       ** is never possible.
   320    656       */
   321         -    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno);
          657  +    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1);
   322    658       if( jZeroRows==0 ){
   323    659         jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
   324    660       }
   325    661       for(i=0; i<nCol; i++){
   326    662         sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0);
   327         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
          663  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   328    664         sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp);
   329    665         sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
   330    666         sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp);
   331    667         sqlite3VdbeAddOp1(v, OP_ToInt, regTemp);
   332         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
          668  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   333    669       }
   334    670       sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   335         -    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid);
   336         -    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid);
          671  +    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
          672  +    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   337    673       sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   338    674     }
   339    675   
   340    676     /* If the table has no indices, create a single sqlite_stat1 entry
   341    677     ** containing NULL as the index name and the row count as the content.
   342    678     */
   343    679     if( pTab->pIndex==0 ){
   344    680       sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
   345    681       VdbeComment((v, "%s", pTab->zName));
   346         -    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno);
          682  +    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat1);
   347    683       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
          684  +    jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1);
   348    685     }else{
   349    686       assert( jZeroRows>0 );
   350    687       addr = sqlite3VdbeAddOp0(v, OP_Goto);
   351    688       sqlite3VdbeJumpHere(v, jZeroRows);
   352    689     }
   353    690     sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
   354    691     sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   355         -  sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid);
   356         -  sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid);
          692  +  sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
          693  +  sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   357    694     sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   358    695     if( pParse->nMem<regRec ) pParse->nMem = regRec;
   359    696     if( jZeroRows ){
   360    697       sqlite3VdbeJumpHere(v, addr);
   361    698     }
   362    699   }
          700  +
   363    701   
   364    702   /*
   365    703   ** Generate code that will cause the most recent index analysis to
   366    704   ** be loaded into internal hash tables where is can be used.
   367    705   */
   368    706   static void loadAnalysis(Parse *pParse, int iDb){
   369    707     Vdbe *v = sqlite3GetVdbe(pParse);
................................................................................
   380    718     Schema *pSchema = db->aDb[iDb].pSchema;    /* Schema of database iDb */
   381    719     HashElem *k;
   382    720     int iStatCur;
   383    721     int iMem;
   384    722   
   385    723     sqlite3BeginWriteOperation(pParse, 0, iDb);
   386    724     iStatCur = pParse->nTab;
   387         -  pParse->nTab += 2;
          725  +  pParse->nTab += 3;
   388    726     openStatTable(pParse, iDb, iStatCur, 0);
   389    727     iMem = pParse->nMem+1;
   390    728     for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
   391    729       Table *pTab = (Table*)sqliteHashData(k);
   392    730       analyzeOneTable(pParse, pTab, iStatCur, iMem);
   393    731     }
   394    732     loadAnalysis(pParse, iDb);
................................................................................
   403    741     int iStatCur;
   404    742   
   405    743     assert( pTab!=0 );
   406    744     assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
   407    745     iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
   408    746     sqlite3BeginWriteOperation(pParse, 0, iDb);
   409    747     iStatCur = pParse->nTab;
   410         -  pParse->nTab += 2;
          748  +  pParse->nTab += 3;
   411    749     openStatTable(pParse, iDb, iStatCur, pTab->zName);
   412    750     analyzeOneTable(pParse, pTab, iStatCur, pParse->nMem+1);
   413    751     loadAnalysis(pParse, iDb);
   414    752   }
   415    753   
   416    754   /*
   417    755   ** Generate code for the ANALYZE command.  The parser calls this routine
................................................................................
   501    839   ** the table.
   502    840   */
   503    841   static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){
   504    842     analysisInfo *pInfo = (analysisInfo*)pData;
   505    843     Index *pIndex;
   506    844     Table *pTable;
   507    845     int i, c, n;
   508         -  unsigned int v;
          846  +  tRowcnt v;
   509    847     const char *z;
   510    848   
   511    849     assert( argc==3 );
   512    850     UNUSED_PARAMETER2(NotUsed, argc);
   513    851   
   514    852     if( argv==0 || argv[0]==0 || argv[2]==0 ){
   515    853       return 0;
................................................................................
   544    882   }
   545    883   
   546    884   /*
   547    885   ** If the Index.aSample variable is not NULL, delete the aSample[] array
   548    886   ** and its contents.
   549    887   */
   550    888   void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
   551         -#ifdef SQLITE_ENABLE_STAT2
          889  +#ifdef SQLITE_ENABLE_STAT3
   552    890     if( pIdx->aSample ){
   553    891       int j;
   554         -    for(j=0; j<SQLITE_INDEX_SAMPLES; j++){
          892  +    for(j=0; j<pIdx->nSample; j++){
   555    893         IndexSample *p = &pIdx->aSample[j];
   556    894         if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){
   557    895           sqlite3DbFree(db, p->u.z);
   558    896         }
   559    897       }
   560    898       sqlite3DbFree(db, pIdx->aSample);
          899  +  }
          900  +  if( db && db->pnBytesFreed==0 ){
          901  +    pIdx->nSample = 0;
          902  +    pIdx->aSample = 0;
   561    903     }
   562    904   #else
   563    905     UNUSED_PARAMETER(db);
   564    906     UNUSED_PARAMETER(pIdx);
   565    907   #endif
   566    908   }
   567    909   
          910  +#ifdef SQLITE_ENABLE_STAT3
          911  +/*
          912  +** Load content from the sqlite_stat3 table into the Index.aSample[]
          913  +** arrays of all indices.
          914  +*/
          915  +static int loadStat3(sqlite3 *db, const char *zDb){
          916  +  int rc;                       /* Result codes from subroutines */
          917  +  sqlite3_stmt *pStmt = 0;      /* An SQL statement being run */
          918  +  char *zSql;                   /* Text of the SQL statement */
          919  +  Index *pPrevIdx = 0;          /* Previous index in the loop */
          920  +  int idx = 0;                  /* slot in pIdx->aSample[] for next sample */
          921  +  int eType;                    /* Datatype of a sample */
          922  +  IndexSample *pSample;         /* A slot in pIdx->aSample[] */
          923  +
          924  +  if( !sqlite3FindTable(db, "sqlite_stat3", zDb) ){
          925  +    return SQLITE_OK;
          926  +  }
          927  +
          928  +  zSql = sqlite3MPrintf(db, 
          929  +      "SELECT idx,count(*) FROM %Q.sqlite_stat3"
          930  +      " GROUP BY idx", zDb);
          931  +  if( !zSql ){
          932  +    return SQLITE_NOMEM;
          933  +  }
          934  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          935  +  sqlite3DbFree(db, zSql);
          936  +  if( rc ) return rc;
          937  +
          938  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          939  +    char *zIndex;   /* Index name */
          940  +    Index *pIdx;    /* Pointer to the index object */
          941  +    int nSample;    /* Number of samples */
          942  +
          943  +    zIndex = (char *)sqlite3_column_text(pStmt, 0);
          944  +    if( zIndex==0 ) continue;
          945  +    nSample = sqlite3_column_int(pStmt, 1);
          946  +    if( nSample>255 ) continue;
          947  +    pIdx = sqlite3FindIndex(db, zIndex, zDb);
          948  +    if( pIdx==0 ) continue;
          949  +    assert( pIdx->nSample==0 );
          950  +    pIdx->nSample = (u8)nSample;
          951  +    pIdx->aSample = sqlite3MallocZero( nSample*sizeof(IndexSample) );
          952  +    pIdx->avgEq = pIdx->aiRowEst[1];
          953  +    if( pIdx->aSample==0 ){
          954  +      db->mallocFailed = 1;
          955  +      sqlite3_finalize(pStmt);
          956  +      return SQLITE_NOMEM;
          957  +    }
          958  +  }
          959  +  rc = sqlite3_finalize(pStmt);
          960  +  if( rc ) return rc;
          961  +
          962  +  zSql = sqlite3MPrintf(db, 
          963  +      "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat3", zDb);
          964  +  if( !zSql ){
          965  +    return SQLITE_NOMEM;
          966  +  }
          967  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          968  +  sqlite3DbFree(db, zSql);
          969  +  if( rc ) return rc;
          970  +
          971  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          972  +    char *zIndex;   /* Index name */
          973  +    Index *pIdx;    /* Pointer to the index object */
          974  +    int i;          /* Loop counter */
          975  +    tRowcnt sumEq;  /* Sum of the nEq values */
          976  +
          977  +    zIndex = (char *)sqlite3_column_text(pStmt, 0);
          978  +    if( zIndex==0 ) continue;
          979  +    pIdx = sqlite3FindIndex(db, zIndex, zDb);
          980  +    if( pIdx==0 ) continue;
          981  +    if( pIdx==pPrevIdx ){
          982  +      idx++;
          983  +    }else{
          984  +      pPrevIdx = pIdx;
          985  +      idx = 0;
          986  +    }
          987  +    assert( idx<pIdx->nSample );
          988  +    pSample = &pIdx->aSample[idx];
          989  +    pSample->nEq = (tRowcnt)sqlite3_column_int64(pStmt, 1);
          990  +    pSample->nLt = (tRowcnt)sqlite3_column_int64(pStmt, 2);
          991  +    pSample->nDLt = (tRowcnt)sqlite3_column_int64(pStmt, 3);
          992  +    if( idx==pIdx->nSample-1 ){
          993  +      if( pSample->nDLt>0 ){
          994  +        for(i=0, sumEq=0; i<=idx-1; i++) sumEq += pIdx->aSample[i].nEq;
          995  +        pIdx->avgEq = (pSample->nLt - sumEq)/pSample->nDLt;
          996  +      }
          997  +      if( pIdx->avgEq<=0 ) pIdx->avgEq = 1;
          998  +    }
          999  +    eType = sqlite3_column_type(pStmt, 4);
         1000  +    pSample->eType = (u8)eType;
         1001  +    switch( eType ){
         1002  +      case SQLITE_INTEGER: {
         1003  +        pSample->u.i = sqlite3_column_int64(pStmt, 4);
         1004  +        break;
         1005  +      }
         1006  +      case SQLITE_FLOAT: {
         1007  +        pSample->u.r = sqlite3_column_double(pStmt, 4);
         1008  +        break;
         1009  +      }
         1010  +      case SQLITE_NULL: {
         1011  +        break;
         1012  +      }
         1013  +      default: assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB ); {
         1014  +        const char *z = (const char *)(
         1015  +              (eType==SQLITE_BLOB) ?
         1016  +              sqlite3_column_blob(pStmt, 4):
         1017  +              sqlite3_column_text(pStmt, 4)
         1018  +           );
         1019  +        int n = z ? sqlite3_column_bytes(pStmt, 4) : 0;
         1020  +        if( n>0xffff ) n = 0xffff;
         1021  +        pSample->nByte = (u16)n;
         1022  +        if( n < 1){
         1023  +          pSample->u.z = 0;
         1024  +        }else{
         1025  +          pSample->u.z = sqlite3Malloc(n);
         1026  +          if( pSample->u.z==0 ){
         1027  +            db->mallocFailed = 1;
         1028  +            sqlite3_finalize(pStmt);
         1029  +            return SQLITE_NOMEM;
         1030  +          }
         1031  +          memcpy(pSample->u.z, z, n);
         1032  +        }
         1033  +      }
         1034  +    }
         1035  +  }
         1036  +  return sqlite3_finalize(pStmt);
         1037  +}
         1038  +#endif /* SQLITE_ENABLE_STAT3 */
         1039  +
   568   1040   /*
   569         -** Load the content of the sqlite_stat1 and sqlite_stat2 tables. The
         1041  +** Load the content of the sqlite_stat1 and sqlite_stat3 tables. The
   570   1042   ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[]
   571         -** arrays. The contents of sqlite_stat2 are used to populate the
         1043  +** arrays. The contents of sqlite_stat3 are used to populate the
   572   1044   ** Index.aSample[] arrays.
   573   1045   **
   574   1046   ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR
   575         -** is returned. In this case, even if SQLITE_ENABLE_STAT2 was defined 
   576         -** during compilation and the sqlite_stat2 table is present, no data is 
         1047  +** is returned. In this case, even if SQLITE_ENABLE_STAT3 was defined 
         1048  +** during compilation and the sqlite_stat3 table is present, no data is 
   577   1049   ** read from it.
   578   1050   **
   579         -** If SQLITE_ENABLE_STAT2 was defined during compilation and the 
   580         -** sqlite_stat2 table is not present in the database, SQLITE_ERROR is
         1051  +** If SQLITE_ENABLE_STAT3 was defined during compilation and the 
         1052  +** sqlite_stat3 table is not present in the database, SQLITE_ERROR is
   581   1053   ** returned. However, in this case, data is read from the sqlite_stat1
   582   1054   ** table (if it is present) before returning.
   583   1055   **
   584   1056   ** If an OOM error occurs, this function always sets db->mallocFailed.
   585   1057   ** This means if the caller does not care about other errors, the return
   586   1058   ** code may be ignored.
   587   1059   */
................................................................................
   595   1067     assert( db->aDb[iDb].pBt!=0 );
   596   1068     assert( sqlite3BtreeHoldsMutex(db->aDb[iDb].pBt) );
   597   1069   
   598   1070     /* Clear any prior statistics */
   599   1071     for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
   600   1072       Index *pIdx = sqliteHashData(i);
   601   1073       sqlite3DefaultRowEst(pIdx);
         1074  +#ifdef SQLITE_ENABLE_STAT3
   602   1075       sqlite3DeleteIndexSamples(db, pIdx);
   603   1076       pIdx->aSample = 0;
         1077  +#endif
   604   1078     }
   605   1079   
   606   1080     /* Check to make sure the sqlite_stat1 table exists */
   607   1081     sInfo.db = db;
   608   1082     sInfo.zDatabase = db->aDb[iDb].zName;
   609   1083     if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){
   610   1084       return SQLITE_ERROR;
   611   1085     }
   612   1086   
   613   1087     /* Load new statistics out of the sqlite_stat1 table */
   614   1088     zSql = sqlite3MPrintf(db, 
   615         -      "SELECT tbl, idx, stat FROM %Q.sqlite_stat1", sInfo.zDatabase);
         1089  +      "SELECT tbl,idx,stat FROM %Q.sqlite_stat1", sInfo.zDatabase);
   616   1090     if( zSql==0 ){
   617   1091       rc = SQLITE_NOMEM;
   618   1092     }else{
   619   1093       rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
   620   1094       sqlite3DbFree(db, zSql);
   621   1095     }
   622   1096   
   623   1097   
   624         -  /* Load the statistics from the sqlite_stat2 table. */
   625         -#ifdef SQLITE_ENABLE_STAT2
   626         -  if( rc==SQLITE_OK && !sqlite3FindTable(db, "sqlite_stat2", sInfo.zDatabase) ){
   627         -    rc = SQLITE_ERROR;
   628         -  }
         1098  +  /* Load the statistics from the sqlite_stat3 table. */
         1099  +#ifdef SQLITE_ENABLE_STAT3
   629   1100     if( rc==SQLITE_OK ){
   630         -    sqlite3_stmt *pStmt = 0;
   631         -
   632         -    zSql = sqlite3MPrintf(db, 
   633         -        "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase);
   634         -    if( !zSql ){
   635         -      rc = SQLITE_NOMEM;
   636         -    }else{
   637         -      rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
   638         -      sqlite3DbFree(db, zSql);
   639         -    }
   640         -
   641         -    if( rc==SQLITE_OK ){
   642         -      while( sqlite3_step(pStmt)==SQLITE_ROW ){
   643         -        char *zIndex;   /* Index name */
   644         -        Index *pIdx;    /* Pointer to the index object */
   645         -
   646         -        zIndex = (char *)sqlite3_column_text(pStmt, 0);
   647         -        pIdx = zIndex ? sqlite3FindIndex(db, zIndex, sInfo.zDatabase) : 0;
   648         -        if( pIdx ){
   649         -          int iSample = sqlite3_column_int(pStmt, 1);
   650         -          if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){
   651         -            int eType = sqlite3_column_type(pStmt, 2);
   652         -
   653         -            if( pIdx->aSample==0 ){
   654         -              static const int sz = sizeof(IndexSample)*SQLITE_INDEX_SAMPLES;
   655         -              pIdx->aSample = (IndexSample *)sqlite3DbMallocRaw(0, sz);
   656         -              if( pIdx->aSample==0 ){
   657         -                db->mallocFailed = 1;
   658         -                break;
   659         -              }
   660         -	      memset(pIdx->aSample, 0, sz);
   661         -            }
   662         -
   663         -            assert( pIdx->aSample );
   664         -            {
   665         -              IndexSample *pSample = &pIdx->aSample[iSample];
   666         -              pSample->eType = (u8)eType;
   667         -              if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
   668         -                pSample->u.r = sqlite3_column_double(pStmt, 2);
   669         -              }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){
   670         -                const char *z = (const char *)(
   671         -                    (eType==SQLITE_BLOB) ?
   672         -                    sqlite3_column_blob(pStmt, 2):
   673         -                    sqlite3_column_text(pStmt, 2)
   674         -                );
   675         -                int n = sqlite3_column_bytes(pStmt, 2);
   676         -                if( n>24 ){
   677         -                  n = 24;
   678         -                }
   679         -                pSample->nByte = (u8)n;
   680         -                if( n < 1){
   681         -                  pSample->u.z = 0;
   682         -                }else{
   683         -                  pSample->u.z = sqlite3DbStrNDup(0, z, n);
   684         -                  if( pSample->u.z==0 ){
   685         -                    db->mallocFailed = 1;
   686         -                    break;
   687         -                  }
   688         -                }
   689         -              }
   690         -            }
   691         -          }
   692         -        }
   693         -      }
   694         -      rc = sqlite3_finalize(pStmt);
   695         -    }
         1101  +    rc = loadStat3(db, sInfo.zDatabase);
   696   1102     }
   697   1103   #endif
   698   1104   
   699   1105     if( rc==SQLITE_NOMEM ){
   700   1106       db->mallocFailed = 1;
   701   1107     }
   702   1108     return rc;
   703   1109   }
   704   1110   
   705   1111   
   706   1112   #endif /* SQLITE_OMIT_ANALYZE */

Changes to src/build.c.

  1936   1936         int iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
  1937   1937         destroyRootPage(pParse, iLargest, iDb);
  1938   1938         iDestroyed = iLargest;
  1939   1939       }
  1940   1940     }
  1941   1941   #endif
  1942   1942   }
         1943  +
         1944  +/*
         1945  +** Remove entries from the sqlite_stat1 and sqlite_stat2 tables
         1946  +** after a DROP INDEX or DROP TABLE command.
         1947  +*/
         1948  +static void sqlite3ClearStatTables(
         1949  +  Parse *pParse,         /* The parsing context */
         1950  +  int iDb,               /* The database number */
         1951  +  const char *zType,     /* "idx" or "tbl" */
         1952  +  const char *zName      /* Name of index or table */
         1953  +){
         1954  +  static const char *azStatTab[] = { 
         1955  +    "sqlite_stat1",
         1956  +    "sqlite_stat2",
         1957  +    "sqlite_stat3",
         1958  +  };
         1959  +  int i;
         1960  +  const char *zDbName = pParse->db->aDb[iDb].zName;
         1961  +  for(i=0; i<ArraySize(azStatTab); i++){
         1962  +    if( sqlite3FindTable(pParse->db, azStatTab[i], zDbName) ){
         1963  +      sqlite3NestedParse(pParse,
         1964  +        "DELETE FROM %Q.%s WHERE %s=%Q",
         1965  +        zDbName, azStatTab[i], zType, zName
         1966  +      );
         1967  +    }
         1968  +  }
         1969  +}
         1970  +
         1971  +/*
         1972  +** Generate code to drop a table.
         1973  +*/
         1974  +void sqlite3CodeDropTable(Parse *pParse, Table *pTab, int iDb, int isView){
         1975  +  Vdbe *v;
         1976  +  sqlite3 *db = pParse->db;
         1977  +  Trigger *pTrigger;
         1978  +  Db *pDb = &db->aDb[iDb];
         1979  +
         1980  +  v = sqlite3GetVdbe(pParse);
         1981  +  assert( v!=0 );
         1982  +  sqlite3BeginWriteOperation(pParse, 1, iDb);
         1983  +
         1984  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         1985  +  if( IsVirtual(pTab) ){
         1986  +    sqlite3VdbeAddOp0(v, OP_VBegin);
         1987  +  }
         1988  +#endif
         1989  +
         1990  +  /* Drop all triggers associated with the table being dropped. Code
         1991  +  ** is generated to remove entries from sqlite_master and/or
         1992  +  ** sqlite_temp_master if required.
         1993  +  */
         1994  +  pTrigger = sqlite3TriggerList(pParse, pTab);
         1995  +  while( pTrigger ){
         1996  +    assert( pTrigger->pSchema==pTab->pSchema || 
         1997  +        pTrigger->pSchema==db->aDb[1].pSchema );
         1998  +    sqlite3DropTriggerPtr(pParse, pTrigger);
         1999  +    pTrigger = pTrigger->pNext;
         2000  +  }
         2001  +
         2002  +#ifndef SQLITE_OMIT_AUTOINCREMENT
         2003  +  /* Remove any entries of the sqlite_sequence table associated with
         2004  +  ** the table being dropped. This is done before the table is dropped
         2005  +  ** at the btree level, in case the sqlite_sequence table needs to
         2006  +  ** move as a result of the drop (can happen in auto-vacuum mode).
         2007  +  */
         2008  +  if( pTab->tabFlags & TF_Autoincrement ){
         2009  +    sqlite3NestedParse(pParse,
         2010  +      "DELETE FROM %Q.sqlite_sequence WHERE name=%Q",
         2011  +      pDb->zName, pTab->zName
         2012  +    );
         2013  +  }
         2014  +#endif
         2015  +
         2016  +  /* Drop all SQLITE_MASTER table and index entries that refer to the
         2017  +  ** table. The program name loops through the master table and deletes
         2018  +  ** every row that refers to a table of the same name as the one being
         2019  +  ** dropped. Triggers are handled seperately because a trigger can be
         2020  +  ** created in the temp database that refers to a table in another
         2021  +  ** database.
         2022  +  */
         2023  +  sqlite3NestedParse(pParse, 
         2024  +      "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
         2025  +      pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
         2026  +  if( !isView && !IsVirtual(pTab) ){
         2027  +    destroyTable(pParse, pTab);
         2028  +  }
         2029  +
         2030  +  /* Remove the table entry from SQLite's internal schema and modify
         2031  +  ** the schema cookie.
         2032  +  */
         2033  +  if( IsVirtual(pTab) ){
         2034  +    sqlite3VdbeAddOp4(v, OP_VDestroy, iDb, 0, 0, pTab->zName, 0);
         2035  +  }
         2036  +  sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
         2037  +  sqlite3ChangeCookie(pParse, iDb);
         2038  +  sqliteViewResetAll(db, iDb);
         2039  +}
  1943   2040   
  1944   2041   /*
  1945   2042   ** This routine is called to do the work of a DROP TABLE statement.
  1946   2043   ** pName is the name of the table to be dropped.
  1947   2044   */
  1948   2045   void sqlite3DropTable(Parse *pParse, SrcList *pName, int isView, int noErr){
  1949   2046     Table *pTab;
................................................................................
  2004   2101         goto exit_drop_table;
  2005   2102       }
  2006   2103       if( sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){
  2007   2104         goto exit_drop_table;
  2008   2105       }
  2009   2106     }
  2010   2107   #endif
  2011         -  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
         2108  +  if( !pParse->nested && sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
  2012   2109       sqlite3ErrorMsg(pParse, "table %s may not be dropped", pTab->zName);
  2013   2110       goto exit_drop_table;
  2014   2111     }
  2015   2112   
  2016   2113   #ifndef SQLITE_OMIT_VIEW
  2017   2114     /* Ensure DROP TABLE is not used on a view, and DROP VIEW is not used
  2018   2115     ** on a table.
................................................................................
  2028   2125   #endif
  2029   2126   
  2030   2127     /* Generate code to remove the table from the master table
  2031   2128     ** on disk.
  2032   2129     */
  2033   2130     v = sqlite3GetVdbe(pParse);
  2034   2131     if( v ){
  2035         -    Trigger *pTrigger;
  2036         -    Db *pDb = &db->aDb[iDb];
  2037   2132       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2038         -
  2039         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  2040         -    if( IsVirtual(pTab) ){
  2041         -      sqlite3VdbeAddOp0(v, OP_VBegin);
  2042         -    }
  2043         -#endif
         2133  +    sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2044   2134       sqlite3FkDropTable(pParse, pName, pTab);
  2045         -
  2046         -    /* Drop all triggers associated with the table being dropped. Code
  2047         -    ** is generated to remove entries from sqlite_master and/or
  2048         -    ** sqlite_temp_master if required.
  2049         -    */
  2050         -    pTrigger = sqlite3TriggerList(pParse, pTab);
  2051         -    while( pTrigger ){
  2052         -      assert( pTrigger->pSchema==pTab->pSchema || 
  2053         -          pTrigger->pSchema==db->aDb[1].pSchema );
  2054         -      sqlite3DropTriggerPtr(pParse, pTrigger);
  2055         -      pTrigger = pTrigger->pNext;
  2056         -    }
  2057         -
  2058         -#ifndef SQLITE_OMIT_AUTOINCREMENT
  2059         -    /* Remove any entries of the sqlite_sequence table associated with
  2060         -    ** the table being dropped. This is done before the table is dropped
  2061         -    ** at the btree level, in case the sqlite_sequence table needs to
  2062         -    ** move as a result of the drop (can happen in auto-vacuum mode).
  2063         -    */
  2064         -    if( pTab->tabFlags & TF_Autoincrement ){
  2065         -      sqlite3NestedParse(pParse,
  2066         -        "DELETE FROM %s.sqlite_sequence WHERE name=%Q",
  2067         -        pDb->zName, pTab->zName
  2068         -      );
  2069         -    }
  2070         -#endif
  2071         -
  2072         -    /* Drop all SQLITE_MASTER table and index entries that refer to the
  2073         -    ** table. The program name loops through the master table and deletes
  2074         -    ** every row that refers to a table of the same name as the one being
  2075         -    ** dropped. Triggers are handled seperately because a trigger can be
  2076         -    ** created in the temp database that refers to a table in another
  2077         -    ** database.
  2078         -    */
  2079         -    sqlite3NestedParse(pParse, 
  2080         -        "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
  2081         -        pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
  2082         -
  2083         -    /* Drop any statistics from the sqlite_stat1 table, if it exists */
  2084         -    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
  2085         -      sqlite3NestedParse(pParse,
  2086         -        "DELETE FROM %Q.sqlite_stat1 WHERE tbl=%Q", pDb->zName, pTab->zName
  2087         -      );
  2088         -    }
  2089         -
  2090         -    if( !isView && !IsVirtual(pTab) ){
  2091         -      destroyTable(pParse, pTab);
  2092         -    }
  2093         -
  2094         -    /* Remove the table entry from SQLite's internal schema and modify
  2095         -    ** the schema cookie.
  2096         -    */
  2097         -    if( IsVirtual(pTab) ){
  2098         -      sqlite3VdbeAddOp4(v, OP_VDestroy, iDb, 0, 0, pTab->zName, 0);
  2099         -    }
  2100         -    sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
  2101         -    sqlite3ChangeCookie(pParse, iDb);
         2135  +    sqlite3CodeDropTable(pParse, pTab, iDb, isView);
  2102   2136     }
  2103         -  sqliteViewResetAll(db, iDb);
  2104   2137   
  2105   2138   exit_drop_table:
  2106   2139     sqlite3SrcListDelete(db, pName);
  2107   2140   }
  2108   2141   
  2109   2142   /*
  2110   2143   ** This routine is called to create a new foreign key on the table
................................................................................
  2539   2572     /* 
  2540   2573     ** Allocate the index structure. 
  2541   2574     */
  2542   2575     nName = sqlite3Strlen30(zName);
  2543   2576     nCol = pList->nExpr;
  2544   2577     pIndex = sqlite3DbMallocZero(db, 
  2545   2578         sizeof(Index) +              /* Index structure  */
         2579  +      sizeof(tRowcnt)*(nCol+1) +   /* Index.aiRowEst   */
  2546   2580         sizeof(int)*nCol +           /* Index.aiColumn   */
  2547         -      sizeof(int)*(nCol+1) +       /* Index.aiRowEst   */
  2548   2581         sizeof(char *)*nCol +        /* Index.azColl     */
  2549   2582         sizeof(u8)*nCol +            /* Index.aSortOrder */
  2550   2583         nName + 1 +                  /* Index.zName      */
  2551   2584         nExtra                       /* Collation sequence names */
  2552   2585     );
  2553   2586     if( db->mallocFailed ){
  2554   2587       goto exit_create_index;
  2555   2588     }
  2556         -  pIndex->azColl = (char**)(&pIndex[1]);
         2589  +  pIndex->aiRowEst = (tRowcnt*)(&pIndex[1]);
         2590  +  pIndex->azColl = (char**)(&pIndex->aiRowEst[nCol+1]);
  2557   2591     pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]);
  2558         -  pIndex->aiRowEst = (unsigned *)(&pIndex->aiColumn[nCol]);
  2559         -  pIndex->aSortOrder = (u8 *)(&pIndex->aiRowEst[nCol+1]);
         2592  +  pIndex->aSortOrder = (u8 *)(&pIndex->aiColumn[nCol]);
  2560   2593     pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]);
  2561   2594     zExtra = (char *)(&pIndex->zName[nName+1]);
  2562   2595     memcpy(pIndex->zName, zName, nName+1);
  2563   2596     pIndex->pTable = pTab;
  2564   2597     pIndex->nColumn = pList->nExpr;
  2565   2598     pIndex->onError = (u8)onError;
  2566   2599     pIndex->autoIndex = (u8)(pName==0);
................................................................................
  2827   2860   **           aiRowEst[N]>=1
  2828   2861   **
  2829   2862   ** Apart from that, we have little to go on besides intuition as to
  2830   2863   ** how aiRowEst[] should be initialized.  The numbers generated here
  2831   2864   ** are based on typical values found in actual indices.
  2832   2865   */
  2833   2866   void sqlite3DefaultRowEst(Index *pIdx){
  2834         -  unsigned *a = pIdx->aiRowEst;
         2867  +  tRowcnt *a = pIdx->aiRowEst;
  2835   2868     int i;
  2836         -  unsigned n;
         2869  +  tRowcnt n;
  2837   2870     assert( a!=0 );
  2838   2871     a[0] = pIdx->pTable->nRowEst;
  2839   2872     if( a[0]<10 ) a[0] = 10;
  2840   2873     n = 10;
  2841   2874     for(i=1; i<=pIdx->nColumn; i++){
  2842   2875       a[i] = n;
  2843   2876       if( n>5 ) n--;

Changes to src/ctime.c.

   112    112     "ENABLE_OVERSIZE_CELL_CHECK",
   113    113   #endif
   114    114   #ifdef SQLITE_ENABLE_RTREE
   115    115     "ENABLE_RTREE",
   116    116   #endif
   117    117   #ifdef SQLITE_ENABLE_STAT2
   118    118     "ENABLE_STAT2",
          119  +#endif
          120  +#ifdef SQLITE_ENABLE_STAT3
          121  +  "ENABLE_STAT3",
   119    122   #endif
   120    123   #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
   121    124     "ENABLE_UNLOCK_NOTIFY",
   122    125   #endif
   123    126   #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   124    127     "ENABLE_UPDATE_DELETE_LIMIT",
   125    128   #endif

Changes to src/sqlite.h.in.

  2546   2546   ** [sqlite3_step()] would only return a generic [SQLITE_ERROR] result code
  2547   2547   ** and the application would have to make a second call to [sqlite3_reset()]
  2548   2548   ** in order to find the underlying cause of the problem. With the "v2" prepare
  2549   2549   ** interfaces, the underlying reason for the error is returned immediately.
  2550   2550   ** </li>
  2551   2551   **
  2552   2552   ** <li>
  2553         -** ^If the value of a [parameter | host parameter] in the WHERE clause might
  2554         -** change the query plan for a statement, then the statement may be
  2555         -** automatically recompiled (as if there had been a schema change) on the first 
  2556         -** [sqlite3_step()] call following any change to the 
  2557         -** [sqlite3_bind_text | bindings] of the [parameter]. 
         2553  +** ^If the specific value bound to [parameter | host parameter] in the 
         2554  +** WHERE clause might influence the choice of query plan for a statement,
         2555  +** then the statement will be automatically recompiled, as if there had been 
         2556  +** a schema change, on the first  [sqlite3_step()] call following any change
         2557  +** to the [sqlite3_bind_text | bindings] of that [parameter]. 
         2558  +** ^The specific value of WHERE-clause [parameter] might influence the 
         2559  +** choice of query plan if the parameter is the left-hand side of a [LIKE]
         2560  +** or [GLOB] operator or if the parameter is compared to an indexed column
         2561  +** and the [SQLITE_ENABLE_STAT3] compile-time option is enabled.
         2562  +** the 
  2558   2563   ** </li>
  2559   2564   ** </ol>
  2560   2565   */
  2561   2566   int sqlite3_prepare(
  2562   2567     sqlite3 *db,            /* Database handle */
  2563   2568     const char *zSql,       /* SQL statement, UTF-8 encoded */
  2564   2569     int nByte,              /* Maximum length of zSql in bytes. */

Changes to src/sqliteInt.h.

   435    435   ** SQLITE_MAX_U32 is a u64 constant that is the maximum u64 value
   436    436   ** that can be stored in a u32 without loss of data.  The value
   437    437   ** is 0x00000000ffffffff.  But because of quirks of some compilers, we
   438    438   ** have to specify the value in the less intuitive manner shown:
   439    439   */
   440    440   #define SQLITE_MAX_U32  ((((u64)1)<<32)-1)
   441    441   
          442  +/*
          443  +** The datatype used to store estimates of the number of rows in a
          444  +** table or index.  This is an unsigned integer type.  For 99.9% of
          445  +** the world, a 32-bit integer is sufficient.  But a 64-bit integer
          446  +** can be used at compile-time if desired.
          447  +*/
          448  +#ifdef SQLITE_64BIT_STATS
          449  + typedef u64 tRowcnt;    /* 64-bit only if requested at compile-time */
          450  +#else
          451  + typedef u32 tRowcnt;    /* 32-bit is the default */
          452  +#endif
          453  +
   442    454   /*
   443    455   ** Macros to determine whether the machine is big or little endian,
   444    456   ** evaluated at runtime.
   445    457   */
   446    458   #ifdef SQLITE_AMALGAMATION
   447    459   const int sqlite3one = 1;
   448    460   #else
................................................................................
  1220   1232   struct Table {
  1221   1233     char *zName;         /* Name of the table or view */
  1222   1234     int iPKey;           /* If not negative, use aCol[iPKey] as the primary key */
  1223   1235     int nCol;            /* Number of columns in this table */
  1224   1236     Column *aCol;        /* Information about each column */
  1225   1237     Index *pIndex;       /* List of SQL indexes on this table. */
  1226   1238     int tnum;            /* Root BTree node for this table (see note above) */
  1227         -  unsigned nRowEst;    /* Estimated rows in table - from sqlite_stat1 table */
         1239  +  tRowcnt nRowEst;     /* Estimated rows in table - from sqlite_stat1 table */
  1228   1240     Select *pSelect;     /* NULL for tables.  Points to definition if a view. */
  1229   1241     u16 nRef;            /* Number of pointers to this Table */
  1230   1242     u8 tabFlags;         /* Mask of TF_* values */
  1231   1243     u8 keyConf;          /* What to do in case of uniqueness conflict on iPKey */
  1232   1244     FKey *pFKey;         /* Linked list of all foreign keys in this table */
  1233   1245     char *zColAff;       /* String defining the affinity of each column */
  1234   1246   #ifndef SQLITE_OMIT_CHECK
................................................................................
  1419   1431   ** algorithm to employ whenever an attempt is made to insert a non-unique
  1420   1432   ** element.
  1421   1433   */
  1422   1434   struct Index {
  1423   1435     char *zName;     /* Name of this index */
  1424   1436     int nColumn;     /* Number of columns in the table used by this index */
  1425   1437     int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  1426         -  unsigned *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
         1438  +  tRowcnt *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
  1427   1439     Table *pTable;   /* The SQL table being indexed */
  1428   1440     int tnum;        /* Page containing root of this index in database file */
  1429   1441     u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1430   1442     u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  1431   1443     u8 bUnordered;   /* Use this index for == or IN queries only */
         1444  +  u8 nSample;      /* Number of elements in aSample[] */
  1432   1445     char *zColAff;   /* String defining the affinity of each column */
  1433   1446     Index *pNext;    /* The next index associated with the same table */
  1434   1447     Schema *pSchema; /* Schema containing this index */
  1435   1448     u8 *aSortOrder;  /* Array of size Index.nColumn. True==DESC, False==ASC */
  1436   1449     char **azColl;   /* Array of collation sequence names for index */
  1437         -  IndexSample *aSample;    /* Array of SQLITE_INDEX_SAMPLES samples */
         1450  +#ifdef SQLITE_ENABLE_STAT3
         1451  +  tRowcnt avgEq;           /* Average nEq value for key values not in aSample */
         1452  +  IndexSample *aSample;    /* Samples of the left-most key */
         1453  +#endif
  1438   1454   };
  1439   1455   
  1440   1456   /*
  1441   1457   ** Each sample stored in the sqlite_stat2 table is represented in memory 
  1442   1458   ** using a structure of this type.
  1443   1459   */
  1444   1460   struct IndexSample {
  1445   1461     union {
  1446   1462       char *z;        /* Value if eType is SQLITE_TEXT or SQLITE_BLOB */
  1447         -    double r;       /* Value if eType is SQLITE_FLOAT or SQLITE_INTEGER */
         1463  +    double r;       /* Value if eType is SQLITE_FLOAT */
         1464  +    i64 i;          /* Value if eType is SQLITE_INTEGER */
  1448   1465     } u;
  1449   1466     u8 eType;         /* SQLITE_NULL, SQLITE_INTEGER ... etc. */
  1450         -  u8 nByte;         /* Size in byte of text or blob. */
         1467  +  u16 nByte;        /* Size in byte of text or blob. */
         1468  +  tRowcnt nEq;      /* Est. number of rows where the key equals this sample */
         1469  +  tRowcnt nLt;      /* Est. number of rows where key is less than this sample */
         1470  +  tRowcnt nDLt;     /* Est. number of distinct keys less than this sample */
  1451   1471   };
  1452   1472   
  1453   1473   /*
  1454   1474   ** Each token coming out of the lexer is an instance of
  1455   1475   ** this structure.  Tokens are also used as part of an expression.
  1456   1476   **
  1457   1477   ** Note if Token.z==0 then Token.dyn and Token.n are undefined and
................................................................................
  2637   2657   #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
  2638   2658     int sqlite3ViewGetColumnNames(Parse*,Table*);
  2639   2659   #else
  2640   2660   # define sqlite3ViewGetColumnNames(A,B) 0
  2641   2661   #endif
  2642   2662   
  2643   2663   void sqlite3DropTable(Parse*, SrcList*, int, int);
         2664  +void sqlite3CodeDropTable(Parse*, Table*, int, int);
  2644   2665   void sqlite3DeleteTable(sqlite3*, Table*);
  2645   2666   #ifndef SQLITE_OMIT_AUTOINCREMENT
  2646   2667     void sqlite3AutoincrementBegin(Parse *pParse);
  2647   2668     void sqlite3AutoincrementEnd(Parse *pParse);
  2648   2669   #else
  2649   2670   # define sqlite3AutoincrementBegin(X)
  2650   2671   # define sqlite3AutoincrementEnd(X)
................................................................................
  2882   2903   const void *sqlite3ValueText(sqlite3_value*, u8);
  2883   2904   int sqlite3ValueBytes(sqlite3_value*, u8);
  2884   2905   void sqlite3ValueSetStr(sqlite3_value*, int, const void *,u8, 
  2885   2906                           void(*)(void*));
  2886   2907   void sqlite3ValueFree(sqlite3_value*);
  2887   2908   sqlite3_value *sqlite3ValueNew(sqlite3 *);
  2888   2909   char *sqlite3Utf16to8(sqlite3 *, const void*, int, u8);
  2889         -#ifdef SQLITE_ENABLE_STAT2
         2910  +#ifdef SQLITE_ENABLE_STAT3
  2890   2911   char *sqlite3Utf8to16(sqlite3 *, u8, char *, int, int *);
  2891   2912   #endif
  2892   2913   int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **);
  2893   2914   void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8);
  2894   2915   #ifndef SQLITE_AMALGAMATION
  2895   2916   extern const unsigned char sqlite3OpcodeProperty[];
  2896   2917   extern const unsigned char sqlite3UpperToLower[];

Changes to src/test_config.c.

   399    399   #endif
   400    400   
   401    401   #ifdef SQLITE_ENABLE_STAT2
   402    402     Tcl_SetVar2(interp, "sqlite_options", "stat2", "1", TCL_GLOBAL_ONLY);
   403    403   #else
   404    404     Tcl_SetVar2(interp, "sqlite_options", "stat2", "0", TCL_GLOBAL_ONLY);
   405    405   #endif
          406  +
          407  +#ifdef SQLITE_ENABLE_STAT3
          408  +  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
          409  +#else
          410  +  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
          411  +#endif
   406    412   
   407    413   #if !defined(SQLITE_ENABLE_LOCKING_STYLE)
   408    414   #  if defined(__APPLE__)
   409    415   #    define SQLITE_ENABLE_LOCKING_STYLE 1
   410    416   #  else
   411    417   #    define SQLITE_ENABLE_LOCKING_STYLE 0
   412    418   #  endif

Changes to src/utf.c.

   460    460   ** is set to the length of the returned string in bytes. The call should
   461    461   ** arrange to call sqlite3DbFree() on the returned pointer when it is
   462    462   ** no longer required.
   463    463   ** 
   464    464   ** If a malloc failure occurs, NULL is returned and the db.mallocFailed
   465    465   ** flag set.
   466    466   */
   467         -#ifdef SQLITE_ENABLE_STAT2
          467  +#ifdef SQLITE_ENABLE_STAT3
   468    468   char *sqlite3Utf8to16(sqlite3 *db, u8 enc, char *z, int n, int *pnOut){
   469    469     Mem m;
   470    470     memset(&m, 0, sizeof(m));
   471    471     m.db = db;
   472    472     sqlite3VdbeMemSetStr(&m, z, n, SQLITE_UTF8, SQLITE_STATIC);
   473    473     if( sqlite3VdbeMemTranslate(&m, enc) ){
   474    474       assert( db->mallocFailed );

Changes to src/vdbeaux.c.

   555    555   }
   556    556   
   557    557   /*
   558    558   ** Change the P2 operand of instruction addr so that it points to
   559    559   ** the address of the next instruction to be coded.
   560    560   */
   561    561   void sqlite3VdbeJumpHere(Vdbe *p, int addr){
   562         -  sqlite3VdbeChangeP2(p, addr, p->nOp);
          562  +  assert( addr>=0 || p->db->mallocFailed );
          563  +  if( addr>=0 ) sqlite3VdbeChangeP2(p, addr, p->nOp);
   563    564   }
   564    565   
   565    566   
   566    567   /*
   567    568   ** If the input FuncDef structure is ephemeral, then free it.  If
   568    569   ** the FuncDef is not ephermal, then do nothing.
   569    570   */

Changes to src/vdbemem.c.

  1012   1012   
  1013   1013     if( !pExpr ){
  1014   1014       *ppVal = 0;
  1015   1015       return SQLITE_OK;
  1016   1016     }
  1017   1017     op = pExpr->op;
  1018   1018   
  1019         -  /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT2.
         1019  +  /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT3.
  1020   1020     ** The ifdef here is to enable us to achieve 100% branch test coverage even
  1021         -  ** when SQLITE_ENABLE_STAT2 is omitted.
         1021  +  ** when SQLITE_ENABLE_STAT3 is omitted.
  1022   1022     */
  1023         -#ifdef SQLITE_ENABLE_STAT2
         1023  +#ifdef SQLITE_ENABLE_STAT3
  1024   1024     if( op==TK_REGISTER ) op = pExpr->op2;
  1025   1025   #else
  1026   1026     if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;
  1027   1027   #endif
  1028   1028   
  1029   1029     if( op==TK_STRING || op==TK_FLOAT || op==TK_INTEGER ){
  1030   1030       pVal = sqlite3ValueNew(db);

Changes to src/where.c.

   114    114   #define TERM_DYNAMIC    0x01   /* Need to call sqlite3ExprDelete(db, pExpr) */
   115    115   #define TERM_VIRTUAL    0x02   /* Added by the optimizer.  Do not code */
   116    116   #define TERM_CODED      0x04   /* This term is already coded */
   117    117   #define TERM_COPIED     0x08   /* Has a child */
   118    118   #define TERM_ORINFO     0x10   /* Need to free the WhereTerm.u.pOrInfo object */
   119    119   #define TERM_ANDINFO    0x20   /* Need to free the WhereTerm.u.pAndInfo obj */
   120    120   #define TERM_OR_OK      0x40   /* Used during OR-clause processing */
   121         -#ifdef SQLITE_ENABLE_STAT2
          121  +#ifdef SQLITE_ENABLE_STAT3
   122    122   #  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
   123    123   #else
   124    124   #  define TERM_VNULL    0x00   /* Disabled if not using stat2 */
   125    125   #endif
   126    126   
   127    127   /*
   128    128   ** An instance of the following structure holds all information about a
................................................................................
  1329   1329         pTerm->nChild = 1;
  1330   1330         pTerm->wtFlags |= TERM_COPIED;
  1331   1331         pNewTerm->prereqAll = pTerm->prereqAll;
  1332   1332       }
  1333   1333     }
  1334   1334   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  1335   1335   
  1336         -#ifdef SQLITE_ENABLE_STAT2
         1336  +#ifdef SQLITE_ENABLE_STAT3
  1337   1337     /* When sqlite_stat2 histogram data is available an operator of the
  1338   1338     ** form "x IS NOT NULL" can sometimes be evaluated more efficiently
  1339   1339     ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
  1340   1340     ** virtual term of that form.
  1341   1341     **
  1342   1342     ** Note that the virtual term must be tagged with TERM_VNULL.  This
  1343   1343     ** TERM_VNULL tag will suppress the not-null check at the beginning
................................................................................
  1368   1368         pNewTerm->iParent = idxTerm;
  1369   1369         pTerm = &pWC->a[idxTerm];
  1370   1370         pTerm->nChild = 1;
  1371   1371         pTerm->wtFlags |= TERM_COPIED;
  1372   1372         pNewTerm->prereqAll = pTerm->prereqAll;
  1373   1373       }
  1374   1374     }
  1375         -#endif /* SQLITE_ENABLE_STAT2 */
         1375  +#endif /* SQLITE_ENABLE_STAT */
  1376   1376   
  1377   1377     /* Prevent ON clause terms of a LEFT JOIN from being used to drive
  1378   1378     ** an index for tables to the left of the join.
  1379   1379     */
  1380   1380     pTerm->prereqRight |= extraRight;
  1381   1381   }
  1382   1382   
................................................................................
  2412   2412     /* Try to find a more efficient access pattern by using multiple indexes
  2413   2413     ** to optimize an OR expression within the WHERE clause. 
  2414   2414     */
  2415   2415     bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost);
  2416   2416   }
  2417   2417   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  2418   2418   
         2419  +#ifdef SQLITE_ENABLE_STAT3
  2419   2420   /*
  2420         -** Argument pIdx is a pointer to an index structure that has an array of
  2421         -** SQLITE_INDEX_SAMPLES evenly spaced samples of the first indexed column
  2422         -** stored in Index.aSample. These samples divide the domain of values stored
  2423         -** the index into (SQLITE_INDEX_SAMPLES+1) regions.
  2424         -** Region 0 contains all values less than the first sample value. Region
  2425         -** 1 contains values between the first and second samples.  Region 2 contains
  2426         -** values between samples 2 and 3.  And so on.  Region SQLITE_INDEX_SAMPLES
  2427         -** contains values larger than the last sample.
         2421  +** Estimate the location of a particular key among all keys in an
         2422  +** index.  Store the results in aStat as follows:
  2428   2423   **
  2429         -** If the index contains many duplicates of a single value, then it is
  2430         -** possible that two or more adjacent samples can hold the same value.
  2431         -** When that is the case, the smallest possible region code is returned
  2432         -** when roundUp is false and the largest possible region code is returned
  2433         -** when roundUp is true.
         2424  +**    aStat[0]      Est. number of rows less than pVal
         2425  +**    aStat[1]      Est. number of rows equal to pVal
  2434   2426   **
  2435         -** If successful, this function determines which of the regions value 
  2436         -** pVal lies in, sets *piRegion to the region index (a value between 0
  2437         -** and SQLITE_INDEX_SAMPLES+1, inclusive) and returns SQLITE_OK.
  2438         -** Or, if an OOM occurs while converting text values between encodings,
  2439         -** SQLITE_NOMEM is returned and *piRegion is undefined.
         2427  +** Return SQLITE_OK on success.
  2440   2428   */
  2441         -#ifdef SQLITE_ENABLE_STAT2
  2442         -static int whereRangeRegion(
         2429  +static int whereKeyStats(
  2443   2430     Parse *pParse,              /* Database connection */
  2444   2431     Index *pIdx,                /* Index to consider domain of */
  2445   2432     sqlite3_value *pVal,        /* Value to consider */
  2446         -  int roundUp,                /* Return largest valid region if true */
  2447         -  int *piRegion               /* OUT: Region of domain in which value lies */
         2433  +  int roundUp,                /* Round up if true.  Round down if false */
         2434  +  tRowcnt *aStat              /* OUT: stats written here */
  2448   2435   ){
         2436  +  tRowcnt n;
         2437  +  IndexSample *aSample;
         2438  +  int i, eType;
         2439  +  int isEq = 0;
         2440  +  i64 v;
         2441  +  double r, rS;
         2442  +
  2449   2443     assert( roundUp==0 || roundUp==1 );
  2450         -  if( ALWAYS(pVal) ){
  2451         -    IndexSample *aSample = pIdx->aSample;
  2452         -    int i = 0;
  2453         -    int eType = sqlite3_value_type(pVal);
  2454         -
  2455         -    if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
  2456         -      double r = sqlite3_value_double(pVal);
  2457         -      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
  2458         -        if( aSample[i].eType==SQLITE_NULL ) continue;
  2459         -        if( aSample[i].eType>=SQLITE_TEXT ) break;
  2460         -        if( roundUp ){
  2461         -          if( aSample[i].u.r>r ) break;
  2462         -        }else{
  2463         -          if( aSample[i].u.r>=r ) break;
  2464         -        }
  2465         -      }
  2466         -    }else if( eType==SQLITE_NULL ){
  2467         -      i = 0;
  2468         -      if( roundUp ){
  2469         -        while( i<SQLITE_INDEX_SAMPLES && aSample[i].eType==SQLITE_NULL ) i++;
  2470         -      }
  2471         -    }else{ 
         2444  +  if( pVal==0 ) return SQLITE_ERROR;
         2445  +  n = pIdx->aiRowEst[0];
         2446  +  aSample = pIdx->aSample;
         2447  +  i = 0;
         2448  +  eType = sqlite3_value_type(pVal);
         2449  +
         2450  +  if( eType==SQLITE_INTEGER ){
         2451  +    v = sqlite3_value_int64(pVal);
         2452  +    r = (i64)v;
         2453  +    for(i=0; i<pIdx->nSample; i++){
         2454  +      if( aSample[i].eType==SQLITE_NULL ) continue;
         2455  +      if( aSample[i].eType>=SQLITE_TEXT ) break;
         2456  +      if( aSample[i].eType==SQLITE_INTEGER ){
         2457  +        if( aSample[i].u.i>=v ){
         2458  +          isEq = aSample[i].u.i==v;
         2459  +          break;
         2460  +        }
         2461  +      }else{
         2462  +        assert( aSample[i].eType==SQLITE_FLOAT );
         2463  +        if( aSample[i].u.r>=r ){
         2464  +          isEq = aSample[i].u.r==r;
         2465  +          break;
         2466  +        }
         2467  +      }
         2468  +    }
         2469  +  }else if( eType==SQLITE_FLOAT ){
         2470  +    r = sqlite3_value_double(pVal);
         2471  +    for(i=0; i<pIdx->nSample; i++){
         2472  +      if( aSample[i].eType==SQLITE_NULL ) continue;
         2473  +      if( aSample[i].eType>=SQLITE_TEXT ) break;
         2474  +      if( aSample[i].eType==SQLITE_FLOAT ){
         2475  +        rS = aSample[i].u.r;
         2476  +      }else{
         2477  +        rS = aSample[i].u.i;
         2478  +      }
         2479  +      if( rS>=r ){
         2480  +        isEq = rS==r;
         2481  +        break;
         2482  +      }
         2483  +    }
         2484  +  }else if( eType==SQLITE_NULL ){
         2485  +    i = 0;
         2486  +    if( pIdx->nSample>=1 && aSample[0].eType==SQLITE_NULL ) isEq = 1;
         2487  +  }else{
         2488  +    assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB );
         2489  +    for(i=0; i<pIdx->nSample; i++){
         2490  +      if( aSample[i].eType==SQLITE_TEXT || aSample[i].eType==SQLITE_BLOB ){
         2491  +        break;
         2492  +      }
         2493  +    }
         2494  +    if( i<pIdx->nSample ){      
  2472   2495         sqlite3 *db = pParse->db;
  2473   2496         CollSeq *pColl;
  2474   2497         const u8 *z;
  2475         -      int n;
  2476         -
  2477         -      /* pVal comes from sqlite3ValueFromExpr() so the type cannot be NULL */
  2478         -      assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB );
  2479         -
  2480   2498         if( eType==SQLITE_BLOB ){
  2481   2499           z = (const u8 *)sqlite3_value_blob(pVal);
  2482   2500           pColl = db->pDfltColl;
  2483   2501           assert( pColl->enc==SQLITE_UTF8 );
  2484   2502         }else{
  2485   2503           pColl = sqlite3GetCollSeq(db, SQLITE_UTF8, 0, *pIdx->azColl);
  2486   2504           if( pColl==0 ){
................................................................................
  2491   2509           z = (const u8 *)sqlite3ValueText(pVal, pColl->enc);
  2492   2510           if( !z ){
  2493   2511             return SQLITE_NOMEM;
  2494   2512           }
  2495   2513           assert( z && pColl && pColl->xCmp );
  2496   2514         }
  2497   2515         n = sqlite3ValueBytes(pVal, pColl->enc);
  2498         -
  2499         -      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
         2516  +  
         2517  +      for(; i<pIdx->nSample; i++){
  2500   2518           int c;
  2501   2519           int eSampletype = aSample[i].eType;
  2502         -        if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue;
  2503         -        if( (eSampletype!=eType) ) break;
         2520  +        if( eSampletype<eType ) continue;
         2521  +        if( eSampletype!=eType ) break;
  2504   2522   #ifndef SQLITE_OMIT_UTF16
  2505   2523           if( pColl->enc!=SQLITE_UTF8 ){
  2506   2524             int nSample;
  2507   2525             char *zSample = sqlite3Utf8to16(
  2508   2526                 db, pColl->enc, aSample[i].u.z, aSample[i].nByte, &nSample
  2509   2527             );
  2510   2528             if( !zSample ){
................................................................................
  2514   2532             c = pColl->xCmp(pColl->pUser, nSample, zSample, n, z);
  2515   2533             sqlite3DbFree(db, zSample);
  2516   2534           }else
  2517   2535   #endif
  2518   2536           {
  2519   2537             c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
  2520   2538           }
  2521         -        if( c-roundUp>=0 ) break;
         2539  +        if( c>=0 ){
         2540  +          if( c==0 ) isEq = 1;
         2541  +          break;
         2542  +        }
  2522   2543         }
  2523   2544       }
         2545  +  }
  2524   2546   
  2525         -    assert( i>=0 && i<=SQLITE_INDEX_SAMPLES );
  2526         -    *piRegion = i;
         2547  +  /* At this point, aSample[i] is the first sample that is greater than
         2548  +  ** or equal to pVal.  Or if i==pIdx->nSample, then all samples are less
         2549  +  ** than pVal.  If aSample[i]==pVal, then isEq==1.
         2550  +  */
         2551  +  if( isEq ){
         2552  +    assert( i<pIdx->nSample );
         2553  +    aStat[0] = aSample[i].nLt;
         2554  +    aStat[1] = aSample[i].nEq;
         2555  +  }else{
         2556  +    tRowcnt iLower, iUpper, iGap;
         2557  +    if( i==0 ){
         2558  +      iLower = 0;
         2559  +      iUpper = aSample[0].nLt;
         2560  +    }else{
         2561  +      iUpper = i>=pIdx->nSample ? n : aSample[i].nLt;
         2562  +      iLower = aSample[i-1].nEq + aSample[i-1].nLt;
         2563  +    }
         2564  +    aStat[1] = pIdx->avgEq;
         2565  +    if( iLower>=iUpper ){
         2566  +      iGap = 0;
         2567  +    }else{
         2568  +      iGap = iUpper - iLower;
         2569  +      if( iGap>=aStat[1]/2 ) iGap -= aStat[1]/2;
         2570  +    }
         2571  +    if( roundUp ){
         2572  +      iGap = (iGap*2)/3;
         2573  +    }else{
         2574  +      iGap = iGap/3;
         2575  +    }
         2576  +    aStat[0] = iLower + iGap;
  2527   2577     }
  2528   2578     return SQLITE_OK;
  2529   2579   }
  2530         -#endif   /* #ifdef SQLITE_ENABLE_STAT2 */
         2580  +#endif /* SQLITE_ENABLE_STAT3 */
  2531   2581   
  2532   2582   /*
  2533   2583   ** If expression pExpr represents a literal value, set *pp to point to
  2534   2584   ** an sqlite3_value structure containing the same value, with affinity
  2535   2585   ** aff applied to it, before returning. It is the responsibility of the 
  2536   2586   ** caller to eventually release this structure by passing it to 
  2537   2587   ** sqlite3ValueFree().
................................................................................
  2541   2591   ** create an sqlite3_value structure containing this value, again with
  2542   2592   ** affinity aff applied to it, instead.
  2543   2593   **
  2544   2594   ** If neither of the above apply, set *pp to NULL.
  2545   2595   **
  2546   2596   ** If an error occurs, return an error code. Otherwise, SQLITE_OK.
  2547   2597   */
  2548         -#ifdef SQLITE_ENABLE_STAT2
         2598  +#ifdef SQLITE_ENABLE_STAT3
  2549   2599   static int valueFromExpr(
  2550   2600     Parse *pParse, 
  2551   2601     Expr *pExpr, 
  2552   2602     u8 aff, 
  2553   2603     sqlite3_value **pp
  2554   2604   ){
  2555   2605     if( pExpr->op==TK_VARIABLE
................................................................................
  2589   2639   ** then nEq should be passed the value 1 (as the range restricted column,
  2590   2640   ** b, is the second left-most column of the index). Or, if the query is:
  2591   2641   **
  2592   2642   **   ... FROM t1 WHERE a > ? AND a < ? ...
  2593   2643   **
  2594   2644   ** then nEq should be passed 0.
  2595   2645   **
  2596         -** The returned value is an integer between 1 and 100, inclusive. A return
  2597         -** value of 1 indicates that the proposed range scan is expected to visit
  2598         -** approximately 1/100th (1%) of the rows selected by the nEq equality
  2599         -** constraints (if any). A return value of 100 indicates that it is expected
  2600         -** that the range scan will visit every row (100%) selected by the equality
  2601         -** constraints.
         2646  +** The returned value is an integer divisor to reduce the estimated
         2647  +** search space.  A return value of 1 means that range constraints are
         2648  +** no help at all.  A return value of 2 means range constraints are
         2649  +** expected to reduce the search space by half.  And so forth...
  2602   2650   **
  2603         -** In the absence of sqlite_stat2 ANALYZE data, each range inequality
  2604         -** reduces the search space by 3/4ths.  Hence a single constraint (x>?)
  2605         -** results in a return of 25 and a range constraint (x>? AND x<?) results
  2606         -** in a return of 6.
         2651  +** In the absence of sqlite_stat3 ANALYZE data, each range inequality
         2652  +** reduces the search space by a factor of 4.  Hence a single constraint (x>?)
         2653  +** results in a return of 4 and a range constraint (x>? AND x<?) results
         2654  +** in a return of 16.
  2607   2655   */
  2608   2656   static int whereRangeScanEst(
  2609   2657     Parse *pParse,       /* Parsing & code generating context */
  2610   2658     Index *p,            /* The index containing the range-compared column; "x" */
  2611   2659     int nEq,             /* index into p->aCol[] of the range-compared column */
  2612   2660     WhereTerm *pLower,   /* Lower bound on the range. ex: "x>123" Might be NULL */
  2613   2661     WhereTerm *pUpper,   /* Upper bound on the range. ex: "x<455" Might be NULL */
  2614         -  int *piEst           /* OUT: Return value */
         2662  +  double *pRangeDiv   /* OUT: Reduce search space by this divisor */
  2615   2663   ){
  2616   2664     int rc = SQLITE_OK;
  2617   2665   
  2618         -#ifdef SQLITE_ENABLE_STAT2
         2666  +#ifdef SQLITE_ENABLE_STAT3
  2619   2667   
  2620         -  if( nEq==0 && p->aSample ){
  2621         -    sqlite3_value *pLowerVal = 0;
  2622         -    sqlite3_value *pUpperVal = 0;
  2623         -    int iEst;
  2624         -    int iLower = 0;
  2625         -    int iUpper = SQLITE_INDEX_SAMPLES;
  2626         -    int roundUpUpper;
  2627         -    int roundUpLower;
         2668  +  if( nEq==0 && p->nSample ){
         2669  +    sqlite3_value *pRangeVal;
         2670  +    tRowcnt iLower = 0;
         2671  +    tRowcnt iUpper = p->aiRowEst[0];
         2672  +    tRowcnt a[2];
  2628   2673       u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2629   2674   
  2630   2675       if( pLower ){
  2631   2676         Expr *pExpr = pLower->pExpr->pRight;
  2632         -      rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal);
         2677  +      rc = valueFromExpr(pParse, pExpr, aff, &pRangeVal);
  2633   2678         assert( pLower->eOperator==WO_GT || pLower->eOperator==WO_GE );
  2634         -      roundUpLower = (pLower->eOperator==WO_GT) ?1:0;
         2679  +      if( rc==SQLITE_OK
         2680  +       && whereKeyStats(pParse, p, pRangeVal, 0, a)==SQLITE_OK
         2681  +      ){
         2682  +        iLower = a[0];
         2683  +        if( pLower->eOperator==WO_GT ) iLower += a[1];
         2684  +      }
         2685  +      sqlite3ValueFree(pRangeVal);
  2635   2686       }
  2636   2687       if( rc==SQLITE_OK && pUpper ){
  2637   2688         Expr *pExpr = pUpper->pExpr->pRight;
  2638         -      rc = valueFromExpr(pParse, pExpr, aff, &pUpperVal);
         2689  +      rc = valueFromExpr(pParse, pExpr, aff, &pRangeVal);
  2639   2690         assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE );
  2640         -      roundUpUpper = (pUpper->eOperator==WO_LE) ?1:0;
  2641         -    }
  2642         -
  2643         -    if( rc!=SQLITE_OK || (pLowerVal==0 && pUpperVal==0) ){
  2644         -      sqlite3ValueFree(pLowerVal);
  2645         -      sqlite3ValueFree(pUpperVal);
  2646         -      goto range_est_fallback;
  2647         -    }else if( pLowerVal==0 ){
  2648         -      rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
  2649         -      if( pLower ) iLower = iUpper/2;
  2650         -    }else if( pUpperVal==0 ){
  2651         -      rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
  2652         -      if( pUpper ) iUpper = (iLower + SQLITE_INDEX_SAMPLES + 1)/2;
  2653         -    }else{
  2654         -      rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
  2655         -      if( rc==SQLITE_OK ){
  2656         -        rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
  2657         -      }
  2658         -    }
  2659         -    WHERETRACE(("range scan regions: %d..%d\n", iLower, iUpper));
  2660         -
  2661         -    iEst = iUpper - iLower;
  2662         -    testcase( iEst==SQLITE_INDEX_SAMPLES );
  2663         -    assert( iEst<=SQLITE_INDEX_SAMPLES );
  2664         -    if( iEst<1 ){
  2665         -      *piEst = 50/SQLITE_INDEX_SAMPLES;
  2666         -    }else{
  2667         -      *piEst = (iEst*100)/SQLITE_INDEX_SAMPLES;
  2668         -    }
  2669         -    sqlite3ValueFree(pLowerVal);
  2670         -    sqlite3ValueFree(pUpperVal);
  2671         -    return rc;
  2672         -  }
  2673         -range_est_fallback:
         2691  +      if( rc==SQLITE_OK
         2692  +       && whereKeyStats(pParse, p, pRangeVal, 1, a)==SQLITE_OK
         2693  +      ){
         2694  +        iUpper = a[0];
         2695  +        if( pUpper->eOperator==WO_LE ) iUpper += a[1];
         2696  +      }
         2697  +      sqlite3ValueFree(pRangeVal);
         2698  +    }
         2699  +    if( rc==SQLITE_OK ){
         2700  +      if( iUpper<=iLower ){
         2701  +        *pRangeDiv = (double)p->aiRowEst[0];
         2702  +      }else{
         2703  +        *pRangeDiv = (double)p->aiRowEst[0]/(double)(iUpper - iLower);
         2704  +      }
         2705  +      WHERETRACE(("range scan regions: %u..%u  div=%g\n",
         2706  +                  (u32)iLower, (u32)iUpper, *pRangeDiv));
         2707  +      return SQLITE_OK;
         2708  +    }
         2709  +  }
  2674   2710   #else
  2675   2711     UNUSED_PARAMETER(pParse);
  2676   2712     UNUSED_PARAMETER(p);
  2677   2713     UNUSED_PARAMETER(nEq);
  2678   2714   #endif
  2679   2715     assert( pLower || pUpper );
  2680         -  *piEst = 100;
  2681         -  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *piEst /= 4;
  2682         -  if( pUpper ) *piEst /= 4;
         2716  +  *pRangeDiv = (double)1;
         2717  +  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (double)4;
         2718  +  if( pUpper ) *pRangeDiv *= (double)4;
  2683   2719     return rc;
  2684   2720   }
  2685   2721   
  2686         -#ifdef SQLITE_ENABLE_STAT2
         2722  +#ifdef SQLITE_ENABLE_STAT3
  2687   2723   /*
  2688   2724   ** Estimate the number of rows that will be returned based on
  2689   2725   ** an equality constraint x=VALUE and where that VALUE occurs in
  2690   2726   ** the histogram data.  This only works when x is the left-most
  2691         -** column of an index and sqlite_stat2 histogram data is available
         2727  +** column of an index and sqlite_stat3 histogram data is available
  2692   2728   ** for that index.
  2693   2729   **
  2694   2730   ** Write the estimated row count into *pnRow and return SQLITE_OK. 
  2695   2731   ** If unable to make an estimate, leave *pnRow unchanged and return
  2696   2732   ** non-zero.
  2697   2733   **
  2698   2734   ** This routine can fail if it is unable to load a collating sequence
................................................................................
  2703   2739   int whereEqualScanEst(
  2704   2740     Parse *pParse,       /* Parsing & code generating context */
  2705   2741     Index *p,            /* The index whose left-most column is pTerm */
  2706   2742     Expr *pExpr,         /* Expression for VALUE in the x=VALUE constraint */
  2707   2743     double *pnRow        /* Write the revised row estimate here */
  2708   2744   ){
  2709   2745     sqlite3_value *pRhs = 0;  /* VALUE on right-hand side of pTerm */
  2710         -  int iLower, iUpper;       /* Range of histogram regions containing pRhs */
  2711   2746     u8 aff;                   /* Column affinity */
  2712   2747     int rc;                   /* Subfunction return code */
  2713         -  double nRowEst;           /* New estimate of the number of rows */
         2748  +  tRowcnt a[2];             /* Statistics */
  2714   2749   
  2715   2750     assert( p->aSample!=0 );
  2716   2751     aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2717   2752     if( pExpr ){
  2718   2753       rc = valueFromExpr(pParse, pExpr, aff, &pRhs);
  2719   2754       if( rc ) goto whereEqualScanEst_cancel;
  2720   2755     }else{
  2721   2756       pRhs = sqlite3ValueNew(pParse->db);
  2722   2757     }
  2723   2758     if( pRhs==0 ) return SQLITE_NOTFOUND;
  2724         -  rc = whereRangeRegion(pParse, p, pRhs, 0, &iLower);
  2725         -  if( rc ) goto whereEqualScanEst_cancel;
  2726         -  rc = whereRangeRegion(pParse, p, pRhs, 1, &iUpper);
  2727         -  if( rc ) goto whereEqualScanEst_cancel;
  2728         -  WHERETRACE(("equality scan regions: %d..%d\n", iLower, iUpper));
  2729         -  if( iLower>=iUpper ){
  2730         -    nRowEst = p->aiRowEst[0]/(SQLITE_INDEX_SAMPLES*2);
  2731         -    if( nRowEst<*pnRow ) *pnRow = nRowEst;
  2732         -  }else{
  2733         -    nRowEst = (iUpper-iLower)*p->aiRowEst[0]/SQLITE_INDEX_SAMPLES;
  2734         -    *pnRow = nRowEst;
         2759  +  rc = whereKeyStats(pParse, p, pRhs, 0, a);
         2760  +  if( rc==SQLITE_OK ){
         2761  +    WHERETRACE(("equality scan regions: %d\n", (int)a[1]));
         2762  +    *pnRow = a[1];
  2735   2763     }
  2736         -
  2737   2764   whereEqualScanEst_cancel:
  2738   2765     sqlite3ValueFree(pRhs);
  2739   2766     return rc;
  2740   2767   }
  2741         -#endif /* defined(SQLITE_ENABLE_STAT2) */
         2768  +#endif /* defined(SQLITE_ENABLE_STAT3) */
  2742   2769   
  2743         -#ifdef SQLITE_ENABLE_STAT2
         2770  +#ifdef SQLITE_ENABLE_STAT3
  2744   2771   /*
  2745   2772   ** Estimate the number of rows that will be returned based on
  2746   2773   ** an IN constraint where the right-hand side of the IN operator
  2747   2774   ** is a list of values.  Example:
  2748   2775   **
  2749   2776   **        WHERE x IN (1,2,3,4)
  2750   2777   **
................................................................................
  2759   2786   */
  2760   2787   int whereInScanEst(
  2761   2788     Parse *pParse,       /* Parsing & code generating context */
  2762   2789     Index *p,            /* The index whose left-most column is pTerm */
  2763   2790     ExprList *pList,     /* The value list on the RHS of "x IN (v1,v2,v3,...)" */
  2764   2791     double *pnRow        /* Write the revised row estimate here */
  2765   2792   ){
  2766         -  sqlite3_value *pVal = 0;  /* One value from list */
  2767         -  int iLower, iUpper;       /* Range of histogram regions containing pRhs */
  2768         -  u8 aff;                   /* Column affinity */
  2769         -  int rc = SQLITE_OK;       /* Subfunction return code */
  2770         -  double nRowEst;           /* New estimate of the number of rows */
  2771         -  int nSpan = 0;            /* Number of histogram regions spanned */
  2772         -  int nSingle = 0;          /* Histogram regions hit by a single value */
  2773         -  int nNotFound = 0;        /* Count of values that are not constants */
  2774         -  int i;                               /* Loop counter */
  2775         -  u8 aSpan[SQLITE_INDEX_SAMPLES+1];    /* Histogram regions that are spanned */
  2776         -  u8 aSingle[SQLITE_INDEX_SAMPLES+1];  /* Histogram regions hit once */
         2793  +  int rc = SQLITE_OK;         /* Subfunction return code */
         2794  +  double nEst;                /* Number of rows for a single term */
         2795  +  double nRowEst = (double)0; /* New estimate of the number of rows */
         2796  +  int i;                      /* Loop counter */
  2777   2797   
  2778   2798     assert( p->aSample!=0 );
  2779         -  aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2780         -  memset(aSpan, 0, sizeof(aSpan));
  2781         -  memset(aSingle, 0, sizeof(aSingle));
  2782         -  for(i=0; i<pList->nExpr; i++){
  2783         -    sqlite3ValueFree(pVal);
  2784         -    rc = valueFromExpr(pParse, pList->a[i].pExpr, aff, &pVal);
  2785         -    if( rc ) break;
  2786         -    if( pVal==0 || sqlite3_value_type(pVal)==SQLITE_NULL ){
  2787         -      nNotFound++;
  2788         -      continue;
  2789         -    }
  2790         -    rc = whereRangeRegion(pParse, p, pVal, 0, &iLower);
  2791         -    if( rc ) break;
  2792         -    rc = whereRangeRegion(pParse, p, pVal, 1, &iUpper);
  2793         -    if( rc ) break;
  2794         -    if( iLower>=iUpper ){
  2795         -      aSingle[iLower] = 1;
  2796         -    }else{
  2797         -      assert( iLower>=0 && iUpper<=SQLITE_INDEX_SAMPLES );
  2798         -      while( iLower<iUpper ) aSpan[iLower++] = 1;
  2799         -    }
         2799  +  for(i=0; rc==SQLITE_OK && i<pList->nExpr; i++){
         2800  +    nEst = p->aiRowEst[0];
         2801  +    rc = whereEqualScanEst(pParse, p, pList->a[i].pExpr, &nEst);
         2802  +    nRowEst += nEst;
  2800   2803     }
  2801   2804     if( rc==SQLITE_OK ){
  2802         -    for(i=nSpan=0; i<=SQLITE_INDEX_SAMPLES; i++){
  2803         -      if( aSpan[i] ){
  2804         -        nSpan++;
  2805         -      }else if( aSingle[i] ){
  2806         -        nSingle++;
  2807         -      }
  2808         -    }
  2809         -    nRowEst = (nSpan*2+nSingle)*p->aiRowEst[0]/(2*SQLITE_INDEX_SAMPLES)
  2810         -               + nNotFound*p->aiRowEst[1];
  2811   2805       if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
  2812   2806       *pnRow = nRowEst;
  2813         -    WHERETRACE(("IN row estimate: nSpan=%d, nSingle=%d, nNotFound=%d, est=%g\n",
  2814         -                 nSpan, nSingle, nNotFound, nRowEst));
         2807  +    WHERETRACE(("IN row estimate: est=%g\n", nRowEst));
  2815   2808     }
  2816         -  sqlite3ValueFree(pVal);
  2817   2809     return rc;
  2818   2810   }
  2819         -#endif /* defined(SQLITE_ENABLE_STAT2) */
         2811  +#endif /* defined(SQLITE_ENABLE_STAT3) */
  2820   2812   
  2821   2813   
  2822   2814   /*
  2823   2815   ** Find the best query plan for accessing a particular table.  Write the
  2824   2816   ** best query plan and its cost into the WhereCost object supplied as the
  2825   2817   ** last parameter.
  2826   2818   **
................................................................................
  2859   2851   ){
  2860   2852     int iCur = pSrc->iCursor;   /* The cursor of the table to be accessed */
  2861   2853     Index *pProbe;              /* An index we are evaluating */
  2862   2854     Index *pIdx;                /* Copy of pProbe, or zero for IPK index */
  2863   2855     int eqTermMask;             /* Current mask of valid equality operators */
  2864   2856     int idxEqTermMask;          /* Index mask of valid equality operators */
  2865   2857     Index sPk;                  /* A fake index object for the primary key */
  2866         -  unsigned int aiRowEstPk[2]; /* The aiRowEst[] value for the sPk index */
         2858  +  tRowcnt aiRowEstPk[2];      /* The aiRowEst[] value for the sPk index */
  2867   2859     int aiColumnPk = -1;        /* The aColumn[] value for the sPk index */
  2868   2860     int wsFlagMask;             /* Allowed flags in pCost->plan.wsFlag */
  2869   2861   
  2870   2862     /* Initialize the cost to a worst-case value */
  2871   2863     memset(pCost, 0, sizeof(*pCost));
  2872   2864     pCost->rCost = SQLITE_BIG_DBL;
  2873   2865   
................................................................................
  2914   2906       eqTermMask = WO_EQ|WO_IN;
  2915   2907       pIdx = 0;
  2916   2908     }
  2917   2909   
  2918   2910     /* Loop over all indices looking for the best one to use
  2919   2911     */
  2920   2912     for(; pProbe; pIdx=pProbe=pProbe->pNext){
  2921         -    const unsigned int * const aiRowEst = pProbe->aiRowEst;
         2913  +    const tRowcnt * const aiRowEst = pProbe->aiRowEst;
  2922   2914       double cost;                /* Cost of using pProbe */
  2923   2915       double nRow;                /* Estimated number of rows in result set */
  2924   2916       double log10N;              /* base-10 logarithm of nRow (inexact) */
  2925   2917       int rev;                    /* True to scan in reverse order */
  2926   2918       int wsFlags = 0;
  2927   2919       Bitmask used = 0;
  2928   2920   
................................................................................
  2957   2949       **
  2958   2950       **  bInEst:  
  2959   2951       **    Set to true if there was at least one "x IN (SELECT ...)" term used 
  2960   2952       **    in determining the value of nInMul.  Note that the RHS of the
  2961   2953       **    IN operator must be a SELECT, not a value list, for this variable
  2962   2954       **    to be true.
  2963   2955       **
  2964         -    **  estBound:
  2965         -    **    An estimate on the amount of the table that must be searched.  A
  2966         -    **    value of 100 means the entire table is searched.  Range constraints
  2967         -    **    might reduce this to a value less than 100 to indicate that only
  2968         -    **    a fraction of the table needs searching.  In the absence of
  2969         -    **    sqlite_stat2 ANALYZE data, a single inequality reduces the search
  2970         -    **    space to 1/4rd its original size.  So an x>? constraint reduces
  2971         -    **    estBound to 25.  Two constraints (x>? AND x<?) reduce estBound to 6.
         2956  +    **  rangeDiv:
         2957  +    **    An estimate of a divisor by which to reduce the search space due
         2958  +    **    to inequality constraints.  In the absence of sqlite_stat3 ANALYZE
         2959  +    **    data, a single inequality reduces the search space to 1/4rd its
         2960  +    **    original size (rangeDiv==4).  Two inequalities reduce the search
         2961  +    **    space to 1/16th of its original size (rangeDiv==16).
  2972   2962       **
  2973   2963       **  bSort:   
  2974   2964       **    Boolean. True if there is an ORDER BY clause that will require an 
  2975   2965       **    external sort (i.e. scanning the index being evaluated will not 
  2976   2966       **    correctly order records).
  2977   2967       **
  2978   2968       **  bLookup: 
................................................................................
  2989   2979       **
  2990   2980       **             SELECT a, b    FROM tbl WHERE a = 1;
  2991   2981       **             SELECT a, b, c FROM tbl WHERE a = 1;
  2992   2982       */
  2993   2983       int nEq;                      /* Number of == or IN terms matching index */
  2994   2984       int bInEst = 0;               /* True if "x IN (SELECT...)" seen */
  2995   2985       int nInMul = 1;               /* Number of distinct equalities to lookup */
  2996         -    int estBound = 100;           /* Estimated reduction in search space */
         2986  +    double rangeDiv = (double)1;  /* Estimated reduction in search space */
  2997   2987       int nBound = 0;               /* Number of range constraints seen */
  2998   2988       int bSort = !!pOrderBy;       /* True if external sort required */
  2999   2989       int bDist = !!pDistinct;      /* True if index cannot help with DISTINCT */
  3000   2990       int bLookup = 0;              /* True if not a covering index */
  3001   2991       WhereTerm *pTerm;             /* A single term of the WHERE clause */
  3002         -#ifdef SQLITE_ENABLE_STAT2
         2992  +#ifdef SQLITE_ENABLE_STAT3
  3003   2993       WhereTerm *pFirstTerm = 0;    /* First term matching the index */
  3004   2994   #endif
  3005   2995   
  3006   2996       /* Determine the values of nEq and nInMul */
  3007   2997       for(nEq=0; nEq<pProbe->nColumn; nEq++){
  3008   2998         int j = pProbe->aiColumn[nEq];
  3009   2999         pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pIdx);
................................................................................
  3019   3009           }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
  3020   3010             /* "x IN (value, value, ...)" */
  3021   3011             nInMul *= pExpr->x.pList->nExpr;
  3022   3012           }
  3023   3013         }else if( pTerm->eOperator & WO_ISNULL ){
  3024   3014           wsFlags |= WHERE_COLUMN_NULL;
  3025   3015         }
  3026         -#ifdef SQLITE_ENABLE_STAT2
         3016  +#ifdef SQLITE_ENABLE_STAT3
  3027   3017         if( nEq==0 && pProbe->aSample ) pFirstTerm = pTerm;
  3028   3018   #endif
  3029   3019         used |= pTerm->prereqRight;
  3030   3020       }
  3031   3021   
  3032         -    /* Determine the value of estBound. */
         3022  +    /* Determine the value of rangeDiv */
  3033   3023       if( nEq<pProbe->nColumn && pProbe->bUnordered==0 ){
  3034   3024         int j = pProbe->aiColumn[nEq];
  3035   3025         if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE, pIdx) ){
  3036   3026           WhereTerm *pTop = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pIdx);
  3037   3027           WhereTerm *pBtm = findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pIdx);
  3038         -        whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &estBound);
         3028  +        whereRangeScanEst(pParse, pProbe, nEq, pBtm, pTop, &rangeDiv);
  3039   3029           if( pTop ){
  3040   3030             nBound = 1;
  3041   3031             wsFlags |= WHERE_TOP_LIMIT;
  3042   3032             used |= pTop->prereqRight;
  3043   3033           }
  3044   3034           if( pBtm ){
  3045   3035             nBound++;
................................................................................
  3103   3093       */
  3104   3094       nRow = (double)(aiRowEst[nEq] * nInMul);
  3105   3095       if( bInEst && nRow*2>aiRowEst[0] ){
  3106   3096         nRow = aiRowEst[0]/2;
  3107   3097         nInMul = (int)(nRow / aiRowEst[nEq]);
  3108   3098       }
  3109   3099   
  3110         -#ifdef SQLITE_ENABLE_STAT2
         3100  +#ifdef SQLITE_ENABLE_STAT3
  3111   3101       /* If the constraint is of the form x=VALUE or x IN (E1,E2,...)
  3112   3102       ** and we do not think that values of x are unique and if histogram
  3113   3103       ** data is available for column x, then it might be possible
  3114   3104       ** to get a better estimate on the number of rows based on
  3115   3105       ** VALUE and how common that value is according to the histogram.
  3116   3106       */
  3117   3107       if( nRow>(double)1 && nEq==1 && pFirstTerm!=0 && aiRowEst[1]>1 ){
................................................................................
  3119   3109           testcase( pFirstTerm->eOperator==WO_EQ );
  3120   3110           testcase( pFirstTerm->pOperator==WO_ISNULL );
  3121   3111           whereEqualScanEst(pParse, pProbe, pFirstTerm->pExpr->pRight, &nRow);
  3122   3112         }else if( pFirstTerm->eOperator==WO_IN && bInEst==0 ){
  3123   3113           whereInScanEst(pParse, pProbe, pFirstTerm->pExpr->x.pList, &nRow);
  3124   3114         }
  3125   3115       }
  3126         -#endif /* SQLITE_ENABLE_STAT2 */
         3116  +#endif /* SQLITE_ENABLE_STAT3 */
  3127   3117   
  3128   3118       /* Adjust the number of output rows and downward to reflect rows
  3129   3119       ** that are excluded by range constraints.
  3130   3120       */
  3131         -    nRow = (nRow * (double)estBound) / (double)100;
         3121  +    nRow = nRow/rangeDiv;
  3132   3122       if( nRow<1 ) nRow = 1;
  3133   3123   
  3134   3124       /* Experiments run on real SQLite databases show that the time needed
  3135   3125       ** to do a binary search to locate a row in a table or index is roughly
  3136   3126       ** log10(N) times the time to move from one row to the next row within
  3137   3127       ** a table or index.  The actual times can vary, with the size of
  3138   3128       ** records being an important factor.  Both moves and searches are
................................................................................
  3253   3243           }
  3254   3244         }
  3255   3245         if( nRow<2 ) nRow = 2;
  3256   3246       }
  3257   3247   
  3258   3248   
  3259   3249       WHERETRACE((
  3260         -      "%s(%s): nEq=%d nInMul=%d estBound=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
         3250  +      "%s(%s): nEq=%d nInMul=%d rangeDiv=%d bSort=%d bLookup=%d wsFlags=0x%x\n"
  3261   3251         "         notReady=0x%llx log10N=%.1f nRow=%.1f cost=%.1f used=0x%llx\n",
  3262   3252         pSrc->pTab->zName, (pIdx ? pIdx->zName : "ipk"), 
  3263         -      nEq, nInMul, estBound, bSort, bLookup, wsFlags,
         3253  +      nEq, nInMul, (int)rangeDiv, bSort, bLookup, wsFlags,
  3264   3254         notReady, log10N, nRow, cost, used
  3265   3255       ));
  3266   3256   
  3267   3257       /* If this index is the best we have seen so far, then record this
  3268   3258       ** index and its cost in the pCost structure.
  3269   3259       */
  3270   3260       if( (!pIdx || wsFlags)

Changes to test/alter.test.

   835    835   } {1 {Cannot add a UNIQUE column}}
   836    836   do_test alter-14.2 {
   837    837     catchsql {
   838    838       ALTER TABLE t3651 ADD COLUMN b PRIMARY KEY;
   839    839     }
   840    840   } {1 {Cannot add a PRIMARY KEY column}}
   841    841   
          842  +
          843  +#-------------------------------------------------------------------------
          844  +# Test that it is not possible to use ALTER TABLE on any system table.
          845  +#
          846  +set system_table_list {1 sqlite_master}
          847  +catchsql ANALYZE
          848  +ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
          849  +ifcapable stat2   { lappend system_table_list 3 sqlite_stat2 }
          850  +ifcapable stat3   { lappend system_table_list 4 sqlite_stat3 }
          851  +
          852  +foreach {tn tbl} $system_table_list {
          853  +  do_test alter-15.$tn.1 {
          854  +    catchsql "ALTER TABLE $tbl RENAME TO xyz"
          855  +  } [list 1 "table $tbl may not be altered"]
          856  +
          857  +  do_test alter-15.$tn.2 {
          858  +    catchsql "ALTER TABLE $tbl ADD COLUMN xyz"
          859  +  } [list 1 "table $tbl may not be altered"]
          860  +}
          861  +
   842    862   
   843    863   finish_test

Changes to test/analyze.test.

   282    282     }
   283    283     db close
   284    284     sqlite3 db test.db
   285    285     execsql {
   286    286       SELECT * FROM t4 WHERE x=1234;
   287    287     }
   288    288   } {}
          289  +
          290  +# Verify that DROP TABLE and DROP INDEX remove entries from the 
          291  +# sqlite_stat1 and sqlite_stat3 tables.
          292  +#
          293  +do_test analyze-5.0 {
          294  +  execsql {
          295  +    DELETE FROM t3;
          296  +    DELETE FROM t4;
          297  +    INSERT INTO t3 VALUES(1,2,3,4);
          298  +    INSERT INTO t3 VALUES(5,6,7,8);
          299  +    INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3;
          300  +    INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3;
          301  +    INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3;
          302  +    INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3;
          303  +    INSERT INTO t4 SELECT a, b, c FROM t3;
          304  +    ANALYZE;
          305  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          306  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          307  +  }
          308  +} {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
          309  +ifcapable stat3 {
          310  +  do_test analyze-5.1 {
          311  +    execsql {
          312  +      SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
          313  +      SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
          314  +    }
          315  +  } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
          316  +}
          317  +do_test analyze-5.2 {
          318  +  execsql {
          319  +    DROP INDEX t3i2;
          320  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          321  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          322  +  }
          323  +} {t3i1 t3i3 t4i1 t4i2 t3 t4}
          324  +ifcapable stat3 {
          325  +  do_test analyze-5.3 {
          326  +    execsql {
          327  +      SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
          328  +      SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
          329  +    }
          330  +  } {t3i1 t3i3 t4i1 t4i2 t3 t4}
          331  +}
          332  +do_test analyze-5.4 {
          333  +  execsql {
          334  +    DROP TABLE t3;
          335  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          336  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          337  +  }
          338  +} {t4i1 t4i2 t4}
          339  +ifcapable stat3 {
          340  +  do_test analyze-5.5 {
          341  +    execsql {
          342  +      SELECT DISTINCT idx FROM sqlite_stat3 ORDER BY 1;
          343  +      SELECT DISTINCT tbl FROM sqlite_stat3 ORDER BY 1;
          344  +    }
          345  +  } {t4i1 t4i2 t4}
          346  +}
   289    347   
   290    348   # This test corrupts the database file so it must be the last test
   291    349   # in the series.
   292    350   #
   293    351   do_test analyze-99.1 {
   294    352     execsql {
   295    353       PRAGMA writable_schema=on;

Changes to test/analyze3.test.

    13     13   # implements tests for range and LIKE constraints that use bound variables
    14     14   # instead of literal constant arguments.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20         -ifcapable !stat2 {
           20  +ifcapable !stat3 {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
    25     25   #----------------------------------------------------------------------
    26     26   # Test Organization:
    27     27   #
................................................................................
    93     93       COMMIT;
    94     94       ANALYZE;
    95     95     }
    96     96   } {}
    97     97   
    98     98   do_eqp_test analyze3-1.1.2 {
    99     99     SELECT sum(y) FROM t1 WHERE x>200 AND x<300
   100         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~100 rows)}}
          100  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
   101    101   do_eqp_test analyze3-1.1.3 {
   102    102     SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
   103         -} {0 0 0 {SCAN TABLE t1 (~111 rows)}}
          103  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}
   104    104   
   105    105   do_test analyze3-1.1.4 {
   106    106     sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
   107    107   } {199 0 14850}
   108    108   do_test analyze3-1.1.5 {
   109    109     set l [string range "200" 0 end]
   110    110     set u [string range "300" 0 end]
................................................................................
   113    113   do_test analyze3-1.1.6 {
   114    114     set l [expr int(200)]
   115    115     set u [expr int(300)]
   116    116     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   117    117   } {199 0 14850}
   118    118   do_test analyze3-1.1.7 {
   119    119     sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
   120         -} {999 999 499500}
          120  +} {2000 0 499500}
   121    121   do_test analyze3-1.1.8 {
   122    122     set l [string range "0" 0 end]
   123    123     set u [string range "1100" 0 end]
   124    124     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   125         -} {999 999 499500}
          125  +} {2000 0 499500}
   126    126   do_test analyze3-1.1.9 {
   127    127     set l [expr int(0)]
   128    128     set u [expr int(1100)]
   129    129     sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
   130         -} {999 999 499500}
          130  +} {2000 0 499500}
   131    131   
   132    132   
   133    133   # The following tests are similar to the block above. The difference is
   134    134   # that the indexed column has TEXT affinity in this case. In the tests
   135    135   # above the affinity is INTEGER.
   136    136   #
   137    137   do_test analyze3-1.2.1 {
................................................................................
   142    142         CREATE INDEX i2 ON t2(x);
   143    143       COMMIT;
   144    144       ANALYZE;
   145    145     }
   146    146   } {}
   147    147   do_eqp_test analyze3-1.2.2 {
   148    148     SELECT sum(y) FROM t2 WHERE x>1 AND x<2
   149         -} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
          149  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
   150    150   do_eqp_test analyze3-1.2.3 {
   151    151     SELECT sum(y) FROM t2 WHERE x>0 AND x<99
   152         -} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
          152  +} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~982 rows)}}
   153    153   do_test analyze3-1.2.4 {
   154    154     sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
   155    155   } {161 0 4760}
   156    156   do_test analyze3-1.2.5 {
   157    157     set l [string range "12" 0 end]
   158    158     set u [string range "20" 0 end]
   159    159     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
................................................................................
   161    161   do_test analyze3-1.2.6 {
   162    162     set l [expr int(12)]
   163    163     set u [expr int(20)]
   164    164     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   165    165   } {161 0 integer integer 4760}
   166    166   do_test analyze3-1.2.7 {
   167    167     sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
   168         -} {999 999 490555}
          168  +} {1981 0 490555}
   169    169   do_test analyze3-1.2.8 {
   170    170     set l [string range "0" 0 end]
   171    171     set u [string range "99" 0 end]
   172    172     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   173         -} {999 999 text text 490555}
          173  +} {1981 0 text text 490555}
   174    174   do_test analyze3-1.2.9 {
   175    175     set l [expr int(0)]
   176    176     set u [expr int(99)]
   177    177     sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
   178         -} {999 999 integer integer 490555}
          178  +} {1981 0 integer integer 490555}
   179    179   
   180    180   # Same tests a third time. This time, column x has INTEGER affinity and
   181    181   # is not the leftmost column of the table. This triggered a bug causing
   182    182   # SQLite to use sub-optimal query plans in 3.6.18 and earlier.
   183    183   #
   184    184   do_test analyze3-1.3.1 {
   185    185     execsql {
................................................................................
   189    189         CREATE INDEX i3 ON t3(x);
   190    190       COMMIT;
   191    191       ANALYZE;
   192    192     }
   193    193   } {}
   194    194   do_eqp_test analyze3-1.3.2 {
   195    195     SELECT sum(y) FROM t3 WHERE x>200 AND x<300
   196         -} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~100 rows)}}
          196  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
   197    197   do_eqp_test analyze3-1.3.3 {
   198    198     SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
   199         -} {0 0 0 {SCAN TABLE t3 (~111 rows)}}
          199  +} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}
   200    200   
   201    201   do_test analyze3-1.3.4 {
   202    202     sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
   203    203   } {199 0 14850}
   204    204   do_test analyze3-1.3.5 {
   205    205     set l [string range "200" 0 end]
   206    206     set u [string range "300" 0 end]
................................................................................
   209    209   do_test analyze3-1.3.6 {
   210    210     set l [expr int(200)]
   211    211     set u [expr int(300)]
   212    212     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   213    213   } {199 0 14850}
   214    214   do_test analyze3-1.3.7 {
   215    215     sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
   216         -} {999 999 499500}
          216  +} {2000 0 499500}
   217    217   do_test analyze3-1.3.8 {
   218    218     set l [string range "0" 0 end]
   219    219     set u [string range "1100" 0 end]
   220    220     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   221         -} {999 999 499500}
          221  +} {2000 0 499500}
   222    222   do_test analyze3-1.3.9 {
   223    223     set l [expr int(0)]
   224    224     set u [expr int(1100)]
   225    225     sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
   226         -} {999 999 499500}
          226  +} {2000 0 499500}
   227    227   
   228    228   #-------------------------------------------------------------------------
   229    229   # Test that the values of bound SQL variables may be used for the LIKE
   230    230   # optimization.
   231    231   #
   232    232   drop_all_tables
   233    233   do_test analyze3-2.1 {
................................................................................
   244    244       append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
   245    245       execsql { INSERT INTO t1 VALUES($i, $t) }
   246    246     }
   247    247     execsql COMMIT
   248    248   } {}
   249    249   do_eqp_test analyze3-2.2 {
   250    250     SELECT count(a) FROM t1 WHERE b LIKE 'a%'
   251         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~30000 rows)}}
          251  +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~31250 rows)}}
   252    252   do_eqp_test analyze3-2.3 {
   253    253     SELECT count(a) FROM t1 WHERE b LIKE '%a'
   254    254   } {0 0 0 {SCAN TABLE t1 (~500000 rows)}}
   255    255   
   256    256   do_test analyze3-2.4 {
   257    257     sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
   258    258   } {101 0 100}

Changes to test/analyze5.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   # This file implements tests for SQLite library.  The focus of the tests
    13         -# in this file is the use of the sqlite_stat2 histogram data on tables
           13  +# in this file is the use of the sqlite_stat3 histogram data on tables
    14     14   # with many repeated values and only a few distinct values.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20         -ifcapable !stat2 {
           20  +ifcapable !stat3 {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
    25     25   set testprefix analyze5
    26     26   
    27     27   proc eqp {sql {db db}} {
................................................................................
    51     51       CREATE INDEX t1u ON t1(u);  -- text
    52     52       CREATE INDEX t1v ON t1(v);  -- mixed case text
    53     53       CREATE INDEX t1w ON t1(w);  -- integers 0, 1, 2 and a few NULLs
    54     54       CREATE INDEX t1x ON t1(x);  -- integers 1, 2, 3 and many NULLs
    55     55       CREATE INDEX t1y ON t1(y);  -- integers 0 and very few 1s
    56     56       CREATE INDEX t1z ON t1(z);  -- integers 0, 1, 2, and 3
    57     57       ANALYZE;
    58         -    SELECT sample FROM sqlite_stat2 WHERE idx='t1u' ORDER BY sampleno;
           58  +    SELECT sample FROM sqlite_stat3 WHERE idx='t1u' ORDER BY nlt;
    59     59     }
    60         -} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
           60  +} {alpha bravo charlie delta}
           61  +
    61     62   do_test analyze5-1.1 {
    62         -  string tolower \
    63         -   [db eval {SELECT sample from sqlite_stat2 WHERE idx='t1v' ORDER BY sampleno}]
    64         -} {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta}
           63  +  db eval {SELECT DISTINCT lower(sample) FROM sqlite_stat3 WHERE idx='t1v'
           64  +             ORDER BY 1}
           65  +} {alpha bravo charlie delta}
    65     66   do_test analyze5-1.2 {
    66         -  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1w' ORDER BY sampleno}
    67         -} {{} 0 0 0 0 1 1 1 2 2}
    68         -do_test analyze5-1.3 {
    69         -  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno}
    70         -} {{} {} {} {} 1 1 1 2 2 3}
    71         -do_test analyze5-1.4 {
    72         -  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1y' ORDER BY sampleno}
    73         -} {0 0 0 0 0 0 0 0 0 0}
    74         -do_test analyze5-1.5 {
    75         -  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1z' ORDER BY sampleno}
    76         -} {0 0 0 0 1 1 1 2 2 3}
    77         -do_test analyze5-1.6 {
    78         -  db eval {SELECT sample from sqlite_stat2 WHERE idx='t1t' ORDER BY sampleno}
    79         -} {0.5 0.5 0.5 0.5 1.5 1.5 1.5 2.5 2.5 3.5}
    80         -
           67  +  db eval {SELECT idx, count(*) FROM sqlite_stat3 GROUP BY 1 ORDER BY 1}
           68  +} {t1t 4 t1u 4 t1v 4 t1w 4 t1x 4 t1y 2 t1z 4}
    81     69   
    82     70   # Verify that range queries generate the correct row count estimates
    83     71   #
    84     72   foreach {testid where index rows} {
    85     73       1  {z>=0 AND z<=0}       t1z  400
    86     74       2  {z>=1 AND z<=1}       t1z  300
    87         -    3  {z>=2 AND z<=2}       t1z  200
    88         -    4  {z>=3 AND z<=3}       t1z  100
    89         -    5  {z>=4 AND z<=4}       t1z   50
    90         -    6  {z>=-1 AND z<=-1}     t1z   50
    91         -    7  {z>1 AND z<3}         t1z  200
           75  +    3  {z>=2 AND z<=2}       t1z  175
           76  +    4  {z>=3 AND z<=3}       t1z  125
           77  +    5  {z>=4 AND z<=4}       t1z    1
           78  +    6  {z>=-1 AND z<=-1}     t1z    1
           79  +    7  {z>1 AND z<3}         t1z  175
    92     80       8  {z>0 AND z<100}       t1z  600
    93     81       9  {z>=1 AND z<100}      t1z  600
    94     82      10  {z>1 AND z<100}       t1z  300
    95     83      11  {z>=2 AND z<100}      t1z  300
    96         -   12  {z>2 AND z<100}       t1z  100
    97         -   13  {z>=3 AND z<100}      t1z  100
    98         -   14  {z>3 AND z<100}       t1z   50
    99         -   15  {z>=4 AND z<100}      t1z   50
   100         -   16  {z>=-100 AND z<=-1}   t1z   50
           84  +   12  {z>2 AND z<100}       t1z  125
           85  +   13  {z>=3 AND z<100}      t1z  125
           86  +   14  {z>3 AND z<100}       t1z    1
           87  +   15  {z>=4 AND z<100}      t1z    1
           88  +   16  {z>=-100 AND z<=-1}   t1z    1
   101     89      17  {z>=-100 AND z<=0}    t1z  400
   102         -   18  {z>=-100 AND z<0}     t1z   50
           90  +   18  {z>=-100 AND z<0}     t1z    1
   103     91      19  {z>=-100 AND z<=1}    t1z  700
   104     92      20  {z>=-100 AND z<2}     t1z  700
   105         -   21  {z>=-100 AND z<=2}    t1z  900
   106         -   22  {z>=-100 AND z<3}     t1z  900
           93  +   21  {z>=-100 AND z<=2}    t1z  875
           94  +   22  {z>=-100 AND z<3}     t1z  875
   107     95     
   108     96      31  {z>=0.0 AND z<=0.0}   t1z  400
   109     97      32  {z>=1.0 AND z<=1.0}   t1z  300
   110         -   33  {z>=2.0 AND z<=2.0}   t1z  200
   111         -   34  {z>=3.0 AND z<=3.0}   t1z  100
   112         -   35  {z>=4.0 AND z<=4.0}   t1z   50
   113         -   36  {z>=-1.0 AND z<=-1.0} t1z   50
   114         -   37  {z>1.5 AND z<3.0}     t1z  200
   115         -   38  {z>0.5 AND z<100}     t1z  600
           98  +   33  {z>=2.0 AND z<=2.0}   t1z  175
           99  +   34  {z>=3.0 AND z<=3.0}   t1z  125
          100  +   35  {z>=4.0 AND z<=4.0}   t1z    1
          101  +   36  {z>=-1.0 AND z<=-1.0} t1z    1
          102  +   37  {z>1.5 AND z<3.0}     t1z  174
          103  +   38  {z>0.5 AND z<100}     t1z  599
   116    104      39  {z>=1.0 AND z<100}    t1z  600
   117         -   40  {z>1.5 AND z<100}     t1z  300
          105  +   40  {z>1.5 AND z<100}     t1z  299
   118    106      41  {z>=2.0 AND z<100}    t1z  300
   119         -   42  {z>2.1 AND z<100}     t1z  100
   120         -   43  {z>=3.0 AND z<100}    t1z  100
   121         -   44  {z>3.2 AND z<100}     t1z   50
   122         -   45  {z>=4.0 AND z<100}    t1z   50
   123         -   46  {z>=-100 AND z<=-1.0} t1z   50
          107  +   42  {z>2.1 AND z<100}     t1z  124
          108  +   43  {z>=3.0 AND z<100}    t1z  125
          109  +   44  {z>3.2 AND z<100}     t1z    1
          110  +   45  {z>=4.0 AND z<100}    t1z    1
          111  +   46  {z>=-100 AND z<=-1.0} t1z    1
   124    112      47  {z>=-100 AND z<=0.0}  t1z  400
   125         -   48  {z>=-100 AND z<0.0}   t1z   50
          113  +   48  {z>=-100 AND z<0.0}   t1z    1
   126    114      49  {z>=-100 AND z<=1.0}  t1z  700
   127    115      50  {z>=-100 AND z<2.0}   t1z  700
   128         -   51  {z>=-100 AND z<=2.0}  t1z  900
   129         -   52  {z>=-100 AND z<3.0}   t1z  900
          116  +   51  {z>=-100 AND z<=2.0}  t1z  875
          117  +   52  {z>=-100 AND z<3.0}   t1z  875
   130    118     
   131         -  101  {z=-1}                t1z   50
          119  +  101  {z=-1}                t1z    1
   132    120     102  {z=0}                 t1z  400
   133    121     103  {z=1}                 t1z  300
   134         -  104  {z=2}                 t1z  200
   135         -  105  {z=3}                 t1z  100
   136         -  106  {z=4}                 t1z   50
   137         -  107  {z=-10.0}             t1z   50
          122  +  104  {z=2}                 t1z  175
          123  +  105  {z=3}                 t1z  125
          124  +  106  {z=4}                 t1z    1
          125  +  107  {z=-10.0}             t1z    1
   138    126     108  {z=0.0}               t1z  400
   139    127     109  {z=1.0}               t1z  300
   140         -  110  {z=2.0}               t1z  200
   141         -  111  {z=3.0}               t1z  100
   142         -  112  {z=4.0}               t1z   50
   143         -  113  {z=1.5}               t1z   50
   144         -  114  {z=2.5}               t1z   50
          128  +  110  {z=2.0}               t1z  175
          129  +  111  {z=3.0}               t1z  125
          130  +  112  {z=4.0}               t1z    1
          131  +  113  {z=1.5}               t1z    1
          132  +  114  {z=2.5}               t1z    1
   145    133     
   146         -  201  {z IN (-1)}           t1z   50
          134  +  201  {z IN (-1)}           t1z    1
   147    135     202  {z IN (0)}            t1z  400
   148    136     203  {z IN (1)}            t1z  300
   149         -  204  {z IN (2)}            t1z  200
   150         -  205  {z IN (3)}            t1z  100
   151         -  206  {z IN (4)}            t1z   50
   152         -  207  {z IN (0.5)}          t1z   50
          137  +  204  {z IN (2)}            t1z  175
          138  +  205  {z IN (3)}            t1z  125
          139  +  206  {z IN (4)}            t1z    1
          140  +  207  {z IN (0.5)}          t1z    1
   153    141     208  {z IN (0,1)}          t1z  700
   154         -  209  {z IN (0,1,2)}        t1z  900
          142  +  209  {z IN (0,1,2)}        t1z  875
   155    143     210  {z IN (0,1,2,3)}      {}   100
   156    144     211  {z IN (0,1,2,3,4,5)}  {}   100
   157         -  212  {z IN (1,2)}          t1z  500
          145  +  212  {z IN (1,2)}          t1z  475
   158    146     213  {z IN (2,3)}          t1z  300
   159    147     214  {z=3 OR z=2}          t1z  300
   160         -  215  {z IN (-1,3)}         t1z  150
   161         -  216  {z=-1 OR z=3}         t1z  150
          148  +  215  {z IN (-1,3)}         t1z  126
          149  +  216  {z=-1 OR z=3}         t1z  126
   162    150   
   163         -  300  {y=0}                 {}   100
   164         -  301  {y=1}                 t1y   50
   165         -  302  {y=0.1}               t1y   50
          151  +  300  {y=0}                 t1y  974
          152  +  301  {y=1}                 t1y   26
          153  +  302  {y=0.1}               t1y    1
   166    154   
   167    155     400  {x IS NULL}           t1x  400
   168    156   
   169    157   } {
   170    158     # Verify that the expected index is used with the expected row count
   171    159     do_test analyze5-1.${testid}a {
   172    160       set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
................................................................................
   200    188       WHERE rowid IN (SELECT rowid FROM t1 ORDER BY random() LIMIT 5);
   201    189      ANALYZE;
   202    190   }
   203    191   
   204    192   # Verify that range queries generate the correct row count estimates
   205    193   #
   206    194   foreach {testid where index rows} {
   207         -  500  {x IS NULL AND u='charlie'}         t1u  20
   208         -  501  {x=1 AND u='charlie'}               t1x   5
   209         -  502  {x IS NULL}                          {} 100
   210         -  503  {x=1}                               t1x  50
   211         -  504  {x IS NOT NULL}                     t1x  25
          195  +  500  {x IS NULL AND u='charlie'}         t1u  17
          196  +  501  {x=1 AND u='charlie'}               t1x   1
          197  +  502  {x IS NULL}                         t1x 995
          198  +  503  {x=1}                               t1x   1
          199  +  504  {x IS NOT NULL}                     t1x   2
   212    200     505  {+x IS NOT NULL}                     {} 500
   213    201     506  {upper(x) IS NOT NULL}               {} 500
   214    202   
   215    203   } {
   216    204     # Verify that the expected index is used with the expected row count
          205  +if {$testid==50299} {breakpoint; set sqlite_where_trace 1}
   217    206     do_test analyze5-1.${testid}a {
   218    207       set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
   219    208       set idx {}
   220    209       regexp {INDEX (t1.) } $x all idx
   221    210       regexp {~([0-9]+) rows} $x all nrow
   222    211       list $idx $nrow
   223    212     } [list $index $rows]
          213  +if {$testid==50299} exit
   224    214   
   225    215     # Verify that the same result is achieved regardless of whether or not
   226    216     # the index is used
   227    217     do_test analyze5-1.${testid}b {
   228    218       set w2 [string map {y +y z +z} $where]
   229    219       set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\
   230    220                        ORDER BY +rowid"]

Changes to test/analyze6.test.

    13     13   # in this file a corner-case query planner optimization involving the
    14     14   # join order of two tables of different sizes.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20         -ifcapable !stat2 {
           20  +ifcapable !stat3 {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
    25     25   set testprefix analyze6
    26     26   
    27     27   proc eqp {sql {db db}} {

Added test/analyze8.test.

            1  +# 2011 August 13
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements tests for SQLite library.  The focus of the tests
           13  +# in this file is testing the capabilities of sqlite_stat3.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +ifcapable !stat3 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +set testprefix analyze8
           25  +
           26  +proc eqp {sql {db db}} {
           27  +  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
           28  +}
           29  +
           30  +# Scenario:
           31  +#
           32  +#    Two indices.  One has mostly singleton entries, but for a few
           33  +#    values there are hundreds of entries.  The other has 10-20
           34  +#    entries per value.
           35  +#
           36  +# Verify that the query planner chooses the first index for the singleton
           37  +# entries and the second index for the others.
           38  +#
           39  +do_test 1.0 {
           40  +  db eval {
           41  +    CREATE TABLE t1(a,b,c,d);
           42  +    CREATE INDEX t1a ON t1(a);
           43  +    CREATE INDEX t1b ON t1(b);
           44  +    CREATE INDEX t1c ON t1(c);
           45  +  }
           46  +  for {set i 0} {$i<1000} {incr i} {
           47  +    if {$i%2==0} {set a $i} {set a [expr {($i%8)*100}]}
           48  +    set b [expr {$i/10}]
           49  +    set c [expr {$i/8}]
           50  +    set c [expr {$c*$c*$c}]
           51  +    db eval {INSERT INTO t1 VALUES($a,$b,$c,$i)}
           52  +  }
           53  +  db eval {ANALYZE}
           54  +} {}
           55  +
           56  +# The a==100 comparison is expensive because there are many rows
           57  +# with a==100.  And so for those cases, choose the t1b index.
           58  +#
           59  +# Buf ro a==99 and a==101, there are far fewer rows so choose
           60  +# the t1a index.
           61  +#
           62  +do_test 1.1 {
           63  +  eqp {SELECT * FROM t1 WHERE a=100 AND b=55}
           64  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
           65  +do_test 1.2 {
           66  +  eqp {SELECT * FROM t1 WHERE a=99 AND b=55}
           67  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           68  +do_test 1.3 {
           69  +  eqp {SELECT * FROM t1 WHERE a=101 AND b=55}
           70  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           71  +do_test 1.4 {
           72  +  eqp {SELECT * FROM t1 WHERE a=100 AND b=56}
           73  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
           74  +do_test 1.5 {
           75  +  eqp {SELECT * FROM t1 WHERE a=99 AND b=56}
           76  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           77  +do_test 1.6 {
           78  +  eqp {SELECT * FROM t1 WHERE a=101 AND b=56}
           79  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
           80  +do_test 2.1 {
           81  +  eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54}
           82  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~2 rows)}}
           83  +
           84  +# There are many more values of c between 0 and 100000 than there are
           85  +# between 800000 and 900000.  So t1c is more selective for the latter
           86  +# range.
           87  +#
           88  +do_test 3.1 {
           89  +  eqp {SELECT * FROM t1 WHERE b BETWEEN 50 AND 54 AND c BETWEEN 0 AND 100000}
           90  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) (~6 rows)}}
           91  +do_test 3.2 {
           92  +  eqp {SELECT * FROM t1
           93  +       WHERE b BETWEEN 50 AND 54 AND c BETWEEN 800000 AND 900000}
           94  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~4 rows)}}
           95  +do_test 3.3 {
           96  +  eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
           97  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~63 rows)}}
           98  +do_test 3.4 {
           99  +  eqp {SELECT * FROM t1
          100  +       WHERE a=100 AND c BETWEEN 800000 AND 900000}
          101  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?) (~2 rows)}}
          102  +
          103  +finish_test

Changes to test/auth.test.

  2309   2309           DROP TABLE v1chng;
  2310   2310         }
  2311   2311       }
  2312   2312     }
  2313   2313     ifcapable stat2 {
  2314   2314       set stat2 "sqlite_stat2 "
  2315   2315     } else {
  2316         -    set stat2 ""
         2316  +    ifcapable stat3 {
         2317  +      set stat2 "sqlite_stat3 "
         2318  +    } else {
         2319  +      set stat2 ""
         2320  +    }
  2317   2321     }
  2318   2322     do_test auth-5.2 {
  2319   2323       execsql {
  2320   2324         SELECT name FROM (
  2321   2325           SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
  2322   2326         WHERE type='table'
  2323   2327         ORDER BY name

Changes to test/dbstatus.test.

    44     44   
    45     45   
    46     46   proc lookaside {db} {
    47     47     expr { $::lookaside_buffer_size *
    48     48       [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1]
    49     49     }
    50     50   }
           51  +
           52  +ifcapable stat3 {
           53  +  set STAT3 1
           54  +} else {
           55  +  set STAT3 0
           56  +}
    51     57   
    52     58   #---------------------------------------------------------------------------
    53     59   # Run the dbstatus-2 and dbstatus-3 tests with several of different
    54     60   # lookaside buffer sizes.
    55     61   #
    56     62   foreach ::lookaside_buffer_size {0 64 120} {
    57     63   
................................................................................
   107    113         END;
   108    114       }
   109    115       5 {
   110    116         CREATE TABLE t1(a, b);
   111    117         CREATE TABLE t2(c, d);
   112    118         CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;
   113    119       }
   114         -    6 {
          120  +    6y {
   115    121         CREATE TABLE t1(a, b);
   116    122         CREATE INDEX i1 ON t1(a);
   117    123         CREATE INDEX i2 ON t1(a,b);
   118    124         CREATE INDEX i3 ON t1(b,b);
   119    125         INSERT INTO t1 VALUES(randomblob(20), randomblob(25));
   120    126         INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
   121    127         INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
................................................................................
   187    193       # for any reason is not counted as "schema memory".
   188    194       #
   189    195       # Additionally, in auto-vacuum mode, dropping tables and indexes causes
   190    196       # the page-cache to shrink. So the amount of memory freed is always
   191    197       # much greater than just that reported by DBSTATUS_SCHEMA_USED in this
   192    198       # case.
   193    199       #
   194         -    if {[string match *x $tn] || $AUTOVACUUM} {
          200  +    # Some of the memory used for sqlite_stat3 is unaccounted for by
          201  +    # dbstatus.
          202  +    #
          203  +    if {[string match *x $tn] || $AUTOVACUUM
          204  +         || ([string match *y $tn] && $STAT3)} {
   195    205         do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1
   196    206       } else {
   197    207         do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree
   198    208       }
   199    209     
   200    210       do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3"
   201    211       do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4"

Added test/stat3.test.

            1  +# 2011 August 08
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library. This file 
           13  +# implements tests for the extra functionality provided by the ANALYZE 
           14  +# command when the library is compiled with SQLITE_ENABLE_STAT3 defined.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +set testprefix stat3
           21  +
           22  +
           23  +# Verify that if not compiled with SQLITE_ENABLE_STAT2 that the ANALYZE
           24  +# command will delete the sqlite_stat2 table.  Likewise, if not compiled
           25  +# with SQLITE_ENABLE_STAT3, the sqlite_stat3 table is deleted.
           26  +#
           27  +do_test 1.1 {
           28  +  db eval {
           29  +    PRAGMA writable_schema=ON;
           30  +    CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
           31  +    CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
           32  +    SELECT name FROM sqlite_master ORDER BY 1;
           33  +  }
           34  +} {sqlite_stat2 sqlite_stat3}
           35  +do_test 1.2 {
           36  +  db close
           37  +  sqlite3 db test.db
           38  +  db eval {SELECT name FROM sqlite_master ORDER BY 1}
           39  +} {sqlite_stat2 sqlite_stat3}
           40  +
           41  +ifcapable {stat3} {
           42  +  do_test 1.3 {
           43  +    db eval {ANALYZE; SELECT name FROM sqlite_master ORDER BY 1}
           44  +  } {sqlite_stat1 sqlite_stat3}
           45  +} else {
           46  +  do_test 1.4 {
           47  +    db eval {ANALYZE; SELECT name FROM sqlite_master ORDER BY 1}
           48  +  } {sqlite_stat1}
           49  +  finish_test
           50  +  return
           51  +}
           52  +
           53  +
           54  +
           55  +
           56  +finish_test

Changes to test/tkt-cbd054fa6b.test.

    12     12   # This file implements tests to verify that ticket [cbd054fa6b] has been
    13     13   # fixed.  
    14     14   #
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19         -ifcapable !stat2 {
           19  +ifcapable !stat3 {
    20     20     finish_test
    21     21     return
    22     22   }
    23     23   
    24     24   do_test tkt-cbd05-1.1 {
    25     25     db eval {
    26     26       CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL);
................................................................................
    42     42     db eval {
    43     43       ANALYZE;
    44     44     }
    45     45   } {}
    46     46   do_test tkt-cbd05-1.3 {
    47     47     execsql { 
    48     48       SELECT tbl,idx,group_concat(sample,' ') 
    49         -    FROM sqlite_stat2 
           49  +    FROM sqlite_stat3 
    50     50       WHERE idx = 't1_x' 
    51     51       GROUP BY tbl,idx
    52     52     }
    53     53   } {t1 t1_x { A B C D E F G H I}}
    54     54   
    55     55   do_test tkt-cbd05-2.1 {
    56     56     db eval {
................................................................................
    74     74     db eval {
    75     75       ANALYZE;
    76     76     }
    77     77   } {}
    78     78   do_test tkt-cbd05-2.3 {
    79     79     execsql { 
    80     80       SELECT tbl,idx,group_concat(sample,' ') 
    81         -    FROM sqlite_stat2 
           81  +    FROM sqlite_stat3 
    82     82       WHERE idx = 't1_x' 
    83     83       GROUP BY tbl,idx
    84     84     }
    85     85   } {t1 t1_x { A B C D E F G H I}}
    86     86   
    87     87   finish_test

Changes to test/unordered.test.

    27     27     INSERT INTO t1 SELECT a+16, b FROM t1;
    28     28     INSERT INTO t1 SELECT a+32, b FROM t1;
    29     29     INSERT INTO t1 SELECT a+64, b FROM t1;
    30     30     ANALYZE;
    31     31   } {}
    32     32   
    33     33   foreach idxmode {ordered unordered} {
           34  +  catchsql { DELETE FROM sqlite_stat2 }
           35  +  catchsql { DELETE FROM sqlite_stat3 }
    34     36     if {$idxmode == "unordered"} {
    35     37       execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' }
    36         -    db close
    37         -    sqlite3 db test.db
    38     38     }
           39  +  db close
           40  +  sqlite3 db test.db
    39     41     foreach {tn sql r(ordered) r(unordered)} {
    40     42       1   "SELECT * FROM t1 ORDER BY a"
    41     43           {0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
    42     44           {0 0 0 {SCAN TABLE t1 (~128 rows)}}
    43     45       2   "SELECT * FROM t1 WHERE a >?"
    44     46           {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~32 rows)}}
    45     47           {0 0 0 {SCAN TABLE t1 (~42 rows)}}