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

     1  # 2014-06-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 regression tests for SQLite library.  The
    13  # focus of this script is testing automatic index creation logic,
    14  # and specifically that an automatic index will not be created that
    15  # shadows a declared index.
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  set testprefix autoindex3
    21  
    22  # The t1b and t2d indexes are not very selective.  It used to be that
    23  # the autoindex mechanism would create automatic indexes on t1(b) or
    24  # t2(d), make assumptions that they were reasonably selective, and use
    25  # them instead of t1b or t2d.  But that would be cheating, because the
    26  # automatic index cannot be any more selective than the real index.
    27  #
    28  # This test verifies that the cheat is no longer allowed.
    29  #
    30  do_execsql_test autoindex3-100 {
    31    CREATE TABLE t1(a,b,x);
    32    CREATE TABLE t2(c,d,y);
    33    CREATE INDEX t1b ON t1(b);
    34    CREATE INDEX t2d ON t2(d);
    35    ANALYZE sqlite_master;
    36    INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500');
    37    INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500');
    38    ANALYZE sqlite_master;
    39    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b;
    40  } {~/AUTO/}
    41  
    42  # Automatic indexes can still be used if existing indexes do not
    43  # participate in == constraints.
    44  #
    45  do_execsql_test autoindex3-110 {
    46    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y;
    47  } {/AUTO/}
    48  do_execsql_test autoindex3-120 {
    49    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y;
    50  } {/AUTO/}
    51  do_execsql_test autoindex3-130 {
    52    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y;
    53  } {/AUTO/}
    54  do_execsql_test autoindex3-140 {
    55    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y;
    56  } {/AUTO/}
    57  
    58  reset_db
    59  do_execsql_test 210 {
    60    CREATE TABLE v(b, d, e);
    61    CREATE TABLE u(a, b, c);
    62    ANALYZE sqlite_master;
    63    INSERT INTO "sqlite_stat1" VALUES('u','uab','40000 400 1');
    64    INSERT INTO "sqlite_stat1" VALUES('v','vbde','40000 400 1 1');
    65    INSERT INTO "sqlite_stat1" VALUES('v','ve','40000 21');
    66  
    67    CREATE INDEX uab on u(a, b);
    68    CREATE INDEX ve on v(e);
    69    CREATE INDEX vbde on v(b,d,e);
    70  
    71    DROP TABLE IF EXISTS sqlite_stat4;
    72    ANALYZE sqlite_master;
    73  }
    74  
    75  # At one point, SQLite was using the inferior plan:
    76  #
    77  #   0|0|1|SEARCH v USING INDEX ve (e>?)
    78  #   0|1|0|SEARCH u USING COVERING INDEX uab (ANY(a) AND b=?)
    79  #
    80  # on the basis that the real index "uab" must be better than the automatic
    81  # index. This is not right - a skip-scan is not necessarily better than an
    82  # automatic index scan.
    83  #
    84  do_eqp_test 220 {
    85    select count(*) from u, v where u.b = v.b and v.e > 34;
    86  } {
    87    QUERY PLAN
    88    |--SEARCH v USING INDEX ve (e>?)
    89    `--SEARCH u USING AUTOMATIC COVERING INDEX (b=?)
    90  }
    91  
    92  
    93  finish_test