# 2018-06-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.
#
#***********************************************************************
#
# A multi-key index that uses an IN operator on one of the keys other
# than the left-most key is able to abort the IN-operator loop early
# if key terms further to the left do not match.
#
# Call this the "multikey-IN-operator early-out optimization" or
# just "IN-early-out" optimization for short.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix in6
do_test in6-1.1 {
db eval {
CREATE TABLE t1(a,b,c,d);
WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
INSERT INTO t1(a,b,c,d)
SELECT 100, 200+x/2, 300+x/5, x FROM c;
CREATE INDEX t1abc ON t1(a,b,c);
ANALYZE;
UPDATE sqlite_stat1 SET stat='1000000 500000 500 50';
ANALYZE sqlite_master;
}
set ::sqlite_search_count 0
db eval {
SELECT d FROM t1
WHERE a=99
AND b IN (200,205,201,204)
AND c IN (304,302,309,308);
}
} {}
do_test in6-1.2 {
set ::sqlite_search_count
} {0} ;# Without the IN-early-out optimization, this value would be 15
# The multikey-IN-operator early-out optimization does not apply
# when the IN operator is on the left-most column of the index.
#
do_test in6-1.3 {
db eval {
EXPLAIN
SELECT d FROM t1
WHERE a IN (98,99,100,101)
AND b=200 AND c=300;
}
} {~/(IfNoHope|SeekHit)/}
set sqlite_search_count 0
do_execsql_test in6-1.4 {
SELECT d FROM t1
WHERE a=100
AND b IN (200,201,202,204)
AND c IN (300,302,301,305)
ORDER BY +d;
} {1 2 3 4 5 8 9}
do_test in6-1.5 {
set ::sqlite_search_count
} {39}
do_execsql_test in6-2.1 {
CREATE TABLE t2(e INT UNIQUE, f TEXT);
SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d)
WHERE a=100
AND b IN (200,201,202,204)
AND c IN (300,302,301,305)
ORDER BY +d;
} {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}}
finish_test