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