| OLD | NEW |
|---|
| (Empty) |
| 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 # EVIDENCE-OF: R-34271-33106 PRAGMA automatic_index; PRAGMA | |
| 15 # automatic_index = boolean; Query, set, or clear the automatic indexing | |
| 16 # capability. | |
| 17 | |
| 18 set testdir [file dirname $argv0] | |
| 19 source $testdir/tester.tcl | |
| 20 | |
| 21 # If the library is not compiled with automatic index support then | |
| 22 # skip all tests in this file. | |
| 23 # | |
| 24 ifcapable {!autoindex} { | |
| 25 finish_test | |
| 26 return | |
| 27 } | |
| 28 | |
| 29 # Setup for logging | |
| 30 db close | |
| 31 sqlite3_shutdown | |
| 32 test_sqlite3_log [list lappend ::log] | |
| 33 set ::log [list] | |
| 34 sqlite3 db test.db | |
| 35 | |
| 36 | |
| 37 # With automatic index turned off, we do a full scan of the T2 table | |
| 38 do_test autoindex1-100 { | |
| 39 db eval { | |
| 40 CREATE TABLE t1(a,b); | |
| 41 INSERT INTO t1 VALUES(1,11); | |
| 42 INSERT INTO t1 VALUES(2,22); | |
| 43 INSERT INTO t1 SELECT a+2, b+22 FROM t1; | |
| 44 INSERT INTO t1 SELECT a+4, b+44 FROM t1; | |
| 45 CREATE TABLE t2(c,d); | |
| 46 INSERT INTO t2 SELECT a, 900+b FROM t1; | |
| 47 } | |
| 48 db eval { | |
| 49 PRAGMA automatic_index=OFF; | |
| 50 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; | |
| 51 } | |
| 52 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 53 do_test autoindex1-101 { | |
| 54 db status step | |
| 55 } {63} | |
| 56 do_test autoindex1-102 { | |
| 57 db status autoindex | |
| 58 } {0} | |
| 59 | |
| 60 # With autoindex turned on, we build an index once and then use that index | |
| 61 # to find T2 values. | |
| 62 do_test autoindex1-110 { | |
| 63 db eval { | |
| 64 PRAGMA automatic_index=ON; | |
| 65 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; | |
| 66 } | |
| 67 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 68 do_test autoindex1-111 { | |
| 69 db status step | |
| 70 } {7} | |
| 71 do_test autoindex1-112 { | |
| 72 db status autoindex | |
| 73 } {7} | |
| 74 do_test autoindex1-113 { | |
| 75 set ::log | |
| 76 } {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} | |
| 77 | |
| 78 db close | |
| 79 sqlite3_shutdown | |
| 80 test_sqlite3_log | |
| 81 sqlite3_initialize | |
| 82 sqlite3 db test.db | |
| 83 | |
| 84 # The same test as above, but this time the T2 query is a subquery rather | |
| 85 # than a join. | |
| 86 do_test autoindex1-200 { | |
| 87 db eval { | |
| 88 PRAGMA automatic_index=OFF; | |
| 89 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; | |
| 90 } | |
| 91 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 92 do_test autoindex1-201 { | |
| 93 db status step | |
| 94 } {35} | |
| 95 do_test autoindex1-202 { | |
| 96 db status autoindex | |
| 97 } {0} | |
| 98 do_test autoindex1-210 { | |
| 99 db eval { | |
| 100 PRAGMA automatic_index=ON; | |
| 101 ANALYZE; | |
| 102 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; | |
| 103 -- Table t2 actually contains 8 rows. | |
| 104 UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2'; | |
| 105 ANALYZE sqlite_master; | |
| 106 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; | |
| 107 } | |
| 108 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 109 do_test autoindex1-211 { | |
| 110 db status step | |
| 111 } {7} | |
| 112 do_test autoindex1-212 { | |
| 113 db status autoindex | |
| 114 } {7} | |
| 115 | |
| 116 | |
| 117 # Modify the second table of the join while the join is in progress | |
| 118 # | |
| 119 do_execsql_test autoindex1-299 { | |
| 120 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2'; | |
| 121 ANALYZE sqlite_master; | |
| 122 EXPLAIN QUERY PLAN | |
| 123 SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a); | |
| 124 } {/AUTOMATIC COVERING INDEX/} | |
| 125 do_test autoindex1-300 { | |
| 126 set r {} | |
| 127 db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} { | |
| 128 lappend r $b $d | |
| 129 db eval {UPDATE t2 SET d=d+1} | |
| 130 } | |
| 131 set r | |
| 132 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 133 do_test autoindex1-310 { | |
| 134 db eval {SELECT d FROM t2 ORDER BY d} | |
| 135 } {919 930 941 952 963 974 985 996} | |
| 136 | |
| 137 # The next test does a 10-way join on unindexed tables. Without | |
| 138 # automatic indices, the join will take a long time to complete. | |
| 139 # With automatic indices, it should only take about a second. | |
| 140 # | |
| 141 do_test autoindex1-400 { | |
| 142 db eval { | |
| 143 CREATE TABLE t4(a, b); | |
| 144 INSERT INTO t4 VALUES(1,2); | |
| 145 INSERT INTO t4 VALUES(2,3); | |
| 146 } | |
| 147 for {set n 2} {$n<4096} {set n [expr {$n+$n}]} { | |
| 148 db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4} | |
| 149 } | |
| 150 db eval { | |
| 151 SELECT count(*) FROM t4; | |
| 152 } | |
| 153 } {4096} | |
| 154 do_test autoindex1-401 { | |
| 155 db eval { | |
| 156 SELECT count(*) | |
| 157 FROM t4 AS x1 | |
| 158 JOIN t4 AS x2 ON x2.a=x1.b | |
| 159 JOIN t4 AS x3 ON x3.a=x2.b | |
| 160 JOIN t4 AS x4 ON x4.a=x3.b | |
| 161 JOIN t4 AS x5 ON x5.a=x4.b | |
| 162 JOIN t4 AS x6 ON x6.a=x5.b | |
| 163 JOIN t4 AS x7 ON x7.a=x6.b | |
| 164 JOIN t4 AS x8 ON x8.a=x7.b | |
| 165 JOIN t4 AS x9 ON x9.a=x8.b | |
| 166 JOIN t4 AS x10 ON x10.a=x9.b; | |
| 167 } | |
| 168 } {4087} | |
| 169 | |
| 170 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 | |
| 171 # Make sure automatic indices are not created for the RHS of an IN expression | |
| 172 # that is not a correlated subquery. | |
| 173 # | |
| 174 do_execsql_test autoindex1-500 { | |
| 175 CREATE TABLE t501(a INTEGER PRIMARY KEY, b); | |
| 176 CREATE TABLE t502(x INTEGER PRIMARY KEY, y); | |
| 177 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); | |
| 178 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); | |
| 179 ANALYZE sqlite_master; | |
| 180 EXPLAIN QUERY PLAN | |
| 181 SELECT b FROM t501 | |
| 182 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); | |
| 183 } { | |
| 184 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 185 0 0 0 {EXECUTE LIST SUBQUERY 1} | |
| 186 1 0 0 {SCAN TABLE t502} | |
| 187 } | |
| 188 do_execsql_test autoindex1-501 { | |
| 189 EXPLAIN QUERY PLAN | |
| 190 SELECT b FROM t501 | |
| 191 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); | |
| 192 } { | |
| 193 0 0 0 {SCAN TABLE t501} | |
| 194 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} | |
| 195 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} | |
| 196 } | |
| 197 do_execsql_test autoindex1-502 { | |
| 198 EXPLAIN QUERY PLAN | |
| 199 SELECT b FROM t501 | |
| 200 WHERE t501.a=123 | |
| 201 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); | |
| 202 } { | |
| 203 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 204 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} | |
| 205 1 0 0 {SCAN TABLE t502} | |
| 206 } | |
| 207 | |
| 208 | |
| 209 # The following code checks a performance regression reported on the | |
| 210 # mailing list on 2010-10-19. The problem is that the nRowEst field | |
| 211 # of ephermeral tables was not being initialized correctly and so no | |
| 212 # automatic index was being created for the emphemeral table when it was | |
| 213 # used as part of a join. | |
| 214 # | |
| 215 do_execsql_test autoindex1-600 { | |
| 216 CREATE TABLE flock_owner( | |
| 217 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, | |
| 218 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), | |
| 219 owner_person_id INTEGER NOT NULL REFERENCES person (person_id), | |
| 220 owner_change_date TEXT, last_changed TEXT NOT NULL, | |
| 221 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) | |
| 222 ); | |
| 223 CREATE TABLE sheep ( | |
| 224 Sheep_No char(7) NOT NULL, | |
| 225 Date_of_Birth char(8), | |
| 226 Sort_DoB text, | |
| 227 Flock_Book_Vol char(2), | |
| 228 Breeder_No char(6), | |
| 229 Breeder_Person integer, | |
| 230 Originating_Flock char(6), | |
| 231 Registering_Flock char(6), | |
| 232 Tag_Prefix char(9), | |
| 233 Tag_No char(15), | |
| 234 Sort_Tag_No integer, | |
| 235 Breeders_Temp_Tag char(15), | |
| 236 Sex char(1), | |
| 237 Sheep_Name char(32), | |
| 238 Sire_No char(7), | |
| 239 Dam_No char(7), | |
| 240 Register_Code char(1), | |
| 241 Colour char(48), | |
| 242 Colour_Code char(2), | |
| 243 Pattern_Code char(8), | |
| 244 Horns char(1), | |
| 245 Litter_Size char(1), | |
| 246 Coeff_of_Inbreeding real, | |
| 247 Date_of_Registration text, | |
| 248 Date_Last_Changed text, | |
| 249 UNIQUE(Sheep_No)); | |
| 250 CREATE INDEX fo_flock_no_index | |
| 251 ON flock_owner (flock_no); | |
| 252 CREATE INDEX fo_owner_change_date_index | |
| 253 ON flock_owner (owner_change_date); | |
| 254 CREATE INDEX fo_owner_person_id_index | |
| 255 ON flock_owner (owner_person_id); | |
| 256 CREATE INDEX sheep_org_flock_index | |
| 257 ON sheep (originating_flock); | |
| 258 CREATE INDEX sheep_reg_flock_index | |
| 259 ON sheep (registering_flock); | |
| 260 EXPLAIN QUERY PLAN | |
| 261 SELECT x.sheep_no, x.registering_flock, x.date_of_registration | |
| 262 FROM sheep x LEFT JOIN | |
| 263 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, | |
| 264 s.date_of_registration, prev.owner_change_date | |
| 265 FROM sheep s JOIN flock_owner prev ON s.registering_flock = | |
| 266 prev.flock_no | |
| 267 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') | |
| 268 WHERE NOT EXISTS | |
| 269 (SELECT 'x' FROM flock_owner later | |
| 270 WHERE prev.flock_no = later.flock_no | |
| 271 AND later.owner_change_date > prev.owner_change_date | |
| 272 AND later.owner_change_date <= s.date_of_registration||' 00:00:00') | |
| 273 ) y ON x.sheep_no = y.sheep_no | |
| 274 WHERE y.sheep_no IS NULL | |
| 275 ORDER BY x.registering_flock; | |
| 276 } { | |
| 277 1 0 0 {SCAN TABLE sheep AS s} | |
| 278 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_own
er_1 (flock_no=? AND owner_change_date<?)} | |
| 279 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} | |
| 280 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<?)} | |
| 281 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} | |
| 282 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} | |
| 283 } | |
| 284 | |
| 285 | |
| 286 do_execsql_test autoindex1-700 { | |
| 287 CREATE TABLE t5(a, b, c); | |
| 288 EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; | |
| 289 } { | |
| 290 0 0 0 {SCAN TABLE t5} | |
| 291 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 292 } | |
| 293 | |
| 294 # The following checks a performance issue reported on the sqlite-dev | |
| 295 # mailing list on 2013-01-10 | |
| 296 # | |
| 297 do_execsql_test autoindex1-800 { | |
| 298 CREATE TABLE accounts( | |
| 299 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 300 account_name TEXT, | |
| 301 account_type TEXT, | |
| 302 data_set TEXT | |
| 303 ); | |
| 304 CREATE TABLE data( | |
| 305 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 306 package_id INTEGER REFERENCES package(_id), | |
| 307 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL, | |
| 308 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL, | |
| 309 is_read_only INTEGER NOT NULL DEFAULT 0, | |
| 310 is_primary INTEGER NOT NULL DEFAULT 0, | |
| 311 is_super_primary INTEGER NOT NULL DEFAULT 0, | |
| 312 data_version INTEGER NOT NULL DEFAULT 0, | |
| 313 data1 TEXT, | |
| 314 data2 TEXT, | |
| 315 data3 TEXT, | |
| 316 data4 TEXT, | |
| 317 data5 TEXT, | |
| 318 data6 TEXT, | |
| 319 data7 TEXT, | |
| 320 data8 TEXT, | |
| 321 data9 TEXT, | |
| 322 data10 TEXT, | |
| 323 data11 TEXT, | |
| 324 data12 TEXT, | |
| 325 data13 TEXT, | |
| 326 data14 TEXT, | |
| 327 data15 TEXT, | |
| 328 data_sync1 TEXT, | |
| 329 data_sync2 TEXT, | |
| 330 data_sync3 TEXT, | |
| 331 data_sync4 TEXT | |
| 332 ); | |
| 333 CREATE TABLE mimetypes( | |
| 334 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 335 mimetype TEXT NOT NULL | |
| 336 ); | |
| 337 CREATE TABLE raw_contacts( | |
| 338 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 339 account_id INTEGER REFERENCES accounts(_id), | |
| 340 sourceid TEXT, | |
| 341 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0, | |
| 342 version INTEGER NOT NULL DEFAULT 1, | |
| 343 dirty INTEGER NOT NULL DEFAULT 0, | |
| 344 deleted INTEGER NOT NULL DEFAULT 0, | |
| 345 contact_id INTEGER REFERENCES contacts(_id), | |
| 346 aggregation_mode INTEGER NOT NULL DEFAULT 0, | |
| 347 aggregation_needed INTEGER NOT NULL DEFAULT 1, | |
| 348 custom_ringtone TEXT, | |
| 349 send_to_voicemail INTEGER NOT NULL DEFAULT 0, | |
| 350 times_contacted INTEGER NOT NULL DEFAULT 0, | |
| 351 last_time_contacted INTEGER, | |
| 352 starred INTEGER NOT NULL DEFAULT 0, | |
| 353 display_name TEXT, | |
| 354 display_name_alt TEXT, | |
| 355 display_name_source INTEGER NOT NULL DEFAULT 0, | |
| 356 phonetic_name TEXT, | |
| 357 phonetic_name_style TEXT, | |
| 358 sort_key TEXT, | |
| 359 sort_key_alt TEXT, | |
| 360 name_verified INTEGER NOT NULL DEFAULT 0, | |
| 361 sync1 TEXT, | |
| 362 sync2 TEXT, | |
| 363 sync3 TEXT, | |
| 364 sync4 TEXT, | |
| 365 sync_uid TEXT, | |
| 366 sync_version INTEGER NOT NULL DEFAULT 1, | |
| 367 has_calendar_event INTEGER NOT NULL DEFAULT 0, | |
| 368 modified_time INTEGER, | |
| 369 is_restricted INTEGER DEFAULT 0, | |
| 370 yp_source TEXT, | |
| 371 method_selected INTEGER DEFAULT 0, | |
| 372 custom_vibration_type INTEGER DEFAULT 0, | |
| 373 custom_ringtone_path TEXT, | |
| 374 message_notification TEXT, | |
| 375 message_notification_path TEXT | |
| 376 ); | |
| 377 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); | |
| 378 CREATE INDEX data_raw_contact_id ON data (raw_contact_id); | |
| 379 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); | |
| 380 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); | |
| 381 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); | |
| 382 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); | |
| 383 CREATE INDEX raw_contacts_source_id_account_id_index | |
| 384 ON raw_contacts (sourceid, account_id); | |
| 385 ANALYZE sqlite_master; | |
| 386 INSERT INTO sqlite_stat1 | |
| 387 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4'); | |
| 388 INSERT INTO sqlite_stat1 | |
| 389 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4'); | |
| 390 INSERT INTO sqlite_stat1 | |
| 391 VALUES('raw_contacts','raw_contacts_source_id_account_id_index', | |
| 392 '1600 1600 1600'); | |
| 393 INSERT INTO sqlite_stat1 | |
| 394 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1'); | |
| 395 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1'); | |
| 396 INSERT INTO sqlite_stat1 | |
| 397 VALUES('data','data_mimetype_data1_index','9819 2455 3'); | |
| 398 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7'); | |
| 399 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1'); | |
| 400 DROP TABLE IF EXISTS sqlite_stat3; | |
| 401 ANALYZE sqlite_master; | |
| 402 | |
| 403 EXPLAIN QUERY PLAN | |
| 404 SELECT * FROM | |
| 405 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) | |
| 406 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) | |
| 407 JOIN accounts ON (raw_contacts.account_id=accounts._id) | |
| 408 WHERE mimetype_id=10 AND data14 IS NOT NULL; | |
| 409 } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} | |
| 410 do_execsql_test autoindex1-801 { | |
| 411 EXPLAIN QUERY PLAN | |
| 412 SELECT * FROM | |
| 413 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) | |
| 414 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) | |
| 415 JOIN accounts ON (raw_contacts.account_id=accounts._id) | |
| 416 WHERE mimetypes._id=10 AND data14 IS NOT NULL; | |
| 417 } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} | |
| 418 | |
| 419 # Another test case from an important user of SQLite. The key feature of | |
| 420 # this test is that the "aggindex" subquery should make use of an | |
| 421 # automatic index. If it does, the query is fast. If it does not, the | |
| 422 # query is deathly slow. It worked OK in 3.7.17 but started going slow | |
| 423 # with version 3.8.0. The problem was fixed for 3.8.7 by reducing the | |
| 424 # cost estimate for automatic indexes on views and subqueries. | |
| 425 # | |
| 426 db close | |
| 427 forcedelete test.db | |
| 428 sqlite3 db test.db | |
| 429 do_execsql_test autoindex1-900 { | |
| 430 CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id, do
cument_id BLOB, in_reply_to, remote_id INTEGER, sender INTEGER, subject_prefix,
subject INTEGER, date_sent INTEGER, date_received INTEGER, date_created INTEGER,
date_last_viewed INTEGER, mailbox INTEGER, remote_mailbox INTEGER, original_mai
lbox INTEGER, flags INTEGER, read, flagged, size INTEGER, color, encoding, type
INTEGER, pad, conversation_id INTEGER DEFAULT -1, snippet TEXT DEFAULT NULL, fuz
zy_ancestor INTEGER DEFAULT NULL, automated_conversation INTEGER DEFAULT 0, root
_status INTEGER DEFAULT -1, conversation_position INTEGER DEFAULT -1); | |
| 431 CREATE INDEX date_index ON messages(date_received); | |
| 432 CREATE INDEX date_last_viewed_index ON messages(date_last_viewed); | |
| 433 CREATE INDEX date_created_index ON messages(date_created); | |
| 434 CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox)
; | |
| 435 CREATE INDEX message_document_id_index ON messages(document_id); | |
| 436 CREATE INDEX message_read_index ON messages(read); | |
| 437 CREATE INDEX message_flagged_index ON messages(flagged); | |
| 438 CREATE INDEX message_mailbox_index ON messages(mailbox, date_received); | |
| 439 CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_i
d); | |
| 440 CREATE INDEX message_type_index ON messages(type); | |
| 441 CREATE INDEX message_conversation_id_conversation_position_index ON messages(c
onversation_id, conversation_position); | |
| 442 CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor); | |
| 443 CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_a
ncestor); | |
| 444 CREATE INDEX message_sender_subject_automated_conversation_index ON messages(s
ender, subject, automated_conversation); | |
| 445 CREATE INDEX message_sender_index ON messages(sender); | |
| 446 CREATE INDEX message_root_status ON messages(root_status); | |
| 447 CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, norma
lized_subject COLLATE RTRIM); | |
| 448 CREATE INDEX subject_subject_index ON subjects(subject); | |
| 449 CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject); | |
| 450 CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, com
ment, UNIQUE(address, comment)); | |
| 451 CREATE INDEX addresses_address_index ON addresses(address); | |
| 452 CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY, url UNIQUE, total_count INT
EGER DEFAULT 0, unread_count INTEGER DEFAULT 0, unseen_count INTEGER DEFAULT 0,
deleted_count INTEGER DEFAULT 0, unread_count_adjusted_for_duplicates INTEGER DE
FAULT 0, change_identifier, source INTEGER, alleged_change_identifier); | |
| 453 CREATE INDEX mailboxes_source_index ON mailboxes(source); | |
| 454 CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, m
ailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id)); | |
| 455 CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_
id); | |
| 456 CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id); | |
| 457 | |
| 458 explain query plan | |
| 459 SELECT messages.ROWID, | |
| 460 messages.message_id, | |
| 461 messages.remote_id, | |
| 462 messages.date_received, | |
| 463 messages.date_sent, | |
| 464 messages.flags, | |
| 465 messages.size, | |
| 466 messages.color, | |
| 467 messages.date_last_viewed, | |
| 468 messages.subject_prefix, | |
| 469 subjects.subject, | |
| 470 sender.comment, | |
| 471 sender.address, | |
| 472 NULL, | |
| 473 messages.mailbox, | |
| 474 messages.original_mailbox, | |
| 475 NULL, | |
| 476 NULL, | |
| 477 messages.type, | |
| 478 messages.document_id, | |
| 479 sender, | |
| 480 NULL, | |
| 481 messages.conversation_id, | |
| 482 messages.conversation_position, | |
| 483 agglabels.labels | |
| 484 FROM mailboxes AS mailbox | |
| 485 JOIN messages ON mailbox.ROWID = messages.mailbox | |
| 486 LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID | |
| 487 LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID | |
| 488 LEFT OUTER JOIN ( | |
| 489 SELECT message_id, group_concat(mailbox_id) as labels | |
| 490 FROM labels GROUP BY message_id | |
| 491 ) AS agglabels ON messages.ROWID = agglabels.message_id | |
| 492 WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail') | |
| 493 AND (messages.ROWID IN ( | |
| 494 SELECT labels.message_id | |
| 495 FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID | |
| 496 WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX')) | |
| 497 AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8, | |
| 498 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28, | |
| 499 34,40,5,11,17,23,35,41) | |
| 500 ORDER BY date_received DESC; | |
| 501 } {/agglabels USING AUTOMATIC COVERING INDEX/} | |
| 502 | |
| 503 # A test case for VIEWs | |
| 504 # | |
| 505 do_execsql_test autoindex1-901 { | |
| 506 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z); | |
| 507 CREATE TABLE t2(a, b); | |
| 508 CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a; | |
| 509 EXPLAIN QUERY PLAN | |
| 510 SELECT t1.z, agg2.m | |
| 511 FROM t1 JOIN agg2 ON t1.y=agg2.m | |
| 512 WHERE t1.x IN (1,2,3); | |
| 513 } {/USING AUTOMATIC COVERING INDEX/} | |
| 514 | |
| 515 # 2015-04-15: A NULL CollSeq pointer in automatic index creation. | |
| 516 # | |
| 517 do_execsql_test autoindex1-920 { | |
| 518 CREATE TABLE t920(x); | |
| 519 INSERT INTO t920 VALUES(3),(4),(5); | |
| 520 SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x); | |
| 521 } {5 0 9 5 0 9 5 0 9} | |
| 522 | |
| 523 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
