gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/autoanalyze1.test (about)

     1  # 2017-02-17
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  # This file implements tests for the logic used to estimate when
    13  # running ANALYZE would be beneficial.
    14  #
    15  # Note that this test uses some hard-coded bitmask values from sqliteInt.h.
    16  # If any of the following constants changes:
    17  #
    18  #    define TF_HasStat1   0x0010
    19  #    define TF_StatsUsed  0x0100
    20  #
    21  # then some of the magic numbers in test results below might need to be
    22  # adjusted.
    23  #
    24  
    25  set testdir [file dirname $argv0]
    26  source $testdir/tester.tcl
    27  
    28  # There is nothing to test if ANALYZE is disable for this build.
    29  # These tests also use "PRAGMA stats" which are only enabled for
    30  # debugging builds.
    31  #
    32  ifcapable {!debug || !analyze || !vtab} {
    33    finish_test
    34    return
    35  }
    36  
    37  do_execsql_test autoanalyze1-100 {
    38    -- Build up a test table with some indexes
    39    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
    40    CREATE UNIQUE INDEX t1bc ON t1(b,c);
    41    CREATE INDEX t1d ON t1(d);
    42    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
    43      INSERT INTO t1(a,b,c,d) SELECT x, x, x, x FROM c;
    44    -- Verify that the hasStat1 flag is clear on on indexes
    45    SELECT idx, flgs FROM pragma_stats
    46     WHERE idx IS NOT NULL
    47     ORDER BY idx;
    48    -- Verify that the TF_HasStat1 flag is clear on the table
    49    SELECT tbl, (flgs & 0x10)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    50  } {t1bc 0 t1d 0 t1 0}
    51  
    52  # No use of stat1 recorded so far
    53  do_execsql_test autoanalyze1-110 {
    54    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    55  } {0}
    56  
    57  # Access using a unique index does not set the TF_StatsUsed flag.
    58  #
    59  do_execsql_test autoanalyze1-200 {
    60    SELECT * FROM t1 WHERE a=55;
    61  } {55 55 55 55}
    62  do_execsql_test autoanalyze1-201 {
    63    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    64  } {0}
    65  
    66  do_execsql_test autoanalyze1-210 {
    67    SELECT * FROM t1 WHERE a IN (55,199,299);
    68  } {55 55 55 55}
    69  do_execsql_test autoanalyze1-211 {
    70    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    71  } {0}
    72  
    73  do_execsql_test autoanalyze1-220 {
    74    SELECT * FROM t1 WHERE (b,c)=(45,45);
    75  } {45 45 45 45}
    76  do_execsql_test autoanalyze1-221 {
    77    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    78  } {0}
    79  
    80  # Any use of the non-unique t1d index triggers the use of stats.
    81  #
    82  sqlite3 db test.db
    83  do_execsql_test autoanalyze1-300 {
    84    SELECT * FROM t1 WHERE d=45;
    85  } {45 45 45 45}
    86  do_execsql_test autoanalyze1-301 {
    87    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    88  } {1}
    89  
    90  sqlite3 db test.db
    91  do_execsql_test autoanalyze1-310 {
    92    SELECT * FROM t1 WHERE d=45 AND a=45;
    93  } {45 45 45 45}
    94  do_execsql_test autoanalyze1-311 {
    95    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
    96  } {0}  ;# The ROWID lookup short-circuits the d=45 constraint
    97  
    98  sqlite3 db test.db
    99  do_execsql_test autoanalyze1-320 {
   100    SELECT * FROM t1 WHERE d=45 AND a IN (45,46);
   101  } {45 45 45 45}
   102  do_execsql_test autoanalyze1-321 {
   103    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
   104  } {1}
   105  
   106  # Any use of prefix of a unique index triggers the use of stats
   107  #
   108  sqlite3 db test.db
   109  do_execsql_test autoanalyze1-400 {
   110    SELECT * FROM t1 WHERE b=45;
   111  } {45 45 45 45}
   112  do_execsql_test autoanalyze1-401 {
   113    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
   114  } {1}
   115  
   116  # The TF_StatsUsed flag is reset when the database is reopened
   117  #
   118  sqlite3 db test.db
   119  do_execsql_test autoanalyze1-500 {
   120    SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
   121  } {0}
   122  
   123  finish_test