| # 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<?)} |
| 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<?)} |
| 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} |
| 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} |
| } |
| |
| |
| 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} |
| 0 0 0 {USE TEMP B-TREE FOR ORDER BY} |
| } |
| |
| # The following checks a performance issue reported on the sqlite-dev |
| # mailing list on 2013-01-10 |
| # |
| do_execsql_test autoindex1-800 { |
| CREATE TABLE accounts( |
| _id INTEGER PRIMARY KEY AUTOINCREMENT, |
| account_name TEXT, |
| account_type TEXT, |
| data_set TEXT |
| ); |
| CREATE TABLE data( |
| _id INTEGER PRIMARY KEY AUTOINCREMENT, |
| package_id INTEGER REFERENCES package(_id), |
| mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL, |
| raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL, |
| is_read_only INTEGER NOT NULL DEFAULT 0, |
| is_primary INTEGER NOT NULL DEFAULT 0, |
| is_super_primary INTEGER NOT NULL DEFAULT 0, |
| data_version INTEGER NOT NULL DEFAULT 0, |
| data1 TEXT, |
| data2 TEXT, |
| data3 TEXT, |
| data4 TEXT, |
| data5 TEXT, |
| data6 TEXT, |
| data7 TEXT, |
| data8 TEXT, |
| data9 TEXT, |
| data10 TEXT, |
| data11 TEXT, |
| data12 TEXT, |
| data13 TEXT, |
| data14 TEXT, |
| data15 TEXT, |
| data_sync1 TEXT, |
| data_sync2 TEXT, |
| data_sync3 TEXT, |
| data_sync4 TEXT |
| ); |
| CREATE TABLE mimetypes( |
| _id INTEGER PRIMARY KEY AUTOINCREMENT, |
| mimetype TEXT NOT NULL |
| ); |
| CREATE TABLE raw_contacts( |
| _id INTEGER PRIMARY KEY AUTOINCREMENT, |
| account_id INTEGER REFERENCES accounts(_id), |
| sourceid TEXT, |
| raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0, |
| version INTEGER NOT NULL DEFAULT 1, |
| dirty INTEGER NOT NULL DEFAULT 0, |
| deleted INTEGER NOT NULL DEFAULT 0, |
| contact_id INTEGER REFERENCES contacts(_id), |
| aggregation_mode INTEGER NOT NULL DEFAULT 0, |
| aggregation_needed INTEGER NOT NULL DEFAULT 1, |
| custom_ringtone TEXT, |
| send_to_voicemail INTEGER NOT NULL DEFAULT 0, |
| times_contacted INTEGER NOT NULL DEFAULT 0, |
| last_time_contacted INTEGER, |
| starred INTEGER NOT NULL DEFAULT 0, |
| display_name TEXT, |
| display_name_alt TEXT, |
| display_name_source INTEGER NOT NULL DEFAULT 0, |
| phonetic_name TEXT, |
| phonetic_name_style TEXT, |
| sort_key TEXT, |
| sort_key_alt TEXT, |
| name_verified INTEGER NOT NULL DEFAULT 0, |
| sync1 TEXT, |
| sync2 TEXT, |
| sync3 TEXT, |
| sync4 TEXT, |
| sync_uid TEXT, |
| sync_version INTEGER NOT NULL DEFAULT 1, |
| has_calendar_event INTEGER NOT NULL DEFAULT 0, |
| modified_time INTEGER, |
| is_restricted INTEGER DEFAULT 0, |
| yp_source TEXT, |
| method_selected INTEGER DEFAULT 0, |
| custom_vibration_type INTEGER DEFAULT 0, |
| custom_ringtone_path TEXT, |
| message_notification TEXT, |
| message_notification_path TEXT |
| ); |
| CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); |
| CREATE INDEX data_raw_contact_id ON data (raw_contact_id); |
| CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); |
| CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); |
| CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); |
| CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); |
| CREATE INDEX raw_contacts_source_id_account_id_index |
| ON raw_contacts (sourceid, account_id); |
| ANALYZE sqlite_master; |
| INSERT INTO sqlite_stat1 |
| VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4'); |
| INSERT INTO sqlite_stat1 |
| VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4'); |
| INSERT INTO sqlite_stat1 |
| VALUES('raw_contacts','raw_contacts_source_id_account_id_index', |
| '1600 1600 1600'); |
| INSERT INTO sqlite_stat1 |
| VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1'); |
| INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1'); |
| INSERT INTO sqlite_stat1 |
| VALUES('data','data_mimetype_data1_index','9819 2455 3'); |
| INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7'); |
| INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1'); |
| DROP TABLE IF EXISTS sqlite_stat3; |
| ANALYZE sqlite_master; |
| |
| EXPLAIN QUERY PLAN |
| SELECT * FROM |
| data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) |
| JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) |
| JOIN accounts ON (raw_contacts.account_id=accounts._id) |
| WHERE mimetype_id=10 AND data14 IS NOT NULL; |
| } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} |
| do_execsql_test autoindex1-801 { |
| EXPLAIN QUERY PLAN |
| SELECT * FROM |
| data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) |
| JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) |
| JOIN accounts ON (raw_contacts.account_id=accounts._id) |
| WHERE mimetypes._id=10 AND data14 IS NOT NULL; |
| } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} |
| |
| finish_test |