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