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      `--CREATE BLOOM FILTER
 190 }
 191 do_eqp_test autoindex1-501 {
 192   SELECT b FROM t501
 193    WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 194 } {
 195   QUERY PLAN
 196   |--SCAN t501
 197   `--CORRELATED LIST SUBQUERY xxxxxx
 198      |--BLOOM FILTER ON t502 (y=?)
 199      `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)
 200 }
 201 do_eqp_test autoindex1-502 {
 202   SELECT b FROM t501
 203    WHERE t501.a=123
 204      AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
 205 } {
 206   QUERY PLAN
 207   |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
 208   `--CORRELATED LIST SUBQUERY xxxxxx
 209      `--SCAN t502
 210 }
 211
 212 # The following code checks a performance regression reported on the
 213 # mailing list on 2010-10-19.  The problem is that the nRowEst field
 214 # of ephermeral tables was not being initialized correctly and so no
 215 # automatic index was being created for the emphemeral table when it was
 216 # used as part of a join.
 217 #
 218 do_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 }
 264 do_eqp_test autoindex1-600a {
 265   SELECT x.sheep_no, x.registering_flock, x.date_of_registration
 266    FROM sheep x LEFT JOIN
 267        (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
 268        s.date_of_registration, prev.owner_change_date
 269        FROM sheep s JOIN flock_owner prev ON s.registering_flock =
 270    prev.flock_no
 271        AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
 272        WHERE NOT EXISTS
 273            (SELECT 'x' FROM flock_owner later
 274            WHERE prev.flock_no = later.flock_no
 275            AND later.owner_change_date > prev.owner_change_date
 276            AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
 277        ) y ON x.sheep_no = y.sheep_no
 278    WHERE y.sheep_no IS NULL
 279    ORDER BY x.registering_flock;
 280 } {
 281   QUERY PLAN
 282   |--MATERIALIZE y
 283   |  |--SCAN s
 284   |  |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
 285   |  `--CORRELATED SCALAR SUBQUERY xxxxxx
 286   |     `--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_index
 288   `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
 289 }
 290
 291
 292 do_execsql_test autoindex1-700 {
 293   CREATE TABLE t5(a, b, c);
 294 }
 295 do_eqp_test autoindex1-700a {
 296   SELECT a FROM t5 WHERE b=10 ORDER BY c;
 297 } {
 298   QUERY PLAN
 299   |--SCAN t5
 300   `--USE TEMP B-TREE FOR ORDER BY
 301 }
 302
 303 # The following checks a performance issue reported on the sqlite-dev
 304 # mailing list on 2013-01-10
 305 #
 306 do_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 TEXT
 312   );
 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 NULL
 345   );
 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 TEXT
 385   );
 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_index
 393       ON raw_contacts (sourceid, account_id);
 394   ANALYZE sqlite_master;
 395   INSERT INTO sqlite_stat1
 396      VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
 397   INSERT INTO sqlite_stat1
 398      VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
 399   INSERT INTO sqlite_stat1
 400      VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
 401             '1600 1600 1600');
 402   INSERT INTO sqlite_stat1
 403      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_stat1
 406      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 PLAN
 413   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/}
 419 do_execsql_test autoindex1-801 {
 420   EXPLAIN QUERY PLAN
 421   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 of
 429 # this test is that the "aggindex" subquery should make use of an
 430 # automatic index.  If it does, the query is fast.  If it does not, the
 431 # query is deathly slow.  It worked OK in 3.7.17 but started going slow
 432 # with version 3.8.0.  The problem was fixed for 3.8.7 by reducing the
 433 # cost estimate for automatic indexes on views and subqueries.
 434 #
 435 db close
 436 forcedelete test.db
 437 sqlite3 db test.db
 438 do_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 plan
 468   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.labels
 493    FROM mailboxes AS mailbox
 494         JOIN messages ON mailbox.ROWID = messages.mailbox
 495         LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID
 496         LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID
 497         LEFT OUTER JOIN (
 498                SELECT message_id, group_concat(mailbox_id) as labels
 499                FROM labels GROUP BY message_id
 500              ) AS agglabels ON messages.ROWID = agglabels.message_id
 501   WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail')
 502     AND (messages.ROWID IN (
 503             SELECT labels.message_id
 504               FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID
 505              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 VIEWs
 513 #
 514 do_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 PLAN
 519   SELECT t1.z, agg2.m
 520     FROM t1 JOIN agg2 ON t1.y=agg2.m
 521    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 #
 526 do_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 an
 534 # index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
 535 # the following SELECT count(*) would incorrectly return 1.
 536 #
 537 do_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 }
 543 do_execsql_test autoindex1-1020 {
 544   SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
 545 } 0
 546
 547 # 2022-04-25
 548 # https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3
 549 #
 550 reset_db
 551 do_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 } {}
 559 do_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 {} {}}
 562 do_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 finish_test