/ Check-in [aed2bf7a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Purge lingering references to SQLITE_STAT2 from the code and test scripts.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: aed2bf7a3c828a7191389b3f8235a9387977b476
User & Date: drh 2011-10-21 19:06:32
Context
2011-10-22
21:00
Avoid a harmless reference to an uninitialized variable following an error in FTS3. This is not a bug. The change is to silence a valgrind warning. check-in: d980c5b2 user: drh tags: trunk
2011-10-21
19:06
Purge lingering references to SQLITE_STAT2 from the code and test scripts. check-in: aed2bf7a user: drh tags: trunk
16:47
Remove stale requirements marks from the query planner. check-in: 76de9914 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   115    115   */
   116    116   #ifndef SQLITE_OMIT_ANALYZE
   117    117   #include "sqliteInt.h"
   118    118   
   119    119   /*
   120    120   ** This routine generates code that opens the sqlite_stat1 table for
   121    121   ** writing with cursor iStatCur. If the library was built with the
   122         -** SQLITE_ENABLE_STAT2 macro defined, then the sqlite_stat2 table is
          122  +** SQLITE_ENABLE_STAT3 macro defined, then the sqlite_stat3 table is
   123    123   ** opened for writing using cursor (iStatCur+1)
   124    124   **
   125    125   ** If the sqlite_stat1 tables does not previously exist, it is created.
   126         -** Similarly, if the sqlite_stat2 table does not exist and the library
   127         -** is compiled with SQLITE_ENABLE_STAT2 defined, it is created. 
          126  +** Similarly, if the sqlite_stat3 table does not exist and the library
          127  +** is compiled with SQLITE_ENABLE_STAT3 defined, it is created. 
   128    128   **
   129    129   ** Argument zWhere may be a pointer to a buffer containing a table name,
   130    130   ** or it may be a NULL pointer. If it is not NULL, then all entries in
   131         -** the sqlite_stat1 and (if applicable) sqlite_stat2 tables associated
          131  +** the sqlite_stat1 and (if applicable) sqlite_stat3 tables associated
   132    132   ** with the named table are deleted. If zWhere==0, then code is generated
   133    133   ** to delete all stat table entries.
   134    134   */
   135    135   static void openStatTable(
   136    136     Parse *pParse,          /* Parsing context */
   137    137     int iDb,                /* The database we are looking in */
   138    138     int iStatCur,           /* Open the sqlite_stat1 table on this cursor */

Changes to src/build.c.

  1977   1977         iDestroyed = iLargest;
  1978   1978       }
  1979   1979     }
  1980   1980   #endif
  1981   1981   }
  1982   1982   
  1983   1983   /*
  1984         -** Remove entries from the sqlite_stat1 and sqlite_stat2 tables
         1984  +** Remove entries from the sqlite_statN tables (for N in (1,2,3))
  1985   1985   ** after a DROP INDEX or DROP TABLE command.
  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[] = { 
  1994         -    "sqlite_stat1",
  1995         -    "sqlite_stat2",
  1996         -    "sqlite_stat3",
  1997         -  };
  1998   1993     int i;
  1999   1994     const char *zDbName = pParse->db->aDb[iDb].zName;
  2000         -  for(i=0; i<ArraySize(azStatTab); i++){
  2001         -    if( sqlite3FindTable(pParse->db, azStatTab[i], zDbName) ){
         1995  +  for(i=1; i<=3; i++){
         1996  +    char zTab[24];
         1997  +    sqlite3_snprintf(sizeof(zTab),zTab,"sqlite_stat%d",i);
         1998  +    if( sqlite3FindTable(pParse->db, zTab, zDbName) ){
  2002   1999         sqlite3NestedParse(pParse,
  2003   2000           "DELETE FROM %Q.%s WHERE %s=%Q",
  2004         -        zDbName, azStatTab[i], zType, zName
         2001  +        zDbName, zTab, zType, zName
  2005   2002         );
  2006   2003       }
  2007   2004     }
  2008   2005   }
  2009   2006   
  2010   2007   /*
  2011   2008   ** Generate code to drop a table.

Changes to src/ctime.c.

   110    110   #endif
   111    111   #ifdef SQLITE_ENABLE_OVERSIZE_CELL_CHECK
   112    112     "ENABLE_OVERSIZE_CELL_CHECK",
   113    113   #endif
   114    114   #ifdef SQLITE_ENABLE_RTREE
   115    115     "ENABLE_RTREE",
   116    116   #endif
   117         -#ifdef SQLITE_ENABLE_STAT2
   118         -  "ENABLE_STAT2",
   119         -#endif
   120    117   #ifdef SQLITE_ENABLE_STAT3
   121    118     "ENABLE_STAT3",
   122    119   #endif
   123    120   #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
   124    121     "ENABLE_UNLOCK_NOTIFY",
   125    122   #endif
   126    123   #ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT

Changes to src/sqliteInt.h.

    72     72   #ifdef HAVE_STDINT_H
    73     73   #include <stdint.h>
    74     74   #endif
    75     75   #ifdef HAVE_INTTYPES_H
    76     76   #include <inttypes.h>
    77     77   #endif
    78     78   
    79         -/*
    80         -** The number of samples of an index that SQLite takes in order to 
    81         -** construct a histogram of the table content when running ANALYZE
    82         -** and with SQLITE_ENABLE_STAT2
    83         -*/
    84         -#define SQLITE_INDEX_SAMPLES 10
    85         -
    86     79   /*
    87     80   ** The following macros are used to cast pointers to integers and
    88     81   ** integers to pointers.  The way you do this varies from one compiler
    89     82   ** to the next, so we have developed the following set of #if statements
    90     83   ** to generate appropriate macros for a wide range of compilers.
    91     84   **
    92     85   ** The correct "ANSI" way to do this is to use the intptr_t type. 
................................................................................
  1510   1503     int nSample;             /* Number of elements in aSample[] */
  1511   1504     tRowcnt avgEq;           /* Average nEq value for key values not in aSample */
  1512   1505     IndexSample *aSample;    /* Samples of the left-most key */
  1513   1506   #endif
  1514   1507   };
  1515   1508   
  1516   1509   /*
  1517         -** Each sample stored in the sqlite_stat2 table is represented in memory 
  1518         -** using a structure of this type.
         1510  +** Each sample stored in the sqlite_stat3 table is represented in memory 
         1511  +** using a structure of this type.  See documentation at the top of the
         1512  +** analyze.c source file for additional information.
  1519   1513   */
  1520   1514   struct IndexSample {
  1521   1515     union {
  1522   1516       char *z;        /* Value if eType is SQLITE_TEXT or SQLITE_BLOB */
  1523   1517       double r;       /* Value if eType is SQLITE_FLOAT */
  1524   1518       i64 i;          /* Value if eType is SQLITE_INTEGER */
  1525   1519     } u;

Changes to src/test_config.c.

   420    420   
   421    421   #ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
   422    422     Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY);
   423    423   #else
   424    424     Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY);
   425    425   #endif
   426    426   
   427         -#ifdef SQLITE_ENABLE_STAT2
   428         -  Tcl_SetVar2(interp, "sqlite_options", "stat2", "1", TCL_GLOBAL_ONLY);
   429         -#else
   430         -  Tcl_SetVar2(interp, "sqlite_options", "stat2", "0", TCL_GLOBAL_ONLY);
   431         -#endif
   432         -
   433    427   #ifdef SQLITE_ENABLE_STAT3
   434    428     Tcl_SetVar2(interp, "sqlite_options", "stat3", "1", TCL_GLOBAL_ONLY);
   435    429   #else
   436    430     Tcl_SetVar2(interp, "sqlite_options", "stat3", "0", TCL_GLOBAL_ONLY);
   437    431   #endif
   438    432   
   439    433   #if !defined(SQLITE_ENABLE_LOCKING_STYLE)

