Debian Codegraph @

test/autoindex1.test

raw
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 }
181 do_eqp_test autoindex1-500.1 {
182 SELECT b FROM t501
183 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
184 } {
185 QUERY PLAN
186 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
187 `--LIST SUBQUERY xxxxxx
188 `--SCAN t502
189 }
190 do_eqp_test autoindex1-501 {
191 SELECT b FROM t501
192 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
193 } {
194 QUERY PLAN
195 |--SCAN t501
196 `--CORRELATED LIST SUBQUERY xxxxxx
197 |--BLOOM FILTER ON t502 (y=?)
198 `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)
199 }
200 do_eqp_test autoindex1-502 {
201 SELECT b FROM t501
202 WHERE t501.a=123
203 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
204 } {
205 QUERY PLAN
206 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
207 `--CORRELATED LIST SUBQUERY xxxxxx
208 `--SCAN t502
209 }
210
211 # The following code checks a performance regression reported on the
212 # mailing list on 2010-10-19. The problem is that the nRowEst field
213 # of ephermeral tables was not being initialized correctly and so no
214 # automatic index was being created for the emphemeral table when it was
215 # used as part of a join.
216 #
217 do_execsql_test autoindex1-600 {
218 CREATE TABLE flock_owner(
219 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
220 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
221 owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
222 owner_change_date TEXT, last_changed TEXT NOT NULL,
223 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
224 );
225 CREATE TABLE sheep (
226 Sheep_No char(7) NOT NULL,
227 Date_of_Birth char(8),
228 Sort_DoB text,
229 Flock_Book_Vol char(2),
230 Breeder_No char(6),
231 Breeder_Person integer,
232 Originating_Flock char(6),
233 Registering_Flock char(6),
234 Tag_Prefix char(9),
235 Tag_No char(15),
236 Sort_Tag_No integer,
237 Breeders_Temp_Tag char(15),
238 Sex char(1),
239 Sheep_Name char(32),
240 Sire_No char(7),
241 Dam_No char(7),
242 Register_Code char(1),
243 Colour char(48),
244 Colour_Code char(2),
245 Pattern_Code char(8),
246 Horns char(1),
247 Litter_Size char(1),
248 Coeff_of_Inbreeding real,
249 Date_of_Registration text,
250 Date_Last_Changed text,
251 UNIQUE(Sheep_No));
252 CREATE INDEX fo_flock_no_index
253 ON flock_owner (flock_no);
254 CREATE INDEX fo_owner_change_date_index
255 ON flock_owner (owner_change_date);
256 CREATE INDEX fo_owner_person_id_index
257 ON flock_owner (owner_person_id);
258 CREATE INDEX sheep_org_flock_index
259 ON sheep (originating_flock);
260 CREATE INDEX sheep_reg_flock_index
261 ON sheep (registering_flock);
262 }
263 do_eqp_test autoindex1-600a {
264 SELECT x.sheep_no, x.registering_flock, x.date_of_registration
265 FROM sheep x LEFT JOIN
266 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
267 s.date_of_registration, prev.owner_change_date
268 FROM sheep s JOIN flock_owner prev ON s.registering_flock =
269 prev.flock_no
270 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
271 WHERE NOT EXISTS
272 (SELECT 'x' FROM flock_owner later
273 WHERE prev.flock_no = later.flock_no
274 AND later.owner_change_date > prev.owner_change_date
275 AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
276 ) y ON x.sheep_no = y.sheep_no
277 WHERE y.sheep_no IS NULL
278 ORDER BY x.registering_flock;
279 } {
280 QUERY PLAN
281 |--MATERIALIZE y
282 | |--SCAN s
283 | |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
284 | `--CORRELATED SCALAR SUBQUERY xxxxxx
285 | `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
286 |--SCAN x USING INDEX sheep_reg_flock_index
287 `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
288 }
289
290
291 do_execsql_test autoindex1-700 {
292 CREATE TABLE t5(a, b, c);
293 }
294 do_eqp_test autoindex1-700a {
295 SELECT a FROM t5 WHERE b=10 ORDER BY c;
296 } {
297 QUERY PLAN
298 |--SCAN t5
299 `--USE TEMP B-TREE FOR ORDER BY
300 }
301
302 # The following checks a performance issue reported on the sqlite-dev
303 # mailing list on 2013-01-10
304 #
305 do_execsql_test autoindex1-800 {
306 CREATE TABLE accounts(
307 _id INTEGER PRIMARY KEY AUTOINCREMENT,
308 account_name TEXT,
309 account_type TEXT,
310 data_set TEXT
311 );
312 CREATE TABLE data(
313 _id INTEGER PRIMARY KEY AUTOINCREMENT,
314 package_id INTEGER REFERENCES package(_id),
315 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
316 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
317 is_read_only INTEGER NOT NULL DEFAULT 0,
318 is_primary INTEGER NOT NULL DEFAULT 0,
319 is_super_primary INTEGER NOT NULL DEFAULT 0,
320 data_version INTEGER NOT NULL DEFAULT 0,
321 data1 TEXT,
322 data2 TEXT,
323 data3 TEXT,
324 data4 TEXT,
325 data5 TEXT,
326 data6 TEXT,
327 data7 TEXT,
328 data8 TEXT,
329 data9 TEXT,
330 data10 TEXT,
331 data11 TEXT,
332 data12 TEXT,
333 data13 TEXT,
334 data14 TEXT,
335 data15 TEXT,
336 data_sync1 TEXT,
337 data_sync2 TEXT,
338 data_sync3 TEXT,
339 data_sync4 TEXT
340 );
341 CREATE TABLE mimetypes(
342 _id INTEGER PRIMARY KEY AUTOINCREMENT,
343 mimetype TEXT NOT NULL
344 );
345 CREATE TABLE raw_contacts(
346 _id INTEGER PRIMARY KEY AUTOINCREMENT,
347 account_id INTEGER REFERENCES accounts(_id),
348 sourceid TEXT,
349 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
350 version INTEGER NOT NULL DEFAULT 1,
351 dirty INTEGER NOT NULL DEFAULT 0,
352 deleted INTEGER NOT NULL DEFAULT 0,
353 contact_id INTEGER REFERENCES contacts(_id),
354 aggregation_mode INTEGER NOT NULL DEFAULT 0,
355 aggregation_needed INTEGER NOT NULL DEFAULT 1,
356 custom_ringtone TEXT,
357 send_to_voicemail INTEGER NOT NULL DEFAULT 0,
358 times_contacted INTEGER NOT NULL DEFAULT 0,
359 last_time_contacted INTEGER,
360 starred INTEGER NOT NULL DEFAULT 0,
361 display_name TEXT,
362 display_name_alt TEXT,
363 display_name_source INTEGER NOT NULL DEFAULT 0,
364 phonetic_name TEXT,
365 phonetic_name_style TEXT,
366 sort_key TEXT,
367 sort_key_alt TEXT,
368 name_verified INTEGER NOT NULL DEFAULT 0,
369 sync1 TEXT,
370 sync2 TEXT,
371 sync3 TEXT,
372 sync4 TEXT,
373 sync_uid TEXT,
374 sync_version INTEGER NOT NULL DEFAULT 1,
375 has_calendar_event INTEGER NOT NULL DEFAULT 0,
376 modified_time INTEGER,
377 is_restricted INTEGER DEFAULT 0,
378 yp_source TEXT,
379 method_selected INTEGER DEFAULT 0,
380 custom_vibration_type INTEGER DEFAULT 0,
381 custom_ringtone_path TEXT,
382 message_notification TEXT,
383 message_notification_path TEXT
384 );
385 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
386 CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
387 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
388 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
389 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
390 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
391 CREATE INDEX raw_contacts_source_id_account_id_index
392 ON raw_contacts (sourceid, account_id);
393 ANALYZE sqlite_master;
394 INSERT INTO sqlite_stat1
395 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
396 INSERT INTO sqlite_stat1
397 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
398 INSERT INTO sqlite_stat1
399 VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
400 '1600 1600 1600');
401 INSERT INTO sqlite_stat1
402 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
403 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
404 INSERT INTO sqlite_stat1
405 VALUES('data','data_mimetype_data1_index','9819 2455 3');
406 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
407 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
408 DROP TABLE IF EXISTS sqlite_stat3;
409 ANALYZE sqlite_master;
410
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 mimetype_id=10 AND data14 IS NOT NULL;
417 } {/SEARCH data .*SEARCH raw_contacts/}
418 do_execsql_test autoindex1-801 {
419 EXPLAIN QUERY PLAN
420 SELECT * FROM
421 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id)
422 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id)
423 JOIN accounts ON (raw_contacts.account_id=accounts._id)
424 WHERE mimetypes._id=10 AND data14 IS NOT NULL;
425 } {/SEARCH data .*SEARCH raw_contacts/}
426
427 # Another test case from an important user of SQLite. The key feature of
428 # this test is that the "aggindex" subquery should make use of an
429 # automatic index. If it does, the query is fast. If it does not, the
430 # query is deathly slow. It worked OK in 3.7.17 but started going slow
431 # with version 3.8.0. The problem was fixed for 3.8.7 by reducing the
432 # cost estimate for automatic indexes on views and subqueries.
433 #
434 db close
435 forcedelete test.db
436 sqlite3 db test.db
437 do_execsql_test autoindex1-900 {
438 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);
439 CREATE INDEX date_index ON messages(date_received);
440 CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
441 CREATE INDEX date_created_index ON messages(date_created);
442 CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox);
443 CREATE INDEX message_document_id_index ON messages(document_id);
444 CREATE INDEX message_read_index ON messages(read);
445 CREATE INDEX message_flagged_index ON messages(flagged);
446 CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
447 CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
448 CREATE INDEX message_type_index ON messages(type);
449 CREATE INDEX message_conversation_id_conversation_position_index ON messages(conversation_id, conversation_position);
450 CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor);
451 CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_ancestor);
452 CREATE INDEX message_sender_subject_automated_conversation_index ON messages(sender, subject, automated_conversation);
453 CREATE INDEX message_sender_index ON messages(sender);
454 CREATE INDEX message_root_status ON messages(root_status);
455 CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, normalized_subject COLLATE RTRIM);
456 CREATE INDEX subject_subject_index ON subjects(subject);
457 CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject);
458 CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, comment, UNIQUE(address, comment));
459 CREATE INDEX addresses_address_index ON addresses(address);
460 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);
461 CREATE INDEX mailboxes_source_index ON mailboxes(source);
462 CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, mailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id));
463 CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_id);
464 CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id);
465
466 explain query plan
467 SELECT messages.ROWID,
468 messages.message_id,
469 messages.remote_id,
470 messages.date_received,
471 messages.date_sent,
472 messages.flags,
473 messages.size,
474 messages.color,
475 messages.date_last_viewed,
476 messages.subject_prefix,
477 subjects.subject,
478 sender.comment,
479 sender.address,
480 NULL,
481 messages.mailbox,
482 messages.original_mailbox,
483 NULL,
484 NULL,
485 messages.type,
486 messages.document_id,
487 sender,
488 NULL,
489 messages.conversation_id,
490 messages.conversation_position,
491 agglabels.labels
492 FROM mailboxes AS mailbox
493 JOIN messages ON mailbox.ROWID = messages.mailbox
494 LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID
495 LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID
496 LEFT OUTER JOIN (
497 SELECT message_id, group_concat(mailbox_id) as labels
498 FROM labels GROUP BY message_id
499 ) AS agglabels ON messages.ROWID = agglabels.message_id
500 WHERE (mailbox.url = 'imap://[email protected]/%5BGmail%5D/All%20Mail')
501 AND (messages.ROWID IN (
502 SELECT labels.message_id
503 FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID
504 WHERE mailboxes.url = 'imap://[email protected]/INBOX'))
505 AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8,
506 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28,
507 34,40,5,11,17,23,35,41)
508 ORDER BY date_received DESC;
509 } {/agglabels USING AUTOMATIC COVERING INDEX/}
510
511 # A test case for VIEWs
512 #
513 do_execsql_test autoindex1-901 {
514 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
515 CREATE TABLE t2(a, b);
516 CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a;
517 EXPLAIN QUERY PLAN
518 SELECT t1.z, agg2.m
519 FROM t1 JOIN agg2 ON t1.y=agg2.m
520 WHERE t1.x IN (1,2,3);
521 } {/USING AUTOMATIC COVERING INDEX/}
522
523 # 2015-04-15: A NULL CollSeq pointer in automatic index creation.
524 #
525 do_execsql_test autoindex1-920 {
526 CREATE TABLE t920(x);
527 INSERT INTO t920 VALUES(3),(4),(5);
528 SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
529 } {5 0 9 5 0 9 5 0 9}
530
531 #-------------------------------------------------------------------------
532 # An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
533 # index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
534 # the following SELECT count(*) would incorrectly return 1.
535 #
536 do_execsql_test autoindex1-1010 {
537 CREATE TABLE t11(w);
538 CREATE TABLE t12(y);
539 INSERT INTO t11 VALUES(NULL);
540 INSERT INTO t12 VALUES('notnull');
541 }
542 do_execsql_test autoindex1-1020 {
543 SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
544 } 0
545
546 # 2022-04-25
547 # https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3
548 #
549 reset_db
550 do_execsql_test autoindex-1100 {
551 CREATE TABLE t1(a INT, b INT);
552 CREATE TABLE t2(c INT, d INT);
553 CREATE TABLE t3(e TEXT, f TEXT);
554 INSERT INTO t1 VALUES(1, 1);
555 INSERT INTO t2 VALUES(1, 2);
556 INSERT INTO t3 VALUES('abc', 'def');
557 } {}
558 do_execsql_test autoindex-1110 {
559 SELECT * FROM t1, t2 LEFT JOIN t3 ON (t2.d=1) WHERE t2.c = +t1.a;
560 } {1 1 1 2 {} {}}
561 do_execsql_test autoindex-1120 {
562 SELECT * FROM t1 LEFT JOIN t2 ON (t2.c=+t1.a) LEFT JOIN t3 ON (t2.d IS NULL);
563 } {1 1 1 2 {} {}}
564
565 # 2025-01-18
566 # Added support for automatic indexes on WITHOUT ROWID tables.
567 #
568 reset_db
569 do_execsql_test autoindex-1200 {
570 CREATE TABLE t1(a INT, b INT, x INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
571 INSERT INTO t1 VALUES(1,2,90),(1,3,91),(1,4,92);
572 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
573 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
574 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
575 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
576 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
577 CREATE TABLE dual(dummy TEXT);
578 INSERT INTO dual(dummy) VALUES('x');
579 }
580 db null NULL
581 do_execsql_test autoindex-1210 {
582 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
583 } {
584 NULL NULL NULL 5 55
585 1 3 91 3 33
586 1 4 92 4 44
587 }
588 do_execsql_test autoindex-1211 {
589 EXPLAIN QUERY PLAN
590 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
591 } {/SEARCH t1 USING AUTOMATIC COVERING INDEX/}
592
593 finish_test