test/autoindex1.test

   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.app@imap.gmail.com/%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.app@imap.gmail.com/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 finish_test