1# 2013-10-30 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# This file implements regression tests for SQLite library. The 13# focus of this file is testing WITHOUT ROWID tables. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix without_rowid1 19 20# Create and query a WITHOUT ROWID table. 21# 22do_execsql_test without_rowid1-1.0 { 23 CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID; 24 CREATE INDEX t1bd ON t1(b, d); 25 INSERT INTO t1 VALUES('journal','sherman','ammonia','helena'); 26 INSERT INTO t1 VALUES('dynamic','juliet','flipper','command'); 27 INSERT INTO t1 VALUES('journal','sherman','gamma','patriot'); 28 INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena'); 29 SELECT *, '|' FROM t1 ORDER BY c, a; 30} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} 31 32integrity_check without_rowid1-1.0ic 33 34do_execsql_test without_rowid1-1.0ixi { 35 SELECT name, key FROM pragma_index_xinfo('t1'); 36} {c 1 a 1 b 0 d 0} 37 38do_execsql_test without_rowid1-1.1 { 39 SELECT *, '|' FROM t1 ORDER BY +c, a; 40} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} 41 42do_execsql_test without_rowid1-1.2 { 43 SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC; 44} {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |} 45 46do_execsql_test without_rowid1-1.11 { 47 SELECT *, '|' FROM t1 ORDER BY b, d; 48} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} 49 50do_execsql_test without_rowid1-1.12 { 51 SELECT *, '|' FROM t1 ORDER BY +b, d; 52} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} 53 54# Trying to insert a duplicate PRIMARY KEY fails. 55# 56do_test without_rowid1-1.21 { 57 catchsql { 58 INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard'); 59 } 60} {1 {UNIQUE constraint failed: t1.c, t1.a}} 61 62# REPLACE INTO works, however. 63# 64do_execsql_test without_rowid1-1.22 { 65 REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard'); 66 SELECT *, '|' FROM t1 ORDER BY c, a; 67} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |} 68 69do_execsql_test without_rowid1-1.23 { 70 SELECT *, '|' FROM t1 ORDER BY b, d; 71} {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} 72 73# UPDATE statements. 74# 75do_execsql_test without_rowid1-1.31 { 76 UPDATE t1 SET d=3.1415926 WHERE a='journal'; 77 SELECT *, '|' FROM t1 ORDER BY c, a; 78} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |} 79do_execsql_test without_rowid1-1.32 { 80 SELECT *, '|' FROM t1 ORDER BY b, d; 81} {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 82 83do_execsql_test without_rowid1-1.35 { 84 UPDATE t1 SET a=1250 WHERE b='phone'; 85 SELECT *, '|' FROM t1 ORDER BY c, a; 86} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |} 87integrity_check without_rowid1-1.36 88 89do_execsql_test without_rowid1-1.37 { 90 SELECT *, '|' FROM t1 ORDER BY b, d; 91} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 92 93do_execsql_test without_rowid1-1.40 { 94 VACUUM; 95 SELECT *, '|' FROM t1 ORDER BY b, d; 96} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 97integrity_check without_rowid1-1.41 98 99# Verify that ANALYZE works 100# 101do_execsql_test without_rowid1-1.50 { 102 ANALYZE; 103 SELECT * FROM sqlite_stat1 ORDER BY idx; 104} {t1 t1 {4 2 1} t1 t1bd {4 2 2}} 105ifcapable stat4 { 106 do_execsql_test without_rowid1-1.52 { 107 SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx; 108 } {t1 t1 t1 t1bd} 109} 110 111#---------- 112 113do_execsql_test 2.1.1 { 114 CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID; 115 INSERT INTO t4 VALUES('abc', 'def'); 116 SELECT * FROM t4; 117} {abc def} 118do_execsql_test 2.1.2 { 119 UPDATE t4 SET a = 'ABC'; 120 SELECT * FROM t4; 121} {ABC def} 122do_execsql_test 2.1.3 { 123 SELECT name, coll, key FROM pragma_index_xinfo('t4'); 124} {a nocase 1 b BINARY 0} 125 126do_execsql_test 2.2.1 { 127 DROP TABLE t4; 128 CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID; 129 INSERT INTO t4(a, b) VALUES('abc', 'def'); 130 SELECT * FROM t4; 131} {def abc} 132 133do_execsql_test 2.2.2 { 134 UPDATE t4 SET a = 'ABC', b = 'xyz'; 135 SELECT * FROM t4; 136} {xyz ABC} 137 138do_execsql_test 2.2.3 { 139 SELECT name, coll, key FROM pragma_index_xinfo('t4'); 140} {a nocase 1 b BINARY 0} 141 142 143do_execsql_test 2.3.1 { 144 CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID; 145 INSERT INTO t5(a, b) VALUES('abc', 'def'); 146 UPDATE t5 SET a='abc', b='def'; 147} {} 148 149do_execsql_test 2.3.2 { 150 SELECT name, coll, key FROM pragma_index_xinfo('t5'); 151} {b BINARY 1 a BINARY 1} 152 153 154do_execsql_test 2.4.1 { 155 CREATE TABLE t6 ( 156 a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a) 157 ) WITHOUT ROWID; 158 159 INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi'); 160 UPDATE t6 SET a='ABC', c='ghi'; 161} {} 162 163do_execsql_test 2.4.2 { 164 SELECT * FROM t6 ORDER BY b, a; 165 SELECT * FROM t6 ORDER BY c; 166} {ABC def ghi ABC def ghi} 167 168do_execsql_test 2.4.3 { 169 SELECT name, coll, key FROM pragma_index_xinfo('t6'); 170} {b BINARY 1 a nocase 1 c BINARY 0} 171 172 173#------------------------------------------------------------------------- 174# Unless the destination table is completely empty, the xfer optimization 175# is disabled for WITHOUT ROWID tables. The following tests check for 176# some problems that might occur if this were not the case. 177# 178reset_db 179do_execsql_test 3.1.1 { 180 CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; 181 CREATE UNIQUE INDEX i1 ON t1(b); 182 183 CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID; 184 CREATE UNIQUE INDEX i2 ON t2(b); 185 186 INSERT INTO t1 VALUES('one', 'two'); 187 INSERT INTO t2 VALUES('three', 'two'); 188} 189 190do_execsql_test 3.1.2 { 191 INSERT OR REPLACE INTO t1 SELECT * FROM t2; 192 SELECT * FROM t1; 193} {three two} 194 195do_execsql_test 3.1.3 { 196 DELETE FROM t1; 197 INSERT INTO t1 SELECT * FROM t2; 198 SELECT * FROM t1; 199} {three two} 200 201do_catchsql_test 3.1.4 { 202 INSERT INTO t2 VALUES('four', 'four'); 203 INSERT INTO t2 VALUES('six', 'two'); 204 INSERT INTO t1 SELECT * FROM t2; 205} {1 {UNIQUE constraint failed: t2.b}} 206 207do_execsql_test 3.1.5 { 208 CREATE TABLE t3(a PRIMARY KEY); 209 CREATE TABLE t4(a PRIMARY KEY); 210 211 INSERT INTO t4 VALUES('i'); 212 INSERT INTO t4 VALUES('ii'); 213 INSERT INTO t4 VALUES('iii'); 214 215 INSERT INTO t3 SELECT * FROM t4; 216 SELECT * FROM t3; 217} {i ii iii} 218 219############################################################################ 220# Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc] 221# Name resolution issue with WITHOUT ROWID 222# 223do_execsql_test 4.1 { 224 CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID; 225 INSERT INTO t41 VALUES('abc'); 226 CREATE TABLE t42(x); 227 INSERT INTO t42 VALUES('xyz'); 228 SELECT t42.rowid FROM t41, t42; 229} {1} 230do_execsql_test 4.2 { 231 SELECT t42.rowid FROM t42, t41; 232} {1} 233 234 235#-------------------------------------------------------------------------- 236# The following tests verify that the trailing PK fields added to each 237# entry in an index on a WITHOUT ROWID table are used correctly. 238# 239do_execsql_test 5.0 { 240 CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID; 241 CREATE INDEX i45 ON t45(b); 242 243 INSERT INTO t45 VALUES(2, 'one', 'x'); 244 INSERT INTO t45 VALUES(4, 'one', 'x'); 245 INSERT INTO t45 VALUES(6, 'one', 'x'); 246 INSERT INTO t45 VALUES(8, 'one', 'x'); 247 INSERT INTO t45 VALUES(10, 'one', 'x'); 248 249 INSERT INTO t45 VALUES(1, 'two', 'x'); 250 INSERT INTO t45 VALUES(3, 'two', 'x'); 251 INSERT INTO t45 VALUES(5, 'two', 'x'); 252 INSERT INTO t45 VALUES(7, 'two', 'x'); 253 INSERT INTO t45 VALUES(9, 'two', 'x'); 254} 255 256do_eqp_test 5.1 { 257 SELECT * FROM t45 WHERE b=? AND a>? 258} {USING INDEX i45 (b=? AND a>?)} 259 260do_execsql_test 5.2 { 261 SELECT * FROM t45 WHERE b='two' AND a>4 262} {5 two x 7 two x 9 two x} 263 264do_execsql_test 5.3 { 265 SELECT * FROM t45 WHERE b='one' AND a<8 266} { 2 one x 4 one x 6 one x } 267 268do_execsql_test 5.4 { 269 CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; 270 WITH r(x) AS ( 271 SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100 272 ) 273 INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r; 274} 275 276set queries { 277 1 2 "c = 5 AND a = 1" {i46 (c=? AND a=?)} 278 2 6 "c = 4 AND a < 3" {i46 (c=? AND a<?)} 279 3 4 "c = 2 AND a >= 3" {i46 (c=? AND a>?)} 280 4 1 "c = 2 AND a = 1 AND b<10" {i46 (c=? AND a=? AND b<?)} 281 5 1 "c = 0 AND a = 0 AND b>5" {i46 (c=? AND a=? AND b>?)} 282} 283 284foreach {tn cnt where eqp} $queries { 285 do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt 286} 287 288do_execsql_test 5.6 { 289 CREATE INDEX i46 ON t46(c); 290} 291 292foreach {tn cnt where eqp} $queries { 293 do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt 294 do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp 295} 296 297#------------------------------------------------------------------------- 298# Check that redundant UNIQUE constraints do not cause a problem. 299# 300do_execsql_test 6.0 { 301 CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID; 302 CREATE INDEX i47 ON t47(a); 303 INSERT INTO t47 VALUES(1, 2); 304 INSERT INTO t47 VALUES(2, 4); 305 INSERT INTO t47 VALUES(3, 6); 306 INSERT INTO t47 VALUES(4, 8); 307 308 VACUUM; 309 PRAGMA integrity_check; 310 SELECT name FROM sqlite_master WHERE tbl_name = 't47'; 311} {ok t47 i47} 312 313do_execsql_test 6.1 { 314 CREATE TABLE t48( 315 a UNIQUE UNIQUE, 316 b UNIQUE, 317 PRIMARY KEY(a), 318 UNIQUE(a) 319 ) WITHOUT ROWID; 320 INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f'); 321 VACUUM; 322 PRAGMA integrity_check; 323 SELECT name FROM sqlite_master WHERE tbl_name = 't48'; 324} { 325 ok t48 sqlite_autoindex_t48_2 326} 327 328# 2015-05-28: CHECK constraints can refer to the rowid in a 329# rowid table, but not in a WITHOUT ROWID table. 330# 331do_execsql_test 7.1 { 332 CREATE TABLE t70a( 333 a INT CHECK( rowid!=33 ), 334 b TEXT PRIMARY KEY 335 ); 336 INSERT INTO t70a(a,b) VALUES(99,'hello'); 337} {} 338do_catchsql_test 7.2 { 339 INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy'); 340} {1 {CHECK constraint failed: t70a}} 341do_catchsql_test 7.3 { 342 CREATE TABLE t70b( 343 a INT CHECK( rowid!=33 ), 344 b TEXT PRIMARY KEY 345 ) WITHOUT ROWID; 346} {1 {no such column: rowid}} 347 348# 2017-07-30: OSSFuzz discovered that an extra entry was being 349# added in the sqlite_master table for an "INTEGER PRIMARY KEY UNIQUE" 350# WITHOUT ROWID table. Make sure this has now been fixed. 351# 352db close 353sqlite3 db :memory: 354do_execsql_test 8.1 { 355 CREATE TABLE t1(x INTEGER PRIMARY KEY UNIQUE, b) WITHOUT ROWID; 356 CREATE INDEX t1x ON t1(x); 357 INSERT INTO t1(x,b) VALUES('funny','buffalo'); 358 SELECT type, name, '|' FROM sqlite_master; 359} {table t1 | index t1x |} 360 361# 2018-04-05: OSSFuzz found that the following was accessing an 362# unintialized memory cell. Which was not actually causing a 363# malfunction, but does cause an assert() to fail. 364# 365do_execsql_test 9.0 { 366 CREATE TABLE t2(b, c, PRIMARY KEY(b,c)) WITHOUT ROWID; 367 CREATE UNIQUE INDEX t2b ON t2(b); 368 UPDATE t2 SET b=1 WHERE b=''; 369} 370 371do_execsql_test 10.1 { 372 DELETE FROM t2 WHERE b=1 373} 374 375#------------------------------------------------------------------------- 376# UNIQUE constraint violation in an UPDATE with a multi-column PK. 377# 378reset_db 379do_execsql_test 10.0 { 380 CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID; 381 INSERT INTO t1 VALUES('a', 'a', 1); 382 INSERT INTO t1 VALUES('a', 'b', 2); 383 INSERT INTO t1 VALUES('b', 'a', 3); 384 INSERT INTO t1 VALUES('b', 'b', 4); 385} 386 387do_catchsql_test 10.1 { 388 UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a'); 389} {0 {}} 390do_catchsql_test 10.2 { 391 UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b'); 392} {1 {UNIQUE constraint failed: t1.c}} 393do_catchsql_test 10.3 { 394 UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a'); 395} {1 {UNIQUE constraint failed: t1.c}} 396do_catchsql_test 10.4 { 397 UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b'); 398} {1 {UNIQUE constraint failed: t1.c}} 399do_catchsql_test 10.5 { 400 UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c'); 401} {0 {}} 402 403do_execsql_test 10.6 { 404 CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN 405 DELETE FROM t1 WHERE a = new.a; 406 END; 407 UPDATE t1 SET c = c+1 WHERE a = 'a'; 408 SELECT * FROM t1; 409} {b a 3 b b 4} 410 411# 2019-04-29 ticket https://www.sqlite.org/src/info/3182d3879020ef3 412do_execsql_test 11.1 { 413 CREATE TABLE t11(a TEXT PRIMARY KEY, b INT) WITHOUT ROWID; 414 CREATE INDEX t11a ON t11(a COLLATE NOCASE); 415 INSERT INTO t11(a,b) VALUES ('A',1),('a',2); 416 PRAGMA integrity_check; 417 SELECT a FROM t11 ORDER BY a COLLATE binary; 418} {ok A a} 419 420# 2019-05-13 ticket https://www.sqlite.org/src/info/bba7b69f9849b5b 421do_execsql_test 12.1 { 422 DROP TABLE IF EXISTS t0; 423 CREATE TABLE t0 (c0 INTEGER PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID; 424 INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); 425 REINDEX; 426 PRAGMA integrity_check; 427} {ok} 428 429 430finish_test 431