modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/without_rowid6.test (about)

     1  # 2014-12-28
     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  # Verify that WITHOUT ROWID tables work correctly when the PRIMARY KEY
    13  # has redundant columns.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  do_execsql_test without_rowid6-100 {
    20    CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
    21    CREATE INDEX t1a ON t1(b, b);
    22    WITH RECURSIVE
    23      c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
    24    INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
    25    ANALYZE;
    26  } {}
    27  do_execsql_test without_rowid6-110 {
    28    SELECT c FROM t1 WHERE a=123;
    29  } {x123y}
    30  do_execsql_test without_rowid6-120 {
    31    SELECT c FROM t1 WHERE b=1123;
    32  } {x123y}
    33  do_execsql_test without_rowid6-130 {
    34    SELECT c FROM t1 ORDER BY a DESC LIMIT 5;
    35  } {x1000y x999y x998y x997y x996y}
    36  do_execsql_test without_rowid6-140 {
    37    SELECT c FROM t1 ORDER BY b LIMIT 5;
    38  } {x1y x2y x3y x4y x5y}
    39  
    40  # Column t1.b starts out as a unique index, but that index is
    41  # subsequently converted into a PRIMARY KEY.
    42  #
    43  do_execsql_test without_rowid6-200 {
    44    DROP TABLE IF EXISTS t1;
    45    CREATE TABLE t1(
    46      a UNIQUE,
    47      b UNIQUE,
    48      c UNIQUE,
    49      PRIMARY KEY(b)
    50    ) WITHOUT ROWID;
    51    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    52    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    53  } {4 1}
    54  do_execsql_test without_rowid6-210 {
    55    EXPLAIN QUERY PLAN
    56    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    57  } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
    58  do_execsql_test without_rowid6-220 {
    59    PRAGMA index_list(t1);
    60  } {/sqlite_autoindex_t1_2 1 pk/}
    61  
    62  do_execsql_test without_rowid6-300 {
    63    DROP TABLE IF EXISTS t1;
    64    CREATE TABLE t1(
    65      a UNIQUE,
    66      b PRIMARY KEY,
    67      c UNIQUE,
    68      UNIQUE(b)
    69    ) WITHOUT ROWID;
    70    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    71    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    72  } {4 1}
    73  do_execsql_test without_rowid6-310 {
    74    EXPLAIN QUERY PLAN
    75    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    76  } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
    77  do_execsql_test without_rowid6-320 {
    78    PRAGMA index_list(t1);
    79  } {/sqlite_autoindex_t1_2 1 pk/}
    80  
    81  do_execsql_test without_rowid6-400 {
    82    DROP TABLE IF EXISTS t1;
    83    CREATE TABLE t1(
    84      a UNIQUE,
    85      b UNIQUE PRIMARY KEY,
    86      c UNIQUE
    87    ) WITHOUT ROWID;
    88    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    89    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    90  } {4 1}
    91  do_execsql_test without_rowid6-410 {
    92    EXPLAIN QUERY PLAN
    93    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    94  } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
    95  do_execsql_test without_rowid6-420 {
    96    PRAGMA index_list(t1);
    97  } {/sqlite_autoindex_t1_2 1 pk/}
    98  
    99  do_execsql_test without_rowid6-500 {
   100    DROP TABLE IF EXISTS t1;
   101    CREATE TABLE t1(a,b,c,
   102      UNIQUE(b,c),
   103      PRIMARY KEY(b,c)
   104    ) WITHOUT ROWID;
   105    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
   106    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   107  } {4 1}
   108  do_execsql_test without_rowid6-510 {
   109    EXPLAIN QUERY PLAN
   110    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   111  } {/SEARCH TABLE t1 USING PRIMARY KEY .b>../}
   112  do_execsql_test without_rowid6-520 {
   113    PRAGMA index_list(t1);
   114  } {/sqlite_autoindex_t1_1 1 pk/}
   115  
   116  do_catchsql_test without_rowid6-600 {
   117    CREATE TABLE t6(a,b,c,PRIMARY KEY(a,rowid,b))WITHOUT ROWID;
   118  } {1 {no such column: rowid}}
   119  
   120  
   121  finish_test