/ Check-in [63f2c785]
Login

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

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

Changes to src/build.c.

  1986   1986   */
  1987   1987   static void sqlite3ClearStatTables(
  1988   1988     Parse *pParse,         /* The parsing context */
  1989   1989     int iDb,               /* The database number */
  1990   1990     const char *zType,     /* "idx" or "tbl" */
  1991   1991     const char *zName      /* Name of index or table */
  1992   1992   ){
  1993         -  static const char *azStatTab[] = { "sqlite_stat1", "sqlite_stat2" };
         1993  +  static const char *azStatTab[] = { 
         1994  +    "sqlite_stat1",
         1995  +    "sqlite_stat2",
         1996  +    "sqlite_stat3",
         1997  +  };
  1994   1998     int i;
  1995   1999     const char *zDbName = pParse->db->aDb[iDb].zName;
  1996   2000     for(i=0; i<ArraySize(azStatTab); i++){
  1997   2001       if( sqlite3FindTable(pParse->db, azStatTab[i], zDbName) ){
  1998   2002         sqlite3NestedParse(pParse,
  1999   2003           "DELETE FROM %Q.%s WHERE %s=%Q",
  2000   2004           zDbName, azStatTab[i], zType, zName
  2001   2005         );
  2002   2006       }
  2003   2007     }
  2004   2008   }
         2009  +
         2010  +/*
         2011  +** Generate code to drop a table.
         2012  +*/
         2013  +void sqlite3CodeDropTable(Parse *pParse, Table *pTab, int iDb, int isView){
         2014  +  Vdbe *v;
         2015  +  sqlite3 *db = pParse->db;
         2016  +  Trigger *pTrigger;
         2017  +  Db *pDb = &db->aDb[iDb];
         2018  +
         2019  +  v = sqlite3GetVdbe(pParse);
         2020  +  assert( v!=0 );
         2021  +  sqlite3BeginWriteOperation(pParse, 1, iDb);
         2022  +
         2023  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         2024  +  if( IsVirtual(pTab) ){
         2025  +    sqlite3VdbeAddOp0(v, OP_VBegin);
         2026  +  }
         2027  +#endif
         2028  +
         2029  +  /* Drop all triggers associated with the table being dropped. Code
         2030  +  ** is generated to remove entries from sqlite_master and/or
         2031  +  ** sqlite_temp_master if required.
         2032  +  */
         2033  +  pTrigger = sqlite3TriggerList(pParse, pTab);
         2034  +  while( pTrigger ){
         2035  +    assert( pTrigger->pSchema==pTab->pSchema || 
         2036  +        pTrigger->pSchema==db->aDb[1].pSchema );
         2037  +    sqlite3DropTriggerPtr(pParse, pTrigger);
         2038  +    pTrigger = pTrigger->pNext;
         2039  +  }
         2040  +
         2041  +#ifndef SQLITE_OMIT_AUTOINCREMENT
         2042  +  /* Remove any entries of the sqlite_sequence table associated with
         2043  +  ** the table being dropped. This is done before the table is dropped
         2044  +  ** at the btree level, in case the sqlite_sequence table needs to
         2045  +  ** move as a result of the drop (can happen in auto-vacuum mode).
         2046  +  */
         2047  +  if( pTab->tabFlags & TF_Autoincrement ){
         2048  +    sqlite3NestedParse(pParse,
         2049  +      "DELETE FROM %Q.sqlite_sequence WHERE name=%Q",
         2050  +      pDb->zName, pTab->zName
         2051  +    );
         2052  +  }
         2053  +#endif
         2054  +
         2055  +  /* Drop all SQLITE_MASTER table and index entries that refer to the
         2056  +  ** table. The program name loops through the master table and deletes
         2057  +  ** every row that refers to a table of the same name as the one being
         2058  +  ** dropped. Triggers are handled seperately because a trigger can be
         2059  +  ** created in the temp database that refers to a table in another
         2060  +  ** database.
         2061  +  */
         2062  +  sqlite3NestedParse(pParse, 
         2063  +      "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
         2064  +      pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
         2065  +  if( !isView && !IsVirtual(pTab) ){
         2066  +    destroyTable(pParse, pTab);
         2067  +  }
         2068  +
         2069  +  /* Remove the table entry from SQLite's internal schema and modify
         2070  +  ** the schema cookie.
         2071  +  */
         2072  +  if( IsVirtual(pTab) ){
         2073  +    sqlite3VdbeAddOp4(v, OP_VDestroy, iDb, 0, 0, pTab->zName, 0);
         2074  +  }
         2075  +  sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
         2076  +  sqlite3ChangeCookie(pParse, iDb);
         2077  +  sqliteViewResetAll(db, iDb);
         2078  +}
  2005   2079   
  2006   2080   /*
  2007   2081   ** This routine is called to do the work of a DROP TABLE statement.
  2008   2082   ** pName is the name of the table to be dropped.
  2009   2083   */
  2010   2084   void sqlite3DropTable(Parse *pParse, SrcList *pName, int isView, int noErr){
  2011   2085     Table *pTab;
................................................................................
  2067   2141         goto exit_drop_table;
  2068   2142       }
  2069   2143       if( sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){
  2070   2144         goto exit_drop_table;
  2071   2145       }
  2072   2146     }
  2073   2147   #endif
  2074         -  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
         2148  +  if( !pParse->nested && sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
  2075   2149       sqlite3ErrorMsg(pParse, "table %s may not be dropped", pTab->zName);
  2076   2150       goto exit_drop_table;
  2077   2151     }
  2078   2152   
  2079   2153   #ifndef SQLITE_OMIT_VIEW
  2080   2154     /* Ensure DROP TABLE is not used on a view, and DROP VIEW is not used
  2081   2155     ** on a table.
................................................................................
  2091   2165   #endif
  2092   2166   
  2093   2167     /* Generate code to remove the table from the master table
  2094   2168     ** on disk.
  2095   2169     */
  2096   2170     v = sqlite3GetVdbe(pParse);
  2097   2171     if( v ){
  2098         -    Trigger *pTrigger;
  2099         -    Db *pDb = &db->aDb[iDb];
  2100   2172       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2101         -
  2102         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  2103         -    if( IsVirtual(pTab) ){
  2104         -      sqlite3VdbeAddOp0(v, OP_VBegin);
  2105         -    }
  2106         -#endif
         2173  +    sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2107   2174       sqlite3FkDropTable(pParse, pName, pTab);
  2108         -
  2109         -    /* Drop all triggers associated with the table being dropped. Code
  2110         -    ** is generated to remove entries from sqlite_master and/or
  2111         -    ** sqlite_temp_master if required.
  2112         -    */
  2113         -    pTrigger = sqlite3TriggerList(pParse, pTab);
  2114         -    while( pTrigger ){
  2115         -      assert( pTrigger->pSchema==pTab->pSchema || 
  2116         -          pTrigger->pSchema==db->aDb[1].pSchema );
  2117         -      sqlite3DropTriggerPtr(pParse, pTrigger);
  2118         -      pTrigger = pTrigger->pNext;
  2119         -    }
  2120         -
  2121         -#ifndef SQLITE_OMIT_AUTOINCREMENT
  2122         -    /* Remove any entries of the sqlite_sequence table associated with
  2123         -    ** the table being dropped. This is done before the table is dropped
  2124         -    ** at the btree level, in case the sqlite_sequence table needs to
  2125         -    ** move as a result of the drop (can happen in auto-vacuum mode).
  2126         -    */
  2127         -    if( pTab->tabFlags & TF_Autoincrement ){
  2128         -      sqlite3NestedParse(pParse,
  2129         -        "DELETE FROM %s.sqlite_sequence WHERE name=%Q",
  2130         -        pDb->zName, pTab->zName
  2131         -      );
  2132         -    }
  2133         -#endif
  2134         -
  2135         -    /* Drop all SQLITE_MASTER table and index entries that refer to the
  2136         -    ** table. The program name loops through the master table and deletes
  2137         -    ** every row that refers to a table of the same name as the one being
  2138         -    ** dropped. Triggers are handled seperately because a trigger can be
  2139         -    ** created in the temp database that refers to a table in another
  2140         -    ** database.
  2141         -    */
  2142         -    sqlite3NestedParse(pParse, 
  2143         -        "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
  2144         -        pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
  2145         -    sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2146         -    if( !isView && !IsVirtual(pTab) ){
  2147         -      destroyTable(pParse, pTab);
  2148         -    }
  2149         -
  2150         -    /* Remove the table entry from SQLite's internal schema and modify
  2151         -    ** the schema cookie.
  2152         -    */
  2153         -    if( IsVirtual(pTab) ){
  2154         -      sqlite3VdbeAddOp4(v, OP_VDestroy, iDb, 0, 0, pTab->zName, 0);
  2155         -    }
  2156         -    sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
  2157         -    sqlite3ChangeCookie(pParse, iDb);
         2175  +    sqlite3CodeDropTable(pParse, pTab, iDb, isView);
  2158   2176     }
  2159         -  sqliteViewResetAll(db, iDb);
  2160   2177   
  2161   2178   exit_drop_table:
  2162   2179     sqlite3SrcListDelete(db, pName);
  2163   2180   }
  2164   2181   
  2165   2182   /*
  2166   2183   ** This routine is called to create a new foreign key on the table
................................................................................
  2630   2647     /* 
  2631   2648     ** Allocate the index structure. 
  2632   2649     */
  2633   2650     nName = sqlite3Strlen30(zName);
  2634   2651     nCol = pList->nExpr;
  2635   2652     pIndex = sqlite3DbMallocZero(db, 
  2636   2653         sizeof(Index) +              /* Index structure  */
         2654  +      sizeof(tRowcnt)*(nCol+1) +   /* Index.aiRowEst   */
  2637   2655         sizeof(int)*nCol +           /* Index.aiColumn   */
  2638         -      sizeof(int)*(nCol+1) +       /* Index.aiRowEst   */
  2639   2656         sizeof(char *)*nCol +        /* Index.azColl     */
  2640   2657         sizeof(u8)*nCol +            /* Index.aSortOrder */
  2641   2658         nName + 1 +                  /* Index.zName      */
  2642   2659         nExtra                       /* Collation sequence names */
  2643   2660     );
  2644   2661     if( db->mallocFailed ){
  2645   2662       goto exit_create_index;
  2646   2663     }
  2647         -  pIndex->azColl = (char**)(&pIndex[1]);
         2664  +  pIndex->aiRowEst = (tRowcnt*)(&pIndex[1]);
         2665  +  pIndex->azColl = (char**)(&pIndex->aiRowEst[nCol+1]);
  2648   2666     pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]);
  2649         -  pIndex->aiRowEst = (unsigned *)(&pIndex->aiColumn[nCol]);
  2650         -  pIndex->aSortOrder = (u8 *)(&pIndex->aiRowEst[nCol+1]);
         2667  +  pIndex->aSortOrder = (u8 *)(&pIndex->aiColumn[nCol]);
  2651   2668     pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]);
  2652   2669     zExtra = (char *)(&pIndex->zName[nName+1]);
  2653   2670     memcpy(pIndex->zName, zName, nName+1);
  2654   2671     pIndex->pTable = pTab;
  2655   2672     pIndex->nColumn = pList->nExpr;
  2656   2673     pIndex->onError = (u8)onError;
  2657   2674     pIndex->autoIndex = (u8)(pName==0);
