/ Changes On Branch stat3-trunk
Login

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

Changes In Branch stat3-trunk Excluding Merge-Ins

This is equivalent to a diff from 6d8d4e1694 to 4a153f0100

2011-10-03
15:30
Merge the STAT3 changes into trunk. (check-in: 774d0842bc user: drh tags: trunk)
2011-09-25
17:47
If an open as read/write fails, do not try to reopen as read-only if in exclusive access mode. (Closed-Leaf check-in: 4a153f0100 user: drh tags: stat3-trunk)
2011-09-23
16:34
Add the -DBUILD_sqlite option to the compiler when building testfixture.exe using the configure script. (check-in: 15a13b6c59 user: drh tags: trunk)
14:40
Merge the latest trunk changes into the stat3-trunk branch. (check-in: 0beb88a92c user: drh tags: stat3-trunk)
2011-09-22
15:31
Remove a redundant (and undefined) "forcedelete" command from corruptE.test. (check-in: 6d8d4e1694 user: dan tags: trunk)
14:56
Remove the SQLITE_PAGECACHE_BLOCKALLOC compilation option. (check-in: 4eb4689834 user: dan tags: trunk)

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.8, 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 3.7.9 and later and 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.8.  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 of
           90  +** the sqlite_stat2 table.  There are multiple entries for each index.
           91  +** The idx column names the index and the tbl column is the table of the
           92  +** index.  If the idx and tbl columns are the same, then the sample is
           93  +** of the INTEGER PRIMARY KEY.  The sample column is a value taken from
           94  +** the left-most column of the index.  The nEq column is the approximate
           95  +** number of entires in the index whose left-most column exactly matches
           96  +** the sample.  nLt is the approximate number of entires whose left-most
           97  +** column is less than the sample.  The nDLt column is the approximate
           98  +** number of distinct left-most entries in the index that are less than
           99  +** the sample.
          100  +**
          101  +** Future versions of SQLite might change to store a string containing
          102  +** multiple integers values in the nDLt column of sqlite_stat3.  The first
          103  +** integer will be the number of prior index entires that are distinct in
          104  +** the left-most column.  The second integer will be the number of prior index
          105  +** entries that are distinct in the first two columns.  The third integer
          106  +** will be the number of prior index entries that are distinct in the first
          107  +** three columns.  And so forth.  With that extension, the nDLt field is
          108  +** similar in function to the sqlite_stat1.stat field.
          109  +**
          110  +** There can be an arbitrary number of sqlite_stat3 entries per index.
          111  +** The ANALYZE command will typically generate sqlite_stat3 tables
          112  +** that contain between 10 and 40 samples which are distributed across
          113  +** the key space, though not uniformly, and which include samples with
          114  +** largest possible nEq values.
    13    115   */
    14    116   #ifndef SQLITE_OMIT_ANALYZE
    15    117   #include "sqliteInt.h"
    16    118   
    17    119   /*
    18    120   ** This routine generates code that opens the sqlite_stat1 table for
    19    121   ** writing with cursor iStatCur. If the library was built with the
................................................................................
    38    140     const char *zWhereType  /* Either "tbl" or "idx" */
    39    141   ){
    40    142     static const struct {
    41    143       const char *zName;
    42    144       const char *zCols;
    43    145     } aTable[] = {
    44    146       { "sqlite_stat1", "tbl,idx,stat" },
    45         -#ifdef SQLITE_ENABLE_STAT2
    46         -    { "sqlite_stat2", "tbl,idx,sampleno,sample" },
          147  +#ifdef SQLITE_ENABLE_STAT3
          148  +    { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
          149  +#endif
          150  +  };
          151  +  static const char *azToDrop[] = { 
          152  +    "sqlite_stat2",
          153  +#ifndef SQLITE_ENABLE_STAT3
          154  +    "sqlite_stat3",
    47    155   #endif
    48    156     };
    49    157   
    50    158     int aRoot[] = {0, 0};
    51    159     u8 aCreateTbl[] = {0, 0};
    52    160   
    53    161     int i;
................................................................................
    55    163     Db *pDb;
    56    164     Vdbe *v = sqlite3GetVdbe(pParse);
    57    165     if( v==0 ) return;
    58    166     assert( sqlite3BtreeHoldsAllMutexes(db) );
    59    167     assert( sqlite3VdbeDb(v)==db );
    60    168     pDb = &db->aDb[iDb];
    61    169   
          170  +  /* Drop all statistics tables that this version of SQLite does not
          171  +  ** understand.
          172  +  */
          173  +  for(i=0; i<ArraySize(azToDrop); i++){
          174  +    Table *pTab = sqlite3FindTable(db, azToDrop[i], pDb->zName);
          175  +    if( pTab ){
          176  +      sqlite3CodeDropTable(pParse, pTab, iDb, 0);
          177  +      break;
          178  +    }
          179  +  }
          180  +
          181  +  /* Create new statistic tables if they do not exist, or clear them
          182  +  ** if they do already exist.
          183  +  */
    62    184     for(i=0; i<ArraySize(aTable); i++){
    63    185       const char *zTab = aTable[i].zName;
    64    186       Table *pStat;
    65    187       if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){
    66    188         /* The sqlite_stat[12] table does not exist. Create it. Note that a 
    67    189         ** side-effect of the CREATE TABLE statement is to leave the rootpage 
    68    190         ** of the new table in register pParse->regRoot. This is important 
................................................................................
    85    207         }else{
    86    208           /* The sqlite_stat[12] table already exists.  Delete all rows. */
    87    209           sqlite3VdbeAddOp2(v, OP_Clear, aRoot[i], iDb);
    88    210         }
    89    211       }
    90    212     }
    91    213   
    92         -  /* Open the sqlite_stat[12] tables for writing. */
          214  +  /* Open the sqlite_stat[13] tables for writing. */
    93    215     for(i=0; i<ArraySize(aTable); i++){
    94    216       sqlite3VdbeAddOp3(v, OP_OpenWrite, iStatCur+i, aRoot[i], iDb);
    95    217       sqlite3VdbeChangeP4(v, -1, (char *)3, P4_INT32);
    96    218       sqlite3VdbeChangeP5(v, aCreateTbl[i]);
    97    219     }
    98    220   }
          221  +
          222  +/*
          223  +** Recommended number of samples for sqlite_stat3
          224  +*/
          225  +#ifndef SQLITE_STAT3_SAMPLES
          226  +# define SQLITE_STAT3_SAMPLES 24
          227  +#endif
          228  +
          229  +/*
          230  +** Three SQL functions - stat3_init(), stat3_push(), and stat3_pop() -
          231  +** share an instance of the following structure to hold their state
          232  +** information.
          233  +*/
          234  +typedef struct Stat3Accum Stat3Accum;
          235  +struct Stat3Accum {
          236  +  tRowcnt nRow;             /* Number of rows in the entire table */
          237  +  tRowcnt nPSample;         /* How often to do a periodic sample */
          238  +  int iMin;                 /* Index of entry with minimum nEq and hash */
          239  +  int mxSample;             /* Maximum number of samples to accumulate */
          240  +  int nSample;              /* Current number of samples */
          241  +  u32 iPrn;                 /* Pseudo-random number used for sampling */
          242  +  struct Stat3Sample {
          243  +    i64 iRowid;                /* Rowid in main table of the key */
          244  +    tRowcnt nEq;               /* sqlite_stat3.nEq */
          245  +    tRowcnt nLt;               /* sqlite_stat3.nLt */
          246  +    tRowcnt nDLt;              /* sqlite_stat3.nDLt */
          247  +    u8 isPSample;              /* True if a periodic sample */
          248  +    u32 iHash;                 /* Tiebreaker hash */
          249  +  } *a;                     /* An array of samples */
          250  +};
          251  +
          252  +#ifdef SQLITE_ENABLE_STAT3
          253  +/*
          254  +** Implementation of the stat3_init(C,S) SQL function.  The two parameters
          255  +** are the number of rows in the table or index (C) and the number of samples
          256  +** to accumulate (S).
          257  +**
          258  +** This routine allocates the Stat3Accum object.
          259  +**
          260  +** The return value is the Stat3Accum object (P).
          261  +*/
          262  +static void stat3Init(
          263  +  sqlite3_context *context,
          264  +  int argc,
          265  +  sqlite3_value **argv
          266  +){
          267  +  Stat3Accum *p;
          268  +  tRowcnt nRow;
          269  +  int mxSample;
          270  +  int n;
          271  +
          272  +  UNUSED_PARAMETER(argc);
          273  +  nRow = (tRowcnt)sqlite3_value_int64(argv[0]);
          274  +  mxSample = sqlite3_value_int(argv[1]);
          275  +  n = sizeof(*p) + sizeof(p->a[0])*mxSample;
          276  +  p = sqlite3_malloc( n );
          277  +  if( p==0 ){
          278  +    sqlite3_result_error_nomem(context);
          279  +    return;
          280  +  }
          281  +  memset(p, 0, n);
          282  +  p->a = (struct Stat3Sample*)&p[1];
          283  +  p->nRow = nRow;
          284  +  p->mxSample = mxSample;
          285  +  p->nPSample = p->nRow/(mxSample/3+1) + 1;
          286  +  sqlite3_randomness(sizeof(p->iPrn), &p->iPrn);
          287  +  sqlite3_result_blob(context, p, sizeof(p), sqlite3_free);
          288  +}
          289  +static const FuncDef stat3InitFuncdef = {
          290  +  2,                /* nArg */
          291  +  SQLITE_UTF8,      /* iPrefEnc */
          292  +  0,                /* flags */
          293  +  0,                /* pUserData */
          294  +  0,                /* pNext */
          295  +  stat3Init,        /* xFunc */
          296  +  0,                /* xStep */
          297  +  0,                /* xFinalize */
          298  +  "stat3_init",     /* zName */
          299  +  0,                /* pHash */
          300  +  0                 /* pDestructor */
          301  +};
          302  +
          303  +
          304  +/*
          305  +** Implementation of the stat3_push(nEq,nLt,nDLt,rowid,P) SQL function.  The
          306  +** arguments describe a single key instance.  This routine makes the 
          307  +** decision about whether or not to retain this key for the sqlite_stat3
          308  +** table.
          309  +**
          310  +** The return value is NULL.
          311  +*/
          312  +static void stat3Push(
          313  +  sqlite3_context *context,
          314  +  int argc,
          315  +  sqlite3_value **argv
          316  +){
          317  +  Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[4]);
          318  +  tRowcnt nEq = sqlite3_value_int64(argv[0]);
          319  +  tRowcnt nLt = sqlite3_value_int64(argv[1]);
          320  +  tRowcnt nDLt = sqlite3_value_int64(argv[2]);
          321  +  i64 rowid = sqlite3_value_int64(argv[3]);
          322  +  u8 isPSample = 0;
          323  +  u8 doInsert = 0;
          324  +  int iMin = p->iMin;
          325  +  struct Stat3Sample *pSample;
          326  +  int i;
          327  +  u32 h;
          328  +
          329  +  UNUSED_PARAMETER(context);
          330  +  UNUSED_PARAMETER(argc);
          331  +  if( nEq==0 ) return;
          332  +  h = p->iPrn = p->iPrn*1103515245 + 12345;
          333  +  if( (nLt/p->nPSample)!=((nEq+nLt)/p->nPSample) ){
          334  +    doInsert = isPSample = 1;
          335  +  }else if( p->nSample<p->mxSample ){
          336  +    doInsert = 1;
          337  +  }else{
          338  +    if( nEq>p->a[iMin].nEq || (nEq==p->a[iMin].nEq && h>p->a[iMin].iHash) ){
          339  +      doInsert = 1;
          340  +    }
          341  +  }
          342  +  if( !doInsert ) return;
          343  +  if( p->nSample==p->mxSample ){
          344  +    assert( p->nSample - iMin - 1 >= 0 );
          345  +    memmove(&p->a[iMin], &p->a[iMin+1], sizeof(p->a[0])*(p->nSample-iMin-1));
          346  +    pSample = &p->a[p->nSample-1];
          347  +  }else{
          348  +    pSample = &p->a[p->nSample++];
          349  +  }
          350  +  pSample->iRowid = rowid;
          351  +  pSample->nEq = nEq;
          352  +  pSample->nLt = nLt;
          353  +  pSample->nDLt = nDLt;
          354  +  pSample->iHash = h;
          355  +  pSample->isPSample = isPSample;
          356  +
          357  +  /* Find the new minimum */
          358  +  if( p->nSample==p->mxSample ){
          359  +    pSample = p->a;
          360  +    i = 0;
          361  +    while( pSample->isPSample ){
          362  +      i++;
          363  +      pSample++;
          364  +      assert( i<p->nSample );
          365  +    }
          366  +    nEq = pSample->nEq;
          367  +    h = pSample->iHash;
          368  +    iMin = i;
          369  +    for(i++, pSample++; i<p->nSample; i++, pSample++){
          370  +      if( pSample->isPSample ) continue;
          371  +      if( pSample->nEq<nEq
          372  +       || (pSample->nEq==nEq && pSample->iHash<h)
          373  +      ){
          374  +        iMin = i;
          375  +        nEq = pSample->nEq;
          376  +        h = pSample->iHash;
          377  +      }
          378  +    }
          379  +    p->iMin = iMin;
          380  +  }
          381  +}
          382  +static const FuncDef stat3PushFuncdef = {
          383  +  5,                /* nArg */
          384  +  SQLITE_UTF8,      /* iPrefEnc */
          385  +  0,                /* flags */
          386  +  0,                /* pUserData */
          387  +  0,                /* pNext */
          388  +  stat3Push,        /* xFunc */
          389  +  0,                /* xStep */
          390  +  0,                /* xFinalize */
          391  +  "stat3_push",     /* zName */
          392  +  0,                /* pHash */
          393  +  0                 /* pDestructor */
          394  +};
          395  +
          396  +/*
          397  +** Implementation of the stat3_get(P,N,...) SQL function.  This routine is
          398  +** used to query the results.  Content is returned for the Nth sqlite_stat3
          399  +** row where N is between 0 and S-1 and S is the number of samples.  The
          400  +** value returned depends on the number of arguments.
          401  +**
          402  +**   argc==2    result:  rowid
          403  +**   argc==3    result:  nEq
          404  +**   argc==4    result:  nLt
          405  +**   argc==5    result:  nDLt
          406  +*/
          407  +static void stat3Get(
          408  +  sqlite3_context *context,
          409  +  int argc,
          410  +  sqlite3_value **argv
          411  +){
          412  +  int n = sqlite3_value_int(argv[1]);
          413  +  Stat3Accum *p = (Stat3Accum*)sqlite3_value_blob(argv[0]);
          414  +
          415  +  assert( p!=0 );
          416  +  if( p->nSample<=n ) return;
          417  +  switch( argc ){
          418  +    case 2:  sqlite3_result_int64(context, p->a[n].iRowid); break;
          419  +    case 3:  sqlite3_result_int64(context, p->a[n].nEq);    break;
          420  +    case 4:  sqlite3_result_int64(context, p->a[n].nLt);    break;
          421  +    default: sqlite3_result_int64(context, p->a[n].nDLt);   break;
          422  +  }
          423  +}
          424  +static const FuncDef stat3GetFuncdef = {
          425  +  -1,               /* nArg */
          426  +  SQLITE_UTF8,      /* iPrefEnc */
          427  +  0,                /* flags */
          428  +  0,                /* pUserData */
          429  +  0,                /* pNext */
          430  +  stat3Get,         /* xFunc */
          431  +  0,                /* xStep */
          432  +  0,                /* xFinalize */
          433  +  "stat3_get",     /* zName */
          434  +  0,                /* pHash */
          435  +  0                 /* pDestructor */
          436  +};
          437  +#endif /* SQLITE_ENABLE_STAT3 */
          438  +
          439  +
          440  +
    99    441   
   100    442   /*
   101    443   ** Generate code to do an analysis of all indices associated with
   102    444   ** a single table.
   103    445   */
   104    446   static void analyzeOneTable(
   105    447     Parse *pParse,   /* Parser context */
................................................................................
   115    457     int i;                       /* Loop counter */
   116    458     int topOfLoop;               /* The top of the loop */
   117    459     int endOfLoop;               /* The end of the loop */
   118    460     int jZeroRows = -1;          /* Jump from here if number of rows is zero */
   119    461     int iDb;                     /* Index of database containing pTab */
   120    462     int regTabname = iMem++;     /* Register containing table name */
   121    463     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 */
          464  +  int regStat1 = iMem++;       /* The stat column of sqlite_stat1 */
          465  +#ifdef SQLITE_ENABLE_STAT3
          466  +  int regNumEq = regStat1;     /* Number of instances.  Same as regStat1 */
          467  +  int regNumLt = iMem++;       /* Number of keys less than regSample */
          468  +  int regNumDLt = iMem++;      /* Number of distinct keys less than regSample */
          469  +  int regSample = iMem++;      /* The next sample value */
          470  +  int regRowid = regSample;    /* Rowid of a sample */
          471  +  int regAccum = iMem++;       /* Register to hold Stat3Accum object */
          472  +  int regLoop = iMem++;        /* Loop counter */
          473  +  int regCount = iMem++;       /* Number of rows in the table or index */
          474  +  int regTemp1 = iMem++;       /* Intermediate register */
          475  +  int regTemp2 = iMem++;       /* Intermediate register */
          476  +  int once = 1;                /* One-time initialization */
          477  +  int shortJump = 0;           /* Instruction address */
          478  +  int iTabCur = pParse->nTab++; /* Table cursor */
          479  +#endif
          480  +  int regCol = iMem++;         /* Content of a column in analyzed table */
   124    481     int regRec = iMem++;         /* Register holding completed record */
   125    482     int regTemp = iMem++;        /* Temporary use register */
   126         -  int regRowid = iMem++;       /* Rowid for the inserted record */
          483  +  int regNewRowid = iMem++;    /* Rowid for the inserted record */
   127    484   
   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    485   
   137    486     v = sqlite3GetVdbe(pParse);
   138    487     if( v==0 || NEVER(pTab==0) ){
   139    488       return;
   140    489     }
   141    490     if( pTab->tnum==0 ){
   142    491       /* Do not gather statistics on views or virtual tables */
................................................................................
   161    510     sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
   162    511   
   163    512     iIdxCur = pParse->nTab++;
   164    513     sqlite3VdbeAddOp4(v, OP_String8, 0, regTabname, 0, pTab->zName, 0);
   165    514     for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   166    515       int nCol;
   167    516       KeyInfo *pKey;
          517  +    int addrIfNot = 0;           /* address of OP_IfNot */
          518  +    int *aChngAddr;              /* Array of jump instruction addresses */
   168    519   
   169    520       if( pOnlyIdx && pOnlyIdx!=pIdx ) continue;
          521  +    VdbeNoopComment((v, "Begin analysis of %s", pIdx->zName));
   170    522       nCol = pIdx->nColumn;
          523  +    aChngAddr = sqlite3DbMallocRaw(db, sizeof(int)*nCol);
          524  +    if( aChngAddr==0 ) continue;
   171    525       pKey = sqlite3IndexKeyinfo(pParse, pIdx);
   172    526       if( iMem+1+(nCol*2)>pParse->nMem ){
   173    527         pParse->nMem = iMem+1+(nCol*2);
   174    528       }
   175    529   
   176    530       /* Open a cursor to the index to be analyzed. */
   177    531       assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) );
