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

     1  # 2014-03-31
     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  # The focus of this file is testing the OR optimization on WITHOUT ROWID 
    12  # tables.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set ::testprefix whereI
    18  
    19  do_execsql_test 1.0 {
    20    CREATE TABLE t1(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
    21    INSERT INTO t1 VALUES(1, 'a', 'z');
    22    INSERT INTO t1 VALUES(2, 'b', 'y');
    23    INSERT INTO t1 VALUES(3, 'c', 'x');
    24    INSERT INTO t1 VALUES(4, 'd', 'w');
    25    CREATE INDEX i1 ON t1(b);
    26    CREATE INDEX i2 ON t1(c);
    27  }
    28  
    29  do_eqp_test 1.1 {
    30    SELECT a FROM t1 WHERE b='b' OR c='x'
    31  } {
    32    QUERY PLAN
    33    `--MULTI-INDEX OR
    34       |--INDEX 1
    35       |  `--SEARCH t1 USING INDEX i1 (b=?)
    36       `--INDEX 2
    37          `--SEARCH t1 USING INDEX i2 (c=?)
    38  }
    39  
    40  do_execsql_test 1.2 {
    41    SELECT a FROM t1 WHERE b='b' OR c='x'
    42  } {2 3}
    43  
    44  do_execsql_test 1.3 {
    45    SELECT a FROM t1 WHERE b='a' OR c='z'
    46  } {1}
    47  
    48  #----------------------------------------------------------------------
    49  # Try that again, this time with non integer PRIMARY KEY values.
    50  #
    51  do_execsql_test 2.0 {
    52    CREATE TABLE t2(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
    53    INSERT INTO t2 VALUES('i', 'a', 'z');
    54    INSERT INTO t2 VALUES('ii', 'b', 'y');
    55    INSERT INTO t2 VALUES('iii', 'c', 'x');
    56    INSERT INTO t2 VALUES('iv', 'd', 'w');
    57    CREATE INDEX i3 ON t2(b);
    58    CREATE INDEX i4 ON t2(c);
    59  }
    60  
    61  do_eqp_test 2.1 {
    62    SELECT a FROM t2 WHERE b='b' OR c='x'
    63  } {
    64    QUERY PLAN
    65    `--MULTI-INDEX OR
    66       |--INDEX 1
    67       |  `--SEARCH t2 USING INDEX i3 (b=?)
    68       `--INDEX 2
    69          `--SEARCH t2 USING INDEX i4 (c=?)
    70  }
    71  
    72  do_execsql_test 2.2 {
    73    SELECT a FROM t2 WHERE b='b' OR c='x'
    74  } {ii iii}
    75  
    76  do_execsql_test 2.3 {
    77    SELECT a FROM t2 WHERE b='a' OR c='z'
    78  } {i}
    79  
    80  #----------------------------------------------------------------------
    81  # On a table with a multi-column PK.
    82  #
    83  do_execsql_test 3.0 {
    84    CREATE TABLE t3(a, b, c, d, PRIMARY KEY(c, b)) WITHOUT ROWID;
    85  
    86    INSERT INTO t3 VALUES('f', 1, 1, 'o');
    87    INSERT INTO t3 VALUES('o', 2, 1, 't');
    88    INSERT INTO t3 VALUES('t', 1, 2, 't');
    89    INSERT INTO t3 VALUES('t', 2, 2, 'f');
    90  
    91    CREATE INDEX t3i1 ON t3(d);
    92    CREATE INDEX t3i2 ON t3(a);
    93  
    94    SELECT c||'.'||b FROM t3 WHERE a='t' OR d='t'
    95  } {
    96    2.1 2.2 1.2
    97  }
    98  
    99  finish_test