000001  # 2010 April 07
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #*************************************************************************
000011  # This file implements regression tests for SQLite library.  The
000012  # focus of this script is testing automatic index creation logic.
000013  #
000014  # EVIDENCE-OF: R-34271-33106 PRAGMA automatic_index; PRAGMA
000015  # automatic_index = boolean; Query, set, or clear the automatic indexing
000016  # capability.
000017  
000018  set testdir [file dirname $argv0]
000019  source $testdir/tester.tcl
000020  
000021  # If the library is not compiled with automatic index support then
000022  # skip all tests in this file.
000023  #
000024  ifcapable {!autoindex} {
000025    finish_test
000026    return
000027  }
000028  
000029  # Setup for logging 
000030  db close
000031  sqlite3_shutdown
000032  test_sqlite3_log [list lappend ::log]
000033  set ::log [list]
000034  sqlite3 db test.db
000035  
000036  
000037  # With automatic index turned off, we do a full scan of the T2 table
000038  do_test autoindex1-100 {
000039    db eval {
000040      CREATE TABLE t1(a,b);
000041      INSERT INTO t1 VALUES(1,11);
000042      INSERT INTO t1 VALUES(2,22);
000043      INSERT INTO t1 SELECT a+2, b+22 FROM t1;
000044      INSERT INTO t1 SELECT a+4, b+44 FROM t1;
000045      CREATE TABLE t2(c,d);
000046      INSERT INTO t2 SELECT a, 900+b FROM t1;
000047    }
000048    db eval {
000049      PRAGMA automatic_index=OFF;
000050      SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
000051    }
000052  } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000053  do_test autoindex1-101 {
000054    db status step
000055  } {63}
000056  do_test autoindex1-102 {
000057    db status autoindex
000058  } {0}
000059  
000060  # With autoindex turned on, we build an index once and then use that index
000061  # to find T2 values.
000062  do_test autoindex1-110 {
000063    db eval {
000064      PRAGMA automatic_index=ON;
000065      SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
000066    }
000067  } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000068  do_test autoindex1-111 {
000069    db status step
000070  } {7}
000071  do_test autoindex1-112 {
000072    db status autoindex
000073  } {7}
000074  do_test autoindex1-113 {
000075    set ::log
000076  } {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}}
000077  
000078  db close
000079  sqlite3_shutdown
000080  test_sqlite3_log
000081  sqlite3_initialize
000082  sqlite3 db test.db
000083  
000084  # The same test as above, but this time the T2 query is a subquery rather
000085  # than a join.
000086  do_test autoindex1-200 {
000087    db eval {
000088      PRAGMA automatic_index=OFF;
000089      SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
000090    }
000091  } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000092  do_test autoindex1-201 {
000093    db status step
000094  } {35}
000095  do_test autoindex1-202 {
000096    db status autoindex
000097  } {0}
000098  do_test autoindex1-210 {
000099    db eval {
000100      PRAGMA automatic_index=ON;
000101      ANALYZE;
000102      UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1';
000103      -- Table t2 actually contains 8 rows.
000104      UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2';
000105      ANALYZE sqlite_master;
000106      SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
000107    }
000108  } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000109  do_test autoindex1-211 {
000110    db status step
000111  } {7}
000112  do_test autoindex1-212 {
000113    db status autoindex
000114  } {7}
000115  
000116  
000117  # Modify the second table of the join while the join is in progress
000118  #
000119  do_execsql_test autoindex1-299 {
000120    UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2';
000121    ANALYZE sqlite_master;
000122    EXPLAIN QUERY PLAN
000123    SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a);
000124  } {/AUTOMATIC COVERING INDEX/}
000125  do_test autoindex1-300 {
000126    set r {}
000127    db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} {
000128      lappend r $b $d
000129      db eval {UPDATE t2 SET d=d+1}
000130    }
000131    set r
000132  } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
000133  do_test autoindex1-310 {
000134    db eval {SELECT d FROM t2 ORDER BY d}
000135  } {919 930 941 952 963 974 985 996}
000136  
000137  # The next test does a 10-way join on unindexed tables.  Without
000138  # automatic indices, the join will take a long time to complete.
000139  # With automatic indices, it should only take about a second.
000140  #
000141  do_test autoindex1-400 {
000142    db eval {
000143      CREATE TABLE t4(a, b);
000144      INSERT INTO t4 VALUES(1,2);
000145      INSERT INTO t4 VALUES(2,3);
000146    }
000147    for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
000148      db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
000149    }
000150    db eval {
000151      SELECT count(*) FROM t4;
000152    }
000153  } {4096}
000154  do_test autoindex1-401 {
000155    db eval {
000156      SELECT count(*)
000157        FROM t4 AS x1
000158        JOIN t4 AS x2 ON x2.a=x1.b
000159        JOIN t4 AS x3 ON x3.a=x2.b
000160        JOIN t4 AS x4 ON x4.a=x3.b
000161        JOIN t4 AS x5 ON x5.a=x4.b
000162        JOIN t4 AS x6 ON x6.a=x5.b
000163        JOIN t4 AS x7 ON x7.a=x6.b
000164        JOIN t4 AS x8 ON x8.a=x7.b
000165        JOIN t4 AS x9 ON x9.a=x8.b
000166        JOIN t4 AS x10 ON x10.a=x9.b;
000167    }
000168  } {4087}
000169  
000170  # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
000171  # Make sure automatic indices are not created for the RHS of an IN expression
000172  # that is not a correlated subquery.
000173  #
000174  do_execsql_test autoindex1-500 {
000175    CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
000176    CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
000177    INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
000178    INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
000179    ANALYZE sqlite_master;
000180  }
000181  do_eqp_test autoindex1-500.1 {
000182    SELECT b FROM t501
000183     WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
000184  } {
000185    QUERY PLAN
000186    |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
000187    `--LIST SUBQUERY xxxxxx
000188       `--SCAN t502
000189  }
000190  do_eqp_test autoindex1-501 {
000191    SELECT b FROM t501
000192     WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
000193  } {
000194    QUERY PLAN
000195    |--SCAN t501
000196    `--CORRELATED LIST SUBQUERY xxxxxx
000197       |--BLOOM FILTER ON t502 (y=?)
000198       `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)
000199  }
000200  do_eqp_test autoindex1-502 {
000201    SELECT b FROM t501
000202     WHERE t501.a=123
000203       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
000204  } {
000205    QUERY PLAN
000206    |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
000207    `--CORRELATED LIST SUBQUERY xxxxxx
000208       `--SCAN t502
000209  }
000210  
000211  # The following code checks a performance regression reported on the
000212  # mailing list on 2010-10-19.  The problem is that the nRowEst field
000213  # of ephermeral tables was not being initialized correctly and so no
000214  # automatic index was being created for the emphemeral table when it was
000215  # used as part of a join.
000216  #
000217  do_execsql_test autoindex1-600 {
000218    CREATE TABLE flock_owner(
000219      owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
000220      flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
000221      owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
000222      owner_change_date TEXT, last_changed TEXT NOT NULL,
000223      CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
000224    );
000225    CREATE TABLE sheep (
000226      Sheep_No char(7) NOT NULL,
000227      Date_of_Birth char(8),
000228      Sort_DoB text,
000229      Flock_Book_Vol char(2),
000230      Breeder_No char(6),
000231      Breeder_Person integer,
000232      Originating_Flock char(6),
000233      Registering_Flock char(6),
000234      Tag_Prefix char(9),
000235      Tag_No char(15),
000236      Sort_Tag_No integer,
000237      Breeders_Temp_Tag char(15),
000238      Sex char(1),
000239      Sheep_Name char(32),
000240      Sire_No char(7),
000241      Dam_No char(7),
000242      Register_Code char(1),
000243      Colour char(48),
000244      Colour_Code char(2),
000245      Pattern_Code char(8),
000246      Horns char(1),
000247      Litter_Size char(1),
000248      Coeff_of_Inbreeding real,
000249      Date_of_Registration text,
000250      Date_Last_Changed text,
000251      UNIQUE(Sheep_No));
000252    CREATE INDEX fo_flock_no_index  
000253                ON flock_owner (flock_no);
000254    CREATE INDEX fo_owner_change_date_index  
000255                ON flock_owner (owner_change_date);
000256    CREATE INDEX fo_owner_person_id_index  
000257                ON flock_owner (owner_person_id);
000258    CREATE INDEX sheep_org_flock_index  
000259             ON sheep (originating_flock);
000260    CREATE INDEX sheep_reg_flock_index  
000261             ON sheep (registering_flock);
000262  }
000263  do_eqp_test autoindex1-600a {
000264    SELECT x.sheep_no, x.registering_flock, x.date_of_registration
000265     FROM sheep x LEFT JOIN
000266         (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
000267         s.date_of_registration, prev.owner_change_date
000268         FROM sheep s JOIN flock_owner prev ON s.registering_flock =
000269     prev.flock_no
000270         AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
000271         WHERE NOT EXISTS
000272             (SELECT 'x' FROM flock_owner later
000273             WHERE prev.flock_no = later.flock_no
000274             AND later.owner_change_date > prev.owner_change_date
000275             AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
000276         ) y ON x.sheep_no = y.sheep_no
000277     WHERE y.sheep_no IS NULL
000278     ORDER BY x.registering_flock;
000279  } {
000280    QUERY PLAN
000281    |--MATERIALIZE y
000282    |  |--SCAN s
000283    |  |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
000284    |  `--CORRELATED SCALAR SUBQUERY xxxxxx
000285    |     `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
000286    |--SCAN x USING INDEX sheep_reg_flock_index
000287    `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
000288  }
000289  
000290  
000291  do_execsql_test autoindex1-700 {
000292    CREATE TABLE t5(a, b, c);
000293  }
000294  do_eqp_test autoindex1-700a {
000295    SELECT a FROM t5 WHERE b=10 ORDER BY c;
000296  } {
000297    QUERY PLAN
000298    |--SCAN t5
000299    `--USE TEMP B-TREE FOR ORDER BY
000300  }
000301  
000302  # The following checks a performance issue reported on the sqlite-dev
000303  # mailing list on 2013-01-10
000304  #
000305  do_execsql_test autoindex1-800 {
000306    CREATE TABLE accounts(
000307      _id INTEGER PRIMARY KEY AUTOINCREMENT,
000308      account_name TEXT,
000309      account_type TEXT,
000310      data_set TEXT
000311    );
000312    CREATE TABLE data(
000313      _id INTEGER PRIMARY KEY AUTOINCREMENT,
000314      package_id INTEGER REFERENCES package(_id),
000315      mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
000316      raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
000317      is_read_only INTEGER NOT NULL DEFAULT 0,
000318      is_primary INTEGER NOT NULL DEFAULT 0,
000319      is_super_primary INTEGER NOT NULL DEFAULT 0,
000320      data_version INTEGER NOT NULL DEFAULT 0,
000321      data1 TEXT,
000322      data2 TEXT,
000323      data3 TEXT,
000324      data4 TEXT,
000325      data5 TEXT,
000326      data6 TEXT,
000327      data7 TEXT,
000328      data8 TEXT,
000329      data9 TEXT,
000330      data10 TEXT,
000331      data11 TEXT,
000332      data12 TEXT,
000333      data13 TEXT,
000334      data14 TEXT,
000335      data15 TEXT,
000336      data_sync1 TEXT,
000337      data_sync2 TEXT,
000338      data_sync3 TEXT,
000339      data_sync4 TEXT 
000340    );
000341    CREATE TABLE mimetypes(
000342      _id INTEGER PRIMARY KEY AUTOINCREMENT,
000343      mimetype TEXT NOT NULL
000344    );
000345    CREATE TABLE raw_contacts(
000346      _id INTEGER PRIMARY KEY AUTOINCREMENT,
000347      account_id INTEGER REFERENCES accounts(_id),
000348      sourceid TEXT,
000349      raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
000350      version INTEGER NOT NULL DEFAULT 1,
000351      dirty INTEGER NOT NULL DEFAULT 0,
000352      deleted INTEGER NOT NULL DEFAULT 0,
000353      contact_id INTEGER REFERENCES contacts(_id),
000354      aggregation_mode INTEGER NOT NULL DEFAULT 0,
000355      aggregation_needed INTEGER NOT NULL DEFAULT 1,
000356      custom_ringtone TEXT,
000357      send_to_voicemail INTEGER NOT NULL DEFAULT 0,
000358      times_contacted INTEGER NOT NULL DEFAULT 0,
000359      last_time_contacted INTEGER,
000360      starred INTEGER NOT NULL DEFAULT 0,
000361      display_name TEXT,
000362      display_name_alt TEXT,
000363      display_name_source INTEGER NOT NULL DEFAULT 0,
000364      phonetic_name TEXT,
000365      phonetic_name_style TEXT,
000366      sort_key TEXT,
000367      sort_key_alt TEXT,
000368      name_verified INTEGER NOT NULL DEFAULT 0,
000369      sync1 TEXT,
000370      sync2 TEXT,
000371      sync3 TEXT,
000372      sync4 TEXT,
000373      sync_uid TEXT,
000374      sync_version INTEGER NOT NULL DEFAULT 1,
000375      has_calendar_event INTEGER NOT NULL DEFAULT 0,
000376      modified_time INTEGER,
000377      is_restricted INTEGER DEFAULT 0,
000378      yp_source TEXT,
000379      method_selected INTEGER DEFAULT 0,
000380      custom_vibration_type INTEGER DEFAULT 0,
000381      custom_ringtone_path TEXT,
000382      message_notification TEXT,
000383      message_notification_path TEXT
000384    );
000385    CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
000386    CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
000387    CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
000388    CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
000389    CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
000390    CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
000391    CREATE INDEX raw_contacts_source_id_account_id_index
000392        ON raw_contacts (sourceid, account_id);
000393    ANALYZE sqlite_master;
000394    INSERT INTO sqlite_stat1
000395       VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
000396    INSERT INTO sqlite_stat1
000397       VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
000398    INSERT INTO sqlite_stat1
000399       VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
000400              '1600 1600 1600');
000401    INSERT INTO sqlite_stat1
000402       VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
000403    INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
000404    INSERT INTO sqlite_stat1
000405       VALUES('data','data_mimetype_data1_index','9819 2455 3');
000406    INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
000407    INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
000408    DROP TABLE IF EXISTS sqlite_stat3;
000409    ANALYZE sqlite_master;
000410    
000411    EXPLAIN QUERY PLAN
000412    SELECT * FROM 
000413          data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
000414               JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
000415               JOIN accounts ON (raw_contacts.account_id=accounts._id)
000416     WHERE mimetype_id=10 AND data14 IS NOT NULL;
000417  } {/SEARCH data .*SEARCH raw_contacts/}
000418  do_execsql_test autoindex1-801 {
000419    EXPLAIN QUERY PLAN
000420    SELECT * FROM 
000421          data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
000422               JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
000423               JOIN accounts ON (raw_contacts.account_id=accounts._id)
000424     WHERE mimetypes._id=10 AND data14 IS NOT NULL;
000425  } {/SEARCH data .*SEARCH raw_contacts/}
000426  
000427  # Another test case from an important user of SQLite.  The key feature of
000428  # this test is that the "aggindex" subquery should make use of an
000429  # automatic index.  If it does, the query is fast.  If it does not, the
000430  # query is deathly slow.  It worked OK in 3.7.17 but started going slow
000431  # with version 3.8.0.  The problem was fixed for 3.8.7 by reducing the
000432  # cost estimate for automatic indexes on views and subqueries.
000433  #
000434  db close
000435  forcedelete test.db
000436  sqlite3 db test.db
000437  do_execsql_test autoindex1-900 {
000438    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);
000439    CREATE INDEX date_index ON messages(date_received);
000440    CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
000441    CREATE INDEX date_created_index ON messages(date_created);
000442    CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox);
000443    CREATE INDEX message_document_id_index ON messages(document_id);
000444    CREATE INDEX message_read_index ON messages(read);
000445    CREATE INDEX message_flagged_index ON messages(flagged);
000446    CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
000447    CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
000448    CREATE INDEX message_type_index ON messages(type);
000449    CREATE INDEX message_conversation_id_conversation_position_index ON messages(conversation_id, conversation_position);
000450    CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor);
000451    CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_ancestor);
000452    CREATE INDEX message_sender_subject_automated_conversation_index ON messages(sender, subject, automated_conversation);
000453    CREATE INDEX message_sender_index ON messages(sender);
000454    CREATE INDEX message_root_status ON messages(root_status);
000455    CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, normalized_subject COLLATE RTRIM);
000456    CREATE INDEX subject_subject_index ON subjects(subject);
000457    CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject);
000458    CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, comment, UNIQUE(address, comment));
000459    CREATE INDEX addresses_address_index ON addresses(address);
000460    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);
000461    CREATE INDEX mailboxes_source_index ON mailboxes(source);
000462    CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, mailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id));
000463    CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_id);
000464    CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id);
000465    
000466    explain query plan
000467    SELECT messages.ROWID,
000468           messages.message_id,
000469           messages.remote_id,
000470           messages.date_received,
000471           messages.date_sent,
000472           messages.flags,
000473           messages.size,
000474           messages.color,
000475           messages.date_last_viewed,
000476           messages.subject_prefix,
000477           subjects.subject,
000478           sender.comment,
000479           sender.address,
000480           NULL,
000481           messages.mailbox,
000482           messages.original_mailbox,
000483           NULL,
000484           NULL,
000485           messages.type,
000486           messages.document_id,
000487           sender,
000488           NULL,
000489           messages.conversation_id,
000490           messages.conversation_position,
000491           agglabels.labels
000492     FROM mailboxes AS mailbox
000493          JOIN messages ON mailbox.ROWID = messages.mailbox
000494          LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID
000495          LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID
000496          LEFT OUTER JOIN (
000497                 SELECT message_id, group_concat(mailbox_id) as labels
000498                 FROM labels GROUP BY message_id
000499               ) AS agglabels ON messages.ROWID = agglabels.message_id
000500    WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail')
000501      AND (messages.ROWID IN (
000502              SELECT labels.message_id
000503                FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID
000504               WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX'))
000505      AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8,
000506                               14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28,
000507                               34,40,5,11,17,23,35,41)
000508     ORDER BY date_received DESC;
000509  } {/agglabels USING AUTOMATIC COVERING INDEX/}
000510  
000511  # A test case for VIEWs
000512  #
000513  do_execsql_test autoindex1-901 {
000514    CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
000515    CREATE TABLE t2(a, b);
000516    CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a;
000517    EXPLAIN QUERY PLAN
000518    SELECT t1.z, agg2.m
000519      FROM t1 JOIN agg2 ON t1.y=agg2.m
000520     WHERE t1.x IN (1,2,3);
000521  } {/USING AUTOMATIC COVERING INDEX/}
000522  
000523  # 2015-04-15:  A NULL CollSeq pointer in automatic index creation.
000524  #
000525  do_execsql_test autoindex1-920 {
000526    CREATE TABLE t920(x);
000527    INSERT INTO t920 VALUES(3),(4),(5);
000528    SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
000529  } {5 0 9 5 0 9 5 0 9}
000530  
000531  #-------------------------------------------------------------------------
000532  # An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
000533  # index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
000534  # the following SELECT count(*) would incorrectly return 1.
000535  #
000536  do_execsql_test autoindex1-1010 {
000537    CREATE TABLE t11(w);
000538    CREATE TABLE t12(y);
000539    INSERT INTO t11 VALUES(NULL);
000540    INSERT INTO t12 VALUES('notnull');
000541  }
000542  do_execsql_test autoindex1-1020 {
000543    SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
000544  } 0
000545  
000546  # 2022-04-25
000547  # https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3
000548  #
000549  reset_db
000550  do_execsql_test autoindex-1100 {
000551    CREATE TABLE t1(a INT, b INT);
000552    CREATE TABLE t2(c INT, d INT);
000553    CREATE TABLE t3(e TEXT, f TEXT);
000554    INSERT INTO t1 VALUES(1, 1);
000555    INSERT INTO t2 VALUES(1, 2);
000556    INSERT INTO t3 VALUES('abc', 'def');
000557  } {}
000558  do_execsql_test autoindex-1110 {
000559    SELECT * FROM t1, t2 LEFT JOIN t3 ON (t2.d=1) WHERE t2.c = +t1.a;
000560  } {1 1 1 2 {} {}}
000561  do_execsql_test autoindex-1120 {
000562    SELECT * FROM t1 LEFT JOIN t2 ON (t2.c=+t1.a) LEFT JOIN t3 ON (t2.d IS NULL);
000563  } {1 1 1 2 {} {}}
000564  
000565  finish_test