github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/analyze7.test (about)

     1  # 2011 April 1
     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  # This file implements regression tests for SQLite library.
    12  # This file implements tests for the ANALYZE command when an idnex
    13  # name is given as the argument.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # There is nothing to test if ANALYZE is disable for this build.
    20  #
    21  ifcapable {!analyze||!vtab} {
    22    finish_test
    23    return
    24  }
    25  
    26  # Generate some test data
    27  #
    28  do_test analyze7-1.0 {
    29    load_static_extension db wholenumber
    30    execsql {
    31      CREATE TABLE t1(a,b,c,d);
    32      CREATE INDEX t1a ON t1(a);
    33      CREATE INDEX t1b ON t1(b);
    34      CREATE INDEX t1cd ON t1(c,d);
    35      CREATE VIRTUAL TABLE nums USING wholenumber;
    36      INSERT INTO t1 SELECT value, value, value/100, value FROM nums
    37                      WHERE value BETWEEN 1 AND 256;
    38      EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
    39    }
    40  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
    41  do_test analyze7-1.1 {
    42    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    43  } {/*SEARCH t1 USING INDEX t1b (b=?)*/}
    44  do_test analyze7-1.2 {
    45    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    46  } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}
    47  
    48  # Run an analyze on one of the three indices.  Verify that this
    49  # effects the row-count estimate on the one query that uses that
    50  # one index.
    51  #
    52  do_test analyze7-2.0 {
    53    execsql {ANALYZE t1a;}
    54    db cache flush
    55    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
    56  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
    57  do_test analyze7-2.1 {
    58    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    59  } {/*SEARCH t1 USING INDEX t1b (b=?)*/}
    60  do_test analyze7-2.2 {
    61    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    62  } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}
    63  
    64  # Verify that since the query planner now things that t1a is more
    65  # selective than t1b, it prefers to use t1a.
    66  #
    67  do_test analyze7-2.3 {
    68    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
    69  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
    70  
    71  # Run an analysis on another of the three indices.  Verify  that this
    72  # new analysis works and does not disrupt the previous analysis.
    73  #
    74  do_test analyze7-3.0 {
    75    execsql {ANALYZE t1cd;}
    76    db cache flush;
    77    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
    78  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
    79  do_test analyze7-3.1 {
    80    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
    81  } {/*SEARCH t1 USING INDEX t1b (b=?)*/}
    82  do_test analyze7-3.2.1 {
    83    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
    84  } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}
    85  ifcapable stat4 {
    86    # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated
    87    # row count for (c=2) than it does for (c=?).
    88    do_test analyze7-3.2.2 {
    89      execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    90    } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}
    91  } else {
    92    # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the
    93    # same as that for (c=?).
    94    do_test analyze7-3.2.3 {
    95      execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
    96    } {/*SEARCH t1 USING INDEX t1cd (c=?)*/}
    97  }
    98  do_test analyze7-3.3 {
    99    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
   100  } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
   101  
   102  ifcapable {!stat4} {
   103    do_test analyze7-3.4 {
   104      execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
   105    } {/*SEARCH t1 USING INDEX t1b (b=?)*/}
   106    do_test analyze7-3.5 {
   107      execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
   108    } {/*SEARCH t1 USING INDEX t1a (a=?)*/}
   109  }
   110  do_test analyze7-3.6 {
   111    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
   112  } {/*SEARCH t1 USING INDEX t1cd (c=? AND d=?)*/}
   113  
   114  finish_test