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 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# If the library is not compiled with automatic index support then 22# skip all tests in this file. 23# 24ifcapable {!autoindex} { 25 finish_test 26 return 27} 28 29# Setup for logging 30db close 31sqlite3_shutdown 32test_sqlite3_log [list lappend ::log] 33set ::log [list] 34sqlite3 db test.db 35 36 37# With automatic index turned off, we do a full scan of the T2 table 38do_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} 53do_test autoindex1-101 { 54 db status step 55} {63} 56do_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. 62do_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} 68do_test autoindex1-111 { 69 db status step 70} {7} 71do_test autoindex1-112 { 72 db status autoindex 73} {7} 74do_test autoindex1-113 { 75 set ::log 76} {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} 77 78db close 79sqlite3_shutdown 80test_sqlite3_log 81sqlite3_initialize 82sqlite3 db test.db 83 84# The same test as above, but this time the T2 query is a subquery rather 85# than a join. 86do_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} 92do_test autoindex1-201 { 93 db status step 94} {35} 95do_test autoindex1-202 { 96 db status autoindex 97} {0} 98do_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} 109do_test autoindex1-211 { 110 db status step 111} {7} 112do_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# 119do_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/} 125do_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} 133do_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# 141do_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} 154do_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# 174do_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} 181do_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 xxxxxx 188 `--SCAN TABLE t502 189} 190do_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 xxxxxx 197 `--SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) 198} 199do_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 xxxxxx 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# 216do_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} 262do_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 xxxxxx 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 290do_execsql_test autoindex1-700 { 291 CREATE TABLE t5(a, b, c); 292} 293do_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# 304do_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/} 417do_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# 433db close 434forcedelete test.db 435sqlite3 db test.db 436do_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# 512do_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# 524do_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# 535do_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} 541do_execsql_test autoindex1-1020 { 542 SELECT count(*) FROM t11 LEFT JOIN t12 WHERE t12.y IS t11.w; 543} 0 544 545 546 547 548finish_test 549