................................................................................
   178    532       sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb,
   179    533           (char *)pKey, P4_KEYINFO_HANDOFF);
   180    534       VdbeComment((v, "%s", pIdx->zName));
   181    535   
   182    536       /* Populate the register containing the index name. */
   183    537       sqlite3VdbeAddOp4(v, OP_String8, 0, regIdxname, 0, pIdx->zName, 0);
   184    538   
   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);
          539  +#ifdef SQLITE_ENABLE_STAT3
          540  +    if( once ){
          541  +      once = 0;
          542  +      sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);
   203    543       }
   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
          544  +    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regCount);
          545  +    sqlite3VdbeAddOp2(v, OP_Integer, SQLITE_STAT3_SAMPLES, regTemp1);
          546  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumEq);
          547  +    sqlite3VdbeAddOp2(v, OP_Integer, 0, regNumLt);
          548  +    sqlite3VdbeAddOp2(v, OP_Integer, -1, regNumDLt);
          549  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regCount, regAccum,
          550  +                      (char*)&stat3InitFuncdef, P4_FUNCDEF);
          551  +    sqlite3VdbeChangeP5(v, 2);
          552  +#endif /* SQLITE_ENABLE_STAT3 */
   210    553   
   211    554       /* The block of memory cells initialized here is used as follows.
   212    555       **
   213    556       **    iMem:                
   214    557       **        The total number of rows in the table.
   215    558       **
   216    559       **    iMem+1 .. iMem+nCol: 
................................................................................
   232    575       }
   233    576   
   234    577       /* Start the analysis loop. This loop runs through all the entries in
   235    578       ** the index b-tree.  */
   236    579       endOfLoop = sqlite3VdbeMakeLabel(v);
   237    580       sqlite3VdbeAddOp2(v, OP_Rewind, iIdxCur, endOfLoop);
   238    581       topOfLoop = sqlite3VdbeCurrentAddr(v);
   239         -    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);
          582  +    sqlite3VdbeAddOp2(v, OP_AddImm, iMem, 1);  /* Increment row counter */
   240    583   
   241    584       for(i=0; i<nCol; i++){
   242    585         CollSeq *pColl;
   243    586         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, regCol);
   244    587         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    588           /* Always record the very first row */
   277         -        sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
          589  +        addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, iMem+1);
   278    590         }
   279    591         assert( pIdx->azColl!=0 );
   280    592         assert( pIdx->azColl[i]!=0 );
   281    593         pColl = sqlite3LocateCollSeq(pParse, pIdx->azColl[i]);
   282         -      sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
   283         -                       (char*)pColl, P4_COLLSEQ);
          594  +      aChngAddr[i] = sqlite3VdbeAddOp4(v, OP_Ne, regCol, 0, iMem+nCol+i+1,
          595  +                                      (char*)pColl, P4_COLLSEQ);
   284    596         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;
          597  +      VdbeComment((v, "jump if column %d changed", i));
          598  +#ifdef SQLITE_ENABLE_STAT3
          599  +      if( i==0 ){
          600  +        sqlite3VdbeAddOp2(v, OP_AddImm, regNumEq, 1);
          601  +        VdbeComment((v, "incr repeat count"));
          602  +      }
          603  +#endif
   292    604       }
   293    605       sqlite3VdbeAddOp2(v, OP_Goto, 0, endOfLoop);
   294    606       for(i=0; i<nCol; i++){
   295         -      int addr2 = sqlite3VdbeCurrentAddr(v) - (nCol*2);
          607  +      sqlite3VdbeJumpHere(v, aChngAddr[i]);  /* Set jump dest for the OP_Ne */
   296    608         if( i==0 ){
   297         -        sqlite3VdbeJumpHere(v, addr2-1);  /* Set jump dest for the OP_IfNot */
          609  +        sqlite3VdbeJumpHere(v, addrIfNot);   /* Jump dest for OP_IfNot */
          610  +#ifdef SQLITE_ENABLE_STAT3
          611  +        sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
          612  +                          (char*)&stat3PushFuncdef, P4_FUNCDEF);
          613  +        sqlite3VdbeChangeP5(v, 5);
          614  +        sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, pIdx->nColumn, regRowid);
          615  +        sqlite3VdbeAddOp3(v, OP_Add, regNumEq, regNumLt, regNumLt);
          616  +        sqlite3VdbeAddOp2(v, OP_AddImm, regNumDLt, 1);
          617  +        sqlite3VdbeAddOp2(v, OP_Integer, 1, regNumEq);
          618  +#endif        
   298    619         }
   299         -      sqlite3VdbeJumpHere(v, addr2);      /* Set jump dest for the OP_Ne */
   300    620         sqlite3VdbeAddOp2(v, OP_AddImm, iMem+i+1, 1);
   301    621         sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, i, iMem+nCol+i+1);
   302    622       }
          623  +    sqlite3DbFree(db, aChngAddr);
   303    624   
   304         -    /* End of the analysis loop. */
          625  +    /* Always jump here after updating the iMem+1...iMem+1+nCol counters */
   305    626       sqlite3VdbeResolveLabel(v, endOfLoop);
          627  +
   306    628       sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, topOfLoop);
   307    629       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
          630  +#ifdef SQLITE_ENABLE_STAT3
          631  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regNumEq, regTemp2,
          632  +                      (char*)&stat3PushFuncdef, P4_FUNCDEF);
          633  +    sqlite3VdbeChangeP5(v, 5);
          634  +    sqlite3VdbeAddOp2(v, OP_Integer, -1, regLoop);
          635  +    shortJump = 
          636  +    sqlite3VdbeAddOp2(v, OP_AddImm, regLoop, 1);
          637  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regTemp1,
          638  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          639  +    sqlite3VdbeChangeP5(v, 2);
          640  +    sqlite3VdbeAddOp1(v, OP_IsNull, regTemp1);
          641  +    sqlite3VdbeAddOp3(v, OP_NotExists, iTabCur, shortJump, regTemp1);
          642  +    sqlite3VdbeAddOp3(v, OP_Column, iTabCur, pIdx->aiColumn[0], regSample);
          643  +    sqlite3ColumnDefault(v, pTab, pIdx->aiColumn[0], regSample);
          644  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumEq,
          645  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          646  +    sqlite3VdbeChangeP5(v, 3);
          647  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumLt,
          648  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          649  +    sqlite3VdbeChangeP5(v, 4);
          650  +    sqlite3VdbeAddOp4(v, OP_Function, 1, regAccum, regNumDLt,
          651  +                      (char*)&stat3GetFuncdef, P4_FUNCDEF);
          652  +    sqlite3VdbeChangeP5(v, 5);
          653  +    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 6, regRec, "bbbbbb", 0);
          654  +    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur+1, regNewRowid);
          655  +    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regRec, regNewRowid);
          656  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, shortJump);
          657  +    sqlite3VdbeJumpHere(v, shortJump+2);
          658  +#endif        
   308    659   
   309    660       /* Store the results in sqlite_stat1.
   310    661       **
   311    662       ** The result is a single row of the sqlite_stat1 table.  The first
   312    663       ** two columns are the names of the table and index.  The third column
   313    664       ** is a string composed of a list of integer statistics about the
   314    665       ** index.  The first integer in the list is the total number of entries
................................................................................
   320    671       **
   321    672       **        I = (K+D-1)/D
   322    673       **
   323    674       ** If K==0 then no entry is made into the sqlite_stat1 table.  
   324    675       ** If K>0 then it is always the case the D>0 so division by zero
   325    676       ** is never possible.
   326    677       */
   327         -    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regSampleno);
          678  +    sqlite3VdbeAddOp2(v, OP_SCopy, iMem, regStat1);
   328    679       if( jZeroRows<0 ){
   329    680         jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, iMem);
   330    681       }
   331    682       for(i=0; i<nCol; i++){
   332    683         sqlite3VdbeAddOp4(v, OP_String8, 0, regTemp, 0, " ", 0);
   333         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
          684  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   334    685         sqlite3VdbeAddOp3(v, OP_Add, iMem, iMem+i+1, regTemp);
   335    686         sqlite3VdbeAddOp2(v, OP_AddImm, regTemp, -1);
   336    687         sqlite3VdbeAddOp3(v, OP_Divide, iMem+i+1, regTemp, regTemp);
   337    688         sqlite3VdbeAddOp1(v, OP_ToInt, regTemp);
   338         -      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regSampleno, regSampleno);
          689  +      sqlite3VdbeAddOp3(v, OP_Concat, regTemp, regStat1, regStat1);
   339    690       }
   340    691       sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   341         -    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid);
   342         -    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid);
          692  +    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
          693  +    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   343    694       sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   344    695     }
   345    696   
   346    697     /* If the table has no indices, create a single sqlite_stat1 entry
   347    698     ** containing NULL as the index name and the row count as the content.
   348    699     */
   349    700     if( pTab->pIndex==0 ){
   350    701       sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pTab->tnum, iDb);
   351    702       VdbeComment((v, "%s", pTab->zName));
   352         -    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regSampleno);
          703  +    sqlite3VdbeAddOp2(v, OP_Count, iIdxCur, regStat1);
   353    704       sqlite3VdbeAddOp1(v, OP_Close, iIdxCur);
   354         -    jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regSampleno);
          705  +    jZeroRows = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1);
   355    706     }else{
   356    707       sqlite3VdbeJumpHere(v, jZeroRows);
   357    708       jZeroRows = sqlite3VdbeAddOp0(v, OP_Goto);
   358    709     }
   359    710     sqlite3VdbeAddOp2(v, OP_Null, 0, regIdxname);
   360    711     sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regRec, "aaa", 0);
   361         -  sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regRowid);
   362         -  sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regRowid);
          712  +  sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
          713  +  sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regRec, regNewRowid);
   363    714     sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
   364    715     if( pParse->nMem<regRec ) pParse->nMem = regRec;
   365    716     sqlite3VdbeJumpHere(v, jZeroRows);
   366    717   }
          718  +
   367    719   
   368    720   /*
   369    721   ** Generate code that will cause the most recent index analysis to
   370    722   ** be loaded into internal hash tables where is can be used.
   371    723   */
   372    724   static void loadAnalysis(Parse *pParse, int iDb){
   373    725     Vdbe *v = sqlite3GetVdbe(pParse);
................................................................................
   384    736     Schema *pSchema = db->aDb[iDb].pSchema;    /* Schema of database iDb */
   385    737     HashElem *k;
   386    738     int iStatCur;
   387    739     int iMem;
   388    740   
   389    741     sqlite3BeginWriteOperation(pParse, 0, iDb);
   390    742     iStatCur = pParse->nTab;
   391         -  pParse->nTab += 2;
          743  +  pParse->nTab += 3;
   392    744     openStatTable(pParse, iDb, iStatCur, 0, 0);
   393    745     iMem = pParse->nMem+1;
   394    746     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
   395    747     for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
   396    748       Table *pTab = (Table*)sqliteHashData(k);
   397    749       analyzeOneTable(pParse, pTab, 0, iStatCur, iMem);
   398    750     }
