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