/ Check-in [2daab6bd]
Login

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

Overview
Comment:Merge together the fork in the query-planner-tweaks branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | query-planner-tweaks
Files: files | file ages | folders
SHA1: 2daab6bd42b34b32de46db513437cc4d6ca17975
User & Date: drh 2011-08-06 02:03:10
Context
2011-08-06
19:48
The sqlite_stat2.cnt field is parsed if it is present. But it is not yet used. A large comment added to analyze.c to explain the format of the ANALYZE system tables. check-in: 6d1e2372 user: drh tags: query-planner-tweaks
02:03
Merge together the fork in the query-planner-tweaks branch. check-in: 2daab6bd user: drh tags: query-planner-tweaks
01:22
Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. check-in: 7e914aa9 user: drh tags: query-planner-tweaks
2011-08-05
01:09
Allow the sqlite_stat2 table to contain a number of samples other than the default of 10. check-in: b9d41c34 user: drh tags: query-planner-tweaks
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   586    586   ** If the Index.aSample variable is not NULL, delete the aSample[] array
   587    587   ** and its contents.
   588    588   */
   589    589   void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
   590    590   #ifdef SQLITE_ENABLE_STAT2
   591    591     if( pIdx->aSample ){
   592    592       int j;
   593         -    for(j=0; j<SQLITE_INDEX_SAMPLES; j++){
          593  +    for(j=0; j<pIdx->nSample; j++){
   594    594         IndexSample *p = &pIdx->aSample[j];
   595    595         if( p->eType==SQLITE_TEXT || p->eType==SQLITE_BLOB ){
   596    596           sqlite3DbFree(db, p->u.z);
   597    597         }
   598    598       }
   599    599       sqlite3DbFree(db, pIdx->aSample);
   600    600     }
................................................................................
   636    636     /* Clear any prior statistics */
   637    637     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
   638    638     for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
   639    639       Index *pIdx = sqliteHashData(i);
   640    640       sqlite3DefaultRowEst(pIdx);
   641    641       sqlite3DeleteIndexSamples(db, pIdx);
   642    642       pIdx->aSample = 0;
          643  +    pIdx->nSample = 0;
   643    644     }
   644    645   
   645    646     /* Check to make sure the sqlite_stat1 table exists */
   646    647     sInfo.db = db;
   647    648     sInfo.zDatabase = db->aDb[iDb].zName;
   648    649     if( sqlite3FindTable(db, "sqlite_stat1", sInfo.zDatabase)==0 ){
   649    650       return SQLITE_ERROR;
................................................................................
   665    666     if( rc==SQLITE_OK && !sqlite3FindTable(db, "sqlite_stat2", sInfo.zDatabase) ){
   666    667       rc = SQLITE_ERROR;
   667    668     }
   668    669     if( rc==SQLITE_OK ){
   669    670       sqlite3_stmt *pStmt = 0;
   670    671   
   671    672       zSql = sqlite3MPrintf(db, 
   672         -        "SELECT idx,sampleno,sample FROM %Q.sqlite_stat2", sInfo.zDatabase);
          673  +        "SELECT idx, sampleno, sample FROM %Q.sqlite_stat2"
          674  +        " ORDER BY rowid DESC", sInfo.zDatabase);
   673    675       if( !zSql ){
   674    676         rc = SQLITE_NOMEM;
   675    677       }else{
   676    678         rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
   677    679         sqlite3DbFree(db, zSql);
   678    680       }
   679    681   
   680    682       if( rc==SQLITE_OK ){
   681    683         while( sqlite3_step(pStmt)==SQLITE_ROW ){
   682    684           char *zIndex;   /* Index name */
   683    685           Index *pIdx;    /* Pointer to the index object */
          686  +        int iSample;
          687  +        int eType;
          688  +        IndexSample *pSample;
   684    689   
   685    690           zIndex = (char *)sqlite3_column_text(pStmt, 0);
   686         -        pIdx = zIndex ? sqlite3FindIndex(db, zIndex, sInfo.zDatabase) : 0;
   687         -        if( pIdx ){
   688         -          int iSample = sqlite3_column_int(pStmt, 1);
   689         -          if( iSample<SQLITE_INDEX_SAMPLES && iSample>=0 ){
   690         -            int eType = sqlite3_column_type(pStmt, 2);
   691         -
   692         -            if( pIdx->aSample==0 ){
   693         -              static const int sz = sizeof(IndexSample)*SQLITE_INDEX_SAMPLES;
   694         -              pIdx->aSample = (IndexSample *)sqlite3DbMallocRaw(0, sz);
   695         -              if( pIdx->aSample==0 ){
   696         -                db->mallocFailed = 1;
   697         -                break;
   698         -              }
   699         -	      memset(pIdx->aSample, 0, sz);
   700         -            }
   701         -
   702         -            assert( pIdx->aSample );
   703         -            {
   704         -              IndexSample *pSample = &pIdx->aSample[iSample];
   705         -              pSample->eType = (u8)eType;
   706         -              if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
   707         -                pSample->u.r = sqlite3_column_double(pStmt, 2);
   708         -              }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){
   709         -                const char *z = (const char *)(
   710         -                    (eType==SQLITE_BLOB) ?
   711         -                    sqlite3_column_blob(pStmt, 2):
   712         -                    sqlite3_column_text(pStmt, 2)
   713         -                );
   714         -                int n = sqlite3_column_bytes(pStmt, 2);
   715         -                if( n>24 ){
   716         -                  n = 24;
   717         -                }
   718         -                pSample->nByte = (u8)n;
   719         -                if( n < 1){
   720         -                  pSample->u.z = 0;
   721         -                }else{
   722         -                  pSample->u.z = sqlite3DbStrNDup(0, z, n);
   723         -                  if( pSample->u.z==0 ){
   724         -                    db->mallocFailed = 1;
   725         -                    break;
   726         -                  }
   727         -                }
   728         -              }
          691  +        if( zIndex==0 ) continue;
          692  +        pIdx = sqlite3FindIndex(db, zIndex, sInfo.zDatabase);
          693  +        if( pIdx==0 ) continue;
          694  +        iSample = sqlite3_column_int(pStmt, 1);
          695  +        if( iSample>=SQLITE_MAX_SAMPLES || iSample<0 ) continue;
          696  +        if( pIdx->nSample<=iSample ){
          697  +          IndexSample *pNew;
          698  +          int sz = sizeof(IndexSample)*(iSample+1);
          699  +          pNew = (IndexSample*)sqlite3Realloc(pIdx->aSample, sz);
          700  +          if( pNew==0 ){
          701  +            db->mallocFailed = 1;
          702  +            break;
          703  +          }
          704  +          pIdx->aSample = pNew;
          705  +          pIdx->nSample = iSample+1;
          706  +        }
          707  +        eType = sqlite3_column_type(pStmt, 2);
          708  +        pSample = &pIdx->aSample[iSample];
          709  +        pSample->eType = (u8)eType;
          710  +        if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
          711  +          pSample->u.r = sqlite3_column_double(pStmt, 2);
          712  +        }else if( eType==SQLITE_TEXT || eType==SQLITE_BLOB ){
          713  +          const char *z = (const char *)(
          714  +             (eType==SQLITE_BLOB) ?
          715  +              sqlite3_column_blob(pStmt, 2):
          716  +              sqlite3_column_text(pStmt, 2)
          717  +          );
          718  +          int n = sqlite3_column_bytes(pStmt, 2);
          719  +          if( n>24 ) n = 24;
          720  +          pSample->nByte = (u8)n;
          721  +          if( n < 1){
          722  +            pSample->u.z = 0;
          723  +          }else{
          724  +            pSample->u.z = sqlite3DbStrNDup(0, z, n);
          725  +            if( pSample->u.z==0 ){
          726  +              db->mallocFailed = 1;
          727  +              break;
   729    728               }
   730    729             }
   731    730           }
   732    731         }
   733    732         rc = sqlite3_finalize(pStmt);
   734    733       }
   735    734     }