................................................................................
   409    761     int iStatCur;
   410    762   
   411    763     assert( pTab!=0 );
   412    764     assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
   413    765     iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
   414    766     sqlite3BeginWriteOperation(pParse, 0, iDb);
   415    767     iStatCur = pParse->nTab;
   416         -  pParse->nTab += 2;
          768  +  pParse->nTab += 3;
   417    769     if( pOnlyIdx ){
   418    770       openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx");
   419    771     }else{
   420    772       openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl");
   421    773     }
   422    774     analyzeOneTable(pParse, pTab, pOnlyIdx, iStatCur, pParse->nMem+1);
   423    775     loadAnalysis(pParse, iDb);
................................................................................
   514    866   ** the table.
   515    867   */
   516    868   static int analysisLoader(void *pData, int argc, char **argv, char **NotUsed){
   517    869     analysisInfo *pInfo = (analysisInfo*)pData;
   518    870     Index *pIndex;
   519    871     Table *pTable;
   520    872     int i, c, n;
   521         -  unsigned int v;
          873  +  tRowcnt v;
   522    874     const char *z;
   523    875   
   524    876     assert( argc==3 );
   525    877     UNUSED_PARAMETER2(NotUsed, argc);
   526    878   
   527    879     if( argv==0 || argv[0]==0 || argv[2]==0 ){
   528    880       return 0;
................................................................................
   557    909   }
   558    910   
   559    911   /*
   560    912   ** If the Index.aSample variable is not NULL, delete the aSample[] array
   561    913   ** and its contents.
   562    914   */
   563    915   void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
   564         -#ifdef SQLITE_ENABLE_STAT2
          916  +#ifdef SQLITE_ENABLE_STAT3
   565    917     if( pIdx->aSample ){
   566    918       int j;
   567         -    for(j=0; j<SQLITE_INDEX_SAMPLES; j++){
          919  +    for(j=0; j<pIdx->nSample; j++){
   568    920         IndexSample *p = &pIdx->aSample[j];
   569    921         if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){
   570    922           sqlite3DbFree(db, p->u.z);
   571    923         }
   572    924       }
   573    925       sqlite3DbFree(db, pIdx->aSample);
          926  +  }
          927  +  if( db && db->pnBytesFreed==0 ){
          928  +    pIdx->nSample = 0;
          929  +    pIdx->aSample = 0;
   574    930     }
   575    931   #else
   576    932     UNUSED_PARAMETER(db);
   577    933     UNUSED_PARAMETER(pIdx);
   578    934   #endif
   579    935   }
   580    936   
          937  +#ifdef SQLITE_ENABLE_STAT3
          938  +/*
          939  +** Load content from the sqlite_stat3 table into the Index.aSample[]
          940  +** arrays of all indices.
          941  +*/
          942  +static int loadStat3(sqlite3 *db, const char *zDb){
          943  +  int rc;                       /* Result codes from subroutines */
          944  +  sqlite3_stmt *pStmt = 0;      /* An SQL statement being run */
          945  +  char *zSql;                   /* Text of the SQL statement */
          946  +  Index *pPrevIdx = 0;          /* Previous index in the loop */
          947  +  int idx = 0;                  /* slot in pIdx->aSample[] for next sample */
          948  +  int eType;                    /* Datatype of a sample */
          949  +  IndexSample *pSample;         /* A slot in pIdx->aSample[] */
          950  +
          951  +  if( !sqlite3FindTable(db, "sqlite_stat3", zDb) ){
          952  +    return SQLITE_OK;
          953  +  }
          954  +
          955  +  zSql = sqlite3MPrintf(db, 
          956  +      "SELECT idx,count(*) FROM %Q.sqlite_stat3"
          957  +      " GROUP BY idx", zDb);
          958  +  if( !zSql ){
          959  +    return SQLITE_NOMEM;
          960  +  }
          961  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          962  +  sqlite3DbFree(db, zSql);
          963  +  if( rc ) return rc;
          964  +
          965  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          966  +    char *zIndex;   /* Index name */
          967  +    Index *pIdx;    /* Pointer to the index object */
          968  +    int nSample;    /* Number of samples */
          969  +
          970  +    zIndex = (char *)sqlite3_column_text(pStmt, 0);
          971  +    if( zIndex==0 ) continue;
          972  +    nSample = sqlite3_column_int(pStmt, 1);
          973  +    pIdx = sqlite3FindIndex(db, zIndex, zDb);
          974  +    if( pIdx==0 ) continue;
          975  +    assert( pIdx->nSample==0 );
          976  +    pIdx->nSample = nSample;
          977  +    pIdx->aSample = sqlite3MallocZero( nSample*sizeof(IndexSample) );
          978  +    pIdx->avgEq = pIdx->aiRowEst[1];
          979  +    if( pIdx->aSample==0 ){
          980  +      db->mallocFailed = 1;
          981  +      sqlite3_finalize(pStmt);
          982  +      return SQLITE_NOMEM;
          983  +    }
          984  +  }
          985  +  rc = sqlite3_finalize(pStmt);
          986  +  if( rc ) return rc;
          987  +
          988  +  zSql = sqlite3MPrintf(db, 
          989  +      "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat3", zDb);
          990  +  if( !zSql ){
          991  +    return SQLITE_NOMEM;
          992  +  }
          993  +  rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          994  +  sqlite3DbFree(db, zSql);
          995  +  if( rc ) return rc;
          996  +
          997  +  while( sqlite3_step(pStmt)==SQLITE_ROW ){
          998  +    char *zIndex;   /* Index name */
          999  +    Index *pIdx;    /* Pointer to the index object */
         1000  +    int i;          /* Loop counter */
         1001  +    tRowcnt sumEq;  /* Sum of the nEq values */
         1002  +
         1003  +    zIndex = (char *)sqlite3_column_text(pStmt, 0);
         1004  +    if( zIndex==0 ) continue;
         1005  +    pIdx = sqlite3FindIndex(db, zIndex, zDb);
         1006  +    if( pIdx==0 ) continue;
         1007  +    if( pIdx==pPrevIdx ){
         1008  +      idx++;
         1009  +    }else{
         1010  +      pPrevIdx = pIdx;
         1011  +      idx = 0;
         1012  +    }
         1013  +    assert( idx<pIdx->nSample );
         1014  +    pSample = &pIdx->aSample[idx];
         1015  +    pSample->nEq = (tRowcnt)sqlite3_column_int64(pStmt, 1);
         1016  +    pSample->nLt = (tRowcnt)sqlite3_column_int64(pStmt, 2);
         1017  +    pSample->nDLt = (tRowcnt)sqlite3_column_int64(pStmt, 3);
         1018  +    if( idx==pIdx->nSample-1 ){
         1019  +      if( pSample->nDLt>0 ){
         1020  +        for(i=0, sumEq=0; i<=idx-1; i++) sumEq += pIdx->aSample[i].nEq;
         1021  +        pIdx->avgEq = (pSample->nLt - sumEq)/pSample->nDLt;
         1022  +      }
         1023  +      if( pIdx->avgEq<=0 ) pIdx->avgEq = 1;
         1024  +    }
         1025  +    eType = sqlite3_column_type(pStmt, 4);
         1026  +    pSample->eType = (u8)eType;
         1027  +    switch( eType ){
         1028  +      case SQLITE_INTEGER: {
         1029  +        pSample->u.i = sqlite3_column_int64(pStmt, 4);
         1030  +        break;
         1031  +      }
         1032  +      case SQLITE_FLOAT: {
         1033  +        pSample->u.r = sqlite3_column_double(pStmt, 4);
         1034  +        break;
         1035  +      }
         1036  +      case SQLITE_NULL: {
         1037  +        break;
         1038  +      }
         1039  +      default: assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB ); {
         1040  +        const char *z = (const char *)(
         1041  +              (eType==SQLITE_BLOB) ?
         1042  +              sqlite3_column_blob(pStmt, 4):
         1043  +              sqlite3_column_text(pStmt, 4)
         1044  +           );
         1045  +        int n = z ? sqlite3_column_bytes(pStmt, 4) : 0;
         1046  +        pSample->nByte = n;
         1047  +        if( n < 1){
         1048  +          pSample->u.z = 0;
         1049  +        }else{
         1050  +          pSample->u.z = sqlite3Malloc(n);
         1051  +          if( pSample->u.z==0 ){
         1052  +            db->mallocFailed = 1;
         1053  +            sqlite3_finalize(pStmt);
         1054  +            return SQLITE_NOMEM;
         1055  +          }
         1056  +          memcpy(pSample->u.z, z, n);
         1057  +        }
         1058  +      }
         1059  +    }
         1060  +  }
         1061  +  return sqlite3_finalize(pStmt);
         1062  +}
         1063  +#endif /* SQLITE_ENABLE_STAT3 */
         1064  +
   581   1065   /*
   582         -** Load the content of the sqlite_stat1 and sqlite_stat2 tables. The
         1066  +** Load the content of the sqlite_stat1 and sqlite_stat3 tables. The
   583   1067   ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[]
   584         -** arrays. The contents of sqlite_stat2 are used to populate the
         1068  +** arrays. The contents of sqlite_stat3 are used to populate the
   585   1069   ** Index.aSample[] arrays.
   586   1070   **
   587   1071   ** 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 
         1072  +** is returned. In this case, even if SQLITE_ENABLE_STAT3 was defined 
         1073  +** during compilation and the sqlite_stat3 table is present, no data is 
   590   1074   ** read from it.
   591   1075   **
   592         -** If SQLITE_ENABLE_STAT2 was defined during compilation and the 
   593         -** sqlite_stat2 table is not present in the database, SQLITE_ERROR is
         1076  +** If SQLITE_ENABLE_STAT3 was defined during compilation and the 
         1077  +** sqlite_stat3 table is not present in the database, SQLITE_ERROR is
   594   1078   ** returned. However, in this case, data is read from the sqlite_stat1
   595   1079   ** table (if it is present) before returning.
   596   1080   **
   597   1081   ** If an OOM error occurs, this function always sets db->mallocFailed.
   598   1082   ** This means if the caller does not care about other errors, the return
   599   1083   ** code may be ignored.
   600   1084   */
