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

     1  # 2012 September 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  # Tests for the optimization which attempts to use a covering index
    13  # for a full-table scan (under the theory that the index will be smaller
    14  # and require less I/O and hence will run faster.)
    15  #
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  set testprefix coveridxscan
    21  
    22  do_test 1.1 {
    23    db eval {
    24      CREATE TABLE t1(a,b,c);
    25      INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);
    26      CREATE INDEX t1ab ON t1(a,b);
    27      CREATE INDEX t1b ON t1(b);
    28      SELECT a FROM t1;
    29    }
    30    # covering index used for the scan, hence values are increasing
    31  } {3 4 5}
    32  
    33  do_test 1.2 {
    34    db eval {
    35      SELECT a, c FROM t1;
    36    }
    37    # There is no covering index, hence the values are in rowid order
    38  } {5 3 4 2 3 1}
    39  
    40  do_test 1.3 {
    41    db eval {
    42      SELECT b FROM t1;
    43    }
    44    # Choice of two indices: use the one with fewest columns
    45  } {2 4 8}
    46  
    47  do_test 2.1 {
    48    optimization_control db cover-idx-scan 0
    49    db eval {SELECT a FROM t1}
    50    # With the optimization turned off, output in rowid order
    51  } {5 4 3}
    52  do_test 2.2 {
    53    db eval {SELECT a, c FROM t1}
    54  } {5 3 4 2 3 1}
    55  do_test 2.3 {
    56    db eval {SELECT b FROM t1}
    57  } {4 8 2}
    58  
    59  db close
    60  sqlite3_shutdown
    61  sqlite3_config_cis 0
    62  sqlite3 db test.db
    63  
    64  do_test 3.1 {
    65    db eval {SELECT a FROM t1}
    66    # With the optimization configured off, output in rowid order
    67  } {5 4 3}
    68  do_test 3.2 {
    69    db eval {SELECT a, c FROM t1}
    70  } {5 3 4 2 3 1}
    71  do_test 3.3 {
    72    db eval {SELECT b FROM t1}
    73  } {4 8 2}
    74  
    75  db close
    76  sqlite3_shutdown
    77  sqlite3_config_cis 1
    78  sqlite3 db test.db
    79  
    80  # The CIS optimization is enabled again.  Covering indices are once again
    81  # used for all table scans.
    82  do_test 4.1 {
    83    db eval {SELECT a FROM t1}
    84  } {3 4 5}
    85  do_test 4.2 {
    86    db eval {SELECT a, c FROM t1}
    87  } {5 3 4 2 3 1}
    88  do_test 4.3 {
    89    db eval {SELECT b FROM t1}
    90  } {2 4 8}
    91  
    92  #-------------------------------------------------------------------------
    93  # Test that indexes with large numbers of columns can be correctly 
    94  # identified as covering indexes.
    95  reset_db
    96  set L [list]
    97  for {set i 1} {$i<120} {incr i} {
    98    lappend L "c$i"
    99  }
   100  set cols [join $L ,]
   101  
   102  do_execsql_test 5.1.0 "
   103    CREATE TABLE t1(a, b, c, $cols, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
   104    CREATE INDEX i1 ON t1($cols);
   105  
   106    CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols);
   107    CREATE INDEX i2 ON t2($cols);
   108  "
   109  
   110  do_eqp_test 5.1.1 {
   111    SELECT * FROM t1 ORDER BY c1, c2;
   112  } {SCAN t1 USING COVERING INDEX i1}
   113  
   114  do_eqp_test 5.1.2 {
   115    SELECT * FROM t2 ORDER BY c1, c2;
   116  } {SCAN t2 USING COVERING INDEX i2}
   117  
   118  
   119  finish_test