test/without_rowid5.test

   1 # 2013-11-26
   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 # Requirements testing for WITHOUT ROWID tables.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 ifcapable !incrblob {
  19   finish_test
  20   return
  21 }
  22
  23 # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
  24 # special column, usually called the "rowid", that uniquely identifies
  25 # that row within the table.
  26 #
  27 # EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is
  28 # added to the end of a CREATE TABLE statement, then the special "rowid"
  29 # column is omitted.
  30 #
  31 do_execsql_test without_rowid5-1.1 {
  32   CREATE TABLE t1(a PRIMARY KEY,b,c);
  33   CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID;
  34   INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306);
  35   INSERT INTO t1w SELECT a,b,c FROM t1;
  36   SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC;
  37 } {1 1 1 2 2 2 3 3 3}
  38 do_catchsql_test without_rowid5-1.2 {
  39   SELECT rowid FROM t1w;
  40 } {1 {no such column: rowid}}
  41 do_catchsql_test without_rowid5-1.3 {
  42   SELECT _rowid_ FROM t1w;
  43 } {1 {no such column: _rowid_}}
  44 do_catchsql_test without_rowid5-1.4 {
  45   SELECT oid FROM t1w;
  46 } {1 {no such column: oid}}
  47
  48 # EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add
  49 # the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement.
  50 # For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY
  51 # KEY, cnt INTEGER ) WITHOUT ROWID;
  52 #
  53 do_execsql_test without_rowid5-2.1 {
  54   CREATE TABLE IF NOT EXISTS wordcount(
  55     word TEXT PRIMARY KEY,
  56     cnt INTEGER
  57   ) WITHOUT ROWID;
  58   INSERT INTO wordcount VALUES('one',1);
  59 } {}
  60 do_catchsql_test without_rowid5-2.2 {
  61   SELECT rowid FROM wordcount;
  62 } {1 {no such column: rowid}}
  63
  64 # EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the
  65 # keywords does not matter. One can write "WITHOUT rowid" or "without
  66 # rowid" or "WiThOuT rOwId" and it will mean the same thing.
  67 #
  68 do_execsql_test without_rowid5-2.3 {
  69   CREATE TABLE IF NOT EXISTS wordcount_b(
  70     word TEXT PRIMARY KEY,
  71     cnt INTEGER
  72   ) WITHOUT rowid;
  73   INSERT INTO wordcount_b VALUES('one',1);
  74 } {}
  75 do_catchsql_test without_rowid5-2.4 {
  76   SELECT rowid FROM wordcount_b;
  77 } {1 {no such column: rowid}}
  78 do_execsql_test without_rowid5-2.5 {
  79   CREATE TABLE IF NOT EXISTS wordcount_c(
  80     word TEXT PRIMARY KEY,
  81     cnt INTEGER
  82   ) without rowid;
  83   INSERT INTO wordcount_c VALUES('one',1);
  84 } {}
  85 do_catchsql_test without_rowid5-2.6 {
  86   SELECT rowid FROM wordcount_c;
  87 } {1 {no such column: rowid}}
  88 do_execsql_test without_rowid5-2.7 {
  89   CREATE TABLE IF NOT EXISTS wordcount_d(
  90     word TEXT PRIMARY KEY,
  91     cnt INTEGER
  92   ) WITHOUT rowid;
  93   INSERT INTO wordcount_d VALUES('one',1);
  94 } {}
  95 do_catchsql_test without_rowid5-2.8 {
  96   SELECT rowid FROM wordcount_d;
  97 } {1 {no such column: rowid}}
  98
  99 # EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword
 100 # in the CREATE TABLE statement.
 101 #
 102 do_catchsql_test without_rowid5-3.1 {
 103   CREATE TABLE IF NOT EXISTS error1(
 104     word TEXT PRIMARY KEY,
 105     cnt INTEGER
 106   ) WITHOUT _rowid_;
 107 } {1 {unknown table option: _rowid_}}
 108 do_catchsql_test without_rowid5-3.2 {
 109   CREATE TABLE IF NOT EXISTS error2(
 110     word TEXT PRIMARY KEY,
 111     cnt INTEGER
 112   ) WITHOUT oid;
 113 } {1 {unknown table option: oid}}
 114
 115 # EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE
 116 # statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
 117 #
 118 # EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a
 119 # PRIMARY KEY.
 120 #
 121 # EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table
 122 # without a PRIMARY KEY results in an error.
 123 #
 124 do_catchsql_test without_rowid5-4.1 {
 125   CREATE TABLE IF NOT EXISTS error3(
 126     word TEXT UNIQUE,
 127     cnt INTEGER
 128   ) WITHOUT ROWID;
 129 } {1 {PRIMARY KEY missing on table error3}}
 130
 131 # EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER
 132 # PRIMARY KEY" do not apply on WITHOUT ROWID tables.
 133 #
 134 do_execsql_test without_rowid5-5.1 {
 135   CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID;
 136   INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key
 137   SELECT * FROM ipk;
 138 } {rival bonus}
 139 do_catchsql_test without_rowid5-5.2a {
 140   BEGIN;
 141   INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys
 142 } {1 {NOT NULL constraint failed: ipk.key}}
 143 do_execsql_test without_rowid5-5.2b {
 144   ROLLBACK;
 145 } {}
 146
 147 # EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT
 148 # ROWID tables.
 149 #
 150 # EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT"
 151 # keyword is used in the CREATE TABLE statement for a WITHOUT ROWID
 152 # table.
 153 #
 154 do_catchsql_test without_rowid5-5.3 {
 155   CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID;
 156 } {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}
 157
 158 # EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the
 159 # PRIMARY KEY in a WITHOUT ROWID table.
 160 #
 161 # EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate
 162 # the SQL standard and allow NULL values in PRIMARY KEY fields.
 163 #
 164 # EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the
 165 # standard and will throw an error on any attempt to insert a NULL into
 166 # a PRIMARY KEY column.
 167 #
 168 do_execsql_test without_rowid5-5.4 {
 169   CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e));
 170   CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID;
 171   INSERT INTO nn VALUES(1,2,3,4,5);
 172   INSERT INTO nnw VALUES(1,2,3,4,5);
 173 } {}
 174 do_execsql_test without_rowid5-5.5 {
 175   INSERT INTO nn VALUES(NULL, 3,4,5,6);
 176   INSERT INTO nn VALUES(3,4,NULL,7,8);
 177   INSERT INTO nn VALUES(4,5,6,7,NULL);
 178   SELECT count(*) FROM nn;
 179 } {4}
 180 do_catchsql_test without_rowid5-5.6 {
 181   INSERT INTO nnw VALUES(NULL, 3,4,5,6);
 182 } {1 {NOT NULL constraint failed: nnw.a}}
 183 do_catchsql_test without_rowid5-5.7 {
 184   INSERT INTO nnw VALUES(3,4,NULL,7,8)
 185 } {1 {NOT NULL constraint failed: nnw.c}}
 186 do_catchsql_test without_rowid5-5.8 {
 187   INSERT INTO nnw VALUES(4,5,6,7,NULL)
 188 } {1 {NOT NULL constraint failed: nnw.e}}
 189 do_execsql_test without_rowid5-5.9 {
 190   SELECT count(*) FROM nnw;
 191 } {1}
 192
 193 # Ticket f2be158c57aaa8c6 (2021-08-18)
 194 # NOT NULL ON CONFLICT clauses work on WITHOUT ROWID tables now.
 195 #
 196 do_test without_rowid5-5.100 {
 197   db eval {
 198     DROP TABLE IF EXISTS t5;
 199     CREATE TABLE t5(
 200       a INT NOT NULL ON CONFLICT ROLLBACK,
 201       b TEXT,
 202       c TEXT,
 203       PRIMARY KEY(a,b)
 204     ) WITHOUT ROWID;
 205     BEGIN;
 206     INSERT INTO t5(a,b,c) VALUES(1,2,3);
 207   }
 208   catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
 209   db eval {
 210     SELECT * FROM t5;
 211   }
 212 } {}
 213 do_test without_rowid5-5.101 {
 214   db eval {
 215     DROP TABLE IF EXISTS t5;
 216     CREATE TABLE t5(
 217       a INT NOT NULL ON CONFLICT ABORT,
 218       b TEXT,
 219       c TEXT,
 220       PRIMARY KEY(a,b)
 221     ) WITHOUT ROWID;
 222     BEGIN;
 223     INSERT INTO t5(a,b,c) VALUES(1,2,3);
 224   }
 225   catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
 226   db eval {
 227     COMMIT;
 228     SELECT * FROM t5;
 229   }
 230 } {1 2 3}
 231 do_test without_rowid5-5.102 {
 232   db eval {
 233     DROP TABLE IF EXISTS t5;
 234     CREATE TABLE t5(
 235       a INT NOT NULL ON CONFLICT FAIL,
 236       b TEXT,
 237       c TEXT,
 238       PRIMARY KEY(a,b)
 239     ) WITHOUT ROWID;
 240   }
 241   catch {db eval {INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);}}
 242   db eval {
 243     SELECT * FROM t5;
 244   }
 245 } {1 2 3}
 246 do_test without_rowid5-5.103 {
 247   db eval {
 248     DROP TABLE IF EXISTS t5;
 249     CREATE TABLE t5(
 250       a INT NOT NULL ON CONFLICT IGNORE,
 251       b TEXT,
 252       c TEXT,
 253       PRIMARY KEY(a,b)
 254     ) WITHOUT ROWID;
 255     INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
 256     SELECT * FROM t5;
 257   }
 258 } {1 2 3 6 7 8}
 259 do_test without_rowid5-5.104 {
 260   db eval {
 261     DROP TABLE IF EXISTS t5;
 262     CREATE TABLE t5(
 263       a INT NOT NULL ON CONFLICT REPLACE DEFAULT 3,
 264       b TEXT,
 265       c TEXT,
 266       PRIMARY KEY(a,b)
 267     ) WITHOUT ROWID;
 268     INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
 269     SELECT * FROM t5;
 270   }
 271 } {1 2 3 3 4 5 6 7 8}
 272
 273
 274 # EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
 275 # work for WITHOUT ROWID tables.
 276 #
 277 # EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
 278 #
 279 do_execsql_test without_rowid5-6.1 {
 280   CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
 281   INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f');
 282 } {}
 283 do_test without_rowid5-6.2 {
 284   set rc [catch {db incrblob b1 b 1} msg]
 285   lappend rc $msg
 286 } {1 {cannot open table without rowid: b1}}
 287
 288
 289 finish_test