Changes to src/sqliteInt.h.

    77     77   #endif
    78     78   
    79     79   /*
    80     80   ** The number of samples of an index that SQLite takes in order to 
    81     81   ** construct a histogram of the table content when running ANALYZE
    82     82   ** and with SQLITE_ENABLE_STAT2
    83     83   */
    84         -#define SQLITE_INDEX_SAMPLES 10
           84  +#ifndef SQLITE_INDEX_SAMPLES
           85  +# define SQLITE_INDEX_SAMPLES 10
           86  +#endif
           87  +#define SQLITE_MAX_SAMPLES 100
    85     88   
    86     89   /*
    87     90   ** The following macros are used to cast pointers to integers and
    88     91   ** integers to pointers.  The way you do this varies from one compiler
    89     92   ** to the next, so we have developed the following set of #if statements
    90     93   ** to generate appropriate macros for a wide range of compilers.
    91     94   **
................................................................................
  1479   1482     int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  1480   1483     unsigned *aiRowEst; /* Result of ANALYZE: Est. rows selected by each column */
  1481   1484     Table *pTable;   /* The SQL table being indexed */
  1482   1485     int tnum;        /* Page containing root of this index in database file */
  1483   1486     u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  1484   1487     u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  1485   1488     u8 bUnordered;   /* Use this index for == or IN queries only */
         1489  +  u8 nSample;      /* Number of slots in aSample[] */
  1486   1490     char *zColAff;   /* String defining the affinity of each column */
  1487   1491     Index *pNext;    /* The next index associated with the same table */
  1488   1492     Schema *pSchema; /* Schema containing this index */
  1489   1493     u8 *aSortOrder;  /* Array of size Index.nColumn. True==DESC, False==ASC */
  1490   1494     char **azColl;   /* Array of collation sequence names for index */
  1491   1495     IndexSample *aSample;    /* Array of SQLITE_INDEX_SAMPLES samples */
  1492   1496   };

