1 files changed, 261 insertions, 0 deletions
diff --git a/test/autoindex1.test b/test/autoindex1.test
new file mode 100644
index 0000000..6c8d54d
--- /dev/null
+++ b/test/autoindex1.test
@@ -0,0 +1,261 @@
+# 2010 April 07
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this script is testing automatic index creation logic.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# If the library is not compiled with automatic index support then
+# skip all tests in this file.
+#
+ifcapable {!autoindex} {
+  finish_test
+  return
+}
+
+# With automatic index turned off, we do a full scan of the T2 table
+do_test autoindex1-100 {
+  db eval {
+    CREATE TABLE t1(a,b);
+    INSERT INTO t1 VALUES(1,11);
+    INSERT INTO t1 VALUES(2,22);
+    INSERT INTO t1 SELECT a+2, b+22 FROM t1;
+    INSERT INTO t1 SELECT a+4, b+44 FROM t1;
+    CREATE TABLE t2(c,d);
+    INSERT INTO t2 SELECT a, 900+b FROM t1;
+  }
+  db eval {
+    PRAGMA automatic_index=OFF;
+    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
+  }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-101 {
+  db status step
+} {63}
+do_test autoindex1-102 {
+  db status autoindex
+} {0}
+
+# With autoindex turned on, we build an index once and then use that index
+# to find T2 values.
+do_test autoindex1-110 {
+  db eval {
+    PRAGMA automatic_index=ON;
+    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
+  }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-111 {
+  db status step
+} {7}
+do_test autoindex1-112 {
+  db status autoindex
+} {7}
+
+# The same test as above, but this time the T2 query is a subquery rather
+# than a join.
+do_test autoindex1-200 {
+  db eval {
+    PRAGMA automatic_index=OFF;
+    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
+  }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-201 {
+  db status step
+} {35}
+do_test autoindex1-202 {
+  db status autoindex
+} {0}
+do_test autoindex1-210 {
+  db eval {
+    PRAGMA automatic_index=ON;
+    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
+  }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-211 {
+  db status step
+} {7}
+do_test autoindex1-212 {
+  db status autoindex
+} {7}
+
+
+# Modify the second table of the join while the join is in progress
+#
+do_test autoindex1-300 {
+  set r {}
+  db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
+    lappend r $b $d
+    db eval {UPDATE t2 SET d=d+1}
+  }
+  set r
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-310 {
+  db eval {SELECT d FROM t2 ORDER BY d}
+} {919 930 941 952 963 974 985 996}
+
+# The next test does a 10-way join on unindexed tables.  Without
+# automatic indices, the join will take a long time to complete.
+# With automatic indices, it should only take about a second.
+#
+do_test autoindex1-400 {
+  db eval {
+    CREATE TABLE t4(a, b);
+    INSERT INTO t4 VALUES(1,2);
+    INSERT INTO t4 VALUES(2,3);
+  }
+  for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
+    db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
+  }
+  db eval {
+    SELECT count(*) FROM t4;
+  }
+} {4096}
+do_test autoindex1-401 {
+  db eval {
+    SELECT count(*)
+      FROM t4 AS x1
+      JOIN t4 AS x2 ON x2.a=x1.b
+      JOIN t4 AS x3 ON x3.a=x2.b
+      JOIN t4 AS x4 ON x4.a=x3.b
+      JOIN t4 AS x5 ON x5.a=x4.b
+      JOIN t4 AS x6 ON x6.a=x5.b
+      JOIN t4 AS x7 ON x7.a=x6.b
+      JOIN t4 AS x8 ON x8.a=x7.b
+      JOIN t4 AS x9 ON x9.a=x8.b
+      JOIN t4 AS x10 ON x10.a=x9.b;
+  }
+} {4087}
+
+# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
+# Make sure automatic indices are not created for the RHS of an IN expression
+# that is not a correlated subquery.
+#
+do_execsql_test autoindex1-500 {
+  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
+  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
+  EXPLAIN QUERY PLAN
+  SELECT b FROM t501
+   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
+} {
+  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
+  0 0 0 {EXECUTE LIST SUBQUERY 1} 
+  1 0 0 {SCAN TABLE t502 (~100000 rows)}
+}
+do_execsql_test autoindex1-501 {
+  EXPLAIN QUERY PLAN
+  SELECT b FROM t501
+   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
+} {
+  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
+  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
+  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
+}
+do_execsql_test autoindex1-502 {
+  EXPLAIN QUERY PLAN
+  SELECT b FROM t501
+   WHERE t501.a=123
+     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
+} {
+  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
+  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
+  1 0 0 {SCAN TABLE t502 (~100000 rows)}
+}
+
+
+# The following code checks a performance regression reported on the
+# mailing list on 2010-10-19.  The problem is that the nRowEst field
+# of ephermeral tables was not being initialized correctly and so no
+# automatic index was being created for the emphemeral table when it was
+# used as part of a join.
+#
+do_execsql_test autoindex1-600 {
+  CREATE TABLE flock_owner(
+    owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
+    flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
+    owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
+    owner_change_date TEXT, last_changed TEXT NOT NULL,
+    CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
+  );
+  CREATE TABLE sheep (
+    Sheep_No char(7) NOT NULL,
+    Date_of_Birth char(8),
+    Sort_DoB text,
+    Flock_Book_Vol char(2),
+    Breeder_No char(6),
+    Breeder_Person integer,
+    Originating_Flock char(6),
+    Registering_Flock char(6),
+    Tag_Prefix char(9),
+    Tag_No char(15),
+    Sort_Tag_No integer,
+    Breeders_Temp_Tag char(15),
+    Sex char(1),
+    Sheep_Name char(32),
+    Sire_No char(7),
+    Dam_No char(7),
+    Register_Code char(1),
+    Colour char(48),
+    Colour_Code char(2),
+    Pattern_Code char(8),
+    Horns char(1),
+    Litter_Size char(1),
+    Coeff_of_Inbreeding real,
+    Date_of_Registration text,
+    Date_Last_Changed text,
+    UNIQUE(Sheep_No));
+  CREATE INDEX fo_flock_no_index  
+              ON flock_owner (flock_no);
+  CREATE INDEX fo_owner_change_date_index  
+              ON flock_owner (owner_change_date);
+  CREATE INDEX fo_owner_person_id_index  
+              ON flock_owner (owner_person_id);
+  CREATE INDEX sheep_org_flock_index  
+           ON sheep (originating_flock);
+  CREATE INDEX sheep_reg_flock_index  
+           ON sheep (registering_flock);
+  EXPLAIN QUERY PLAN
+  SELECT x.sheep_no, x.registering_flock, x.date_of_registration
+   FROM sheep x LEFT JOIN
+       (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
+       s.date_of_registration, prev.owner_change_date
+       FROM sheep s JOIN flock_owner prev ON s.registering_flock =
+   prev.flock_no
+       AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
+       WHERE NOT EXISTS
+           (SELECT 'x' FROM flock_owner later
+           WHERE prev.flock_no = later.flock_no
+           AND later.owner_change_date > prev.owner_change_date
+           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
+       ) y ON x.sheep_no = y.sheep_no
+   WHERE y.sheep_no IS NULL
+   ORDER BY x.registering_flock;
+} {
+  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
+  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 
+  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
+  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)} 
+  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
+  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
+}
+
+
+do_execsql_test autoindex1-700 {
+  CREATE TABLE t5(a, b, c);
+  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
+} {
+  0 0 0 {SCAN TABLE t5 (~100000 rows)} 
+  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
+}
+
+
+finish_test