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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18# If the library is not compiled with automatic index support then 19# skip all tests in this file. 20# 21ifcapable {!autoindex} { 22 finish_test 23 return 24} 25 26# Setup for logging 27db close 28sqlite3_shutdown 29test_sqlite3_log [list lappend ::log] 30set ::log [list] 31sqlite3 db test.db 32 33 34# With automatic index turned off, we do a full scan of the T2 table 35do_test autoindex1-100 { 36 db eval { 37 CREATE TABLE t1(a,b); 38 INSERT INTO t1 VALUES(1,11); 39 INSERT INTO t1 VALUES(2,22); 40 INSERT INTO t1 SELECT a+2, b+22 FROM t1; 41 INSERT INTO t1 SELECT a+4, b+44 FROM t1; 42 CREATE TABLE t2(c,d); 43 INSERT INTO t2 SELECT a, 900+b FROM t1; 44 } 45 db eval { 46 PRAGMA automatic_index=OFF; 47 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; 48 } 49} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} 50do_test autoindex1-101 { 51 db status step 52} {63} 53do_test autoindex1-102 { 54 db status autoindex 55} {0} 56 57# With autoindex turned on, we build an index once and then use that index 58# to find T2 values. 59do_test autoindex1-110 { 60 db eval { 61 PRAGMA automatic_index=ON; 62 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; 63 } 64} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} 65do_test autoindex1-111 { 66 db status step 67} {7} 68do_test autoindex1-112 { 69 db status autoindex 70} {7} 71do_test autoindex1-113 { 72 set ::log 73} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} 74 75db close 76sqlite3_shutdown 77test_sqlite3_log 78sqlite3_initialize 79sqlite3 db test.db 80 81# The same test as above, but this time the T2 query is a subquery rather 82# than a join. 83do_test autoindex1-200 { 84 db eval { 85 PRAGMA automatic_index=OFF; 86 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; 87 } 88} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} 89do_test autoindex1-201 { 90 db status step 91} {35} 92do_test autoindex1-202 { 93 db status autoindex 94} {0} 95do_test autoindex1-210 { 96 db eval { 97 PRAGMA automatic_index=ON; 98 ANALYZE; 99 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; 100 ANALYZE sqlite_master; 101 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; 102 } 103} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} 104do_test autoindex1-211 { 105 db status step 106} {7} 107do_test autoindex1-212 { 108 db status autoindex 109} {7} 110 111 112# Modify the second table of the join while the join is in progress 113# 114do_execsql_test autoindex1-299 { 115 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2'; 116 ANALYZE sqlite_master; 117 EXPLAIN QUERY PLAN 118 SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a); 119} {/AUTOMATIC COVERING INDEX/} 120do_test autoindex1-300 { 121 set r {} 122 db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} { 123 lappend r $b $d 124 db eval {UPDATE t2 SET d=d+1} 125 } 126 set r 127} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} 128do_test autoindex1-310 { 129 db eval {SELECT d FROM t2 ORDER BY d} 130} {919 930 941 952 963 974 985 996} 131 132# The next test does a 10-way join on unindexed tables. Without 133# automatic indices, the join will take a long time to complete. 134# With automatic indices, it should only take about a second. 135# 136do_test autoindex1-400 { 137 db eval { 138 CREATE TABLE t4(a, b); 139 INSERT INTO t4 VALUES(1,2); 140 INSERT INTO t4 VALUES(2,3); 141 } 142 for {set n 2} {$n<4096} {set n [expr {$n+$n}]} { 143 db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4} 144 } 145 db eval { 146 SELECT count(*) FROM t4; 147 } 148} {4096} 149do_test autoindex1-401 { 150 db eval { 151 SELECT count(*) 152 FROM t4 AS x1 153 JOIN t4 AS x2 ON x2.a=x1.b 154 JOIN t4 AS x3 ON x3.a=x2.b 155 JOIN t4 AS x4 ON x4.a=x3.b 156 JOIN t4 AS x5 ON x5.a=x4.b 157 JOIN t4 AS x6 ON x6.a=x5.b 158 JOIN t4 AS x7 ON x7.a=x6.b 159 JOIN t4 AS x8 ON x8.a=x7.b 160 JOIN t4 AS x9 ON x9.a=x8.b 161 JOIN t4 AS x10 ON x10.a=x9.b; 162 } 163} {4087} 164 165# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 166# Make sure automatic indices are not created for the RHS of an IN expression 167# that is not a correlated subquery. 168# 169do_execsql_test autoindex1-500 { 170 CREATE TABLE t501(a INTEGER PRIMARY KEY, b); 171 CREATE TABLE t502(x INTEGER PRIMARY KEY, y); 172 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); 173 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); 174 ANALYZE sqlite_master; 175 EXPLAIN QUERY PLAN 176 SELECT b FROM t501 177 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); 178} { 179 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 180 0 0 0 {EXECUTE LIST SUBQUERY 1} 181 1 0 0 {SCAN TABLE t502} 182} 183do_execsql_test autoindex1-501 { 184 EXPLAIN QUERY PLAN 185 SELECT b FROM t501 186 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 187} { 188 0 0 0 {SCAN TABLE t501} 189 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 190 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} 191} 192do_execsql_test autoindex1-502 { 193 EXPLAIN QUERY PLAN 194 SELECT b FROM t501 195 WHERE t501.a=123 196 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 197} { 198 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 199 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 200 1 0 0 {SCAN TABLE t502} 201} 202 203 204# The following code checks a performance regression reported on the 205# mailing list on 2010-10-19. The problem is that the nRowEst field 206# of ephermeral tables was not being initialized correctly and so no 207# automatic index was being created for the emphemeral table when it was 208# used as part of a join. 209# 210do_execsql_test autoindex1-600 { 211 CREATE TABLE flock_owner( 212 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, 213 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), 214 owner_person_id INTEGER NOT NULL REFERENCES person (person_id), 215 owner_change_date TEXT, last_changed TEXT NOT NULL, 216 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) 217 ); 218 CREATE TABLE sheep ( 219 Sheep_No char(7) NOT NULL, 220 Date_of_Birth char(8), 221 Sort_DoB text, 222 Flock_Book_Vol char(2), 223 Breeder_No char(6), 224 Breeder_Person integer, 225 Originating_Flock char(6), 226 Registering_Flock char(6), 227 Tag_Prefix char(9), 228 Tag_No char(15), 229 Sort_Tag_No integer, 230 Breeders_Temp_Tag char(15), 231 Sex char(1), 232 Sheep_Name char(32), 233 Sire_No char(7), 234 Dam_No char(7), 235 Register_Code char(1), 236 Colour char(48), 237 Colour_Code char(2), 238 Pattern_Code char(8), 239 Horns char(1), 240 Litter_Size char(1), 241 Coeff_of_Inbreeding real, 242 Date_of_Registration text, 243 Date_Last_Changed text, 244 UNIQUE(Sheep_No)); 245 CREATE INDEX fo_flock_no_index 246 ON flock_owner (flock_no); 247 CREATE INDEX fo_owner_change_date_index 248 ON flock_owner (owner_change_date); 249 CREATE INDEX fo_owner_person_id_index 250 ON flock_owner (owner_person_id); 251 CREATE INDEX sheep_org_flock_index 252 ON sheep (originating_flock); 253 CREATE INDEX sheep_reg_flock_index 254 ON sheep (registering_flock); 255 EXPLAIN QUERY PLAN 256 SELECT x.sheep_no, x.registering_flock, x.date_of_registration 257 FROM sheep x LEFT JOIN 258 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, 259 s.date_of_registration, prev.owner_change_date 260 FROM sheep s JOIN flock_owner prev ON s.registering_flock = 261 prev.flock_no 262 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') 263 WHERE NOT EXISTS 264 (SELECT 'x' FROM flock_owner later 265 WHERE prev.flock_no = later.flock_no 266 AND later.owner_change_date > prev.owner_change_date 267 AND later.owner_change_date <= s.date_of_registration||' 00:00:00') 268 ) y ON x.sheep_no = y.sheep_no 269 WHERE y.sheep_no IS NULL 270 ORDER BY x.registering_flock; 271} { 272 1 0 0 {SCAN TABLE sheep AS s} 273 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 274 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 275 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<?)} 276 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 277 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} 278} 279 280 281do_execsql_test autoindex1-700 { 282 CREATE TABLE t5(a, b, c); 283 EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; 284} { 285 0 0 0 {SCAN TABLE t5} 286 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 287} 288 289# The following checks a performance issue reported on the sqlite-dev 290# mailing list on 2013-01-10 291# 292do_execsql_test autoindex1-800 { 293 CREATE TABLE accounts( 294 _id INTEGER PRIMARY KEY AUTOINCREMENT, 295 account_name TEXT, 296 account_type TEXT, 297 data_set TEXT 298 ); 299 CREATE TABLE data( 300 _id INTEGER PRIMARY KEY AUTOINCREMENT, 301 package_id INTEGER REFERENCES package(_id), 302 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL, 303 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL, 304 is_read_only INTEGER NOT NULL DEFAULT 0, 305 is_primary INTEGER NOT NULL DEFAULT 0, 306 is_super_primary INTEGER NOT NULL DEFAULT 0, 307 data_version INTEGER NOT NULL DEFAULT 0, 308 data1 TEXT, 309 data2 TEXT, 310 data3 TEXT, 311 data4 TEXT, 312 data5 TEXT, 313 data6 TEXT, 314 data7 TEXT, 315 data8 TEXT, 316 data9 TEXT, 317 data10 TEXT, 318 data11 TEXT, 319 data12 TEXT, 320 data13 TEXT, 321 data14 TEXT, 322 data15 TEXT, 323 data_sync1 TEXT, 324 data_sync2 TEXT, 325 data_sync3 TEXT, 326 data_sync4 TEXT 327 ); 328 CREATE TABLE mimetypes( 329 _id INTEGER PRIMARY KEY AUTOINCREMENT, 330 mimetype TEXT NOT NULL 331 ); 332 CREATE TABLE raw_contacts( 333 _id INTEGER PRIMARY KEY AUTOINCREMENT, 334 account_id INTEGER REFERENCES accounts(_id), 335 sourceid TEXT, 336 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0, 337 version INTEGER NOT NULL DEFAULT 1, 338 dirty INTEGER NOT NULL DEFAULT 0, 339 deleted INTEGER NOT NULL DEFAULT 0, 340 contact_id INTEGER REFERENCES contacts(_id), 341 aggregation_mode INTEGER NOT NULL DEFAULT 0, 342 aggregation_needed INTEGER NOT NULL DEFAULT 1, 343 custom_ringtone TEXT, 344 send_to_voicemail INTEGER NOT NULL DEFAULT 0, 345 times_contacted INTEGER NOT NULL DEFAULT 0, 346 last_time_contacted INTEGER, 347 starred INTEGER NOT NULL DEFAULT 0, 348 display_name TEXT, 349 display_name_alt TEXT, 350 display_name_source INTEGER NOT NULL DEFAULT 0, 351 phonetic_name TEXT, 352 phonetic_name_style TEXT, 353 sort_key TEXT, 354 sort_key_alt TEXT, 355 name_verified INTEGER NOT NULL DEFAULT 0, 356 sync1 TEXT, 357 sync2 TEXT, 358 sync3 TEXT, 359 sync4 TEXT, 360 sync_uid TEXT, 361 sync_version INTEGER NOT NULL DEFAULT 1, 362 has_calendar_event INTEGER NOT NULL DEFAULT 0, 363 modified_time INTEGER, 364 is_restricted INTEGER DEFAULT 0, 365 yp_source TEXT, 366 method_selected INTEGER DEFAULT 0, 367 custom_vibration_type INTEGER DEFAULT 0, 368 custom_ringtone_path TEXT, 369 message_notification TEXT, 370 message_notification_path TEXT 371 ); 372 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); 373 CREATE INDEX data_raw_contact_id ON data (raw_contact_id); 374 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); 375 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); 376 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); 377 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); 378 CREATE INDEX raw_contacts_source_id_account_id_index 379 ON raw_contacts (sourceid, account_id); 380 ANALYZE sqlite_master; 381 INSERT INTO sqlite_stat1 382 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4'); 383 INSERT INTO sqlite_stat1 384 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4'); 385 INSERT INTO sqlite_stat1 386 VALUES('raw_contacts','raw_contacts_source_id_account_id_index', 387 '1600 1600 1600'); 388 INSERT INTO sqlite_stat1 389 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1'); 390 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1'); 391 INSERT INTO sqlite_stat1 392 VALUES('data','data_mimetype_data1_index','9819 2455 3'); 393 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7'); 394 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1'); 395 DROP TABLE IF EXISTS sqlite_stat3; 396 ANALYZE sqlite_master; 397 398 EXPLAIN QUERY PLAN 399 SELECT * FROM 400 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 401 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 402 JOIN accounts ON (raw_contacts.account_id=accounts._id) 403 WHERE mimetype_id=10 AND data14 IS NOT NULL; 404} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} 405do_execsql_test autoindex1-801 { 406 EXPLAIN QUERY PLAN 407 SELECT * FROM 408 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 409 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 410 JOIN accounts ON (raw_contacts.account_id=accounts._id) 411 WHERE mimetypes._id=10 AND data14 IS NOT NULL; 412} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} 413 414finish_test 415