Changes to src/where.c.

  2418   2418     */
  2419   2419     bestOrClauseIndex(pParse, pWC, pSrc, notReady, notValid, pOrderBy, pCost);
  2420   2420   }
  2421   2421   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  2422   2422   
  2423   2423   /*
  2424   2424   ** Argument pIdx is a pointer to an index structure that has an array of
  2425         -** SQLITE_INDEX_SAMPLES evenly spaced samples of the first indexed column
         2425  +** pIdx->nSample evenly spaced samples of the first indexed column
  2426   2426   ** stored in Index.aSample. These samples divide the domain of values stored
  2427         -** the index into (SQLITE_INDEX_SAMPLES+1) regions.
         2427  +** the index into (pIdx->nSample+1) regions.
  2428   2428   ** Region 0 contains all values less than the first sample value. Region
  2429   2429   ** 1 contains values between the first and second samples.  Region 2 contains
  2430         -** values between samples 2 and 3.  And so on.  Region SQLITE_INDEX_SAMPLES
         2430  +** values between samples 2 and 3.  And so on.  Region pIdx->nSample
  2431   2431   ** contains values larger than the last sample.
  2432   2432   **
  2433   2433   ** If the index contains many duplicates of a single value, then it is
  2434   2434   ** possible that two or more adjacent samples can hold the same value.
  2435   2435   ** When that is the case, the smallest possible region code is returned
  2436   2436   ** when roundUp is false and the largest possible region code is returned
  2437   2437   ** when roundUp is true.
  2438   2438   **
  2439   2439   ** If successful, this function determines which of the regions value 
  2440   2440   ** pVal lies in, sets *piRegion to the region index (a value between 0
  2441         -** and SQLITE_INDEX_SAMPLES+1, inclusive) and returns SQLITE_OK.
         2441  +** and S+1, inclusive) and returns SQLITE_OK.
  2442   2442   ** Or, if an OOM occurs while converting text values between encodings,
  2443   2443   ** SQLITE_NOMEM is returned and *piRegion is undefined.
  2444   2444   */
  2445   2445   #ifdef SQLITE_ENABLE_STAT2
  2446   2446   static int whereRangeRegion(
  2447   2447     Parse *pParse,              /* Database connection */
  2448   2448     Index *pIdx,                /* Index to consider domain of */
................................................................................
  2449   2449     sqlite3_value *pVal,        /* Value to consider */
  2450   2450     int roundUp,                /* Return largest valid region if true */
  2451   2451     int *piRegion               /* OUT: Region of domain in which value lies */
  2452   2452   ){
  2453   2453     assert( roundUp==0 || roundUp==1 );
  2454   2454     if( ALWAYS(pVal) ){
  2455   2455       IndexSample *aSample = pIdx->aSample;
         2456  +    int nSample = pIdx->nSample;
  2456   2457       int i = 0;
  2457   2458       int eType = sqlite3_value_type(pVal);
  2458   2459   
         2460  +    assert( nSample>0 );
  2459   2461       if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
  2460   2462         double r = sqlite3_value_double(pVal);
  2461         -      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
         2463  +      for(i=0; i<nSample; i++){
  2462   2464           if( aSample[i].eType==SQLITE_NULL ) continue;
  2463   2465           if( aSample[i].eType>=SQLITE_TEXT ) break;
  2464   2466           if( roundUp ){
  2465   2467             if( aSample[i].u.r>r ) break;
  2466   2468           }else{
  2467   2469             if( aSample[i].u.r>=r ) break;
  2468   2470           }
  2469   2471         }
  2470   2472       }else if( eType==SQLITE_NULL ){
  2471   2473         i = 0;
  2472   2474         if( roundUp ){
  2473         -        while( i<SQLITE_INDEX_SAMPLES && aSample[i].eType==SQLITE_NULL ) i++;
         2475  +        while( i<nSample && aSample[i].eType==SQLITE_NULL ) i++;
  2474   2476         }
  2475   2477       }else{ 
  2476   2478         sqlite3 *db = pParse->db;
  2477   2479         CollSeq *pColl;
  2478   2480         const u8 *z;
  2479   2481         int n;
  2480   2482   
................................................................................
  2496   2498           if( !z ){
  2497   2499             return SQLITE_NOMEM;
  2498   2500           }
  2499   2501           assert( z && pColl && pColl->xCmp );
  2500   2502         }
  2501   2503         n = sqlite3ValueBytes(pVal, pColl->enc);
  2502   2504   
  2503         -      for(i=0; i<SQLITE_INDEX_SAMPLES; i++){
         2505  +      for(i=0; i<nSample; i++){
  2504   2506           int c;
  2505   2507           int eSampletype = aSample[i].eType;
  2506   2508           if( eSampletype==SQLITE_NULL || eSampletype<eType ) continue;
  2507   2509           if( (eSampletype!=eType) ) break;
  2508   2510   #ifndef SQLITE_OMIT_UTF16
  2509   2511           if( pColl->enc!=SQLITE_UTF8 ){
  2510   2512             int nSample;
................................................................................
  2522   2524           {
  2523   2525             c = pColl->xCmp(pColl->pUser, aSample[i].nByte, aSample[i].u.z, n, z);
  2524   2526           }
  2525   2527           if( c-roundUp>=0 ) break;
  2526   2528         }
  2527   2529       }
  2528   2530   
  2529         -    assert( i>=0 && i<=SQLITE_INDEX_SAMPLES );
         2531  +    assert( i>=0 && i<=pIdx->nSample );
  2530   2532       *piRegion = i;
  2531   2533     }
  2532   2534     return SQLITE_OK;
  2533   2535   }
  2534   2536   #endif   /* #ifdef SQLITE_ENABLE_STAT2 */
  2535   2537   
  2536   2538   /*
................................................................................
  2622   2624   #ifdef SQLITE_ENABLE_STAT2
  2623   2625   
  2624   2626     if( nEq==0 && p->aSample ){
  2625   2627       sqlite3_value *pLowerVal = 0;
  2626   2628       sqlite3_value *pUpperVal = 0;
  2627   2629       int iEst;
  2628   2630       int iLower = 0;
  2629         -    int iUpper = SQLITE_INDEX_SAMPLES;
         2631  +    int nSample = p->nSample;
         2632  +    int iUpper = p->nSample;
  2630   2633       int roundUpUpper = 0;
  2631   2634       int roundUpLower = 0;
  2632   2635       u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2633   2636   
  2634   2637       if( pLower ){
  2635   2638         Expr *pExpr = pLower->pExpr->pRight;
  2636   2639         rc = valueFromExpr(pParse, pExpr, aff, &pLowerVal);
................................................................................
  2649   2652         sqlite3ValueFree(pUpperVal);
  2650   2653         goto range_est_fallback;
  2651   2654       }else if( pLowerVal==0 ){
  2652   2655         rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
  2653   2656         if( pLower ) iLower = iUpper/2;
  2654   2657       }else if( pUpperVal==0 ){
  2655   2658         rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
  2656         -      if( pUpper ) iUpper = (iLower + SQLITE_INDEX_SAMPLES + 1)/2;
         2659  +      if( pUpper ) iUpper = (iLower + p->nSample + 1)/2;
  2657   2660       }else{
  2658   2661         rc = whereRangeRegion(pParse, p, pUpperVal, roundUpUpper, &iUpper);
  2659   2662         if( rc==SQLITE_OK ){
  2660   2663           rc = whereRangeRegion(pParse, p, pLowerVal, roundUpLower, &iLower);
  2661   2664         }
  2662   2665       }
  2663   2666       WHERETRACE(("range scan regions: %d..%d\n", iLower, iUpper));
  2664   2667   
  2665   2668       iEst = iUpper - iLower;
  2666         -    testcase( iEst==SQLITE_INDEX_SAMPLES );
  2667         -    assert( iEst<=SQLITE_INDEX_SAMPLES );
         2669  +    testcase( iEst==nSample );
         2670  +    assert( iEst<=nSample );
         2671  +    assert( nSample>0 );
  2668   2672       if( iEst<1 ){
  2669         -      *piEst = 50/SQLITE_INDEX_SAMPLES;
         2673  +      *piEst = 50/nSample;
  2670   2674       }else{
  2671         -      *piEst = (iEst*100)/SQLITE_INDEX_SAMPLES;
         2675  +      *piEst = (iEst*100)/nSample;
  2672   2676       }
  2673   2677       sqlite3ValueFree(pLowerVal);
  2674   2678       sqlite3ValueFree(pUpperVal);
  2675   2679       return rc;
  2676   2680     }
  2677   2681   range_est_fallback:
  2678   2682   #else
................................................................................
  2714   2718     sqlite3_value *pRhs = 0;  /* VALUE on right-hand side of pTerm */
  2715   2719     int iLower, iUpper;       /* Range of histogram regions containing pRhs */
  2716   2720     u8 aff;                   /* Column affinity */
  2717   2721     int rc;                   /* Subfunction return code */
  2718   2722     double nRowEst;           /* New estimate of the number of rows */
  2719   2723   
  2720   2724     assert( p->aSample!=0 );
         2725  +  assert( p->nSample>0 );
  2721   2726     aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2722   2727     if( pExpr ){
  2723   2728       rc = valueFromExpr(pParse, pExpr, aff, &pRhs);
  2724   2729       if( rc ) goto whereEqualScanEst_cancel;
  2725   2730     }else{
  2726   2731       pRhs = sqlite3ValueNew(pParse->db);
  2727   2732     }
