gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/in6.test (about)

     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