000001 # 2013-11-26
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 #
000012 # Requirements testing for WITHOUT ROWID tables.
000013 #
000014
000015 set testdir [file dirname $argv0]
000016 source $testdir/tester.tcl
000017
000018 ifcapable !incrblob {
000019 finish_test
000020 return
000021 }
000022
000023 # EVIDENCE-OF: R-36924-43758 By default, every row in SQLite has a
000024 # special column, usually called the "rowid", that uniquely identifies
000025 # that row within the table.
000026 #
000027 # EVIDENCE-OF: R-32341-39358 However if the phrase "WITHOUT ROWID" is
000028 # added to the end of a CREATE TABLE statement, then the special "rowid"
000029 # column is omitted.
000030 #
000031 do_execsql_test without_rowid5-1.1 {
000032 CREATE TABLE t1(a PRIMARY KEY,b,c);
000033 CREATE TABLE t1w(a PRIMARY KEY,b,c) WITHOUT ROWID;
000034 INSERT INTO t1 VALUES(1565,681,1148),(1429,1190,1619),(425,358,1306);
000035 INSERT INTO t1w SELECT a,b,c FROM t1;
000036 SELECT rowid, _rowid_, oid FROM t1 ORDER BY a DESC;
000037 } {1 1 1 2 2 2 3 3 3}
000038 do_catchsql_test without_rowid5-1.2 {
000039 SELECT rowid FROM t1w;
000040 } {1 {no such column: rowid}}
000041 do_catchsql_test without_rowid5-1.3 {
000042 SELECT _rowid_ FROM t1w;
000043 } {1 {no such column: _rowid_}}
000044 do_catchsql_test without_rowid5-1.4 {
000045 SELECT oid FROM t1w;
000046 } {1 {no such column: oid}}
000047
000048 # EVIDENCE-OF: R-00217-01605 To create a WITHOUT ROWID table, simply add
000049 # the keywords "WITHOUT ROWID" to the end of the CREATE TABLE statement.
000050 # For example: CREATE TABLE IF NOT EXISTS wordcount( word TEXT PRIMARY
000051 # KEY, cnt INTEGER ) WITHOUT ROWID;
000052 #
000053 do_execsql_test without_rowid5-2.1 {
000054 CREATE TABLE IF NOT EXISTS wordcount(
000055 word TEXT PRIMARY KEY,
000056 cnt INTEGER
000057 ) WITHOUT ROWID;
000058 INSERT INTO wordcount VALUES('one',1);
000059 } {}
000060 do_catchsql_test without_rowid5-2.2 {
000061 SELECT rowid FROM wordcount;
000062 } {1 {no such column: rowid}}
000063
000064 # EVIDENCE-OF: R-24770-17719 As with all SQL syntax, the case of the
000065 # keywords does not matter. One can write "WITHOUT rowid" or "without
000066 # rowid" or "WiThOuT rOwId" and it will mean the same thing.
000067 #
000068 do_execsql_test without_rowid5-2.3 {
000069 CREATE TABLE IF NOT EXISTS wordcount_b(
000070 word TEXT PRIMARY KEY,
000071 cnt INTEGER
000072 ) WITHOUT rowid;
000073 INSERT INTO wordcount_b VALUES('one',1);
000074 } {}
000075 do_catchsql_test without_rowid5-2.4 {
000076 SELECT rowid FROM wordcount_b;
000077 } {1 {no such column: rowid}}
000078 do_execsql_test without_rowid5-2.5 {
000079 CREATE TABLE IF NOT EXISTS wordcount_c(
000080 word TEXT PRIMARY KEY,
000081 cnt INTEGER
000082 ) without rowid;
000083 INSERT INTO wordcount_c VALUES('one',1);
000084 } {}
000085 do_catchsql_test without_rowid5-2.6 {
000086 SELECT rowid FROM wordcount_c;
000087 } {1 {no such column: rowid}}
000088 do_execsql_test without_rowid5-2.7 {
000089 CREATE TABLE IF NOT EXISTS wordcount_d(
000090 word TEXT PRIMARY KEY,
000091 cnt INTEGER
000092 ) WITHOUT rowid;
000093 INSERT INTO wordcount_d VALUES('one',1);
000094 } {}
000095 do_catchsql_test without_rowid5-2.8 {
000096 SELECT rowid FROM wordcount_d;
000097 } {1 {no such column: rowid}}
000098
000099 # EVIDENCE-OF: R-01418-51310 However, only "rowid" works as the keyword
000100 # in the CREATE TABLE statement.
000101 #
000102 do_catchsql_test without_rowid5-3.1 {
000103 CREATE TABLE IF NOT EXISTS error1(
000104 word TEXT PRIMARY KEY,
000105 cnt INTEGER
000106 ) WITHOUT _rowid_;
000107 } {1 {unknown table option: _rowid_}}
000108 do_catchsql_test without_rowid5-3.2 {
000109 CREATE TABLE IF NOT EXISTS error2(
000110 word TEXT PRIMARY KEY,
000111 cnt INTEGER
000112 ) WITHOUT oid;
000113 } {1 {unknown table option: oid}}
000114
000115 # EVIDENCE-OF: R-58033-17334 An error is raised if a CREATE TABLE
000116 # statement with the WITHOUT ROWID clause lacks a PRIMARY KEY.
000117 #
000118 # EVIDENCE-OF: R-63443-09418 Every WITHOUT ROWID table must have a
000119 # PRIMARY KEY.
000120 #
000121 # EVIDENCE-OF: R-27966-31616 An attempt to create a WITHOUT ROWID table
000122 # without a PRIMARY KEY results in an error.
000123 #
000124 do_catchsql_test without_rowid5-4.1 {
000125 CREATE TABLE IF NOT EXISTS error3(
000126 word TEXT UNIQUE,
000127 cnt INTEGER
000128 ) WITHOUT ROWID;
000129 } {1 {PRIMARY KEY missing on table error3}}
000130
000131 # EVIDENCE-OF: R-48230-36247 The special behaviors associated "INTEGER
000132 # PRIMARY KEY" do not apply on WITHOUT ROWID tables.
000133 #
000134 do_execsql_test without_rowid5-5.1 {
000135 CREATE TABLE ipk(key INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID;
000136 INSERT INTO ipk VALUES('rival','bonus'); -- ok to insert non-integer key
000137 SELECT * FROM ipk;
000138 } {rival bonus}
000139 do_catchsql_test without_rowid5-5.2a {
000140 BEGIN;
000141 INSERT INTO ipk VALUES(NULL,'sample'); -- no automatic generation of keys
000142 } {1 {NOT NULL constraint failed: ipk.key}}
000143 do_execsql_test without_rowid5-5.2b {
000144 ROLLBACK;
000145 } {}
000146
000147 # EVIDENCE-OF: R-33142-02092 AUTOINCREMENT does not work on WITHOUT
000148 # ROWID tables.
000149 #
000150 # EVIDENCE-OF: R-53084-07740 An error is raised if the "AUTOINCREMENT"
000151 # keyword is used in the CREATE TABLE statement for a WITHOUT ROWID
000152 # table.
000153 #
000154 do_catchsql_test without_rowid5-5.3 {
000155 CREATE TABLE ipk2(key INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)WITHOUT ROWID;
000156 } {1 {AUTOINCREMENT not allowed on WITHOUT ROWID tables}}
000157
000158 # EVIDENCE-OF: R-27831-00579 NOT NULL is enforced on every column of the
000159 # PRIMARY KEY in a WITHOUT ROWID table.
000160 #
000161 # EVIDENCE-OF: R-29781-51289 So, ordinary rowid tables in SQLite violate
000162 # the SQL standard and allow NULL values in PRIMARY KEY fields.
000163 #
000164 # EVIDENCE-OF: R-27472-62612 But WITHOUT ROWID tables do follow the
000165 # standard and will throw an error on any attempt to insert a NULL into
000166 # a PRIMARY KEY column.
000167 #
000168 do_execsql_test without_rowid5-5.4 {
000169 CREATE TABLE nn(a, b, c, d, e, PRIMARY KEY(c,a,e));
000170 CREATE TABLE nnw(a, b, c, d, e, PRIMARY KEY(c,a,e)) WITHOUT ROWID;
000171 INSERT INTO nn VALUES(1,2,3,4,5);
000172 INSERT INTO nnw VALUES(1,2,3,4,5);
000173 } {}
000174 do_execsql_test without_rowid5-5.5 {
000175 INSERT INTO nn VALUES(NULL, 3,4,5,6);
000176 INSERT INTO nn VALUES(3,4,NULL,7,8);
000177 INSERT INTO nn VALUES(4,5,6,7,NULL);
000178 SELECT count(*) FROM nn;
000179 } {4}
000180 do_catchsql_test without_rowid5-5.6 {
000181 INSERT INTO nnw VALUES(NULL, 3,4,5,6);
000182 } {1 {NOT NULL constraint failed: nnw.a}}
000183 do_catchsql_test without_rowid5-5.7 {
000184 INSERT INTO nnw VALUES(3,4,NULL,7,8)
000185 } {1 {NOT NULL constraint failed: nnw.c}}
000186 do_catchsql_test without_rowid5-5.8 {
000187 INSERT INTO nnw VALUES(4,5,6,7,NULL)
000188 } {1 {NOT NULL constraint failed: nnw.e}}
000189 do_execsql_test without_rowid5-5.9 {
000190 SELECT count(*) FROM nnw;
000191 } {1}
000192
000193 # Ticket f2be158c57aaa8c6 (2021-08-18)
000194 # NOT NULL ON CONFLICT clauses work on WITHOUT ROWID tables now.
000195 #
000196 do_test without_rowid5-5.100 {
000197 db eval {
000198 DROP TABLE IF EXISTS t5;
000199 CREATE TABLE t5(
000200 a INT NOT NULL ON CONFLICT ROLLBACK,
000201 b TEXT,
000202 c TEXT,
000203 PRIMARY KEY(a,b)
000204 ) WITHOUT ROWID;
000205 BEGIN;
000206 INSERT INTO t5(a,b,c) VALUES(1,2,3);
000207 }
000208 catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
000209 db eval {
000210 SELECT * FROM t5;
000211 }
000212 } {}
000213 do_test without_rowid5-5.101 {
000214 db eval {
000215 DROP TABLE IF EXISTS t5;
000216 CREATE TABLE t5(
000217 a INT NOT NULL ON CONFLICT ABORT,
000218 b TEXT,
000219 c TEXT,
000220 PRIMARY KEY(a,b)
000221 ) WITHOUT ROWID;
000222 BEGIN;
000223 INSERT INTO t5(a,b,c) VALUES(1,2,3);
000224 }
000225 catch {db eval {INSERT INTO t5(a,b,c) VALUES(NULL,6,7);}}
000226 db eval {
000227 COMMIT;
000228 SELECT * FROM t5;
000229 }
000230 } {1 2 3}
000231 do_test without_rowid5-5.102 {
000232 db eval {
000233 DROP TABLE IF EXISTS t5;
000234 CREATE TABLE t5(
000235 a INT NOT NULL ON CONFLICT FAIL,
000236 b TEXT,
000237 c TEXT,
000238 PRIMARY KEY(a,b)
000239 ) WITHOUT ROWID;
000240 }
000241 catch {db eval {INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);}}
000242 db eval {
000243 SELECT * FROM t5;
000244 }
000245 } {1 2 3}
000246 do_test without_rowid5-5.103 {
000247 db eval {
000248 DROP TABLE IF EXISTS t5;
000249 CREATE TABLE t5(
000250 a INT NOT NULL ON CONFLICT IGNORE,
000251 b TEXT,
000252 c TEXT,
000253 PRIMARY KEY(a,b)
000254 ) WITHOUT ROWID;
000255 INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
000256 SELECT * FROM t5;
000257 }
000258 } {1 2 3 6 7 8}
000259 do_test without_rowid5-5.104 {
000260 db eval {
000261 DROP TABLE IF EXISTS t5;
000262 CREATE TABLE t5(
000263 a INT NOT NULL ON CONFLICT REPLACE DEFAULT 3,
000264 b TEXT,
000265 c TEXT,
000266 PRIMARY KEY(a,b)
000267 ) WITHOUT ROWID;
000268 INSERT INTO t5(a,b,c) VALUES(1,2,3),(NULL,4,5),(6,7,8);
000269 SELECT * FROM t5;
000270 }
000271 } {1 2 3 3 4 5 6 7 8}
000272
000273
000274 # EVIDENCE-OF: R-12643-30541 The incremental blob I/O mechanism does not
000275 # work for WITHOUT ROWID tables.
000276 #
000277 # EVIDENCE-OF: R-40134-30296 Table zTable is a WITHOUT ROWID table
000278 #
000279 do_execsql_test without_rowid5-6.1 {
000280 CREATE TABLE b1(a INTEGER PRIMARY KEY, b BLOB) WITHOUT ROWID;
000281 INSERT INTO b1 VALUES(1,x'0102030405060708090a0b0c0d0e0f');
000282 } {}
000283 do_test without_rowid5-6.2 {
000284 set rc [catch {db incrblob b1 b 1} msg]
000285 lappend rc $msg
000286 } {1 {cannot open table without rowid: b1}}
000287
000288
000289 finish_test