................................................................................
  2728   2733     if( pRhs==0 ) return SQLITE_NOTFOUND;
  2729   2734     rc = whereRangeRegion(pParse, p, pRhs, 0, &iLower);
  2730   2735     if( rc ) goto whereEqualScanEst_cancel;
  2731   2736     rc = whereRangeRegion(pParse, p, pRhs, 1, &iUpper);
  2732   2737     if( rc ) goto whereEqualScanEst_cancel;
  2733   2738     WHERETRACE(("equality scan regions: %d..%d\n", iLower, iUpper));
  2734   2739     if( iLower>=iUpper ){
  2735         -    nRowEst = p->aiRowEst[0]/(SQLITE_INDEX_SAMPLES*2);
         2740  +    nRowEst = p->aiRowEst[0]/(p->nSample*3);
  2736   2741       if( nRowEst<*pnRow ) *pnRow = nRowEst;
  2737   2742     }else{
  2738         -    nRowEst = (iUpper-iLower)*p->aiRowEst[0]/SQLITE_INDEX_SAMPLES;
         2743  +    nRowEst = (iUpper-iLower)*p->aiRowEst[0]/p->nSample;
  2739   2744       *pnRow = nRowEst;
  2740   2745     }
  2741   2746   
  2742   2747   whereEqualScanEst_cancel:
  2743   2748     sqlite3ValueFree(pRhs);
  2744   2749     return rc;
  2745   2750   }