................................................................................
   608   1092     assert( db->aDb[iDb].pBt!=0 );
   609   1093   
   610   1094     /* Clear any prior statistics */
   611   1095     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
   612   1096     for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
   613   1097       Index *pIdx = sqliteHashData(i);
   614   1098       sqlite3DefaultRowEst(pIdx);
         1099  +#ifdef SQLITE_ENABLE_STAT3
   615   1100       sqlite3DeleteIndexSamples(db, pIdx);
   616   1101       pIdx->aSample = 0;
         1102  +#endif
   617   1103     }
   618   1104   
   619   1105     /* Check to make sure the sqlite_stat1 table exists */
   620   1106     sInfo.db = db;
   621   1107     sInfo.zDatabase = db->aDb[iDb].zName;
   622   1108     if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){
   623   1109       return SQLITE_ERROR;
   624   1110     }
   625   1111   
   626   1112     /* Load new statistics out of the sqlite_stat1 table */
   627   1113     zSql = sqlite3MPrintf(db, 
   628         -      "SELECT tbl, idx, stat FROM %Q.sqlite_stat1", sInfo.zDatabase);
         1114  +      "SELECT tbl,idx,stat FROM %Q.sqlite_stat1", sInfo.zDatabase);
   629   1115     if( zSql==0 ){
   630   1116       rc = SQLITE_NOMEM;
   631   1117     }else{
   632   1118       rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
   633   1119       sqlite3DbFree(db, zSql);
   634   1120     }
   635   1121   
   636   1122   
   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         -  }
         1123  +  /* Load the statistics from the sqlite_stat3 table. */
         1124  +#ifdef SQLITE_ENABLE_STAT3
   642   1125     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         -    }
         1126  +    rc = loadStat3(db, sInfo.zDatabase);
   709   1127     }
   710   1128   #endif
   711   1129   
   712   1130     if( rc==SQLITE_NOMEM ){
   713   1131       db->mallocFailed = 1;
   714   1132     }
   715   1133     return rc;
   716   1134   }
   717   1135   
   718   1136   
   719   1137   #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;
