000001 # 2010 April 07
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #*************************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this script is testing automatic index creation logic.
000013 #
000014 # EVIDENCE-OF: R-34271-33106 PRAGMA automatic_index; PRAGMA
000015 # automatic_index = boolean; Query, set, or clear the automatic indexing
000016 # capability.
000017
000018 set testdir [file dirname $argv0]
000019 source $testdir/tester.tcl
000020
000021 # If the library is not compiled with automatic index support then
000022 # skip all tests in this file.
000023 #
000024 ifcapable {!autoindex} {
000025 finish_test
000026 return
000027 }
000028
000029 # Setup for logging
000030 db close
000031 sqlite3_shutdown
000032 test_sqlite3_log [list lappend ::log]
000033 set ::log [list]
000034 sqlite3 db test.db
000035
000036
000037 # With automatic index turned off, we do a full scan of the T2 table
000038 do_test autoindex1-100 {
000039 db eval {
000040 CREATE TABLE t1(a,b);
000041 INSERT INTO t1 VALUES(1,11);
000042 INSERT INTO t1 VALUES(2,22);
000043 INSERT INTO t1 SELECT a+2, b+22 FROM t1;
000044 INSERT INTO t1 SELECT a+4, b+44 FROM t1;
000045 CREATE TABLE t2(c,d);
000046 INSERT INTO t2 SELECT a, 900+b FROM t1;
000047 }
000048 db eval {
000049 PRAGMA automatic_index=OFF;
000050 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
000051 }
000052 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000053 do_test autoindex1-101 {
000054 db status step
000055 } {63}
000056 do_test autoindex1-102 {
000057 db status autoindex
000058 } {0}
000059
000060 # With autoindex turned on, we build an index once and then use that index
000061 # to find T2 values.
000062 do_test autoindex1-110 {
000063 db eval {
000064 PRAGMA automatic_index=ON;
000065 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
000066 }
000067 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000068 do_test autoindex1-111 {
000069 db status step
000070 } {7}
000071 do_test autoindex1-112 {
000072 db status autoindex
000073 } {7}
000074 do_test autoindex1-113 {
000075 set ::log
000076 } {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}}
000077
000078 db close
000079 sqlite3_shutdown
000080 test_sqlite3_log
000081 sqlite3_initialize
000082 sqlite3 db test.db
000083
000084 # The same test as above, but this time the T2 query is a subquery rather
000085 # than a join.
000086 do_test autoindex1-200 {
000087 db eval {
000088 PRAGMA automatic_index=OFF;
000089 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
000090 }
000091 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000092 do_test autoindex1-201 {
000093 db status step
000094 } {35}
000095 do_test autoindex1-202 {
000096 db status autoindex
000097 } {0}
000098 do_test autoindex1-210 {
000099 db eval {
000100 PRAGMA automatic_index=ON;
000101 ANALYZE;
000102 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1';
000103 -- Table t2 actually contains 8 rows.
000104 UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2';
000105 ANALYZE sqlite_master;
000106 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
000107 }
000108 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000109 do_test autoindex1-211 {
000110 db status step
000111 } {7}
000112 do_test autoindex1-212 {
000113 db status autoindex
000114 } {7}
000115
000116
000117 # Modify the second table of the join while the join is in progress
000118 #
000119 do_execsql_test autoindex1-299 {
000120 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2';
000121 ANALYZE sqlite_master;
000122 EXPLAIN QUERY PLAN
000123 SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a);
000124 } {/AUTOMATIC COVERING INDEX/}
000125 do_test autoindex1-300 {
000126 set r {}
000127 db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} {
000128 lappend r $b $d
000129 db eval {UPDATE t2 SET d=d+1}
000130 }
000131 set r
000132 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000133 do_test autoindex1-310 {
000134 db eval {SELECT d FROM t2 ORDER BY d}
000135 } {919 930 941 952 963 974 985 996}
000136
000137 # The next test does a 10-way join on unindexed tables. Without
000138 # automatic indices, the join will take a long time to complete.
000139 # With automatic indices, it should only take about a second.
000140 #
000141 do_test autoindex1-400 {
000142 db eval {
000143 CREATE TABLE t4(a, b);
000144 INSERT INTO t4 VALUES(1,2);
000145 INSERT INTO t4 VALUES(2,3);
000146 }
000147 for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
000148 db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
000149 }
000150 db eval {
000151 SELECT count(*) FROM t4;
000152 }
000153 } {4096}
000154 do_test autoindex1-401 {
000155 db eval {
000156 SELECT count(*)
000157 FROM t4 AS x1
000158 JOIN t4 AS x2 ON x2.a=x1.b
000159 JOIN t4 AS x3 ON x3.a=x2.b
000160 JOIN t4 AS x4 ON x4.a=x3.b
000161 JOIN t4 AS x5 ON x5.a=x4.b
000162 JOIN t4 AS x6 ON x6.a=x5.b
000163 JOIN t4 AS x7 ON x7.a=x6.b
000164 JOIN t4 AS x8 ON x8.a=x7.b
000165 JOIN t4 AS x9 ON x9.a=x8.b
000166 JOIN t4 AS x10 ON x10.a=x9.b;
000167 }
000168 } {4087}
000169
000170 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
000171 # Make sure automatic indices are not created for the RHS of an IN expression
000172 # that is not a correlated subquery.
000173 #
000174 do_execsql_test autoindex1-500 {
000175 CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
000176 CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
000177 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
000178 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
000179 ANALYZE sqlite_master;
000180 }
000181 do_eqp_test autoindex1-500.1 {
000182 SELECT b FROM t501
000183 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
000184 } {
000185 QUERY PLAN
000186 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
000187 `--LIST SUBQUERY xxxxxx
000188 |--SCAN t502
000189 `--CREATE BLOOM FILTER
000190 }
000191 do_eqp_test autoindex1-501 {
000192 SELECT b FROM t501
000193 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
000194 } {
000195 QUERY PLAN
000196 |--SCAN t501
000197 `--CORRELATED LIST SUBQUERY xxxxxx
000198 |--BLOOM FILTER ON t502 (y=?)
000199 `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)
000200 }
000201 do_eqp_test autoindex1-502 {
000202 SELECT b FROM t501
000203 WHERE t501.a=123
000204 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
000205 } {
000206 QUERY PLAN
000207 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
000208 `--CORRELATED LIST SUBQUERY xxxxxx
000209 `--SCAN t502
000210 }
000211
000212 # The following code checks a performance regression reported on the
000213 # mailing list on 2010-10-19. The problem is that the nRowEst field
000214 # of ephermeral tables was not being initialized correctly and so no
000215 # automatic index was being created for the emphemeral table when it was
000216 # used as part of a join.
000217 #
000218 do_execsql_test autoindex1-600 {
000219 CREATE TABLE flock_owner(
000220 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
000221 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
000222 owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
000223 owner_change_date TEXT, last_changed TEXT NOT NULL,
000224 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
000225 );
000226 CREATE TABLE sheep (
000227 Sheep_No char(7) NOT NULL,
000228 Date_of_Birth char(8),
000229 Sort_DoB text,
000230 Flock_Book_Vol char(2),
000231 Breeder_No char(6),
000232 Breeder_Person integer,
000233 Originating_Flock char(6),
000234 Registering_Flock char(6),
000235 Tag_Prefix char(9),
000236 Tag_No char(15),
000237 Sort_Tag_No integer,
000238 Breeders_Temp_Tag char(15),
000239 Sex char(1),
000240 Sheep_Name char(32),
000241 Sire_No char(7),
000242 Dam_No char(7),
000243 Register_Code char(1),
000244 Colour char(48),
000245 Colour_Code char(2),
000246 Pattern_Code char(8),
000247 Horns char(1),
000248 Litter_Size char(1),
000249 Coeff_of_Inbreeding real,
000250 Date_of_Registration text,
000251 Date_Last_Changed text,
000252 UNIQUE(Sheep_No));
000253 CREATE INDEX fo_flock_no_index
000254 ON flock_owner (flock_no);
000255 CREATE INDEX fo_owner_change_date_index
000256 ON flock_owner (owner_change_date);
000257 CREATE INDEX fo_owner_person_id_index
000258 ON flock_owner (owner_person_id);
000259 CREATE INDEX sheep_org_flock_index
000260 ON sheep (originating_flock);
000261 CREATE INDEX sheep_reg_flock_index
000262 ON sheep (registering_flock);
000263 }
000264 do_eqp_test autoindex1-600a {
000265 SELECT x.sheep_no, x.registering_flock, x.date_of_registration
000266 FROM sheep x LEFT JOIN
000267 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
000268 s.date_of_registration, prev.owner_change_date
000269 FROM sheep s JOIN flock_owner prev ON s.registering_flock =
000270 prev.flock_no
000271 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
000272 WHERE NOT EXISTS
000273 (SELECT 'x' FROM flock_owner later
000274 WHERE prev.flock_no = later.flock_no
000275 AND later.owner_change_date > prev.owner_change_date
000276 AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
000277 ) y ON x.sheep_no = y.sheep_no
000278 WHERE y.sheep_no IS NULL
000279 ORDER BY x.registering_flock;
000280 } {
000281 QUERY PLAN
000282 |--MATERIALIZE y
000283 | |--SCAN s
000284 | |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
000285 | `--CORRELATED SCALAR SUBQUERY xxxxxx
000286 | `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
000287 |--SCAN x USING INDEX sheep_reg_flock_index
000288 `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
000289 }
000290
000291
000292 do_execsql_test autoindex1-700 {
000293 CREATE TABLE t5(a, b, c);
000294 }
000295 do_eqp_test autoindex1-700a {
000296 SELECT a FROM t5 WHERE b=10 ORDER BY c;
000297 } {
000298 QUERY PLAN
000299 |--SCAN t5
000300 `--USE TEMP B-TREE FOR ORDER BY
000301 }
000302
000303 # The following checks a performance issue reported on the sqlite-dev
000304 # mailing list on 2013-01-10
000305 #
000306 do_execsql_test autoindex1-800 {
000307 CREATE TABLE accounts(
000308 _id INTEGER PRIMARY KEY AUTOINCREMENT,
000309 account_name TEXT,
000310 account_type TEXT,
000311 data_set TEXT
000312 );
000313 CREATE TABLE data(
000314 _id INTEGER PRIMARY KEY AUTOINCREMENT,
000315 package_id INTEGER REFERENCES package(_id),
000316 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
000317 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
000318 is_read_only INTEGER NOT NULL DEFAULT 0,
000319 is_primary INTEGER NOT NULL DEFAULT 0,
000320 is_super_primary INTEGER NOT NULL DEFAULT 0,
000321 data_version INTEGER NOT NULL DEFAULT 0,
000322 data1 TEXT,
000323 data2 TEXT,
000324 data3 TEXT,
000325 data4 TEXT,
000326 data5 TEXT,
000327 data6 TEXT,
000328 data7 TEXT,
000329 data8 TEXT,
000330 data9 TEXT,
000331 data10 TEXT,
000332 data11 TEXT,
000333 data12 TEXT,
000334 data13 TEXT,
000335 data14 TEXT,
000336 data15 TEXT,
000337 data_sync1 TEXT,
000338 data_sync2 TEXT,
000339 data_sync3 TEXT,
000340 data_sync4 TEXT
000341 );
000342 CREATE TABLE mimetypes(
000343 _id INTEGER PRIMARY KEY AUTOINCREMENT,
000344 mimetype TEXT NOT NULL
000345 );
000346 CREATE TABLE raw_contacts(
000347 _id INTEGER PRIMARY KEY AUTOINCREMENT,
000348 account_id INTEGER REFERENCES accounts(_id),
000349 sourceid TEXT,
000350 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
000351 version INTEGER NOT NULL DEFAULT 1,
000352 dirty INTEGER NOT NULL DEFAULT 0,
000353 deleted INTEGER NOT NULL DEFAULT 0,
000354 contact_id INTEGER REFERENCES contacts(_id),
000355 aggregation_mode INTEGER NOT NULL DEFAULT 0,
000356 aggregation_needed INTEGER NOT NULL DEFAULT 1,
000357 custom_ringtone TEXT,
000358 send_to_voicemail INTEGER NOT NULL DEFAULT 0,
000359 times_contacted INTEGER NOT NULL DEFAULT 0,
000360 last_time_contacted INTEGER,
000361 starred INTEGER NOT NULL DEFAULT 0,
000362 display_name TEXT,
000363 display_name_alt TEXT,
000364 display_name_source INTEGER NOT NULL DEFAULT 0,
000365 phonetic_name TEXT,
000366 phonetic_name_style TEXT,
000367 sort_key TEXT,
000368 sort_key_alt TEXT,
000369 name_verified INTEGER NOT NULL DEFAULT 0,
000370 sync1 TEXT,
000371 sync2 TEXT,
000372 sync3 TEXT,
000373 sync4 TEXT,
000374 sync_uid TEXT,
000375 sync_version INTEGER NOT NULL DEFAULT 1,
000376 has_calendar_event INTEGER NOT NULL DEFAULT 0,
000377 modified_time INTEGER,
000378 is_restricted INTEGER DEFAULT 0,
000379 yp_source TEXT,
000380 method_selected INTEGER DEFAULT 0,
000381 custom_vibration_type INTEGER DEFAULT 0,
000382 custom_ringtone_path TEXT,
000383 message_notification TEXT,
000384 message_notification_path TEXT
000385 );
000386 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
000387 CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
000388 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
000389 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
000390 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
000391 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
000392 CREATE INDEX raw_contacts_source_id_account_id_index
000393 ON raw_contacts (sourceid, account_id);
000394 ANALYZE sqlite_master;
000395 INSERT INTO sqlite_stat1
000396 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
000397 INSERT INTO sqlite_stat1
000398 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
000399 INSERT INTO sqlite_stat1
000400 VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
000401 '1600 1600 1600');
000402 INSERT INTO sqlite_stat1
000403 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
000404 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
000405 INSERT INTO sqlite_stat1
000406 VALUES('data','data_mimetype_data1_index','9819 2455 3');
000407 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
000408 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
000409 DROP TABLE IF EXISTS sqlite_stat3;
000410 ANALYZE sqlite_master;
000411
000412 EXPLAIN QUERY PLAN
000413 SELECT * FROM
000414 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id)
000415 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id)
000416 JOIN accounts ON (raw_contacts.account_id=accounts._id)
000417 WHERE mimetype_id=10 AND data14 IS NOT NULL;
000418 } {/SEARCH data .*SEARCH raw_contacts/}
000419 do_execsql_test autoindex1-801 {
000420 EXPLAIN QUERY PLAN
000421 SELECT * FROM
000422 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id)
000423 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id)
000424 JOIN accounts ON (raw_contacts.account_id=accounts._id)
000425 WHERE mimetypes._id=10 AND data14 IS NOT NULL;
000426 } {/SEARCH data .*SEARCH raw_contacts/}
000427
000428 # Another test case from an important user of SQLite. The key feature of
000429 # this test is that the "aggindex" subquery should make use of an
000430 # automatic index. If it does, the query is fast. If it does not, the
000431 # query is deathly slow. It worked OK in 3.7.17 but started going slow
000432 # with version 3.8.0. The problem was fixed for 3.8.7 by reducing the
000433 # cost estimate for automatic indexes on views and subqueries.
000434 #
000435 db close
000436 forcedelete test.db
000437 sqlite3 db test.db
000438 do_execsql_test autoindex1-900 {
000439 CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id, document_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_mailbox INTEGER, flags INTEGER, read, flagged, size INTEGER, color, encoding, type INTEGER, pad, conversation_id INTEGER DEFAULT -1, snippet TEXT DEFAULT NULL, fuzzy_ancestor INTEGER DEFAULT NULL, automated_conversation INTEGER DEFAULT 0, root_status INTEGER DEFAULT -1, conversation_position INTEGER DEFAULT -1);
000440 CREATE INDEX date_index ON messages(date_received);
000441 CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
000442 CREATE INDEX date_created_index ON messages(date_created);
000443 CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox);
000444 CREATE INDEX message_document_id_index ON messages(document_id);
000445 CREATE INDEX message_read_index ON messages(read);
000446 CREATE INDEX message_flagged_index ON messages(flagged);
000447 CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
000448 CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
000449 CREATE INDEX message_type_index ON messages(type);
000450 CREATE INDEX message_conversation_id_conversation_position_index ON messages(conversation_id, conversation_position);
000451 CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor);
000452 CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_ancestor);
000453 CREATE INDEX message_sender_subject_automated_conversation_index ON messages(sender, subject, automated_conversation);
000454 CREATE INDEX message_sender_index ON messages(sender);
000455 CREATE INDEX message_root_status ON messages(root_status);
000456 CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, normalized_subject COLLATE RTRIM);
000457 CREATE INDEX subject_subject_index ON subjects(subject);
000458 CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject);
000459 CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, comment, UNIQUE(address, comment));
000460 CREATE INDEX addresses_address_index ON addresses(address);
000461 CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY, url UNIQUE, total_count INTEGER DEFAULT 0, unread_count INTEGER DEFAULT 0, unseen_count INTEGER DEFAULT 0, deleted_count INTEGER DEFAULT 0, unread_count_adjusted_for_duplicates INTEGER DEFAULT 0, change_identifier, source INTEGER, alleged_change_identifier);
000462 CREATE INDEX mailboxes_source_index ON mailboxes(source);
000463 CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, mailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id));
000464 CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_id);
000465 CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id);
000466
000467 explain query plan
000468 SELECT messages.ROWID,
000469 messages.message_id,
000470 messages.remote_id,
000471 messages.date_received,
000472 messages.date_sent,
000473 messages.flags,
000474 messages.size,
000475 messages.color,
000476 messages.date_last_viewed,
000477 messages.subject_prefix,
000478 subjects.subject,
000479 sender.comment,
000480 sender.address,
000481 NULL,
000482 messages.mailbox,
000483 messages.original_mailbox,
000484 NULL,
000485 NULL,
000486 messages.type,
000487 messages.document_id,
000488 sender,
000489 NULL,
000490 messages.conversation_id,
000491 messages.conversation_position,
000492 agglabels.labels
000493 FROM mailboxes AS mailbox
000494 JOIN messages ON mailbox.ROWID = messages.mailbox
000495 LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID
000496 LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID
000497 LEFT OUTER JOIN (
000498 SELECT message_id, group_concat(mailbox_id) as labels
000499 FROM labels GROUP BY message_id
000500 ) AS agglabels ON messages.ROWID = agglabels.message_id
000501 WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail')
000502 AND (messages.ROWID IN (
000503 SELECT labels.message_id
000504 FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID
000505 WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX'))
000506 AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8,
000507 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28,
000508 34,40,5,11,17,23,35,41)
000509 ORDER BY date_received DESC;
000510 } {/agglabels USING AUTOMATIC COVERING INDEX/}
000511
000512 # A test case for VIEWs
000513 #
000514 do_execsql_test autoindex1-901 {
000515 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
000516 CREATE TABLE t2(a, b);
000517 CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a;
000518 EXPLAIN QUERY PLAN
000519 SELECT t1.z, agg2.m
000520 FROM t1 JOIN agg2 ON t1.y=agg2.m
000521 WHERE t1.x IN (1,2,3);
000522 } {/USING AUTOMATIC COVERING INDEX/}
000523
000524 # 2015-04-15: A NULL CollSeq pointer in automatic index creation.
000525 #
000526 do_execsql_test autoindex1-920 {
000527 CREATE TABLE t920(x);
000528 INSERT INTO t920 VALUES(3),(4),(5);
000529 SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
000530 } {5 0 9 5 0 9 5 0 9}
000531
000532 #-------------------------------------------------------------------------
000533 # An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
000534 # index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
000535 # the following SELECT count(*) would incorrectly return 1.
000536 #
000537 do_execsql_test autoindex1-1010 {
000538 CREATE TABLE t11(w);
000539 CREATE TABLE t12(y);
000540 INSERT INTO t11 VALUES(NULL);
000541 INSERT INTO t12 VALUES('notnull');
000542 }
000543 do_execsql_test autoindex1-1020 {
000544 SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
000545 } 0
000546
000547 # 2022-04-25
000548 # https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3
000549 #
000550 reset_db
000551 do_execsql_test autoindex-1100 {
000552 CREATE TABLE t1(a INT, b INT);
000553 CREATE TABLE t2(c INT, d INT);
000554 CREATE TABLE t3(e TEXT, f TEXT);
000555 INSERT INTO t1 VALUES(1, 1);
000556 INSERT INTO t2 VALUES(1, 2);
000557 INSERT INTO t3 VALUES('abc', 'def');
000558 } {}
000559 do_execsql_test autoindex-1110 {
000560 SELECT * FROM t1, t2 LEFT JOIN t3 ON (t2.d=1) WHERE t2.c = +t1.a;
000561 } {1 1 1 2 {} {}}
000562 do_execsql_test autoindex-1120 {
000563 SELECT * FROM t1 LEFT JOIN t2 ON (t2.c=+t1.a) LEFT JOIN t3 ON (t2.d IS NULL);
000564 } {1 1 1 2 {} {}}
000565
000566 # 2025-01-18
000567 # Added support for automatic indexes on WITHOUT ROWID tables.
000568 #
000569 reset_db
000570 do_execsql_test autoindex-1200 {
000571 CREATE TABLE t1(a INT, b INT, x INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
000572 INSERT INTO t1 VALUES(1,2,90),(1,3,91),(1,4,92);
000573 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
000574 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
000575 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
000576 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
000577 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
000578 CREATE TABLE dual(dummy TEXT);
000579 INSERT INTO dual(dummy) VALUES('x');
000580 }
000581 db null NULL
000582 do_execsql_test autoindex-1210 {
000583 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
000584 } {
000585 NULL NULL NULL 5 55
000586 1 3 91 3 33
000587 1 4 92 4 44
000588 }
000589 do_execsql_test autoindex-1211 {
000590 EXPLAIN QUERY PLAN
000591 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
000592 } {/SEARCH t1 USING AUTOMATIC COVERING INDEX/}
000593
000594 finish_test