................................................................................
  2772   2777     int iLower, iUpper;       /* Range of histogram regions containing pRhs */
  2773   2778     u8 aff;                   /* Column affinity */
  2774   2779     int rc = SQLITE_OK;       /* Subfunction return code */
  2775   2780     double nRowEst;           /* New estimate of the number of rows */
  2776   2781     int nSpan = 0;            /* Number of histogram regions spanned */
  2777   2782     int nSingle = 0;          /* Histogram regions hit by a single value */
  2778   2783     int nNotFound = 0;        /* Count of values that are not constants */
  2779         -  int i;                               /* Loop counter */
  2780         -  u8 aSpan[SQLITE_INDEX_SAMPLES+1];    /* Histogram regions that are spanned */
  2781         -  u8 aSingle[SQLITE_INDEX_SAMPLES+1];  /* Histogram regions hit once */
         2784  +  int i;                             /* Loop counter */
         2785  +  int nSample = p->nSample;          /* Number of samples */
         2786  +  u8 aSpan[SQLITE_MAX_SAMPLES+1];    /* Histogram regions that are spanned */
         2787  +  u8 aSingle[SQLITE_MAX_SAMPLES+1];  /* Histogram regions hit once */
  2782   2788   
  2783   2789     assert( p->aSample!=0 );
         2790  +  assert( nSample>0 );
  2784   2791     aff = p->pTable->aCol[p->aiColumn[0]].affinity;
  2785         -  memset(aSpan, 0, sizeof(aSpan));
  2786         -  memset(aSingle, 0, sizeof(aSingle));
         2792  +  memset(aSpan, 0, nSample+1);
         2793  +  memset(aSingle, 0, nSample+1);
  2787   2794     for(i=0; i<pList->nExpr; i++){
  2788   2795       sqlite3ValueFree(pVal);
  2789   2796       rc = valueFromExpr(pParse, pList->a[i].pExpr, aff, &pVal);
  2790   2797       if( rc ) break;
  2791   2798       if( pVal==0 || sqlite3_value_type(pVal)==SQLITE_NULL ){
  2792   2799         nNotFound++;
  2793   2800         continue;
................................................................................
  2795   2802       rc = whereRangeRegion(pParse, p, pVal, 0, &iLower);
  2796   2803       if( rc ) break;
  2797   2804       rc = whereRangeRegion(pParse, p, pVal, 1, &iUpper);
  2798   2805       if( rc ) break;
  2799   2806       if( iLower>=iUpper ){
  2800   2807         aSingle[iLower] = 1;
  2801   2808       }else{
  2802         -      assert( iLower>=0 && iUpper<=SQLITE_INDEX_SAMPLES );
         2809  +      assert( iLower>=0 && iUpper<=nSample );
  2803   2810         while( iLower<iUpper ) aSpan[iLower++] = 1;
  2804   2811       }
  2805   2812     }
  2806   2813     if( rc==SQLITE_OK ){
  2807         -    for(i=nSpan=0; i<=SQLITE_INDEX_SAMPLES; i++){
         2814  +    for(i=nSpan=0; i<=nSample; i++){
  2808   2815         if( aSpan[i] ){
  2809   2816           nSpan++;
  2810   2817         }else if( aSingle[i] ){
  2811   2818           nSingle++;
  2812   2819         }
  2813   2820       }
  2814         -    nRowEst = (nSpan*2+nSingle)*p->aiRowEst[0]/(2*SQLITE_INDEX_SAMPLES)
         2821  +    nRowEst = (nSpan*3+nSingle)*p->aiRowEst[0]/(3*nSample)
  2815   2822                  + nNotFound*p->aiRowEst[1];
  2816   2823       if( nRowEst > p->aiRowEst[0] ) nRowEst = p->aiRowEst[0];
  2817   2824       *pnRow = nRowEst;
  2818   2825       WHERETRACE(("IN row estimate: nSpan=%d, nSingle=%d, nNotFound=%d, est=%g\n",
  2819   2826                    nSpan, nSingle, nNotFound, nRowEst));
  2820   2827     }
  2821   2828     sqlite3ValueFree(pVal);

Changes to test/analyze5.test.

   124    124      47  {z>=-100 AND z<=0.0}  t1z  400
   125    125      48  {z>=-100 AND z<0.0}   t1z   50
   126    126      49  {z>=-100 AND z<=1.0}  t1z  700
   127    127      50  {z>=-100 AND z<2.0}   t1z  700
   128    128      51  {z>=-100 AND z<=2.0}  t1z  900
   129    129      52  {z>=-100 AND z<3.0}   t1z  900
   130    130     
   131         -  101  {z=-1}                t1z   50
          131  +  101  {z=-1}                t1z   33
   132    132     102  {z=0}                 t1z  400
   133    133     103  {z=1}                 t1z  300
   134    134     104  {z=2}                 t1z  200
   135    135     105  {z=3}                 t1z  100
   136         -  106  {z=4}                 t1z   50
   137         -  107  {z=-10.0}             t1z   50
          136  +  106  {z=4}                 t1z   33
          137  +  107  {z=-10.0}             t1z   33
   138    138     108  {z=0.0}               t1z  400
   139    139     109  {z=1.0}               t1z  300
   140    140     110  {z=2.0}               t1z  200
   141    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
          142  +  112  {z=4.0}               t1z   33
          143  +  113  {z=1.5}               t1z   33
          144  +  114  {z=2.5}               t1z   33
   145    145     
   146         -  201  {z IN (-1)}           t1z   50
          146  +  201  {z IN (-1)}           t1z   33
   147    147     202  {z IN (0)}            t1z  400
   148    148     203  {z IN (1)}            t1z  300
   149    149     204  {z IN (2)}            t1z  200
   150    150     205  {z IN (3)}            t1z  100
   151         -  206  {z IN (4)}            t1z   50
   152         -  207  {z IN (0.5)}          t1z   50
          151  +  206  {z IN (4)}            t1z   33
          152  +  207  {z IN (0.5)}          t1z   33
   153    153     208  {z IN (0,1)}          t1z  700
   154    154     209  {z IN (0,1,2)}        t1z  900
   155    155     210  {z IN (0,1,2,3)}      {}   100
   156    156     211  {z IN (0,1,2,3,4,5)}  {}   100
   157    157     212  {z IN (1,2)}          t1z  500
   158    158     213  {z IN (2,3)}          t1z  300
   159    159     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
          160  +  215  {z IN (-1,3)}         t1z  133
          161  +  216  {z=-1 OR z=3}         t1z  133
   162    162   
   163    163     300  {y=0}                 {}   100
   164         -  301  {y=1}                 t1y   50
   165         -  302  {y=0.1}               t1y   50
          164  +  301  {y=1}                 t1y   33
          165  +  302  {y=0.1}               t1y   33
   166    166   
   167    167     400  {x IS NULL}           t1x  400
   168    168   
   169    169   } {
   170    170     # Verify that the expected index is used with the expected row count
   171    171     do_test analyze5-1.${testid}a {
   172    172       set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3]
................................................................................
   201    201      ANALYZE;
   202    202   }
   203    203   
   204    204   # Verify that range queries generate the correct row count estimates
   205    205   #
   206    206   foreach {testid where index rows} {
   207    207     500  {x IS NULL AND u='charlie'}         t1u  20
   208         -  501  {x=1 AND u='charlie'}               t1x   5
          208  +  501  {x=1 AND u='charlie'}               t1x   3
   209    209     502  {x IS NULL}                          {} 100
   210         -  503  {x=1}                               t1x  50
          210  +  503  {x=1}                               t1x  33
   211    211     504  {x IS NOT NULL}                     t1x  25
   212    212     505  {+x IS NOT NULL}                     {} 500
   213    213     506  {upper(x) IS NOT NULL}               {} 500
   214    214   
   215    215   } {
   216    216     # Verify that the expected index is used with the expected row count
   217    217     do_test analyze5-1.${testid}a {

Changes to test/analyze7.test.

    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    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)}}
          102  +  set x [execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}]
          103  +  regsub -all {[bcd]+} $x {x} x
          104  +  set x
          105  +} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x (x=?) (~2 rows)}}
   104    106   do_test analyze7-3.5 {
   105    107     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
   106    108   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
   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