................................................................................
  2920   2937   **           aiRowEst[N]>=1
  2921   2938   **
  2922   2939   ** Apart from that, we have little to go on besides intuition as to
  2923   2940   ** how aiRowEst[] should be initialized.  The numbers generated here
  2924   2941   ** are based on typical values found in actual indices.
  2925   2942   */
  2926   2943   void sqlite3DefaultRowEst(Index *pIdx){
  2927         -  unsigned *a = pIdx->aiRowEst;
         2944  +  tRowcnt *a = pIdx->aiRowEst;
  2928   2945     int i;
  2929         -  unsigned n;
         2946  +  tRowcnt n;
  2930   2947     assert( a!=0 );
  2931   2948     a[0] = pIdx->pTable->nRowEst;
  2932   2949     if( a[0]<10 ) a[0] = 10;
  2933   2950     n = 10;
  2934   2951     for(i=1; i<=pIdx->nColumn; i++){
  2935   2952       a[i] = n;
  2936   2953       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.

  2841   2841   ** WHERE clause might influence the choice of query plan for a statement,
  2842   2842   ** then the statement will be automatically recompiled, as if there had been 
  2843   2843   ** a schema change, on the first  [sqlite3_step()] call following any change
  2844   2844   ** to the [sqlite3_bind_text | bindings] of that [parameter]. 
  2845   2845   ** ^The specific value of WHERE-clause [parameter] might influence the 
  2846   2846   ** choice of query plan if the parameter is the left-hand side of a [LIKE]
  2847   2847   ** or [GLOB] operator or if the parameter is compared to an indexed column
  2848         -** and the [SQLITE_ENABLE_STAT2] compile-time option is enabled.
         2848  +** and the [SQLITE_ENABLE_STAT3] compile-time option is enabled.
  2849   2849   ** the 
  2850   2850   ** </li>
  2851   2851   ** </ol>
  2852   2852   */
  2853   2853   int sqlite3_prepare(
  2854   2854     sqlite3 *db,            /* Database handle */
  2855   2855     const char *zSql,       /* SQL statement, UTF-8 encoded */

Changes to src/sqliteInt.h.

   455    455   ** SQLITE_MAX_U32 is a u64 constant that is the maximum u64 value
   456    456   ** that can be stored in a u32 without loss of data.  The value
   457    457   ** is 0x00000000ffffffff.  But because of quirks of some compilers, we
   458    458   ** have to specify the value in the less intuitive manner shown:
   459    459   */
   460    460   #define SQLITE_MAX_U32  ((((u64)1)<<32)-1)
   461    461   
          462  +/*
          463  +** The datatype used to store estimates of the number of rows in a
          464  +** table or index.  This is an unsigned integer type.  For 99.9% of
          465  +** the world, a 32-bit integer is sufficient.  But a 64-bit integer
          466  +** can be used at compile-time if desired.
          467  +*/
          468  +#ifdef SQLITE_64BIT_STATS
          469  + typedef u64 tRowcnt;    /* 64-bit only if requested at compile-time */
          470  +#else
          471  + typedef u32 tRowcnt;    /* 32-bit is the default */
          472  +#endif
          473  +
   462    474   /*
   463    475   ** Macros to determine whether the machine is big or little endian,
   464    476   ** evaluated at runtime.
   465    477   */
   466    478   #ifdef SQLITE_AMALGAMATION
   467    479   const int sqlite3one = 1;
   468    480   #else
................................................................................
  1288   1300   struct Table {
  1289   1301     char *zName;         /* Name of the table or view */
  1290   1302     int iPKey;           /* If not negative, use aCol[iPKey] as the primary key */
  1291   1303     int nCol;            /* Number of columns in this table */
  1292   1304     Column *aCol;        /* Information about each column */
  1293   1305     Index *pIndex;       /* List of SQL indexes on this table. */
  1294   1306     int tnum;            /* Root BTree node for this table (see note above) */
  1295         -  unsigned nRowEst;    /* Estimated rows in table - from sqlite_stat1 table */
         1307  +  tRowcnt nRowEst;     /* Estimated rows in table - from sqlite_stat1 table */
  1296   1308     Select *pSelect;     /* NULL for tables.  Points to definition if a view. */
  1297   1309     u16 nRef;            /* Number of pointers to this Table */
  1298   1310     u8 tabFlags;         /* Mask of TF_* values */
  1299   1311     u8 keyConf;          /* What to do in case of uniqueness conflict on iPKey */
  1300   1312     FKey *pFKey;         /* Linked list of all foreign keys in this table */
  1301   1313     char *zColAff;       /* String defining the affinity of each column */
  1302   1314   #ifndef SQLITE_OMIT_CHECK
................................................................................
  1487   1499   ** algorithm to employ whenever an attempt is made to insert a non-unique
  1488   1500   ** element.
  1489   1501   */
  1490   1502   struct Index {
  1491   1503     char *zName;     /* Name of this index */
  1492   1504     int nColumn;     /* Number of columns in the table used by this index */
  1493   1505     int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  1494         -  unsigned *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
         1506  +  tRowcnt *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
  1495   1507     Table *pTable;   /* The SQL table being indexed */
  1496   1508     int tnum;        /* Page containing root of this index in database file */
  1497   1509     u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1498   1510     u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  1499   1511     u8 bUnordered;   /* Use this index for == or IN queries only */
         1512  +  u8 nSample;      /* Number of elements in aSample[] */
  1500   1513     char *zColAff;   /* String defining the affinity of each column */
  1501   1514     Index *pNext;    /* The next index associated with the same table */
  1502   1515     Schema *pSchema; /* Schema containing this index */
  1503   1516     u8 *aSortOrder;  /* Array of size Index.nColumn. True==DESC, False==ASC */
  1504   1517     char **azColl;   /* Array of collation sequence names for index */
  1505         -  IndexSample *aSample;    /* Array of SQLITE_INDEX_SAMPLES samples */
         1518  +#ifdef SQLITE_ENABLE_STAT3
         1519  +  tRowcnt avgEq;           /* Average nEq value for key values not in aSample */
         1520  +  IndexSample *aSample;    /* Samples of the left-most key */
         1521  +#endif
  1506   1522   };
  1507   1523   
  1508   1524   /*
  1509   1525   ** Each sample stored in the sqlite_stat2 table is represented in memory 
  1510   1526   ** using a structure of this type.
  1511   1527   */
  1512   1528   struct IndexSample {
  1513   1529     union {
  1514   1530       char *z;        /* Value if eType is SQLITE_TEXT or SQLITE_BLOB */
  1515         -    double r;       /* Value if eType is SQLITE_FLOAT or SQLITE_INTEGER */
         1531  +    double r;       /* Value if eType is SQLITE_FLOAT */
         1532  +    i64 i;          /* Value if eType is SQLITE_INTEGER */
  1516   1533     } u;
  1517   1534     u8 eType;         /* SQLITE_NULL, SQLITE_INTEGER ... etc. */
  1518         -  u8 nByte;         /* Size in byte of text or blob. */
         1535  +  u16 nByte;        /* Size in byte of text or blob. */
         1536  +  tRowcnt nEq;      /* Est. number of rows where the key equals this sample */
         1537  +  tRowcnt nLt;      /* Est. number of rows where key is less than this sample */
         1538  +  tRowcnt nDLt;     /* Est. number of distinct keys less than this sample */
  1519   1539   };
  1520   1540   
  1521   1541   /*
  1522   1542   ** Each token coming out of the lexer is an instance of
  1523   1543   ** this structure.  Tokens are also used as part of an expression.
  1524   1544   **
  1525   1545   ** Note if Token.z==0 then Token.dyn and Token.n are undefined and
................................................................................
  2717   2737   #if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_VIRTUALTABLE)
  2718   2738     int sqlite3ViewGetColumnNames(Parse*,Table*);
  2719   2739   #else
  2720   2740   # define sqlite3ViewGetColumnNames(A,B) 0
  2721   2741   #endif
  2722   2742   
  2723   2743   void sqlite3DropTable(Parse*, SrcList*, int, int);
         2744  +void sqlite3CodeDropTable(Parse*, Table*, int, int);
  2724   2745   void sqlite3DeleteTable(sqlite3*, Table*);
  2725   2746   #ifndef SQLITE_OMIT_AUTOINCREMENT
  2726   2747     void sqlite3AutoincrementBegin(Parse *pParse);
  2727   2748     void sqlite3AutoincrementEnd(Parse *pParse);
  2728   2749   #else
  2729   2750   # define sqlite3AutoincrementBegin(X)
  2730   2751   # define sqlite3AutoincrementEnd(X)
................................................................................
  2973   2994   const void *sqlite3ValueText(sqlite3_value*, u8);
  2974   2995   int sqlite3ValueBytes(sqlite3_value*, u8);
  2975   2996   void sqlite3ValueSetStr(sqlite3_value*, int, const void *,u8, 
  2976   2997                           void(*)(void*));
  2977   2998   void sqlite3ValueFree(sqlite3_value*);
  2978   2999   sqlite3_value *sqlite3ValueNew(sqlite3 *);
  2979   3000   char *sqlite3Utf16to8(sqlite3 *, const void*, int, u8);
  2980         -#ifdef SQLITE_ENABLE_STAT2
         3001  +#ifdef SQLITE_ENABLE_STAT3
  2981   3002   char *sqlite3Utf8to16(sqlite3 *, u8, char *, int, int *);
  2982   3003   #endif
  2983   3004   int sqlite3ValueFromExpr(sqlite3 *, Expr *, u8, u8, sqlite3_value **);
  2984   3005   void sqlite3ValueApplyAffinity(sqlite3_value *, u8, u8);
  2985   3006   #ifndef SQLITE_AMALGAMATION
  2986   3007   extern const unsigned char sqlite3OpcodeProperty[];
  2987   3008   extern const unsigned char sqlite3UpperToLower[];

Changes to src/test_config.c.

   419    419   #endif
   420    420   
   421    421   #ifdef SQLITE_ENABLE_STAT2
   422    422     Tcl_SetVar2(interp, "sqlite_options", "stat2", "1", TCL_GLOBAL_ONLY);
   423    423   #else
   424    424     Tcl_SetVar2(interp, "sqlite_options", "stat2", "0", TCL_GLOBAL_ONLY);
   425    425   #endif
          426  +
          427  +#ifdef SQLITE_ENABLE_STAT3
          428  +  Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
          429  +#else
          430  +  Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
          431  +#endif
   426    432   
   427    433   #if !defined(SQLITE_ENABLE_LOCKING_STYLE)
   428    434   #  if defined(__APPLE__)
   429    435   #    define SQLITE_ENABLE_LOCKING_STYLE 1
   430    436   #  else
   431    437   #    define SQLITE_ENABLE_LOCKING_STYLE 0
   432    438   #  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.

   565    565   }
   566    566   
   567    567   /*
   568    568   ** Change the P2 operand of instruction addr so that it points to
   569    569   ** the address of the next instruction to be coded.
   570    570   */
   571    571   void sqlite3VdbeJumpHere(Vdbe *p, int addr){
   572         -  assert( addr>=0 );
   573         -  sqlite3VdbeChangeP2(p, addr, p->nOp);
          572  +  assert( addr>=0 || p->db->mallocFailed );
          573  +  if( addr>=0 ) sqlite3VdbeChangeP2(p, addr, p->nOp);
   574    574   }
   575    575   
   576    576   
   577    577   /*
   578    578   ** If the input FuncDef structure is ephemeral, then free it.  If
   579    579   ** the FuncDef is not ephermal, then do nothing.
   580    580   */

Changes to src/vdbemem.c.

  1028   1028   
  1029   1029     if( !pExpr ){
  1030   1030       *ppVal = 0;
  1031   1031       return SQLITE_OK;
  1032   1032     }
  1033   1033     op = pExpr->op;
  1034   1034   
  1035         -  /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT2.
         1035  +  /* op can only be TK_REGISTER if we have compiled with SQLITE_ENABLE_STAT3.
  1036   1036     ** The ifdef here is to enable us to achieve 100% branch test coverage even
  1037         -  ** when SQLITE_ENABLE_STAT2 is omitted.
         1037  +  ** when SQLITE_ENABLE_STAT3 is omitted.
  1038   1038     */
  1039         -#ifdef SQLITE_ENABLE_STAT2
         1039  +#ifdef SQLITE_ENABLE_STAT3
  1040   1040     if( op==TK_REGISTER ) op = pExpr->op2;
  1041   1041   #else
  1042   1042     if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;
  1043   1043   #endif
  1044   1044   
  1045   1045     /* Handle negative integers in a single step.  This is needed in the
  1046   1046     ** case when the value is -9223372036854775808.

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

Changes to test/alter.test.

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

Changes to test/analyze.test.

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

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}} {

Changes to test/analyze7.test.

    78     78   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
    79     79   do_test analyze7-3.1 {
    80     80     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    81     81   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
    82     82   do_test analyze7-3.2.1 {
    83     83     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
    84     84   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
    85         -ifcapable stat2 {
    86         -  # If ENABLE_STAT2 is defined, SQLite comes up with a different estimated
           85  +ifcapable stat3 {
           86  +  # If ENABLE_STAT3 is defined, SQLite comes up with a different estimated
    87     87     # row count for (c=2) than it does for (c=?).
    88     88     do_test analyze7-3.2.2 {
    89     89       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    90         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~51 rows)}}
           90  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~57 rows)}}
    91     91   } else {
    92         -  # If ENABLE_STAT2 is not defined, the expected row count for (c=2) is the
           92  +  # If ENABLE_STAT3 is not defined, the expected row count for (c=2) is the
    93     93     # same as that for (c=?).
    94     94     do_test analyze7-3.2.3 {
    95     95       execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    96     96     } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
    97     97   }
    98     98   do_test analyze7-3.3 {
    99     99     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
   100    100   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
   101         -do_test analyze7-3.4 {
   102         -  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
   103         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
   104         -do_test analyze7-3.5 {
   105         -  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
   106         -} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
          101  +ifcapable {!stat3} {
          102  +  do_test analyze7-3.4 {
          103  +    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
          104  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
          105  +  do_test analyze7-3.5 {
          106  +    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
          107  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
          108  +}
   107    109   do_test analyze7-3.6 {
   108    110     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
   109    111   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}
   110    112   
   111    113   finish_test

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.

  2320   2320           DROP TABLE v1chng;
  2321   2321         }
  2322   2322       }
  2323   2323     }
  2324   2324     ifcapable stat2 {
  2325   2325       set stat2 "sqlite_stat2 "
  2326   2326     } else {
  2327         -    set stat2 ""
         2327  +    ifcapable stat3 {
         2328  +      set stat2 "sqlite_stat3 "
         2329  +    } else {
         2330  +      set stat2 ""
         2331  +    }
  2328   2332     }
  2329   2333     do_test auth-5.2 {
  2330   2334       execsql {
  2331   2335         SELECT name FROM (
  2332   2336           SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
  2333   2337         WHERE type='table'
  2334   2338         ORDER BY name

Changes to test/dbstatus.test.

    51     51   
    52     52   
    53     53   proc lookaside {db} {
    54     54     expr { $::lookaside_buffer_size *
    55     55       [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1]
    56     56     }
    57     57   }
           58  +
           59  +ifcapable stat3 {
           60  +  set STAT3 1
           61  +} else {
           62  +  set STAT3 0
           63  +}
    58     64   
    59     65   #---------------------------------------------------------------------------
    60     66   # Run the dbstatus-2 and dbstatus-3 tests with several of different
    61     67   # lookaside buffer sizes.
    62     68   #
    63     69   foreach ::lookaside_buffer_size {0 64 120} {
    64     70   
................................................................................
   114    120         END;
   115    121       }
   116    122       5 {
   117    123         CREATE TABLE t1(a, b);
   118    124         CREATE TABLE t2(c, d);
   119    125         CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;
   120    126       }
   121         -    6 {
          127  +    6y {
   122    128         CREATE TABLE t1(a, b);
   123    129         CREATE INDEX i1 ON t1(a);
   124    130         CREATE INDEX i2 ON t1(a,b);
   125    131         CREATE INDEX i3 ON t1(b,b);
   126    132         INSERT INTO t1 VALUES(randomblob(20), randomblob(25));
   127    133         INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
   128    134         INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;
................................................................................
   194    200       # for any reason is not counted as "schema memory".
   195    201       #
   196    202       # Additionally, in auto-vacuum mode, dropping tables and indexes causes
   197    203       # the page-cache to shrink. So the amount of memory freed is always
   198    204       # much greater than just that reported by DBSTATUS_SCHEMA_USED in this
   199    205       # case.
   200    206       #
   201         -    if {[string match *x $tn] || $AUTOVACUUM} {
          207  +    # Some of the memory used for sqlite_stat3 is unaccounted for by
          208  +    # dbstatus.
          209  +    #
          210  +    if {[string match *x $tn] || $AUTOVACUUM
          211  +         || ([string match *y $tn] && $STAT3)} {
   202    212         do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1
   203    213       } else {
   204    214         do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree
   205    215       }
   206    216     
   207    217       do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3"
   208    218       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)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
    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)}}

Changes to tool/warnings.sh.

     5      5   #
     6      6   rm -f sqlite3.c
     7      7   make sqlite3.c-debug
     8      8   echo '********** No optimizations.  Includes FTS4 and RTREE *********'
     9      9   gcc -c -Wshadow -Wall -Wextra -pedantic-errors -Wno-long-long -std=c89 \
    10     10         -ansi -DHAVE_STDINT_H -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE \
    11     11         sqlite3.c
    12         -echo '********** No optimizations. ENABLE_STAT2. THREADSAFE=0 *******'
           12  +echo '********** No optimizations. ENABLE_STAT3. THREADSAFE=0 *******'
    13     13   gcc -c -Wshadow -Wall -Wextra -pedantic-errors -Wno-long-long -std=c89 \
    14         -      -ansi -DSQLITE_ENABLE_STAT2 -DSQLITE_THREADSAFE=0 \
           14  +      -ansi -DSQLITE_ENABLE_STAT3 -DSQLITE_THREADSAFE=0 \
    15     15         sqlite3.c
    16     16   echo '********** Optimized -O3.  Includes FTS4 and RTREE ************'
    17     17   gcc -O3 -c -Wshadow -Wall -Wextra -pedantic-errors -Wno-long-long -std=c89 \
    18     18         -ansi -DHAVE_STDINT_H -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_RTREE \
    19     19         sqlite3.c