summaryrefslogtreecommitdiffstats
path: root/test/autoindex1.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/autoindex1.test')
-rw-r--r--test/autoindex1.test564
1 files changed, 564 insertions, 0 deletions
diff --git a/test/autoindex1.test b/test/autoindex1.test
new file mode 100644
index 0000000..4b290c7
--- /dev/null
+++ b/test/autoindex1.test
@@ -0,0 +1,564 @@
+# 2010 April 07
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#*************************************************************************
+# This file implements regression tests for SQLite library. The
+# focus of this script is testing automatic index creation logic.
+#
+# EVIDENCE-OF: R-34271-33106 PRAGMA automatic_index; PRAGMA
+# automatic_index = boolean; Query, set, or clear the automatic indexing
+# capability.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# If the library is not compiled with automatic index support then
+# skip all tests in this file.
+#
+ifcapable {!autoindex} {
+ finish_test
+ return
+}
+
+# Setup for logging
+db close
+sqlite3_shutdown
+test_sqlite3_log [list lappend ::log]
+set ::log [list]
+sqlite3 db test.db
+
+
+# With automatic index turned off, we do a full scan of the T2 table
+do_test autoindex1-100 {
+ db eval {
+ CREATE TABLE t1(a,b);
+ INSERT INTO t1 VALUES(1,11);
+ INSERT INTO t1 VALUES(2,22);
+ INSERT INTO t1 SELECT a+2, b+22 FROM t1;
+ INSERT INTO t1 SELECT a+4, b+44 FROM t1;
+ CREATE TABLE t2(c,d);
+ INSERT INTO t2 SELECT a, 900+b FROM t1;
+ }
+ db eval {
+ PRAGMA automatic_index=OFF;
+ SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
+ }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-101 {
+ db status step
+} {63}
+do_test autoindex1-102 {
+ db status autoindex
+} {0}
+
+# With autoindex turned on, we build an index once and then use that index
+# to find T2 values.
+do_test autoindex1-110 {
+ db eval {
+ PRAGMA automatic_index=ON;
+ SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
+ }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-111 {
+ db status step
+} {7}
+do_test autoindex1-112 {
+ db status autoindex
+} {7}
+do_test autoindex1-113 {
+ set ::log
+} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}}
+
+db close
+sqlite3_shutdown
+test_sqlite3_log
+sqlite3_initialize
+sqlite3 db test.db
+
+# The same test as above, but this time the T2 query is a subquery rather
+# than a join.
+do_test autoindex1-200 {
+ db eval {
+ PRAGMA automatic_index=OFF;
+ SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
+ }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-201 {
+ db status step
+} {35}
+do_test autoindex1-202 {
+ db status autoindex
+} {0}
+do_test autoindex1-210 {
+ db eval {
+ PRAGMA automatic_index=ON;
+ ANALYZE;
+ UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1';
+ -- Table t2 actually contains 8 rows.
+ UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2';
+ ANALYZE sqlite_master;
+ SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
+ }
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-211 {
+ db status step
+} {7}
+do_test autoindex1-212 {
+ db status autoindex
+} {7}
+
+
+# Modify the second table of the join while the join is in progress
+#
+do_execsql_test autoindex1-299 {
+ UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2';
+ ANALYZE sqlite_master;
+ EXPLAIN QUERY PLAN
+ SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a);
+} {/AUTOMATIC COVERING INDEX/}
+do_test autoindex1-300 {
+ set r {}
+ db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} {
+ lappend r $b $d
+ db eval {UPDATE t2 SET d=d+1}
+ }
+ set r
+} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
+do_test autoindex1-310 {
+ db eval {SELECT d FROM t2 ORDER BY d}
+} {919 930 941 952 963 974 985 996}
+
+# The next test does a 10-way join on unindexed tables. Without
+# automatic indices, the join will take a long time to complete.
+# With automatic indices, it should only take about a second.
+#
+do_test autoindex1-400 {
+ db eval {
+ CREATE TABLE t4(a, b);
+ INSERT INTO t4 VALUES(1,2);
+ INSERT INTO t4 VALUES(2,3);
+ }
+ for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
+ db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
+ }
+ db eval {
+ SELECT count(*) FROM t4;
+ }
+} {4096}
+do_test autoindex1-401 {
+ db eval {
+ SELECT count(*)
+ FROM t4 AS x1
+ JOIN t4 AS x2 ON x2.a=x1.b
+ JOIN t4 AS x3 ON x3.a=x2.b
+ JOIN t4 AS x4 ON x4.a=x3.b
+ JOIN t4 AS x5 ON x5.a=x4.b
+ JOIN t4 AS x6 ON x6.a=x5.b
+ JOIN t4 AS x7 ON x7.a=x6.b
+ JOIN t4 AS x8 ON x8.a=x7.b
+ JOIN t4 AS x9 ON x9.a=x8.b
+ JOIN t4 AS x10 ON x10.a=x9.b;
+ }
+} {4087}
+
+# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
+# Make sure automatic indices are not created for the RHS of an IN expression
+# that is not a correlated subquery.
+#
+do_execsql_test autoindex1-500 {
+ CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
+ CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
+ INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
+ INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
+ ANALYZE sqlite_master;
+}
+do_eqp_test autoindex1-500.1 {
+ SELECT b FROM t501
+ WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
+} {
+ QUERY PLAN
+ |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
+ `--LIST SUBQUERY xxxxxx
+ `--SCAN t502
+}
+do_eqp_test autoindex1-501 {
+ SELECT b FROM t501
+ WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
+} {
+ QUERY PLAN
+ |--SCAN t501
+ `--CORRELATED LIST SUBQUERY xxxxxx
+ `--SEARCH t502 USING AUTOMATIC COVERING INDEX (y=?)
+}
+do_eqp_test autoindex1-502 {
+ SELECT b FROM t501
+ WHERE t501.a=123
+ AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
+} {
+ QUERY PLAN
+ |--SEARCH t501 USING INTEGER PRIMARY KEY (rowid=?)
+ `--CORRELATED LIST SUBQUERY xxxxxx
+ `--SCAN t502
+}
+
+# The following code checks a performance regression reported on the
+# mailing list on 2010-10-19. The problem is that the nRowEst field
+# of ephermeral tables was not being initialized correctly and so no
+# automatic index was being created for the emphemeral table when it was
+# used as part of a join.
+#
+do_execsql_test autoindex1-600 {
+ CREATE TABLE flock_owner(
+ owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
+ flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
+ owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
+ owner_change_date TEXT, last_changed TEXT NOT NULL,
+ CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
+ );
+ CREATE TABLE sheep (
+ Sheep_No char(7) NOT NULL,
+ Date_of_Birth char(8),
+ Sort_DoB text,
+ Flock_Book_Vol char(2),
+ Breeder_No char(6),
+ Breeder_Person integer,
+ Originating_Flock char(6),
+ Registering_Flock char(6),
+ Tag_Prefix char(9),
+ Tag_No char(15),
+ Sort_Tag_No integer,
+ Breeders_Temp_Tag char(15),
+ Sex char(1),
+ Sheep_Name char(32),
+ Sire_No char(7),
+ Dam_No char(7),
+ Register_Code char(1),
+ Colour char(48),
+ Colour_Code char(2),
+ Pattern_Code char(8),
+ Horns char(1),
+ Litter_Size char(1),
+ Coeff_of_Inbreeding real,
+ Date_of_Registration text,
+ Date_Last_Changed text,
+ UNIQUE(Sheep_No));
+ CREATE INDEX fo_flock_no_index
+ ON flock_owner (flock_no);
+ CREATE INDEX fo_owner_change_date_index
+ ON flock_owner (owner_change_date);
+ CREATE INDEX fo_owner_person_id_index
+ ON flock_owner (owner_person_id);
+ CREATE INDEX sheep_org_flock_index
+ ON sheep (originating_flock);
+ CREATE INDEX sheep_reg_flock_index
+ ON sheep (registering_flock);
+}
+do_eqp_test autoindex1-600a {
+ SELECT x.sheep_no, x.registering_flock, x.date_of_registration
+ FROM sheep x LEFT JOIN
+ (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
+ s.date_of_registration, prev.owner_change_date
+ FROM sheep s JOIN flock_owner prev ON s.registering_flock =
+ prev.flock_no
+ AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
+ WHERE NOT EXISTS
+ (SELECT 'x' FROM flock_owner later
+ WHERE prev.flock_no = later.flock_no
+ AND later.owner_change_date > prev.owner_change_date
+ AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
+ ) y ON x.sheep_no = y.sheep_no
+ WHERE y.sheep_no IS NULL
+ ORDER BY x.registering_flock;
+} {
+ QUERY PLAN
+ |--MATERIALIZE y
+ | |--SCAN s
+ | |--SEARCH prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
+ | `--CORRELATED SCALAR SUBQUERY xxxxxx
+ | `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
+ |--SCAN x USING INDEX sheep_reg_flock_index
+ `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
+}
+
+
+do_execsql_test autoindex1-700 {
+ CREATE TABLE t5(a, b, c);
+}
+do_eqp_test autoindex1-700a {
+ SELECT a FROM t5 WHERE b=10 ORDER BY c;
+} {
+ QUERY PLAN
+ |--SCAN t5
+ `--USE TEMP B-TREE FOR ORDER BY
+}
+
+# The following checks a performance issue reported on the sqlite-dev
+# mailing list on 2013-01-10
+#
+do_execsql_test autoindex1-800 {
+ CREATE TABLE accounts(
+ _id INTEGER PRIMARY KEY AUTOINCREMENT,
+ account_name TEXT,
+ account_type TEXT,
+ data_set TEXT
+ );
+ CREATE TABLE data(
+ _id INTEGER PRIMARY KEY AUTOINCREMENT,
+ package_id INTEGER REFERENCES package(_id),
+ mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
+ raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
+ is_read_only INTEGER NOT NULL DEFAULT 0,
+ is_primary INTEGER NOT NULL DEFAULT 0,
+ is_super_primary INTEGER NOT NULL DEFAULT 0,
+ data_version INTEGER NOT NULL DEFAULT 0,
+ data1 TEXT,
+ data2 TEXT,
+ data3 TEXT,
+ data4 TEXT,
+ data5 TEXT,
+ data6 TEXT,
+ data7 TEXT,
+ data8 TEXT,
+ data9 TEXT,
+ data10 TEXT,
+ data11 TEXT,
+ data12 TEXT,
+ data13 TEXT,
+ data14 TEXT,
+ data15 TEXT,
+ data_sync1 TEXT,
+ data_sync2 TEXT,
+ data_sync3 TEXT,
+ data_sync4 TEXT
+ );
+ CREATE TABLE mimetypes(
+ _id INTEGER PRIMARY KEY AUTOINCREMENT,
+ mimetype TEXT NOT NULL
+ );
+ CREATE TABLE raw_contacts(
+ _id INTEGER PRIMARY KEY AUTOINCREMENT,
+ account_id INTEGER REFERENCES accounts(_id),
+ sourceid TEXT,
+ raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,
+ version INTEGER NOT NULL DEFAULT 1,
+ dirty INTEGER NOT NULL DEFAULT 0,
+ deleted INTEGER NOT NULL DEFAULT 0,
+ contact_id INTEGER REFERENCES contacts(_id),
+ aggregation_mode INTEGER NOT NULL DEFAULT 0,
+ aggregation_needed INTEGER NOT NULL DEFAULT 1,
+ custom_ringtone TEXT,
+ send_to_voicemail INTEGER NOT NULL DEFAULT 0,
+ times_contacted INTEGER NOT NULL DEFAULT 0,
+ last_time_contacted INTEGER,
+ starred INTEGER NOT NULL DEFAULT 0,
+ display_name TEXT,
+ display_name_alt TEXT,
+ display_name_source INTEGER NOT NULL DEFAULT 0,
+ phonetic_name TEXT,
+ phonetic_name_style TEXT,
+ sort_key TEXT,
+ sort_key_alt TEXT,
+ name_verified INTEGER NOT NULL DEFAULT 0,
+ sync1 TEXT,
+ sync2 TEXT,
+ sync3 TEXT,
+ sync4 TEXT,
+ sync_uid TEXT,
+ sync_version INTEGER NOT NULL DEFAULT 1,
+ has_calendar_event INTEGER NOT NULL DEFAULT 0,
+ modified_time INTEGER,
+ is_restricted INTEGER DEFAULT 0,
+ yp_source TEXT,
+ method_selected INTEGER DEFAULT 0,
+ custom_vibration_type INTEGER DEFAULT 0,
+ custom_ringtone_path TEXT,
+ message_notification TEXT,
+ message_notification_path TEXT
+ );
+ CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
+ CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
+ CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
+ CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
+ CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
+ CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
+ CREATE INDEX raw_contacts_source_id_account_id_index
+ ON raw_contacts (sourceid, account_id);
+ ANALYZE sqlite_master;
+ INSERT INTO sqlite_stat1
+ VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4');
+ INSERT INTO sqlite_stat1
+ VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
+ INSERT INTO sqlite_stat1
+ VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
+ '1600 1600 1600');
+ INSERT INTO sqlite_stat1
+ VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
+ INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
+ INSERT INTO sqlite_stat1
+ VALUES('data','data_mimetype_data1_index','9819 2455 3');
+ INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
+ INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
+ DROP TABLE IF EXISTS sqlite_stat3;
+ ANALYZE sqlite_master;
+
+ EXPLAIN QUERY PLAN
+ SELECT * FROM
+ data JOIN mimetypes ON (data.mimetype_id=mimetypes._id)
+ JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id)
+ JOIN accounts ON (raw_contacts.account_id=accounts._id)
+ WHERE mimetype_id=10 AND data14 IS NOT NULL;
+} {/SEARCH data .*SEARCH raw_contacts/}
+do_execsql_test autoindex1-801 {
+ EXPLAIN QUERY PLAN
+ SELECT * FROM
+ data JOIN mimetypes ON (data.mimetype_id=mimetypes._id)
+ JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id)
+ JOIN accounts ON (raw_contacts.account_id=accounts._id)
+ WHERE mimetypes._id=10 AND data14 IS NOT NULL;
+} {/SEARCH data .*SEARCH raw_contacts/}
+
+# Another test case from an important user of SQLite. The key feature of
+# this test is that the "aggindex" subquery should make use of an
+# automatic index. If it does, the query is fast. If it does not, the
+# query is deathly slow. It worked OK in 3.7.17 but started going slow
+# with version 3.8.0. The problem was fixed for 3.8.7 by reducing the
+# cost estimate for automatic indexes on views and subqueries.
+#
+db close
+forcedelete test.db
+sqlite3 db test.db
+do_execsql_test autoindex1-900 {
+ 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);
+ CREATE INDEX date_index ON messages(date_received);
+ CREATE INDEX date_last_viewed_index ON messages(date_last_viewed);
+ CREATE INDEX date_created_index ON messages(date_created);
+ CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox);
+ CREATE INDEX message_document_id_index ON messages(document_id);
+ CREATE INDEX message_read_index ON messages(read);
+ CREATE INDEX message_flagged_index ON messages(flagged);
+ CREATE INDEX message_mailbox_index ON messages(mailbox, date_received);
+ CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_id);
+ CREATE INDEX message_type_index ON messages(type);
+ CREATE INDEX message_conversation_id_conversation_position_index ON messages(conversation_id, conversation_position);
+ CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor);
+ CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_ancestor);
+ CREATE INDEX message_sender_subject_automated_conversation_index ON messages(sender, subject, automated_conversation);
+ CREATE INDEX message_sender_index ON messages(sender);
+ CREATE INDEX message_root_status ON messages(root_status);
+ CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, normalized_subject COLLATE RTRIM);
+ CREATE INDEX subject_subject_index ON subjects(subject);
+ CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject);
+ CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, comment, UNIQUE(address, comment));
+ CREATE INDEX addresses_address_index ON addresses(address);
+ 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);
+ CREATE INDEX mailboxes_source_index ON mailboxes(source);
+ CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, mailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id));
+ CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_id);
+ CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id);
+
+ explain query plan
+ SELECT messages.ROWID,
+ messages.message_id,
+ messages.remote_id,
+ messages.date_received,
+ messages.date_sent,
+ messages.flags,
+ messages.size,
+ messages.color,
+ messages.date_last_viewed,
+ messages.subject_prefix,
+ subjects.subject,
+ sender.comment,
+ sender.address,
+ NULL,
+ messages.mailbox,
+ messages.original_mailbox,
+ NULL,
+ NULL,
+ messages.type,
+ messages.document_id,
+ sender,
+ NULL,
+ messages.conversation_id,
+ messages.conversation_position,
+ agglabels.labels
+ FROM mailboxes AS mailbox
+ JOIN messages ON mailbox.ROWID = messages.mailbox
+ LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID
+ LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID
+ LEFT OUTER JOIN (
+ SELECT message_id, group_concat(mailbox_id) as labels
+ FROM labels GROUP BY message_id
+ ) AS agglabels ON messages.ROWID = agglabels.message_id
+ WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail')
+ AND (messages.ROWID IN (
+ SELECT labels.message_id
+ FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID
+ WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX'))
+ AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8,
+ 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28,
+ 34,40,5,11,17,23,35,41)
+ ORDER BY date_received DESC;
+} {/agglabels USING AUTOMATIC COVERING INDEX/}
+
+# A test case for VIEWs
+#
+do_execsql_test autoindex1-901 {
+ CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);
+ CREATE TABLE t2(a, b);
+ CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a;
+ EXPLAIN QUERY PLAN
+ SELECT t1.z, agg2.m
+ FROM t1 JOIN agg2 ON t1.y=agg2.m
+ WHERE t1.x IN (1,2,3);
+} {/USING AUTOMATIC COVERING INDEX/}
+
+# 2015-04-15: A NULL CollSeq pointer in automatic index creation.
+#
+do_execsql_test autoindex1-920 {
+ CREATE TABLE t920(x);
+ INSERT INTO t920 VALUES(3),(4),(5);
+ SELECT * FROM t920,(SELECT 0 FROM t920),(VALUES(9)) WHERE 5 IN (x);
+} {5 0 9 5 0 9 5 0 9}
+
+#-------------------------------------------------------------------------
+# An IS term from the WHERE clause of a LEFT JOIN cannot be used as an
+# index driver for the RHS of a LEFT JOIN. Prior to this being fixed,
+# the following SELECT count(*) would incorrectly return 1.
+#
+do_execsql_test autoindex1-1010 {
+ CREATE TABLE t11(w);
+ CREATE TABLE t12(y);
+ INSERT INTO t11 VALUES(NULL);
+ INSERT INTO t12 VALUES('notnull');
+}
+do_execsql_test autoindex1-1020 {
+ SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w;
+} 0
+
+# 2022-04-25
+# https://sqlite.org/forum/forumpost/0d3200f4f3bcd3a3
+#
+reset_db
+do_execsql_test autoindex-1100 {
+ CREATE TABLE t1(a INT, b INT);
+ CREATE TABLE t2(c INT, d INT);
+ CREATE TABLE t3(e TEXT, f TEXT);
+ INSERT INTO t1 VALUES(1, 1);
+ INSERT INTO t2 VALUES(1, 2);
+ INSERT INTO t3 VALUES('abc', 'def');
+} {}
+do_execsql_test autoindex-1110 {
+ SELECT * FROM t1, t2 LEFT JOIN t3 ON (t2.d=1) WHERE t2.c = +t1.a;
+} {1 1 1 2 {} {}}
+do_execsql_test autoindex-1120 {
+ SELECT * FROM t1 LEFT JOIN t2 ON (t2.c=+t1.a) LEFT JOIN t3 ON (t2.d IS NULL);
+} {1 1 1 2 {} {}}
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-16 12:56:13 +0000