................................................................................
  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
................................................................................
  2635   2652     /* 
  2636   2653     ** Allocate the index structure. 
  2637   2654     */
  2638   2655     nName = sqlite3Strlen30(zName);
  2639   2656     nCol = pList->nExpr;
  2640   2657     pIndex = sqlite3DbMallocZero(db, 
  2641   2658         sizeof(Index) +              /* Index structure  */
         2659  +      sizeof(tRowcnt)*(nCol+1) +   /* Index.aiRowEst   */
  2642   2660         sizeof(int)*nCol +           /* Index.aiColumn   */
  2643         -      sizeof(int)*(nCol+1) +       /* Index.aiRowEst   */
  2644   2661         sizeof(char *)*nCol +        /* Index.azColl     */
  2645   2662         sizeof(u8)*nCol +            /* Index.aSortOrder */
  2646   2663         nName + 1 +                  /* Index.zName      */
  2647   2664         nExtra                       /* Collation sequence names */
  2648   2665     );
  2649   2666     if( db->mallocFailed ){
  2650   2667       goto exit_create_index;
  2651   2668     }
  2652         -  pIndex->azColl = (char**)(&pIndex[1]);
         2669  +  pIndex->aiRowEst = (tRowcnt*)(&pIndex[1]);
         2670  +  pIndex->azColl = (char**)(&pIndex->aiRowEst[nCol+1]);
  2653   2671     pIndex->aiColumn = (int *)(&pIndex->azColl[nCol]);
  2654         -  pIndex->aiRowEst = (unsigned *)(&pIndex->aiColumn[nCol]);
  2655         -  pIndex->aSortOrder = (u8 *)(&pIndex->aiRowEst[nCol+1]);
         2672  +  pIndex->aSortOrder = (u8 *)(&pIndex->aiColumn[nCol]);
  2656   2673     pIndex->zName = (char *)(&pIndex->aSortOrder[nCol]);
  2657   2674     zExtra = (char *)(&pIndex->zName[nName+1]);
  2658   2675     memcpy(pIndex->zName, zName, nName+1);
  2659   2676     pIndex->pTable = pTab;
  2660   2677     pIndex->nColumn = pList->nExpr;
  2661   2678     pIndex->onError = (u8)onError;
  2662   2679     pIndex->autoIndex = (u8)(pName==0);