Changes to src/where.c.

  3163   3163       ** to do a binary search to locate a row in a table or index is roughly
  3164   3164       ** log10(N) times the time to move from one row to the next row within
  3165   3165       ** a table or index.  The actual times can vary, with the size of
  3166   3166       ** records being an important factor.  Both moves and searches are
  3167   3167       ** slower with larger records, presumably because fewer records fit
  3168   3168       ** on one page and hence more pages have to be fetched.
  3169   3169       **
  3170         -    ** The ANALYZE command and the sqlite_stat1 and sqlite_stat2 tables do
         3170  +    ** The ANALYZE command and the sqlite_stat1 and sqlite_stat3 tables do
  3171   3171       ** not give us data on the relative sizes of table and index records.
  3172   3172       ** So this computation assumes table records are about twice as big
  3173   3173       ** as index records
  3174   3174       */
  3175   3175       if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){
  3176   3176         /* The cost of a full table scan is a number of move operations equal
  3177   3177         ** to the number of rows in the table.

Changes to test/alter.test.

   842    842   
   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         -ifcapable stat2   { lappend system_table_list 3 sqlite_stat2 }
   850    849   ifcapable stat3   { lappend system_table_list 4 sqlite_stat3 }
   851    850   
   852    851   foreach {tn tbl} $system_table_list {
   853    852     do_test alter-15.$tn.1 {
   854    853       catchsql "ALTER TABLE $tbl RENAME TO xyz"
   855    854     } [list 1 "table $tbl may not be altered"]
   856    855   

Deleted test/analyze2.test.

     1         -# 2009 August 06
     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_STAT2 defined.
    15         -#
    16         -
    17         -set testdir [file dirname $argv0]
    18         -source $testdir/tester.tcl
    19         -
    20         -ifcapable !stat2 {
    21         -  finish_test
    22         -  return
    23         -}
    24         -
    25         -set testprefix analyze2
    26         -
    27         -# Do not use a codec for tests in this file, as the database file is
    28         -# manipulated directly using tcl scripts (using the [hexio_write] command).
    29         -#
    30         -do_not_use_codec
    31         -
    32         -#--------------------------------------------------------------------
    33         -# Test organization:
    34         -#
    35         -# analyze2-1.*: Tests to verify that ANALYZE creates and populates the
    36         -#               sqlite_stat2 table as expected.
    37         -#
    38         -# analyze2-2.*: Test that when a table has two indexes on it and either
    39         -#               index may be used for the scan, the index suggested by
    40         -#               the contents of sqlite_stat2 table is prefered.
    41         -# 
    42         -# analyze2-3.*: Similar to the previous block of tests, but using tables
    43         -#               that contain a mixture of NULL, numeric, text and blob
    44         -#               values.
    45         -#
    46         -# analyze2-4.*: Check that when an indexed column uses a collation other
    47         -#               than BINARY, the collation is taken into account when
    48         -#               using the contents of sqlite_stat2 to estimate the cost
    49         -#               of a range scan.
    50         -#
    51         -# analyze2-5.*: Check that collation sequences are used as described above
    52         -#               even when the only available version of the collation 
    53         -#               function require UTF-16 encoded arguments.
    54         -#
    55         -# analyze2-6.*: Check that the library behaves correctly when one of the
    56         -#               sqlite_stat2 or sqlite_stat1 tables are missing.
    57         -#
    58         -# analyze2-7.*: Check that in a shared-schema situation, nothing goes
    59         -#               wrong if sqlite_stat2 data is read by one connection,
    60         -#               and freed by another.
    61         -# 
    62         -
    63         -proc eqp {sql {db db}} {
    64         -  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
    65         -}
    66         -
    67         -do_test analyze2-1.1 {
    68         -  execsql { CREATE TABLE t1(x PRIMARY KEY) }
    69         -  for {set i 0} {$i < 1000} {incr i} {
    70         -    execsql { INSERT INTO t1 VALUES($i) }
    71         -  }
    72         -  execsql { 
    73         -    ANALYZE;
    74         -    SELECT * FROM sqlite_stat2;
    75         -  }
    76         -} [list t1 sqlite_autoindex_t1_1 0 50  \
    77         -        t1 sqlite_autoindex_t1_1 1 149 \
    78         -        t1 sqlite_autoindex_t1_1 2 249 \
    79         -        t1 sqlite_autoindex_t1_1 3 349 \
    80         -        t1 sqlite_autoindex_t1_1 4 449 \
    81         -        t1 sqlite_autoindex_t1_1 5 549 \
    82         -        t1 sqlite_autoindex_t1_1 6 649 \
    83         -        t1 sqlite_autoindex_t1_1 7 749 \
    84         -        t1 sqlite_autoindex_t1_1 8 849 \
    85         -        t1 sqlite_autoindex_t1_1 9 949 \
    86         -]
    87         -
    88         -do_test analyze2-1.2 {
    89         -  execsql {
    90         -    DELETE FROM t1 WHERe x>9;
    91         -    ANALYZE;
    92         -    SELECT tbl, idx, group_concat(sample, ' ') FROM sqlite_stat2;
    93         -  }
    94         -} {t1 sqlite_autoindex_t1_1 {0 1 2 3 4 5 6 7 8 9}}
    95         -do_test analyze2-1.3 {
    96         -  execsql {
    97         -    DELETE FROM t1 WHERE x>8;
    98         -    ANALYZE;
    99         -    SELECT * FROM sqlite_stat2;
   100         -  }
   101         -} {}
   102         -do_test analyze2-1.4 {
   103         -  execsql {
   104         -    DELETE FROM t1;
   105         -    ANALYZE;
   106         -    SELECT * FROM sqlite_stat2;
   107         -  }
   108         -} {}
   109         -
   110         -do_test analyze2-2.1 {
   111         -  execsql { 
   112         -    BEGIN;
   113         -    DROP TABLE t1;
   114         -    CREATE TABLE t1(x, y);
   115         -    CREATE INDEX t1_x ON t1(x);
   116         -    CREATE INDEX t1_y ON t1(y);
   117         -  }
   118         -  for {set i 0} {$i < 1000} {incr i} {
   119         -    execsql { INSERT INTO t1 VALUES($i, $i) }
   120         -  }
   121         -  execsql COMMIT
   122         -  execsql ANALYZE
   123         -} {}
   124         -do_eqp_test 2.2 {
   125         -  SELECT * FROM t1 WHERE x>500 AND y>700
   126         -} {
   127         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
   128         -}
   129         -do_eqp_test 2.3 {
   130         -  SELECT * FROM t1 WHERE x>700 AND y>500
   131         -} {
   132         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
   133         -}
   134         -do_eqp_test 2.3 {
   135         -  SELECT * FROM t1 WHERE y>700 AND x>500
   136         -} {
   137         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
   138         -}
   139         -do_eqp_test 2.4 {
   140         -  SELECT * FROM t1 WHERE y>500 AND x>700
   141         -} {
   142         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
   143         -}
   144         -do_eqp_test 2.5 {
   145         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700
   146         -} {
   147         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
   148         -}
   149         -do_eqp_test 2.6 {
   150         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700
   151         -} {
   152         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~75 rows)}
   153         -}
   154         -do_eqp_test 2.7 {
   155         -  SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300
   156         -} {
   157         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
   158         -}
   159         -do_eqp_test 2.8 {
   160         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300
   161         -} {
   162         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
   163         -}
   164         -do_eqp_test 2.9 {
   165         -  SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300
   166         -} {
   167         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~12 rows)}
   168         -}
   169         -do_eqp_test 2.10 {
   170         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100
   171         -} {
   172         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~12 rows)}
   173         -}
   174         -
   175         -do_test analyze2-3.1 {
   176         -  set alphabet [list a b c d e f g h i j]
   177         -  execsql BEGIN
   178         -  for {set i 0} {$i < 1000} {incr i} {
   179         -    set str    [lindex $alphabet [expr ($i/100)%10]] 
   180         -    append str [lindex $alphabet [expr ($i/ 10)%10]]
   181         -    append str [lindex $alphabet [expr ($i/  1)%10]]
   182         -    execsql { INSERT INTO t1 VALUES($str, $str) }
   183         -  }
   184         -  execsql COMMIT
   185         -  execsql ANALYZE
   186         -  execsql { 
   187         -    SELECT tbl,idx,group_concat(sample,' ') 
   188         -    FROM sqlite_stat2 
   189         -    WHERE idx = 't1_x' 
   190         -    GROUP BY tbl,idx
   191         -  }
   192         -} {t1 t1_x {100 299 499 699 899 ajj cjj ejj gjj ijj}}
   193         -do_test analyze2-3.2 {
   194         -  execsql { 
   195         -    SELECT tbl,idx,group_concat(sample,' ') 
   196         -    FROM sqlite_stat2 
   197         -    WHERE idx = 't1_y' 
   198         -    GROUP BY tbl,idx
   199         -  }
   200         -} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}
   201         -
   202         -do_eqp_test 3.3 {
   203         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
   204         -} {
   205         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
   206         -}
   207         -do_eqp_test 3.4 {
   208         -  SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'
   209         -} {
   210         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~100 rows)}
   211         -}
   212         -do_eqp_test 3.5 {
   213         -  SELECT * FROM t1 WHERE x<'a' AND y>'h'
   214         -} {
   215         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
   216         -}
   217         -do_eqp_test 3.6 {
   218         -  SELECT * FROM t1 WHERE x<444 AND y>'h'
   219         -} {
   220         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
   221         -}
   222         -do_eqp_test 3.7 {
   223         -  SELECT * FROM t1 WHERE x<221 AND y>'g'
   224         -} {
   225         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x<?) (~66 rows)}
   226         -}
   227         -
   228         -do_test analyze2-4.1 {
   229         -  execsql { CREATE TABLE t3(a COLLATE nocase, b) }
   230         -  execsql { CREATE INDEX t3a ON t3(a) }
   231         -  execsql { CREATE INDEX t3b ON t3(b) }
   232         -  set alphabet [list A b C d E f G h I j]
   233         -  execsql BEGIN
   234         -  for {set i 0} {$i < 1000} {incr i} {
   235         -    set str    [lindex $alphabet [expr ($i/100)%10]] 
   236         -    append str [lindex $alphabet [expr ($i/ 10)%10]]
   237         -    append str [lindex $alphabet [expr ($i/  1)%10]]
   238         -    execsql { INSERT INTO t3 VALUES($str, $str) }
   239         -  }
   240         -  execsql COMMIT
   241         -  execsql ANALYZE
   242         -} {}
   243         -do_test analyze2-4.2 {
   244         -  execsql { 
   245         -    PRAGMA automatic_index=OFF;
   246         -    SELECT tbl,idx,group_concat(sample,' ') 
   247         -    FROM sqlite_stat2 
   248         -    WHERE idx = 't3a' 
   249         -    GROUP BY tbl,idx;
   250         -    PRAGMA automatic_index=ON;
   251         -  }
   252         -} {t3 t3a {AfA bEj CEj dEj EEj fEj GEj hEj IEj jEj}}
   253         -do_test analyze2-4.3 {
   254         -  execsql { 
   255         -    SELECT tbl,idx,group_concat(sample,' ') 
   256         -    FROM sqlite_stat2 
   257         -    WHERE idx = 't3b' 
   258         -    GROUP BY tbl,idx
   259         -  }
   260         -} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}
   261         -
   262         -do_eqp_test 4.4 {
   263         -  SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'
   264         -} {
   265         -  0 0 0 {SEARCH TABLE t3 USING INDEX t3b (b>? AND b<?) (~11 rows)}
   266         -}
   267         -do_eqp_test 4.5 {
   268         -  SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'
   269         -} {
   270         -  0 0 0 {SEARCH TABLE t3 USING INDEX t3a (a>? AND a<?) (~22 rows)}
   271         -}
   272         -
   273         -ifcapable utf16 {
   274         -  proc test_collate {enc lhs rhs} {
   275         -    # puts $enc
   276         -    return [string compare $lhs $rhs]
   277         -  }
   278         -  do_test analyze2-5.1 {
   279         -    add_test_collate db 0 0 1
   280         -    execsql { CREATE TABLE t4(x COLLATE test_collate) }
   281         -    execsql { CREATE INDEX t4x ON t4(x) }
   282         -    set alphabet [list a b c d e f g h i j]
   283         -    execsql BEGIN
   284         -    for {set i 0} {$i < 1000} {incr i} {
   285         -      set str    [lindex $alphabet [expr ($i/100)%10]] 
   286         -      append str [lindex $alphabet [expr ($i/ 10)%10]]
   287         -      append str [lindex $alphabet [expr ($i/  1)%10]]
   288         -      execsql { INSERT INTO t4 VALUES($str) }
   289         -    }
   290         -    execsql COMMIT
   291         -    execsql ANALYZE
   292         -  } {}
   293         -  do_test analyze2-5.2 {
   294         -    execsql { 
   295         -      SELECT tbl,idx,group_concat(sample,' ') 
   296         -      FROM sqlite_stat2 
   297         -      WHERE tbl = 't4' 
   298         -      GROUP BY tbl,idx
   299         -    }
   300         -  } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
   301         -  do_eqp_test 5.3 {
   302         -    SELECT * FROM t4 WHERE x>'ccc'
   303         -  } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
   304         -  do_eqp_test 5.4 {
   305         -    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'
   306         -  } {
   307         -    0 0 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~300 rows)} 
   308         -    0 1 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~800 rows)}
   309         -  }
   310         -  do_eqp_test 5.5 {
   311         -    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'
   312         -  } {
   313         -    0 0 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~700 rows)} 
   314         -    0 1 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~800 rows)}
   315         -  }
   316         -}
   317         -
   318         -#--------------------------------------------------------------------
   319         -# These tests, analyze2-6.*, verify that the library behaves correctly
   320         -# when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
   321         -#
   322         -# If the sqlite_stat1 table is not present, then the sqlite_stat2
   323         -# table is not read. However, if it is the sqlite_stat2 table that
   324         -# is missing, the data in the sqlite_stat1 table is still used.
   325         -#
   326         -# Tests analyze2-6.1.* test the libary when the sqlite_stat2 table
   327         -# is missing. Tests analyze2-6.2.* test the library when sqlite_stat1
   328         -# is not present.
   329         -#
   330         -do_test analyze2-6.0 {
   331         -  execsql {
   332         -    DROP TABLE IF EXISTS t4;
   333         -    CREATE TABLE t5(a, b); CREATE INDEX t5i ON t5(a, b);
   334         -    CREATE TABLE t6(a, b); CREATE INDEX t6i ON t6(a, b);
   335         -  }
   336         -  for {set ii 0} {$ii < 20} {incr ii} {
   337         -    execsql {
   338         -      INSERT INTO t5 VALUES($ii, $ii);
   339         -      INSERT INTO t6 VALUES($ii/10, $ii/10);
   340         -    }
   341         -  }
   342         -  execsql { 
   343         -    CREATE TABLE master AS 
   344         -    SELECT * FROM sqlite_master WHERE name LIKE 'sqlite_stat%' 
   345         -  }
   346         -} {}
   347         -
   348         -do_test analyze2-6.1.1 {
   349         -  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   350         -       t5.a = 1 AND
   351         -       t6.a = 1 AND t6.b = 1
   352         -  }
   353         -} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   354         -do_test analyze2-6.1.2 {
   355         -  db cache flush
   356         -  execsql ANALYZE
   357         -  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   358         -       t5.a = 1 AND
   359         -       t6.a = 1 AND t6.b = 1
   360         -  }
   361         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   362         -do_test analyze2-6.1.3 {
   363         -  sqlite3 db test.db
   364         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   365         -       t5.a = 1 AND
   366         -       t6.a = 1 AND t6.b = 1
   367         -  }
   368         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   369         -do_test analyze2-6.1.4 {
   370         -  execsql { 
   371         -    PRAGMA writable_schema = 1;
   372         -    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
   373         -  }
   374         -  sqlite3 db test.db
   375         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   376         -       t5.a = 1 AND
   377         -       t6.a = 1 AND t6.b = 1
   378         -  }
   379         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   380         -do_test analyze2-6.1.5 {
   381         -  execsql { 
   382         -    PRAGMA writable_schema = 1;
   383         -    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
   384         -  }
   385         -  sqlite3 db test.db
   386         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   387         -       t5.a = 1 AND
   388         -       t6.a = 1 AND t6.b = 1
   389         -  }
   390         -} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   391         -do_test analyze2-6.1.6 {
   392         -  execsql { 
   393         -    PRAGMA writable_schema = 1;
   394         -    INSERT INTO sqlite_master SELECT * FROM master;
   395         -  }
   396         -  sqlite3 db test.db
   397         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   398         -       t5.a = 1 AND
   399         -       t6.a = 1 AND t6.b = 1
   400         -  }
   401         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   402         -
   403         -do_test analyze2-6.2.1 {
   404         -  execsql { 
   405         -    DELETE FROM sqlite_stat1;
   406         -    DELETE FROM sqlite_stat2;
   407         -  }
   408         -  sqlite3 db test.db
   409         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   410         -        t5.a>1 AND t5.a<15 AND
   411         -        t6.a>1
   412         -  }
   413         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   414         -do_test analyze2-6.2.2 {
   415         -  db cache flush
   416         -  execsql ANALYZE
   417         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   418         -        t5.a>1 AND t5.a<15 AND
   419         -        t6.a>1
   420         -  }
   421         -} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   422         -do_test analyze2-6.2.3 {
   423         -  sqlite3 db test.db
   424         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   425         -        t5.a>1 AND t5.a<15 AND
   426         -        t6.a>1
   427         -  }
   428         -} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   429         -do_test analyze2-6.2.4 {
   430         -  execsql { 
   431         -    PRAGMA writable_schema = 1;
   432         -    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
   433         -  }
   434         -  sqlite3 db test.db
   435         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   436         -        t5.a>1 AND t5.a<15 AND
   437         -        t6.a>1
   438         -  }
   439         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   440         -do_test analyze2-6.2.5 {
   441         -  execsql { 
   442         -    PRAGMA writable_schema = 1;
   443         -    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
   444         -  }
   445         -  sqlite3 db test.db
   446         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   447         -        t5.a>1 AND t5.a<15 AND
   448         -        t6.a>1
   449         -  }
   450         -} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~60000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   451         -do_test analyze2-6.2.6 {
   452         -  execsql { 
   453         -    PRAGMA writable_schema = 1;
   454         -    INSERT INTO sqlite_master SELECT * FROM master;
   455         -  }
   456         -  sqlite3 db test.db
   457         -  execsql ANALYZE
   458         -  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   459         -        t5.a>1 AND t5.a<15 AND
   460         -        t6.a>1
   461         -  }
   462         -} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   463         -
   464         -#--------------------------------------------------------------------
   465         -# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
   466         -# works in shared-cache mode. Note that these tests reuse the database
   467         -# created for the analyze2-6.* tests.
   468         -#
   469         -ifcapable shared_cache {
   470         -  db close
   471         -  set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
   472         -
   473         -  proc incr_schema_cookie {zDb} {
   474         -    foreach iOffset {24 40} {
   475         -      set cookie [hexio_get_int [hexio_read $zDb $iOffset 4]]
   476         -      incr cookie
   477         -      hexio_write $zDb $iOffset [hexio_render_int32 $cookie]
   478         -    }
   479         -  }
   480         -
   481         -  do_test analyze2-7.1 {
   482         -    sqlite3 db1 test.db
   483         -    sqlite3 db2 test.db
   484         -    db1 cache size 0
   485         -    db2 cache size 0
   486         -    execsql { SELECT count(*) FROM t5 } db1
   487         -  } {20}
   488         -  do_test analyze2-7.2 {
   489         -    incr_schema_cookie test.db
   490         -    execsql { SELECT count(*) FROM t5 } db2
   491         -  } {20}
   492         -  do_test analyze2-7.3 {
   493         -    incr_schema_cookie test.db
   494         -    execsql { SELECT count(*) FROM t5 } db1
   495         -  } {20}
   496         -  do_test analyze2-7.4 {
   497         -    incr_schema_cookie test.db
   498         -    execsql { SELECT count(*) FROM t5 } db2
   499         -  } {20}
   500         -
   501         -  do_test analyze2-7.5 {
   502         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   503         -          t5.a>1 AND t5.a<15 AND
   504         -          t6.a>1
   505         -    } db1
   506         -  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   507         -  do_test analyze2-7.6 {
   508         -    incr_schema_cookie test.db
   509         -    execsql { SELECT * FROM sqlite_master } db2
   510         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   511         -          t5.a>1 AND t5.a<15 AND
   512         -          t6.a>1
   513         -    } db2
   514         -  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   515         -  do_test analyze2-7.7 {
   516         -    incr_schema_cookie test.db
   517         -    execsql { SELECT * FROM sqlite_master } db1
   518         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   519         -          t5.a>1 AND t5.a<15 AND
   520         -          t6.a>1
   521         -    } db1
   522         -  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   523         -
   524         -  do_test analyze2-7.8 {
   525         -    execsql { DELETE FROM sqlite_stat2 } db2
   526         -    execsql { SELECT * FROM sqlite_master } db1
   527         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   528         -          t5.a>1 AND t5.a<15 AND
   529         -          t6.a>1
   530         -    } db1
   531         -  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   532         -  do_test analyze2-7.9 {
   533         -    execsql { SELECT * FROM sqlite_master } db2
   534         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   535         -          t5.a>1 AND t5.a<15 AND
   536         -          t6.a>1
   537         -    } db2
   538         -  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~1 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   539         -
   540         -  do_test analyze2-7.10 {
   541         -    incr_schema_cookie test.db
   542         -    execsql { SELECT * FROM sqlite_master } db1
   543         -    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
   544         -          t5.a>1 AND t5.a<15 AND
   545         -          t6.a>1
   546         -    } db1
   547         -  } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
   548         -
   549         -  db1 close
   550         -  db2 close
   551         -  sqlite3_enable_shared_cache $::enable_shared_cache
   552         -}
   553         -
   554         -finish_test

Changes to test/analyze3.test.

    66     66   #   INTEGER) with integer values from 100 to 1100. Create an index on this 
    67     67   #   column. ANALYZE the table.
    68     68   #
    69     69   # analyze3-1.1.2 - 3.1.3
    70     70   #   Show that there are two possible plans for querying the table with
    71     71   #   a range constraint on the indexed column - "full table scan" or "use 
    72     72   #   the index". When the range is specified using literal values, SQLite
    73         -#   is able to pick the best plan based on the samples in sqlite_stat2.
           73  +#   is able to pick the best plan based on the samples in sqlite_stat3.
    74     74   #
    75     75   # analyze3-1.1.4 - 3.1.9
    76     76   #   Show that using SQL variables produces the same results as using
    77     77   #   literal values to constrain the range scan.
    78     78   #
    79     79   #   These tests also check that the compiler code considers column 
    80     80   #   affinities when estimating the number of rows scanned by the "use 

Changes to test/auth.test.

  2317   2317       }
  2318   2318       ifcapable view {
  2319   2319         execsql {
  2320   2320           DROP TABLE v1chng;
  2321   2321         }
  2322   2322       }
  2323   2323     }
  2324         -  ifcapable stat2 {
  2325         -    set stat2 "sqlite_stat2 "
         2324  +  ifcapable stat3 {
         2325  +    set stat3 "sqlite_stat3 "
  2326   2326     } else {
  2327         -    ifcapable stat3 {
  2328         -      set stat2 "sqlite_stat3 "
  2329         -    } else {
  2330         -      set stat2 ""
  2331         -    }
         2327  +    set stat3 ""
  2332   2328     }
  2333   2329     do_test auth-5.2 {
  2334   2330       execsql {
  2335   2331         SELECT name FROM (
  2336   2332           SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)
  2337   2333         WHERE type='table'
  2338   2334         ORDER BY name
  2339   2335       }
  2340         -  } "sqlite_stat1 ${stat2}t1 t2 t3 t4"
         2336  +  } "sqlite_stat1 ${stat3}t1 t2 t3 t4"
  2341   2337   }
  2342   2338   
  2343   2339   # Ticket #3944
  2344   2340   #
  2345   2341   ifcapable trigger {
  2346   2342     do_test auth-5.3.1 {
  2347   2343       execsql {

Changes to test/malloc.test.

   863    863         execsql {INSERT INTO t1 VALUES(3, 4)} db2
   864    864       } {}
   865    865       db2 close
   866    866     }
   867    867     catch { db2 close }
   868    868   }
   869    869   
   870         -ifcapable stat2&&utf16 {
   871         -  do_malloc_test 38 -tclprep {
   872         -    add_test_collate db 0 0 1
   873         -    execsql {
   874         -      ANALYZE;
   875         -      CREATE TABLE t4(x COLLATE test_collate);
   876         -      CREATE INDEX t4x ON t4(x);
   877         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 0, 'aaa');
   878         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 1, 'aaa');
   879         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 2, 'aaa');
   880         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 3, 'aaa');
   881         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 4, 'aaa');
   882         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 5, 'aaa');
   883         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 6, 'aaa');
   884         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 7, 'aaa');
   885         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 8, 'aaa');
   886         -      INSERT INTO sqlite_stat2 VALUES('t4', 't4x', 9, 'aaa');
   887         -    }
   888         -    db close
   889         -    sqlite3 db test.db
   890         -    sqlite3_db_config_lookaside db 0 0 0
   891         -    add_test_collate db 0 0 1
   892         -  } -sqlbody {
   893         -    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'
   894         -  }
   895         -}
   896    870   
   897    871   # Test that if an OOM error occurs, aux-data is still correctly destroyed.
   898    872   # This test case was causing either a memory-leak or an assert() failure
   899    873   # at one point, depending on the configuration.
   900    874   #
   901    875   do_malloc_test 39 -tclprep {
   902    876     sqlite3 db test.db