test/in6.test

   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
  20 set testdir [file dirname $argv0]
  21 source $testdir/tester.tcl
  22 set testprefix in6
  23
  24 do_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 } {}
  43 do_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 #
  50 do_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
  59 set sqlite_search_count 0
  60 do_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}
  67 do_test in6-1.5 {
  68   set ::sqlite_search_count
  69 } {39}
  70
  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)
  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 #
  83 reset_db
  84 do_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 {} {} {}}
  92 do_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
 100 # 2021-04-29 forum https://sqlite.org/forum/forumpost/6a3ec138e9
 101 # An early OP_IsNull bypass might skip over the OP_Affinity and
 102 # cause the OP_IfNoHope to jump on a false-positive, resulting in
 103 # incomplete output.
 104 #
 105 reset_db
 106 do_execsql_test in6-3.120 {
 107   CREATE TABLE t1(a TEXT, b TEXT);
 108   INSERT INTO t1 VALUES(null,10),(0,10),(10,10);
 109   CREATE INDEX t1ab ON t1(a,b);
 110   SELECT quote(a), quote(b), '|' FROM t1 WHERE b in (SELECT a FROM t1) AND a=0;
 111 } {'0' '10' |}
 112 do_execsql_test in6-3.130 {
 113   CREATE TABLE t2(x TEXT);
 114   INSERT INTO t2(x) VALUES(NULL),(0),(10);
 115   SELECT quote(x), quote(a), quote(b), 'x'
 116     FROM t2 LEFT JOIN t1 ON a=x AND b in (null,0,10);
 117 } {NULL NULL NULL x '0' '0' '10' x '10' '10' '10' x}
 118
 119 finish_test