................................................................................
  2925   2942   **           aiRowEst[N]>=1
  2926   2943   **
  2927   2944   ** Apart from that, we have little to go on besides intuition as to
  2928   2945   ** how aiRowEst[] should be initialized.  The numbers generated here
  2929   2946   ** are based on typical values found in actual indices.
  2930   2947   */
  2931   2948   void sqlite3DefaultRowEst(Index *pIdx){
  2932         -  unsigned *a = pIdx->aiRowEst;
         2949  +  tRowcnt *a = pIdx->aiRowEst;
  2933   2950     int i;
  2934         -  unsigned n;
         2951  +  tRowcnt n;
  2935   2952     assert( a!=0 );
  2936   2953     a[0] = pIdx->pTable->nRowEst;
  2937   2954     if( a[0]<10 ) a[0] = 10;
  2938   2955     n = 10;
  2939   2956     for(i=1; i<=pIdx->nColumn; i++){
  2940   2957       a[i] = n;
  2941   2958       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/os_win.c.

  2611   2611              h, zName, dwDesiredAccess, 
  2612   2612              h==INVALID_HANDLE_VALUE ? "failed" : "ok"));
  2613   2613   
  2614   2614     if( h==INVALID_HANDLE_VALUE ){
  2615   2615       pFile->lastErrno = GetLastError();
  2616   2616       winLogError(SQLITE_CANTOPEN, "winOpen", zUtf8Name);
  2617   2617       free(zConverted);
  2618         -    if( isReadWrite ){
         2618  +    if( isReadWrite && !isExclusive ){
  2619   2619         return winOpen(pVfs, zName, id, 
  2620   2620                ((flags|SQLITE_OPEN_READONLY)&~(SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE)), pOutFlags);
  2621   2621       }else{
  2622   2622         return SQLITE_CANTOPEN_BKPT;
  2623   2623       }
  2624   2624     }
  2625   2625   

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.

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

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

Changes to src/vdbemem.c.

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