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