| 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 |
|
| 16 |
set testdir [file dirname $argv0] |
| 17 |
source $testdir/tester.tcl |
| 18 |
set testprefix without_rowid1 |
| 19 |
|
| 20 |
proc 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 |
# |
| 26 |
do_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 |
|
| 36 |
integrity_check without_rowid1-1.0ic |
| 37 |
|
| 38 |
do_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 |
do_execsql_test_if_vtab without_rowid1-1.0tl { |
| 42 |
SELECT wr FROM pragma_table_list('t1'); |
| 43 |
} {1} |
| 44 |
|
| 45 |
do_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 |
|
| 49 |
do_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 |
|
| 53 |
do_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 |
|
| 57 |
do_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 |
# |
| 63 |
do_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 |
# |
| 71 |
do_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 |
|
| 76 |
do_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 |
# |
| 82 |
do_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 |} |
| 86 |
do_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 |
|
| 90 |
do_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 |} |
| 94 |
integrity_check without_rowid1-1.36 |
| 95 |
|
| 96 |
do_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 |
|
| 100 |
do_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 |} |
| 104 |
integrity_check without_rowid1-1.41 |
| 105 |
|
| 106 |
# Verify that ANALYZE works |
| 107 |
# |
| 108 |
do_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}} |
| 112 |
ifcapable 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 |
|
| 120 |
do_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} |
| 125 |
do_execsql_test 2.1.2 { |
| 126 |
UPDATE t4 SET a = 'ABC'; |
| 127 |
SELECT * FROM t4; |
| 128 |
} {ABC def} |
| 129 |
do_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 |
|
| 133 |
do_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 |
|
| 140 |
do_execsql_test 2.2.2 { |
| 141 |
UPDATE t4 SET a = 'ABC', b = 'xyz'; |
| 142 |
SELECT * FROM t4; |
| 143 |
} {xyz ABC} |
| 144 |
|
| 145 |
do_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 |
|
| 150 |
do_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 |
|
| 156 |
do_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 |
|
| 161 |
do_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 |
|
| 170 |
do_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 |
|
| 175 |
do_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 |
# |
| 185 |
reset_db |
| 186 |
do_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 |
|
| 197 |
do_execsql_test 3.1.2 { |
| 198 |
INSERT OR REPLACE INTO t1 SELECT * FROM t2; |
| 199 |
SELECT * FROM t1; |
| 200 |
} {three two} |
| 201 |
|
| 202 |
do_execsql_test 3.1.3 { |
| 203 |
DELETE FROM t1; |
| 204 |
INSERT INTO t1 SELECT * FROM t2; |
| 205 |
SELECT * FROM t1; |
| 206 |
} {three two} |
| 207 |
|
| 208 |
do_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 |
|
| 214 |
do_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 |
# |
| 230 |
do_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} |
| 237 |
do_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 |
# |
| 246 |
do_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 |
|
| 263 |
do_eqp_test 5.1 { |
| 264 |
SELECT * FROM t45 WHERE b=? AND a>? |
| 265 |
} {USING INDEX i45 (b=? AND a>?)} |
| 266 |
|
| 267 |
do_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 |
|
| 271 |
do_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 |
|
| 275 |
do_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 |
|
| 283 |
set 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 |
|
| 291 |
foreach {tn cnt where eqp} $queries { |
| 292 |
do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt |
| 293 |
} |
| 294 |
|
| 295 |
do_execsql_test 5.6 { |
| 296 |
CREATE INDEX i46 ON t46(c); |
| 297 |
} |
| 298 |
|
| 299 |
foreach {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 |
# |
| 307 |
do_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 |
|
| 320 |
do_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 |
# |
| 338 |
do_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 |
} {} |
| 345 |
do_catchsql_test 7.2 { |
| 346 |
INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy'); |
| 347 |
} {1 {CHECK constraint failed: rowid!=33}} |
| 348 |
do_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 |
# |
| 359 |
db close |
| 360 |
sqlite3 db :memory: |
| 361 |
do_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 |
# |
| 372 |
do_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 |
|
| 378 |
do_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 |
# |
| 385 |
reset_db |
| 386 |
do_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 |
|
| 394 |
do_catchsql_test 10.1 { |
| 395 |
UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a'); |
| 396 |
} {0 {}} |
| 397 |
do_catchsql_test 10.2 { |
| 398 |
UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b'); |
| 399 |
} {1 {UNIQUE constraint failed: t1.c}} |
| 400 |
do_catchsql_test 10.3 { |
| 401 |
UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a'); |
| 402 |
} {1 {UNIQUE constraint failed: t1.c}} |
| 403 |
do_catchsql_test 10.4 { |
| 404 |
UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b'); |
| 405 |
} {1 {UNIQUE constraint failed: t1.c}} |
| 406 |
do_catchsql_test 10.5 { |
| 407 |
UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c'); |
| 408 |
} {0 {}} |
| 409 |
|
| 410 |
do_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://sqlite.org/src/info/3182d3879020ef3 |
| 419 |
do_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://sqlite.org/src/info/bba7b69f9849b5b |
| 428 |
do_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://sqlite.org/src/info/302027baf1374498 |
| 437 |
# The xferCompatibleIndex() function confuses a PRIMARY KEY index |
| 438 |
# with a UNIQUE index. |
| 439 |
# |
| 440 |
do_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 |
| 460 |
reset_db |
| 461 |
ifcapable 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 |
# |
| 478 |
ifcapable 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 |
} |
| 490 |
|
| 491 |
# Ensure that the number of columns in an index on a WITHOUT ROWID |
| 492 |
# table does not exceed SQLITE_LIMIT_COLUMN. |
| 493 |
# |
| 494 |
reset_db |
| 495 |
sqlite3_limit db SQLITE_LIMIT_COLUMN 8 |
| 496 |
do_execsql_test 16.1 { |
| 497 |
CREATE TABLE t1( |
| 498 |
c1,c2,c3,c4,c5,c6,c7,c8, |
| 499 |
PRIMARY KEY(c1,c2,c1 COLLATE NOCASE) |
| 500 |
) WITHOUT ROWID; |
| 501 |
} {} |
| 502 |
do_execsql_test 16.2 { |
| 503 |
CREATE TABLE t2( |
| 504 |
c1,c2,c3,c4,c5,c6,c7,c8, |
| 505 |
PRIMARY KEY(c1 COLLATE nocase,c1 COLLATE rtrim, |
| 506 |
c2 COLLATE nocase,c2 COLLATE rtrim, |
| 507 |
c3 COLLATE nocase,c3 COLLATE rtrim, |
| 508 |
c4 COLLATE nocase,c4 COLLATE rtrim) |
| 509 |
) WITHOUT ROWID; |
| 510 |
} {} |
| 511 |
do_execsql_test 16.3 { |
| 512 |
CREATE TABLE t3( |
| 513 |
c1,c2,c3,c4,c5,c6,c7,c8, |
| 514 |
PRIMARY KEY(c1,c2), |
| 515 |
UNIQUE(c3,c4,c5,c6,c7,c8,c3 COLLATE nocase) |
| 516 |
) WITHOUT ROWID; |
| 517 |
} {} |
| 518 |
|
| 519 |
finish_test |