1# 2018-06-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# 12# A multi-key index that uses an IN operator on one of the keys other 13# than the left-most key is able to abort the IN-operator loop early 14# if key terms further to the left do not match. 15# 16# Call this the "multikey-IN-operator early-out optimization" or 17# just "IN-early-out" optimization for short. 18# 19 20set testdir [file dirname $argv0] 21source $testdir/tester.tcl 22set testprefix in6 23 24do_test in6-1.1 { 25 db eval { 26 CREATE TABLE t1(a,b,c,d); 27 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) 28 INSERT INTO t1(a,b,c,d) 29 SELECT 100, 200+x/2, 300+x/5, x FROM c; 30 CREATE INDEX t1abc ON t1(a,b,c); 31 ANALYZE; 32 UPDATE sqlite_stat1 SET stat='1000000 500000 500 50'; 33 ANALYZE sqlite_master; 34 } 35 set ::sqlite_search_count 0 36 db eval { 37 SELECT d FROM t1 38 WHERE a=99 39 AND b IN (200,205,201,204) 40 AND c IN (304,302,309,308); 41 } 42} {} 43do_test in6-1.2 { 44 set ::sqlite_search_count 45} {0} ;# Without the IN-early-out optimization, this value would be 15 46 47# The multikey-IN-operator early-out optimization does not apply 48# when the IN operator is on the left-most column of the index. 49# 50do_test in6-1.3 { 51 db eval { 52 EXPLAIN 53 SELECT d FROM t1 54 WHERE a IN (98,99,100,101) 55 AND b=200 AND c=300; 56 } 57} {~/(IfNoHope|SeekHit)/} 58 59set sqlite_search_count 0 60do_execsql_test in6-1.4 { 61 SELECT d FROM t1 62 WHERE a=100 63 AND b IN (200,201,202,204) 64 AND c IN (300,302,301,305) 65 ORDER BY +d; 66} {1 2 3 4 5 8 9} 67do_test in6-1.5 { 68 set ::sqlite_search_count 69} {39} 70 71do_execsql_test in6-2.1 { 72 CREATE TABLE t2(e INT UNIQUE, f TEXT); 73 SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d) 74 WHERE a=100 75 AND b IN (200,201,202,204) 76 AND c IN (300,302,301,305) 77 ORDER BY +d; 78} {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}} 79 80# 2020-03-16 ticket 82b588d342d515d1 81# Ensure that the IN-early-out optimization works with LEFT JOINs 82# 83reset_db 84do_execsql_test in6-3.100 { 85 CREATE TABLE t1(a); 86 INSERT INTO t1 VALUES(0); 87 CREATE TABLE t2(b, c, d); 88 INSERT INTO t2(b,c,d) VALUES(4,5,3),(4,5,4),(4,5,8); 89 CREATE INDEX t2bcd ON t2(b, c, d); 90 SELECT * FROM t1 LEFT JOIN t2 ON b=NULL AND c=5 AND d IN (2,3,4); 91} {0 {} {} {}} 92do_execsql_test in6-3.110 { 93 CREATE TABLE v0(v1); 94 CREATE TABLE v3(v5, v4); 95 INSERT INTO v0 VALUES(0); 96 CREATE INDEX v9 ON v3(v4, v4, v5); 97 SELECT quote(v5) FROM v0 LEFT JOIN v3 ON v4 = NULL AND v5 IN(0); 98} {NULL} 99 100finish_test 101