test/autoindex3.test

   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   |--BLOOM FILTER ON u (b=?)
  90   `--SEARCH u USING AUTOMATIC COVERING INDEX (b=?)
  91 }
  92
  93 # 2024-05-27
  94 # ticket https://sqlite.org/src/tktview/8ff324e120
  95 # forum post https://sqlite.org/forum/forumpost/b21c2101a559be0a
  96 #
  97 # If an index with STAT1 data indicates that a column is not very
  98 # selective, then do not attempt to create an automatic index on
  99 # that column.
 100 #
 101 reset_db
 102 do_execsql_test 300 {
 103   CREATE TABLE t1(id INTEGER PRIMARY KEY);
 104   CREATE TABLE t2(cid INT, pid INT, rx INT, PRIMARY KEY(cid, pid, rx));
 105   CREATE INDEX x1 ON t2(pid, rx);
 106   ANALYZE sqlite_schema;
 107   REPLACE INTO sqlite_stat1(tbl, idx, stat) VALUES
 108     ('t2', 'x1', '500000 250 250'),
 109     ('t2','sqlite_autoindex_t2_1','500000 1 1 1');
 110   ANALYZE sqlite_schema;
 111 }
 112 do_eqp_test 310 {
 113   WITH RECURSIVE children(id) AS (
 114     SELECT cid FROM t2 WHERE pid = ?1 AND rx = ?2
 115     UNION
 116     SELECT cid FROM t2 JOIN children ON t2.pid = children.id AND rx = ?2
 117   ) SELECT count(id) FROM children;
 118 } {
 119   QUERY PLAN
 120   |--CO-ROUTINE children
 121   |  |--SETUP
 122   |  |  `--SEARCH t2 USING INDEX x1 (pid=? AND rx=?)
 123   |  `--RECURSIVE STEP
 124   |     |--SCAN children
 125   |     `--SEARCH t2 USING INDEX x1 (pid=? AND rx=?)
 126   `--SCAN children
 127 }
 128
 129 finish_test