github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/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  proc do_execsql_test_if_vtab {tn sql {res {}}} {
    20    ifcapable vtab { uplevel [list do_execsql_test $tn $sql $res] }
    21  }
    22  
    23  do_execsql_test without_rowid6-100 {
    24    CREATE TABLE t1(a,b,c,d,e, PRIMARY KEY(a,b,c,a,b,c,d,a,b,c)) WITHOUT ROWID;
    25    CREATE INDEX t1a ON t1(b, b);
    26    WITH RECURSIVE
    27      c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<1000)
    28    INSERT INTO t1(a,b,c,d,e) SELECT i, i+1000, printf('x%dy',i), 0, 0 FROM c;
    29    ANALYZE;
    30  } {}
    31  do_execsql_test_if_vtab without_rowid6-101 {
    32    SELECT name, key FROM pragma_index_xinfo('t1');
    33  } {a 1 b 1 c 1 d 1 e 0}
    34  do_execsql_test without_rowid6-110 {
    35    SELECT c FROM t1 WHERE a=123;
    36  } {x123y}
    37  do_execsql_test without_rowid6-120 {
    38    SELECT c FROM t1 WHERE b=1123;
    39  } {x123y}
    40  do_execsql_test without_rowid6-130 {
    41    SELECT c FROM t1 ORDER BY a DESC LIMIT 5;
    42  } {x1000y x999y x998y x997y x996y}
    43  do_execsql_test without_rowid6-140 {
    44    SELECT c FROM t1 ORDER BY b LIMIT 5;
    45  } {x1y x2y x3y x4y x5y}
    46  
    47  # Column t1.b starts out as a unique index, but that index is
    48  # subsequently converted into a PRIMARY KEY.
    49  #
    50  do_execsql_test without_rowid6-200 {
    51    DROP TABLE IF EXISTS t1;
    52    CREATE TABLE t1(
    53      a UNIQUE,
    54      b UNIQUE,
    55      c UNIQUE,
    56      PRIMARY KEY(b)
    57    ) WITHOUT ROWID;
    58    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    59    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    60  } {4 1}
    61  do_execsql_test_if_vtab without_rowid6-201 {
    62    SELECT name, key FROM pragma_index_xinfo('t1');
    63  } {b 1 a 0 c 0}
    64  do_execsql_test without_rowid6-210 {
    65    EXPLAIN QUERY PLAN
    66    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    67  } {/SEARCH t1 USING PRIMARY KEY .b>../}
    68  do_execsql_test without_rowid6-220 {
    69    PRAGMA index_list(t1);
    70  } {/sqlite_autoindex_t1_2 1 pk/}
    71  
    72  do_execsql_test without_rowid6-300 {
    73    DROP TABLE IF EXISTS t1;
    74    CREATE TABLE t1(
    75      a UNIQUE,
    76      b PRIMARY KEY,
    77      c UNIQUE,
    78      UNIQUE(b)
    79    ) WITHOUT ROWID;
    80    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    81    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    82  } {4 1}
    83  do_execsql_test without_rowid6-310 {
    84    EXPLAIN QUERY PLAN
    85    SELECT a FROM t1 WHERE b>3 ORDER BY b;
    86  } {/SEARCH t1 USING PRIMARY KEY .b>../}
    87  do_execsql_test without_rowid6-320 {
    88    PRAGMA index_list(t1);
    89  } {/sqlite_autoindex_t1_2 1 pk/}
    90  
    91  do_execsql_test without_rowid6-400 {
    92    DROP TABLE IF EXISTS t1;
    93    CREATE TABLE t1(
    94      a UNIQUE,
    95      b UNIQUE PRIMARY KEY,
    96      c UNIQUE
    97    ) WITHOUT ROWID;
    98    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
    99    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   100  } {4 1}
   101  do_execsql_test without_rowid6-410 {
   102    EXPLAIN QUERY PLAN
   103    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   104  } {/SEARCH t1 USING PRIMARY KEY .b>../}
   105  do_execsql_test without_rowid6-420 {
   106    PRAGMA index_list(t1);
   107  } {/sqlite_autoindex_t1_2 1 pk/}
   108  
   109  do_execsql_test without_rowid6-500 {
   110    DROP TABLE IF EXISTS t1;
   111    CREATE TABLE t1(a,b,c,
   112      UNIQUE(b,c),
   113      PRIMARY KEY(b,c)
   114    ) WITHOUT ROWID;
   115    INSERT INTO t1(a,b,c) VALUES(1,8,3),(4,5,6),(7,2,9);
   116    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   117  } {4 1}
   118  do_execsql_test_if_vtab without_rowid6-501 {
   119    SELECT name, key FROM pragma_index_xinfo('t1');
   120  } {b 1 c 1 a 0}
   121  do_execsql_test without_rowid6-510 {
   122    EXPLAIN QUERY PLAN
   123    SELECT a FROM t1 WHERE b>3 ORDER BY b;
   124  } {/SEARCH t1 USING PRIMARY KEY .b>../}
   125  do_execsql_test without_rowid6-520 {
   126    PRAGMA index_list(t1);
   127  } {/sqlite_autoindex_t1_1 1 pk/}
   128  
   129  do_catchsql_test without_rowid6-600 {
   130    CREATE TABLE t6(a,b,c,PRIMARY KEY(a,rowid,b))WITHOUT ROWID;
   131  } {1 {no such column: rowid}}
   132  
   133  
   134  finish_test