# 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 } # Setup for logging db close sqlite3_shutdown test_sqlite3_log [list lappend ::log] set ::log [list] sqlite3 db test.db # 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} do_test autoindex1-113 { set ::log } {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} db close sqlite3_shutdown test_sqlite3_log sqlite3_initialize sqlite3 db test.db # 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; ANALYZE; UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; ANALYZE sqlite_master; 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_execsql_test autoindex1-299 { UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2'; ANALYZE sqlite_master; EXPLAIN QUERY PLAN SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a); } {/AUTOMATIC COVERING INDEX/} do_test autoindex1-300 { set r {} db eval {SELECT b, d FROM t1 CROSS 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); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); ANALYZE sqlite_master; 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=?)} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE t502} } 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} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} } 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=?)} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 1 0 0 {SCAN TABLE t502} } # 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} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date? AND owner_change_date