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