/ Check-in [89b2f708]
Login

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

Overview
Comment:Further testing and bug fixing for sqlite_stat3. Added the Index.avgEq field to index statistics. Fixed several problems in the query planner associated with stat3.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat3-enhancement
Files: files | file ages | folders
SHA1: 89b2f70884cad0abdf4c66cb64ecddb2820ded74
User & Date: drh 2011-08-13 19:35:19
Context
2011-08-15
12:02
Fix a couple of typos in comments in analyze.c. check-in: ae31dc67 user: dan tags: stat3-enhancement
2011-08-13
19:35
Further testing and bug fixing for sqlite_stat3. Added the Index.avgEq field to index statistics. Fixed several problems in the query planner associated with stat3. check-in: 89b2f708 user: drh tags: stat3-enhancement
15:25
Add the sqlite_stat3.nDLT field. Use an linear congruence PRNG to choose which samples to select from among those with the same nEq field. check-in: 1dcd2428 user: drh tags: stat3-enhancement
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   953    953       nSample = sqlite3_column_int(pStmt, 1);
   954    954       if( nSample>255 ) continue;
   955    955       pIdx = sqlite3FindIndex(db, zIndex, zDb);
   956    956       if( pIdx==0 ) continue;
   957    957       assert( pIdx->nSample==0 );
   958    958       pIdx->nSample = (u8)nSample;
   959    959       pIdx->aSample = sqlite3MallocZero( nSample*sizeof(IndexSample) );
          960  +    pIdx->avgEq = pIdx->aiRowEst[1];
   960    961       if( pIdx->aSample==0 ){
   961    962         db->mallocFailed = 1;
   962    963         sqlite3_finalize(pStmt);
   963    964         return SQLITE_NOMEM;
   964    965       }
   965    966     }
   966    967     sqlite3_finalize(pStmt);
   967    968   
   968    969     zSql = sqlite3MPrintf(db, 
   969         -      "SELECT idx,nlt,neq,sample FROM %Q.sqlite_stat3", zDb);
          970  +      "SELECT idx,neq,nlt,ndlt,sample FROM %Q.sqlite_stat3", zDb);
   970    971     if( !zSql ){
   971    972       return SQLITE_NOMEM;
   972    973     }
   973    974     rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
   974    975     sqlite3DbFree(db, zSql);
   975    976     if( rc ) return rc;
   976    977   
   977    978     while( sqlite3_step(pStmt)==SQLITE_ROW ){
   978    979       char *zIndex;   /* Index name */
   979    980       Index *pIdx;    /* Pointer to the index object */
          981  +    int i;          /* Loop counter */
          982  +    tRowcnt sumEq;  /* Sum of the nEq values */
   980    983   
   981    984       zIndex = (char *)sqlite3_column_text(pStmt, 0);
   982    985       if( zIndex==0 ) continue;
   983    986       pIdx = sqlite3FindIndex(db, zIndex, zDb);
   984    987       if( pIdx==0 ) continue;
   985    988       if( pIdx==pPrevIdx ){
   986    989         idx++;
   987    990       }else{
   988    991         pPrevIdx = pIdx;
   989    992         idx = 0;
   990    993       }
   991    994       assert( idx<pIdx->nSample );
   992    995       pSample = &pIdx->aSample[idx];
   993         -    pSample->nLt = (tRowcnt)sqlite3_column_int64(pStmt, 1);
   994         -    pSample->nEq = (tRowcnt)sqlite3_column_int64(pStmt, 2);
   995         -    eType = sqlite3_column_type(pStmt, 3);
          996  +    pSample->nEq = (tRowcnt)sqlite3_column_int64(pStmt, 1);
          997  +    pSample->nLt = (tRowcnt)sqlite3_column_int64(pStmt, 2);
          998  +    pSample->nDLt = (tRowcnt)sqlite3_column_int64(pStmt, 3);
          999  +    if( idx==pIdx->nSample-1 ){
         1000  +      if( pSample->nDLt>0 ){
         1001  +        for(i=0, sumEq=0; i<=idx-1; i++) sumEq += pIdx->aSample[i].nEq;
         1002  +        pIdx->avgEq = (pSample->nLt - sumEq)/pSample->nDLt;
         1003  +      }
         1004  +      if( pIdx->avgEq<=0 ) pIdx->avgEq = 1;
         1005  +    }
         1006  +    eType = sqlite3_column_type(pStmt, 4);
   996   1007       pSample->eType = (u8)eType;
   997   1008       switch( eType ){
   998   1009         case SQLITE_INTEGER: {
   999         -        pSample->u.i = sqlite3_column_int64(pStmt, 3);
         1010  +        pSample->u.i = sqlite3_column_int64(pStmt, 4);
  1000   1011           break;
  1001   1012         }
  1002   1013         case SQLITE_FLOAT: {
  1003         -        pSample->u.r = sqlite3_column_double(pStmt, 3);
         1014  +        pSample->u.r = sqlite3_column_double(pStmt, 4);
  1004   1015           break;
  1005   1016         }
  1006   1017         case SQLITE_NULL: {
  1007   1018           break;
  1008   1019         }
  1009   1020         default: assert( eType==SQLITE_TEXT || eType==SQLITE_BLOB ); {
  1010   1021           const char *z = (const char *)(
  1011   1022                 (eType==SQLITE_BLOB) ?
  1012         -              sqlite3_column_blob(pStmt, 3):
  1013         -              sqlite3_column_text(pStmt, 3)
         1023  +              sqlite3_column_blob(pStmt, 4):
         1024  +              sqlite3_column_text(pStmt, 4)
  1014   1025              );
  1015         -        int n = sqlite3_column_bytes(pStmt, 2);
         1026  +        int n = sqlite3_column_bytes(pStmt, 4);
  1016   1027           if( n>0xffff ) n = 0xffff;
  1017   1028           pSample->nByte = (u16)n;
  1018   1029           if( n < 1){
  1019   1030             pSample->u.z = 0;
  1020   1031           }else{
  1021   1032             pSample->u.z = sqlite3Malloc(n);
  1022   1033             if( pSample->u.z==0 ){

Changes to src/build.c.

  2058   2058     ** dropped. Triggers are handled seperately because a trigger can be
  2059   2059     ** created in the temp database that refers to a table in another
  2060   2060     ** database.
  2061   2061     */
  2062   2062     sqlite3NestedParse(pParse, 
  2063   2063         "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
  2064   2064         pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
  2065         -  sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2066   2065     if( !isView && !IsVirtual(pTab) ){
  2067   2066       destroyTable(pParse, pTab);
  2068   2067     }
  2069   2068   
  2070   2069     /* Remove the table entry from SQLite's internal schema and modify
  2071   2070     ** the schema cookie.
  2072   2071     */
................................................................................
  2170   2169     ** on disk.
  2171   2170     */
  2172   2171     v = sqlite3GetVdbe(pParse);
  2173   2172     if( v ){
  2174   2173       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2175   2174       sqlite3FkDropTable(pParse, pName, pTab);
  2176   2175       sqlite3CodeDropTable(pParse, pTab, iDb, isView);
         2176  +    sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2177   2177     }
  2178   2178   
  2179   2179   exit_drop_table:
  2180   2180     sqlite3SrcListDelete(db, pName);
  2181   2181   }
  2182   2182   
  2183   2183   /*

Changes to src/sqliteInt.h.

  1498   1498     u8 nSample;      /* Number of elements in aSample[] */
  1499   1499     char *zColAff;   /* String defining the affinity of each column */
  1500   1500     Index *pNext;    /* The next index associated with the same table */
  1501   1501     Schema *pSchema; /* Schema containing this index */
  1502   1502     u8 *aSortOrder;  /* Array of size Index.nColumn. True==DESC, False==ASC */
  1503   1503     char **azColl;   /* Array of collation sequence names for index */
  1504   1504   #ifdef SQLITE_ENABLE_STAT3
         1505  +  tRowcnt avgEq;           /* Average nEq value for key values not in aSample */
  1505   1506     IndexSample *aSample;    /* Samples of the left-most key */
  1506   1507   #endif
  1507   1508   };
  1508   1509   
  1509   1510   /*
  1510   1511   ** Each sample stored in the sqlite_stat2 table is represented in memory 
  1511   1512   ** using a structure of this type.
................................................................................
  1516   1517       double r;       /* Value if eType is SQLITE_FLOAT */
  1517   1518       i64 i;          /* Value if eType is SQLITE_INTEGER */
  1518   1519     } u;
  1519   1520     u8 eType;         /* SQLITE_NULL, SQLITE_INTEGER ... etc. */
  1520   1521     u16 nByte;        /* Size in byte of text or blob. */
  1521   1522     tRowcnt nEq;      /* Est. number of rows where the key equals this sample */
  1522   1523     tRowcnt nLt;      /* Est. number of rows where key is less than this sample */
         1524  +  tRowcnt nDLt;     /* Est. number of distinct keys less than this sample */
  1523   1525   };
  1524   1526   
  1525   1527   /*
  1526   1528   ** Each token coming out of the lexer is an instance of
  1527   1529   ** this structure.  Tokens are also used as part of an expression.
  1528   1530   **
  1529   1531   ** Note if Token.z==0 then Token.dyn and Token.n are undefined and

Changes to src/where.c.

  2437   2437     int roundUp,                /* Round up if true.  Round down if false */
  2438   2438     tRowcnt *aStat              /* OUT: stats written here */
  2439   2439   ){
  2440   2440     tRowcnt n;
  2441   2441     IndexSample *aSample;
  2442   2442     int i, eType;
  2443   2443     int isEq = 0;
         2444  +  i64 v;
         2445  +  double r, rS;
  2444   2446   
  2445   2447     assert( roundUp==0 || roundUp==1 );
  2446   2448     if( pVal==0 ) return SQLITE_ERROR;
  2447   2449     n = pIdx->aiRowEst[0];
  2448   2450     aSample = pIdx->aSample;
  2449   2451     i = 0;
  2450   2452     eType = sqlite3_value_type(pVal);
  2451   2453   
  2452   2454     if( eType==SQLITE_INTEGER ){
  2453         -    i64 v = sqlite3_value_int64(pVal);
         2455  +    v = sqlite3_value_int64(pVal);
         2456  +    r = (i64)v;
  2454   2457       for(i=0; i<pIdx->nSample; i++){
  2455   2458         if( aSample[i].eType==SQLITE_NULL ) continue;
  2456   2459         if( aSample[i].eType>=SQLITE_TEXT ) break;
  2457         -      if( aSample[i].u.i>=v ){
  2458         -        isEq = aSample[i].u.i==v;
  2459         -        break;
         2460  +      if( aSample[i].eType==SQLITE_INTEGER ){
         2461  +        if( aSample[i].u.i>=v ){
         2462  +          isEq = aSample[i].u.i==v;
         2463  +          break;
         2464  +        }
         2465  +      }else{
         2466  +        assert( aSample[i].eType==SQLITE_FLOAT );
         2467  +        if( aSample[i].u.r>=r ){
         2468  +          isEq = aSample[i].u.r==r;
         2469  +          break;
         2470  +        }
  2460   2471         }
  2461   2472       }
  2462   2473     }else if( eType==SQLITE_FLOAT ){
  2463         -    double r = sqlite3_value_double(pVal);
         2474  +    r = sqlite3_value_double(pVal);
  2464   2475       for(i=0; i<pIdx->nSample; i++){
  2465   2476         if( aSample[i].eType==SQLITE_NULL ) continue;
  2466   2477         if( aSample[i].eType>=SQLITE_TEXT ) break;
  2467         -      if( aSample[i].u.r>=r ){
  2468         -        isEq = aSample[i].u.r==r;
         2478  +      if( aSample[i].eType==SQLITE_FLOAT ){
         2479  +        rS = aSample[i].u.r;
         2480  +      }else{
         2481  +        rS = aSample[i].u.i;
         2482  +      }
         2483  +      if( rS>=r ){
         2484  +        isEq = rS==r;
  2469   2485           break;
  2470   2486         }
  2471   2487       }
  2472   2488     }else if( eType==SQLITE_NULL ){
  2473   2489       i = 0;
  2474   2490       if( pIdx->nSample>=1 && aSample[0].eType==SQLITE_NULL ) isEq = 1;
  2475   2491     }else{
................................................................................
  2542   2558       aStat[0] = aSample[i].nLt;
  2543   2559       aStat[1] = aSample[i].nEq;
  2544   2560     }else{
  2545   2561       tRowcnt iLower, iUpper, iGap;
  2546   2562       if( i==0 ){
  2547   2563         iLower = 0;
  2548   2564         iUpper = aSample[0].nLt;
  2549         -    }else if( i>=pIdx->nSample ){
  2550         -      iUpper = n;
  2551         -      iLower = aSample[i].nEq + aSample[i].nLt;
  2552   2565       }else{
         2566  +      iUpper = i>=pIdx->nSample ? n : aSample[i].nLt;
  2553   2567         iLower = aSample[i-1].nEq + aSample[i-1].nLt;
  2554         -      iUpper = aSample[i].nLt;
  2555   2568       }
  2556         -    aStat[1] = pIdx->aiRowEst[1];
         2569  +    aStat[1] = pIdx->avgEq;
  2557   2570       if( iLower>=iUpper ){
  2558   2571         iGap = 0;
  2559   2572       }else{
  2560   2573         iGap = iUpper - iLower;
  2561   2574         if( iGap>=aStat[1]/2 ) iGap -= aStat[1]/2;
  2562   2575       }
  2563   2576       if( roundUp ){
................................................................................
  2647   2660   */
  2648   2661   static int whereRangeScanEst(
  2649   2662     Parse *pParse,       /* Parsing & code generating context */
  2650   2663     Index *p,            /* The index containing the range-compared column; "x" */
  2651   2664     int nEq,             /* index into p->aCol[] of the range-compared column */
  2652   2665     WhereTerm *pLower,   /* Lower bound on the range. ex: "x>123" Might be NULL */
  2653   2666     WhereTerm *pUpper,   /* Upper bound on the range. ex: "x<455" Might be NULL */
  2654         -  tRowcnt *pRangeDiv   /* OUT: Reduce search space by this divisor */
         2667  +  double *pRangeDiv   /* OUT: Reduce search space by this divisor */
  2655   2668   ){
  2656   2669     int rc = SQLITE_OK;
  2657   2670   
  2658   2671   #ifdef SQLITE_ENABLE_STAT3
  2659   2672   
  2660   2673     if( nEq==0 && p->nSample ){
  2661   2674       sqlite3_value *pRangeVal;
................................................................................
  2680   2693         Expr *pExpr = pUpper->pExpr->pRight;
  2681   2694         rc = valueFromExpr(pParse, pExpr, aff, &pRangeVal);
  2682   2695         assert( pUpper->eOperator==WO_LT || pUpper->eOperator==WO_LE );
  2683   2696         if( rc==SQLITE_OK
  2684   2697          && whereKeyStats(pParse, p, pRangeVal, 1, a)==SQLITE_OK
  2685   2698         ){
  2686   2699           iUpper = a[0];
  2687         -        if( pLower->eOperator==WO_LE ) iUpper += a[1];
         2700  +        if( pUpper->eOperator==WO_LE ) iUpper += a[1];
  2688   2701         }
  2689   2702         sqlite3ValueFree(pRangeVal);
  2690   2703       }
  2691   2704       if( rc==SQLITE_OK ){
  2692   2705         if( iUpper<=iLower ){
  2693         -        *pRangeDiv = p->aiRowEst[0];
         2706  +        *pRangeDiv = (double)p->aiRowEst[0];
  2694   2707         }else{
  2695         -        *pRangeDiv = p->aiRowEst[0]/(iUpper - iLower);
         2708  +        *pRangeDiv = (double)p->aiRowEst[0]/(double)(iUpper - iLower);
  2696   2709         }
  2697         -      WHERETRACE(("range scan regions: %u..%u  div=%u\n",
  2698         -                  (u32)iLower, (u32)iUpper, (u32)*pRangeDiv));
         2710  +      WHERETRACE(("range scan regions: %u..%u  div=%g\n",
         2711  +                  (u32)iLower, (u32)iUpper, *pRangeDiv));
  2699   2712         return SQLITE_OK;
  2700   2713       }
  2701   2714     }
  2702   2715   #else
  2703   2716     UNUSED_PARAMETER(pParse);
  2704   2717     UNUSED_PARAMETER(p);
  2705   2718     UNUSED_PARAMETER(nEq);
  2706   2719   #endif
  2707   2720     assert( pLower || pUpper );
  2708         -  *pRangeDiv = 1;
  2709         -  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= 4;
  2710         -  if( pUpper ) *pRangeDiv *= 4;
         2721  +  *pRangeDiv = (double)1;
         2722  +  if( pLower && (pLower->wtFlags & TERM_VNULL)==0 ) *pRangeDiv *= (double)4;
         2723  +  if( pUpper ) *pRangeDiv *= (double)4;
  2711   2724     return rc;
  2712   2725   }
  2713   2726   
  2714   2727   #ifdef SQLITE_ENABLE_STAT3
  2715   2728   /*
  2716   2729   ** Estimate the number of rows that will be returned based on
  2717   2730   ** an equality constraint x=VALUE and where that VALUE occurs in
................................................................................
  2972   2985       **
  2973   2986       **             SELECT a, b    FROM tbl WHERE a = 1;
  2974   2987       **             SELECT a, b, c FROM tbl WHERE a = 1;
  2975   2988       */
  2976   2989       int nEq;                      /* Number of == or IN terms matching index */
  2977   2990       int bInEst = 0;               /* True if "x IN (SELECT...)" seen */
  2978   2991       int nInMul = 1;               /* Number of distinct equalities to lookup */
  2979         -    tRowcnt rangeDiv = 1;         /* Estimated reduction in search space */
         2992  +    double rangeDiv = (double)1;  /* Estimated reduction in search space */
  2980   2993       int nBound = 0;               /* Number of range constraints seen */
  2981   2994       int bSort = !!pOrderBy;       /* True if external sort required */
  2982   2995       int bDist = !!pDistinct;      /* True if index cannot help with DISTINCT */
  2983   2996       int bLookup = 0;              /* True if not a covering index */
  2984   2997       WhereTerm *pTerm;             /* A single term of the WHERE clause */
  2985   2998   #ifdef SQLITE_ENABLE_STAT3
  2986   2999       WhereTerm *pFirstTerm = 0;    /* First term matching the index */
................................................................................
  3107   3120         }
  3108   3121       }
  3109   3122   #endif /* SQLITE_ENABLE_STAT3 */
  3110   3123   
  3111   3124       /* Adjust the number of output rows and downward to reflect rows
  3112   3125       ** that are excluded by range constraints.
  3113   3126       */
  3114         -    nRow = nRow/(double)rangeDiv;
         3127  +    nRow = nRow/rangeDiv;
  3115   3128       if( nRow<1 ) nRow = 1;
  3116   3129   
  3117   3130       /* Experiments run on real SQLite databases show that the time needed
  3118   3131       ** to do a binary search to locate a row in a table or index is roughly
  3119   3132       ** log10(N) times the time to move from one row to the next row within
  3120   3133       ** a table or index.  The actual times can vary, with the size of
  3121   3134       ** records being an important factor.  Both moves and searches are

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.

    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]
................................................................................
   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<?) (~166 rows)}}
   197    197   do_eqp_test analyze3-1.3.3 {
   198    198     SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
   199    199   } {0 0 0 {SCAN TABLE t3 (~111 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}
................................................................................
   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/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

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"

Changes to test/stat3.test.

     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 regression tests for SQLite library. This file 
    13     13   # implements tests for the extra functionality provided by the ANALYZE 
    14         -# command when the library is compiled with SQLITE_ENABLE_STAT2 defined.
           14  +# command when the library is compiled with SQLITE_ENABLE_STAT3 defined.
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   set testprefix stat3
    21     21   
................................................................................
    24     24   # command will delete the sqlite_stat2 table.  Likewise, if not compiled
    25     25   # with SQLITE_ENABLE_STAT3, the sqlite_stat3 table is deleted.
    26     26   #
    27     27   do_test 1.1 {
    28     28     db eval {
    29     29       PRAGMA writable_schema=ON;
    30     30       CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
    31         -    CREATE TABLE sqlite_stat3(tbl,idx,sampleno,sample,neq,nlt);
           31  +    CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
    32     32       SELECT name FROM sqlite_master ORDER BY 1;
    33     33     }
    34     34   } {sqlite_stat2 sqlite_stat3}
    35     35   do_test 1.2 {
    36     36     db close
    37     37     sqlite3 db test.db
    38     38     db eval {SELECT name FROM sqlite_master ORDER BY 1}

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