modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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    0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b=?)} 
    33    0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}
    34  }
    35  
    36  do_execsql_test 1.2 {
    37    SELECT a FROM t1 WHERE b='b' OR c='x'
    38  } {2 3}
    39  
    40  do_execsql_test 1.3 {
    41    SELECT a FROM t1 WHERE b='a' OR c='z'
    42  } {1}
    43  
    44  #----------------------------------------------------------------------
    45  # Try that again, this time with non integer PRIMARY KEY values.
    46  #
    47  do_execsql_test 2.0 {
    48    CREATE TABLE t2(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
    49    INSERT INTO t2 VALUES('i', 'a', 'z');
    50    INSERT INTO t2 VALUES('ii', 'b', 'y');
    51    INSERT INTO t2 VALUES('iii', 'c', 'x');
    52    INSERT INTO t2 VALUES('iv', 'd', 'w');
    53    CREATE INDEX i3 ON t2(b);
    54    CREATE INDEX i4 ON t2(c);
    55  }
    56  
    57  do_eqp_test 2.1 {
    58    SELECT a FROM t2 WHERE b='b' OR c='x'
    59  } {
    60    0 0 0 {SEARCH TABLE t2 USING INDEX i3 (b=?)} 
    61    0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
    62  }
    63  
    64  do_execsql_test 2.2 {
    65    SELECT a FROM t2 WHERE b='b' OR c='x'
    66  } {ii iii}
    67  
    68  do_execsql_test 2.3 {
    69    SELECT a FROM t2 WHERE b='a' OR c='z'
    70  } {i}
    71  
    72  #----------------------------------------------------------------------
    73  # On a table with a multi-column PK.
    74  #
    75  do_execsql_test 3.0 {
    76    CREATE TABLE t3(a, b, c, d, PRIMARY KEY(c, b)) WITHOUT ROWID;
    77  
    78    INSERT INTO t3 VALUES('f', 1, 1, 'o');
    79    INSERT INTO t3 VALUES('o', 2, 1, 't');
    80    INSERT INTO t3 VALUES('t', 1, 2, 't');
    81    INSERT INTO t3 VALUES('t', 2, 2, 'f');
    82  
    83    CREATE INDEX t3i1 ON t3(d);
    84    CREATE INDEX t3i2 ON t3(a);
    85  
    86    SELECT c||'.'||b FROM t3 WHERE a='t' OR d='t'
    87  } {
    88    2.1 2.2 1.2
    89  }
    90  
    91  finish_test