AryaWu/sqlite
0
1# 2010 April 072#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing automatic index creation logic.13#14# EVIDENCE-OF: R-34271-33106 PRAGMA automatic_index; PRAGMA15# automatic_index = boolean; Query, set, or clear the automatic indexing16# capability.17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21# If the library is not compiled with automatic index support then22# skip all tests in this file.23#24ifcapable {!autoindex} {25 finish_test26 return27}28 29# Setup for logging 30db close31sqlite3_shutdown32test_sqlite3_log [list lappend ::log]33set ::log [list]34sqlite3 db test.db35 36 37# With automatic index turned off, we do a full scan of the T2 table38do_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}53do_test autoindex1-101 {54 db status step55} {63}56do_test autoindex1-102 {57 db status autoindex58} {0}59 60# With autoindex turned on, we build an index once and then use that index61# to find T2 values.62do_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}68do_test autoindex1-111 {69 db status step70} {7}71do_test autoindex1-112 {72 db status autoindex73} {7}74do_test autoindex1-113 {75 set ::log76} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}}77 78db close79sqlite3_shutdown80test_sqlite3_log81sqlite3_initialize82sqlite3 db test.db83 84# The same test as above, but this time the T2 query is a subquery rather85# than a join.86do_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}92do_test autoindex1-201 {93 db status step94} {35}95do_test autoindex1-202 {96 db status autoindex97} {0}98do_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}109do_test autoindex1-211 {110 db status step111} {7}112do_test autoindex1-212 {113 db status autoindex114} {7}115 116 117# Modify the second table of the join while the join is in progress118#119do_execsql_test autoindex1-299 {120 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2';121 ANALYZE sqlite_master;122 EXPLAIN QUERY PLAN123 SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a);124} {/AUTOMATIC COVERING INDEX/}125do_test autoindex1-300 {126 set r {}127 db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} {128 lappend r $b $d129 db eval {UPDATE t2 SET d=d+1}130 }131 set r132} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}133do_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. Without138# automatic indices, the join will take a long time to complete.139# With automatic indices, it should only take about a second.140#141do_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}154do_test autoindex1-401 {155 db eval {156 SELECT count(*)157 FROM t4 AS x1158 JOIN t4 AS x2 ON x2.a=x1.b159 JOIN t4 AS x3 ON x3.a=x2.b160 JOIN t4 AS x4 ON x4.a=x3.b161 JOIN t4 AS x5 ON x5.a=x4.b162 JOIN t4 AS x6 ON x6.a=x5.b163 JOIN t4 AS x7 ON x7.a=x6.b164 JOIN t4 AS x8 ON x8.a=x7.b165 JOIN t4 AS x9 ON x9.a=x8.b166 JOIN t4 AS x10 ON x10.a=x9.b;167 }168} {4087}169 170# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08171# Make sure automatic indices are not created for the RHS of an IN expression172# that is not a correlated subquery.173#174do_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}181do_eqp_test autoindex1-500.1 {182 SELECT b FROM t501183 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);184} {185 QUERY PLAN186 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)187 `--LIST SUBQUERY xxxxxx188 |--SCAN t502189 `--CREATE BLOOM FILTER190}191do_eqp_test autoindex1-501 {192 SELECT b FROM t501193 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);194} {195 QUERY PLAN196 |--SCAN t501197 `--CORRELATED LIST SUBQUERY xxxxxx198 |--BLOOM FILTER ON t502 (y=?)199 `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)200}201do_eqp_test autoindex1-502 {202 SELECT b FROM t501203 WHERE t501.a=123204 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);205} {206 QUERY PLAN207 |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)208 `--CORRELATED LIST SUBQUERY xxxxxx209 `--SCAN t502210}211 212# The following code checks a performance regression reported on the213# mailing list on 2010-10-19. The problem is that the nRowEst field214# of ephermeral tables was not being initialized correctly and so no215# automatic index was being created for the emphemeral table when it was216# used as part of a join.217#218do_execsql_test autoindex1-600 {219 CREATE TABLE flock_owner(220 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,221 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),222 owner_person_id INTEGER NOT NULL REFERENCES person (person_id),223 owner_change_date TEXT, last_changed TEXT NOT NULL,224 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)225 );226 CREATE TABLE sheep (227 Sheep_No char(7) NOT NULL,228 Date_of_Birth char(8),229 Sort_DoB text,230 Flock_Book_Vol char(2),231 Breeder_No char(6),232 Breeder_Person integer,233 Originating_Flock char(6),234 Registering_Flock char(6),235 Tag_Prefix char(9),236 Tag_No char(15),237 Sort_Tag_No integer,238 Breeders_Temp_Tag char(15),239 Sex char(1),240 Sheep_Name char(32),241 Sire_No char(7),242 Dam_No char(7),243 Register_Code char(1),244 Colour char(48),245 Colour_Code char(2),246 Pattern_Code char(8),247 Horns char(1),248 Litter_Size char(1),249 Coeff_of_Inbreeding real,250 Date_of_Registration text,251 Date_Last_Changed text,252 UNIQUE(Sheep_No));253 CREATE INDEX fo_flock_no_index 254 ON flock_owner (flock_no);255 CREATE INDEX fo_owner_change_date_index 256 ON flock_owner (owner_change_date);257 CREATE INDEX fo_owner_person_id_index 258 ON flock_owner (owner_person_id);259 CREATE INDEX sheep_org_flock_index 260 ON sheep (originating_flock);261 CREATE INDEX sheep_reg_flock_index 262 ON sheep (registering_flock);263}264do_eqp_test autoindex1-600a {265 SELECT x.sheep_no, x.registering_flock, x.date_of_registration266 FROM sheep x LEFT JOIN267 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,268 s.date_of_registration, prev.owner_change_date269 FROM sheep s JOIN flock_owner prev ON s.registering_flock =270 prev.flock_no271 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')272 WHERE NOT EXISTS273 (SELECT 'x' FROM flock_owner later274 WHERE prev.flock_no = later.flock_no275 AND later.owner_change_date > prev.owner_change_date276 AND later.owner_change_date <= s.date_of_registration||' 00:00:00')277 ) y ON x.sheep_no = y.sheep_no278 WHERE y.sheep_no IS NULL279 ORDER BY x.registering_flock;280} {281 QUERY PLAN282 |--MATERIALIZE y283 | |--SCAN s284 | |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)285 | `--CORRELATED SCALAR SUBQUERY xxxxxx286 | `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)287 |--SCAN x USING INDEX sheep_reg_flock_index288 `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN289}290 291 292do_execsql_test autoindex1-700 {293 CREATE TABLE t5(a, b, c);294}295do_eqp_test autoindex1-700a {296 SELECT a FROM t5 WHERE b=10 ORDER BY c;297} {298 QUERY PLAN299 |--SCAN t5300 `--USE TEMP B-TREE FOR ORDER BY301}302 303# The following checks a performance issue reported on the sqlite-dev304# mailing list on 2013-01-10305#306do_execsql_test autoindex1-800 {307 CREATE TABLE accounts(308 _id INTEGER PRIMARY KEY AUTOINCREMENT,309 account_name TEXT,310 account_type TEXT,311 data_set TEXT312 );313 CREATE TABLE data(314 _id INTEGER PRIMARY KEY AUTOINCREMENT,315 package_id INTEGER REFERENCES package(_id),316 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,317 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,318 is_read_only INTEGER NOT NULL DEFAULT 0,319 is_primary INTEGER NOT NULL DEFAULT 0,320 is_super_primary INTEGER NOT NULL DEFAULT 0,321 data_version INTEGER NOT NULL DEFAULT 0,322 data1 TEXT,323 data2 TEXT,324 data3 TEXT,325 data4 TEXT,326 data5 TEXT,327 data6 TEXT,328 data7 TEXT,329 data8 TEXT,330 data9 TEXT,331 data10 TEXT,332 data11 TEXT,333 data12 TEXT,334 data13 TEXT,335 data14 TEXT,336 data15 TEXT,337 data_sync1 TEXT,338 data_sync2 TEXT,339 data_sync3 TEXT,340 data_sync4 TEXT 341 );342 CREATE TABLE mimetypes(343 _id INTEGER PRIMARY KEY AUTOINCREMENT,344 mimetype TEXT NOT NULL345 );346 CREATE TABLE raw_contacts(347 _id INTEGER PRIMARY KEY AUTOINCREMENT,348 account_id INTEGER REFERENCES accounts(_id),349 sourceid TEXT,350 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,351 version INTEGER NOT NULL DEFAULT 1,352 dirty INTEGER NOT NULL DEFAULT 0,353 deleted INTEGER NOT NULL DEFAULT 0,354 contact_id INTEGER REFERENCES contacts(_id),355 aggregation_mode INTEGER NOT NULL DEFAULT 0,356 aggregation_needed INTEGER NOT NULL DEFAULT 1,357 custom_ringtone TEXT,358 send_to_voicemail INTEGER NOT NULL DEFAULT 0,359 times_contacted INTEGER NOT NULL DEFAULT 0,360 last_time_contacted INTEGER,361 starred INTEGER NOT NULL DEFAULT 0,362 display_name TEXT,363 display_name_alt TEXT,364 display_name_source INTEGER NOT NULL DEFAULT 0,365 phonetic_name TEXT,366 phonetic_name_style TEXT,367 sort_key TEXT,368 sort_key_alt TEXT,369 name_verified INTEGER NOT NULL DEFAULT 0,370 sync1 TEXT,371 sync2 TEXT,372 sync3 TEXT,373 sync4 TEXT,374 sync_uid TEXT,375 sync_version INTEGER NOT NULL DEFAULT 1,376 has_calendar_event INTEGER NOT NULL DEFAULT 0,377 modified_time INTEGER,378 is_restricted INTEGER DEFAULT 0,379 yp_source TEXT,380 method_selected INTEGER DEFAULT 0,381 custom_vibration_type INTEGER DEFAULT 0,382 custom_ringtone_path TEXT,383 message_notification TEXT,384 message_notification_path TEXT385 );386 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);387 CREATE INDEX data_raw_contact_id ON data (raw_contact_id);388 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);389 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);390 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);391 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);392 CREATE INDEX raw_contacts_source_id_account_id_index393 ON raw_contacts (sourceid, account_id);394 ANALYZE sqlite_master;395 INSERT INTO sqlite_stat1396 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');397 INSERT INTO sqlite_stat1398 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');399 INSERT INTO sqlite_stat1400 VALUES('raw_contacts','raw_contacts_source_id_account_id_index',401 '1600 1600 1600');402 INSERT INTO sqlite_stat1403 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');404 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');405 INSERT INTO sqlite_stat1406 VALUES('data','data_mimetype_data1_index','9819 2455 3');407 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');408 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');409 DROP TABLE IF EXISTS sqlite_stat3;410 ANALYZE sqlite_master;411 412 EXPLAIN QUERY PLAN413 SELECT * FROM 414 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 415 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 416 JOIN accounts ON (raw_contacts.account_id=accounts._id)417 WHERE mimetype_id=10 AND data14 IS NOT NULL;418} {/SEARCH data .*SEARCH raw_contacts/}419do_execsql_test autoindex1-801 {420 EXPLAIN QUERY PLAN421 SELECT * FROM 422 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 423 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 424 JOIN accounts ON (raw_contacts.account_id=accounts._id)425 WHERE mimetypes._id=10 AND data14 IS NOT NULL;426} {/SEARCH data .*SEARCH raw_contacts/}427 428# Another test case from an important user of SQLite. The key feature of429# this test is that the "aggindex" subquery should make use of an430# automatic index. If it does, the query is fast. If it does not, the431# query is deathly slow. It worked OK in 3.7.17 but started going slow432# with version 3.8.0. The problem was fixed for 3.8.7 by reducing the433# cost estimate for automatic indexes on views and subqueries.434#435db close436forcedelete test.db437sqlite3 db test.db438do_execsql_test autoindex1-900 {439 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);440 CREATE INDEX date_index ON messages(date_received);441 CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);442 CREATE INDEX date_created_index ON messages(date_created);443 CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox);444 CREATE INDEX message_document_id_index ON messages(document_id);445 CREATE INDEX message_read_index ON messages(read);446 CREATE INDEX message_flagged_index ON messages(flagged);447 CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);448 CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);449 CREATE INDEX message_type_index ON messages(type);450 CREATE INDEX message_conversation_id_conversation_position_index ON messages(conversation_id, conversation_position);451 CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor);452 CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_ancestor);453 CREATE INDEX message_sender_subject_automated_conversation_index ON messages(sender, subject, automated_conversation);454 CREATE INDEX message_sender_index ON messages(sender);455 CREATE INDEX message_root_status ON messages(root_status);456 CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, normalized_subject COLLATE RTRIM);457 CREATE INDEX subject_subject_index ON subjects(subject);458 CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject);459 CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, comment, UNIQUE(address, comment));460 CREATE INDEX addresses_address_index ON addresses(address);461 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);462 CREATE INDEX mailboxes_source_index ON mailboxes(source);463 CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, mailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id));464 CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_id);465 CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id);466 467 explain query plan468 SELECT messages.ROWID,469 messages.message_id,470 messages.remote_id,471 messages.date_received,472 messages.date_sent,473 messages.flags,474 messages.size,475 messages.color,476 messages.date_last_viewed,477 messages.subject_prefix,478 subjects.subject,479 sender.comment,480 sender.address,481 NULL,482 messages.mailbox,483 messages.original_mailbox,484 NULL,485 NULL,486 messages.type,487 messages.document_id,488 sender,489 NULL,490 messages.conversation_id,491 messages.conversation_position,492 agglabels.labels493 FROM mailboxes AS mailbox494 JOIN messages ON mailbox.ROWID = messages.mailbox495 LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID496 LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID497 LEFT OUTER JOIN (498 SELECT message_id, group_concat(mailbox_id) as labels499 FROM labels GROUP BY message_id500 ) AS agglabels ON messages.ROWID = agglabels.message_id501 WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail')502 AND (messages.ROWID IN (503 SELECT labels.message_id504 FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID505 WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX'))506 AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8,507 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28,508 34,40,5,11,17,23,35,41)509 ORDER BY date_received DESC;510} {/agglabels USING AUTOMATIC COVERING INDEX/}511 512# A test case for VIEWs513#514do_execsql_test autoindex1-901 {515 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);516 CREATE TABLE t2(a, b);517 CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a;518 EXPLAIN QUERY PLAN519 SELECT t1.z, agg2.m520 FROM t1 JOIN agg2 ON t1.y=agg2.m521 WHERE t1.x IN (1,2,3);522} {/USING AUTOMATIC COVERING INDEX/}523 524# 2015-04-15: A NULL CollSeq pointer in automatic index creation.525#526do_execsql_test autoindex1-920 {527 CREATE TABLE t920(x);528 INSERT INTO t920 VALUES(3),(4),(5);529 SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);530} {5 0 9 5 0 9 5 0 9}531 532#-------------------------------------------------------------------------533# An IS term from the WHERE clause of a LEFT JOIN cannot be used as an534# index driver for the RHS of a LEFT JOIN. Prior to this being fixed,535# the following SELECT count(*) would incorrectly return 1.536#537do_execsql_test autoindex1-1010 {538 CREATE TABLE t11(w);539 CREATE TABLE t12(y);540 INSERT INTO t11 VALUES(NULL);541 INSERT INTO t12 VALUES('notnull');542}543do_execsql_test autoindex1-1020 {544 SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;545} 0546 547# 2022-04-25548# https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3549#550reset_db551do_execsql_test autoindex-1100 {552 CREATE TABLE t1(a INT, b INT);553 CREATE TABLE t2(c INT, d INT);554 CREATE TABLE t3(e TEXT, f TEXT);555 INSERT INTO t1 VALUES(1, 1);556 INSERT INTO t2 VALUES(1, 2);557 INSERT INTO t3 VALUES('abc', 'def');558} {}559do_execsql_test autoindex-1110 {560 SELECT * FROM t1, t2 LEFT JOIN t3 ON (t2.d=1) WHERE t2.c = +t1.a;561} {1 1 1 2 {} {}}562do_execsql_test autoindex-1120 {563 SELECT * FROM t1 LEFT JOIN t2 ON (t2.c=+t1.a) LEFT JOIN t3 ON (t2.d IS NULL);564} {1 1 1 2 {} {}}565 566# 2025-01-18567# Added support for automatic indexes on WITHOUT ROWID tables.568#569reset_db570do_execsql_test autoindex-1200 {571 CREATE TABLE t1(a INT, b INT, x INT, PRIMARY KEY(a,b)) WITHOUT ROWID;572 INSERT INTO t1 VALUES(1,2,90),(1,3,91),(1,4,92);573 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);574 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);575 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;576 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);577 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);578 CREATE TABLE dual(dummy TEXT);579 INSERT INTO dual(dummy) VALUES('x');580}581db null NULL582do_execsql_test autoindex-1210 {583 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;584} {585 NULL NULL NULL 5 55586 1 3 91 3 33587 1 4 92 4 44588}589do_execsql_test autoindex-1211 {590 EXPLAIN QUERY PLAN591 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;592} {/SEARCH t1 USING AUTOMATIC COVERING INDEX/}593 594finish_test595