3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
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.
16 # Call this the "multikey-IN-operator early-out optimization" or
17 # just "IN-early-out" optimization for short.
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
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);
32 UPDATE sqlite_stat1 SET stat='1000000 500000 500 50';
33 ANALYZE sqlite_master;
35 set ::sqlite_search_count 0
39 AND b IN (200,205,201,204)
40 AND c IN (304,302,309,308);
44 set ::sqlite_search_count
45 } {0} ;# Without the IN-early-out optimization, this value would be 15
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.
54 WHERE a IN (98,99,100,101)
57 } {~/(IfNoHope|SeekHit)/}
59 set sqlite_search_count 0
60 do_execsql_test in6-1.4 {
63 AND b IN (200,201,202,204)
64 AND c IN (300,302,301,305)
68 set ::sqlite_search_count
71 do_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)
75 AND b IN (200,201,202,204)
76 AND c IN (300,302,301,305)
78 } {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}}