third_party/sqlite/src/test/autoindex1.test

   1 # 2010 April 07
   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.  The
  12 # focus of this script is testing automatic index creation logic.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 # If the library is not compiled with automatic index support then
  19 # skip all tests in this file.
  20 #
  21 ifcapable {!autoindex} {
  22   finish_test
  23   return
  24 }
  25
  26 # With automatic index turned off, we do a full scan of the T2 table
  27 do_test autoindex1-100 {
  28   db eval {
  29     CREATE TABLE t1(a,b);
  30     INSERT INTO t1 VALUES(1,11);
  31     INSERT INTO t1 VALUES(2,22);
  32     INSERT INTO t1 SELECT a+2, b+22 FROM t1;
  33     INSERT INTO t1 SELECT a+4, b+44 FROM t1;
  34     CREATE TABLE t2(c,d);
  35     INSERT INTO t2 SELECT a, 900+b FROM t1;
  36   }
  37   db eval {
  38     PRAGMA automatic_index=OFF;
  39     SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  40   }
  41 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
  42 do_test autoindex1-101 {
  43   db status step
  44 } {63}
  45 do_test autoindex1-102 {
  46   db status autoindex
  47 } {0}
  48
  49 # With autoindex turned on, we build an index once and then use that index
  50 # to find T2 values.
  51 do_test autoindex1-110 {
  52   db eval {
  53     PRAGMA automatic_index=ON;
  54     SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  55   }
  56 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
  57 do_test autoindex1-111 {
  58   db status step
  59 } {7}
  60 do_test autoindex1-112 {
  61   db status autoindex
  62 } {7}
  63
  64 # The same test as above, but this time the T2 query is a subquery rather
  65 # than a join.
  66 do_test autoindex1-200 {
  67   db eval {
  68     PRAGMA automatic_index=OFF;
  69     SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  70   }
  71 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
  72 do_test autoindex1-201 {
  73   db status step
  74 } {35}
  75 do_test autoindex1-202 {
  76   db status autoindex
  77 } {0}
  78 do_test autoindex1-210 {
  79   db eval {
  80     PRAGMA automatic_index=ON;
  81     SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  82   }
  83 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
  84 do_test autoindex1-211 {
  85   db status step
  86 } {7}
  87 do_test autoindex1-212 {
  88   db status autoindex
  89 } {7}
  90
  91
  92 # Modify the second table of the join while the join is in progress
  93 #
  94 do_test autoindex1-300 {
  95   set r {}
  96   db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
  97     lappend r $b $d
  98     db eval {UPDATE t2 SET d=d+1}
  99   }
 100   set r
 101 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
 102 do_test autoindex1-310 {
 103   db eval {SELECT d FROM t2 ORDER BY d}
 104 } {919 930 941 952 963 974 985 996}
 105
 106 # The next test does a 10-way join on unindexed tables.  Without
 107 # automatic indices, the join will take a long time to complete.
 108 # With automatic indices, it should only take about a second.
 109 #
 110 do_test autoindex1-400 {
 111   db eval {
 112     CREATE TABLE t4(a, b);
 113     INSERT INTO t4 VALUES(1,2);
 114     INSERT INTO t4 VALUES(2,3);
 115   }
 116   for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
 117     db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
 118   }
 119   db eval {
 120     SELECT count(*) FROM t4;
 121   }
 122 } {4096}
 123 do_test autoindex1-401 {
 124   db eval {
 125     SELECT count(*)
 126       FROM t4 AS x1
 127       JOIN t4 AS x2 ON x2.a=x1.b
 128       JOIN t4 AS x3 ON x3.a=x2.b
 129       JOIN t4 AS x4 ON x4.a=x3.b
 130       JOIN t4 AS x5 ON x5.a=x4.b
 131       JOIN t4 AS x6 ON x6.a=x5.b
 132       JOIN t4 AS x7 ON x7.a=x6.b
 133       JOIN t4 AS x8 ON x8.a=x7.b
 134       JOIN t4 AS x9 ON x9.a=x8.b
 135       JOIN t4 AS x10 ON x10.a=x9.b;
 136   }
 137 } {4087}
 138
 139 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
 140 # Make sure automatic indices are not created for the RHS of an IN expression
 141 # that is not a correlated subquery.
 142 #
 143 do_execsql_test autoindex1-500 {
 144   CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
 145   CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
 146   EXPLAIN QUERY PLAN
 147   SELECT b FROM t501
 148    WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
 149 } {
 150   0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)}
 151   0 0 0 {EXECUTE LIST SUBQUERY 1}
 152   1 0 0 {SCAN TABLE t502 (~100000 rows)}
 153 }
 154 do_execsql_test autoindex1-501 {
 155   EXPLAIN QUERY PLAN
 156   SELECT b FROM t501
 157    WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 158 } {
 159   0 0 0 {SCAN TABLE t501 (~500000 rows)}
 160   0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
 161   1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
 162 }
 163 do_execsql_test autoindex1-502 {
 164   EXPLAIN QUERY PLAN
 165   SELECT b FROM t501
 166    WHERE t501.a=123
 167      AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 168 } {
 169   0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
 170   0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
 171   1 0 0 {SCAN TABLE t502 (~100000 rows)}
 172 }
 173
 174
 175 # The following code checks a performance regression reported on the
 176 # mailing list on 2010-10-19.  The problem is that the nRowEst field
 177 # of ephermeral tables was not being initialized correctly and so no
 178 # automatic index was being created for the emphemeral table when it was
 179 # used as part of a join.
 180 #
 181 do_execsql_test autoindex1-600 {
 182   CREATE TABLE flock_owner(
 183     owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
 184     flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
 185     owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
 186     owner_change_date TEXT, last_changed TEXT NOT NULL,
 187     CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
 188   );
 189   CREATE TABLE sheep (
 190     Sheep_No char(7) NOT NULL,
 191     Date_of_Birth char(8),
 192     Sort_DoB text,
 193     Flock_Book_Vol char(2),
 194     Breeder_No char(6),
 195     Breeder_Person integer,
 196     Originating_Flock char(6),
 197     Registering_Flock char(6),
 198     Tag_Prefix char(9),
 199     Tag_No char(15),
 200     Sort_Tag_No integer,
 201     Breeders_Temp_Tag char(15),
 202     Sex char(1),
 203     Sheep_Name char(32),
 204     Sire_No char(7),
 205     Dam_No char(7),
 206     Register_Code char(1),
 207     Colour char(48),
 208     Colour_Code char(2),
 209     Pattern_Code char(8),
 210     Horns char(1),
 211     Litter_Size char(1),
 212     Coeff_of_Inbreeding real,
 213     Date_of_Registration text,
 214     Date_Last_Changed text,
 215     UNIQUE(Sheep_No));
 216   CREATE INDEX fo_flock_no_index
 217               ON flock_owner (flock_no);
 218   CREATE INDEX fo_owner_change_date_index
 219               ON flock_owner (owner_change_date);
 220   CREATE INDEX fo_owner_person_id_index
 221               ON flock_owner (owner_person_id);
 222   CREATE INDEX sheep_org_flock_index
 223            ON sheep (originating_flock);
 224   CREATE INDEX sheep_reg_flock_index
 225            ON sheep (registering_flock);
 226   EXPLAIN QUERY PLAN
 227   SELECT x.sheep_no, x.registering_flock, x.date_of_registration
 228    FROM sheep x LEFT JOIN
 229        (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
 230        s.date_of_registration, prev.owner_change_date
 231        FROM sheep s JOIN flock_owner prev ON s.registering_flock =
 232    prev.flock_no
 233        AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
 234        WHERE NOT EXISTS
 235            (SELECT 'x' FROM flock_owner later
 236            WHERE prev.flock_no = later.flock_no
 237            AND later.owner_change_date > prev.owner_change_date
 238            AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
 239        ) y ON x.sheep_no = y.sheep_no
 240    WHERE y.sheep_no IS NULL
 241    ORDER BY x.registering_flock;
 242 } {
 243   1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
 244   1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
 245   1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
 246   2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)}
 247   0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)}
 248   0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
 249 }
 250
 251 finish_test