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.1 { 35 SELECT *, '|' FROM t1 ORDER BY +c, a; 36} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |} 37 38do_execsql_test without_rowid1-1.2 { 39 SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC; 40} {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |} 41 42do_execsql_test without_rowid1-1.11 { 43 SELECT *, '|' FROM t1 ORDER BY b, d; 44} {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} 45 46do_execsql_test without_rowid1-1.12 { 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 50# Trying to insert a duplicate PRIMARY KEY fails. 51# 52do_test without_rowid1-1.21 { 53 catchsql { 54 INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard'); 55 } 56} {1 {UNIQUE constraint failed: t1.c, t1.a}} 57 58# REPLACE INTO works, however. 59# 60do_execsql_test without_rowid1-1.22 { 61 REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard'); 62 SELECT *, '|' FROM t1 ORDER BY c, a; 63} {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |} 64 65do_execsql_test without_rowid1-1.23 { 66 SELECT *, '|' FROM t1 ORDER BY b, d; 67} {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |} 68 69# UPDATE statements. 70# 71do_execsql_test without_rowid1-1.31 { 72 UPDATE t1 SET d=3.1415926 WHERE a='journal'; 73 SELECT *, '|' FROM t1 ORDER BY c, a; 74} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |} 75do_execsql_test without_rowid1-1.32 { 76 SELECT *, '|' FROM t1 ORDER BY b, d; 77} {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 78 79do_execsql_test without_rowid1-1.35 { 80 UPDATE t1 SET a=1250 WHERE b='phone'; 81 SELECT *, '|' FROM t1 ORDER BY c, a; 82} {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |} 83integrity_check without_rowid1-1.36 84 85do_execsql_test without_rowid1-1.37 { 86 SELECT *, '|' FROM t1 ORDER BY b, d; 87} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 88 89do_execsql_test without_rowid1-1.40 { 90 VACUUM; 91 SELECT *, '|' FROM t1 ORDER BY b, d; 92} {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |} 93integrity_check without_rowid1-1.41 94 95# Verify that ANALYZE works 96# 97do_execsql_test without_rowid1-1.50 { 98 ANALYZE; 99 SELECT * FROM sqlite_stat1 ORDER BY idx; 100} {t1 t1 {4 2 1} t1 t1bd {4 2 2}} 101ifcapable stat3 { 102 do_execsql_test without_rowid1-1.51 { 103 SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx; 104 } {t1 t1 t1 t1bd} 105} 106ifcapable stat4 { 107 do_execsql_test without_rowid1-1.52 { 108 SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx; 109 } {t1 t1 t1 t1bd} 110} 111 112#---------- 113 114do_execsql_test 2.1.1 { 115 CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID; 116 INSERT INTO t4 VALUES('abc', 'def'); 117 SELECT * FROM t4; 118} {abc def} 119do_execsql_test 2.1.2 { 120 UPDATE t4 SET a = 'ABC'; 121 SELECT * FROM t4; 122} {ABC def} 123 124do_execsql_test 2.2.1 { 125 DROP TABLE t4; 126 CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID; 127 INSERT INTO t4(a, b) VALUES('abc', 'def'); 128 SELECT * FROM t4; 129} {def abc} 130 131do_execsql_test 2.2.2 { 132 UPDATE t4 SET a = 'ABC', b = 'xyz'; 133 SELECT * FROM t4; 134} {xyz ABC} 135 136do_execsql_test 2.3.1 { 137 CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID; 138 INSERT INTO t5(a, b) VALUES('abc', 'def'); 139 UPDATE t5 SET a='abc', b='def'; 140} {} 141 142do_execsql_test 2.4.1 { 143 CREATE TABLE t6 ( 144 a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a) 145 ) WITHOUT ROWID; 146 147 INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi'); 148 UPDATE t6 SET a='ABC', c='ghi'; 149} {} 150 151do_execsql_test 2.4.2 { 152 SELECT * FROM t6 ORDER BY b, a; 153 SELECT * FROM t6 ORDER BY c; 154} {ABC def ghi ABC def ghi} 155 156#------------------------------------------------------------------------- 157# Unless the destination table is completely empty, the xfer optimization 158# is disabled for WITHOUT ROWID tables. The following tests check for 159# some problems that might occur if this were not the case. 160# 161reset_db 162do_execsql_test 3.1.1 { 163 CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; 164 CREATE UNIQUE INDEX i1 ON t1(b); 165 166 CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID; 167 CREATE UNIQUE INDEX i2 ON t2(b); 168 169 INSERT INTO t1 VALUES('one', 'two'); 170 INSERT INTO t2 VALUES('three', 'two'); 171} 172 173do_execsql_test 3.1.2 { 174 INSERT OR REPLACE INTO t1 SELECT * FROM t2; 175 SELECT * FROM t1; 176} {three two} 177 178do_execsql_test 3.1.3 { 179 DELETE FROM t1; 180 INSERT INTO t1 SELECT * FROM t2; 181 SELECT * FROM t1; 182} {three two} 183 184do_catchsql_test 3.1.4 { 185 INSERT INTO t2 VALUES('four', 'four'); 186 INSERT INTO t2 VALUES('six', 'two'); 187 INSERT INTO t1 SELECT * FROM t2; 188} {1 {UNIQUE constraint failed: t2.b}} 189 190do_execsql_test 3.1.5 { 191 CREATE TABLE t3(a PRIMARY KEY); 192 CREATE TABLE t4(a PRIMARY KEY); 193 194 INSERT INTO t4 VALUES('i'); 195 INSERT INTO t4 VALUES('ii'); 196 INSERT INTO t4 VALUES('iii'); 197 198 INSERT INTO t3 SELECT * FROM t4; 199 SELECT * FROM t3; 200} {i ii iii} 201 202############################################################################ 203# Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc] 204# Name resolution issue with WITHOUT ROWID 205# 206do_execsql_test 4.1 { 207 CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID; 208 INSERT INTO t41 VALUES('abc'); 209 CREATE TABLE t42(x); 210 INSERT INTO t42 VALUES('xyz'); 211 SELECT t42.rowid FROM t41, t42; 212} {1} 213do_execsql_test 4.2 { 214 SELECT t42.rowid FROM t42, t41; 215} {1} 216 217 218#-------------------------------------------------------------------------- 219# The following tests verify that the trailing PK fields added to each 220# entry in an index on a WITHOUT ROWID table are used correctly. 221# 222do_execsql_test 5.0 { 223 CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID; 224 CREATE INDEX i45 ON t45(b); 225 226 INSERT INTO t45 VALUES(2, 'one', 'x'); 227 INSERT INTO t45 VALUES(4, 'one', 'x'); 228 INSERT INTO t45 VALUES(6, 'one', 'x'); 229 INSERT INTO t45 VALUES(8, 'one', 'x'); 230 INSERT INTO t45 VALUES(10, 'one', 'x'); 231 232 INSERT INTO t45 VALUES(1, 'two', 'x'); 233 INSERT INTO t45 VALUES(3, 'two', 'x'); 234 INSERT INTO t45 VALUES(5, 'two', 'x'); 235 INSERT INTO t45 VALUES(7, 'two', 'x'); 236 INSERT INTO t45 VALUES(9, 'two', 'x'); 237} 238 239do_eqp_test 5.1 { 240 SELECT * FROM t45 WHERE b=? AND a>? 241} {/*USING INDEX i45 (b=? AND a>?)*/} 242 243do_execsql_test 5.2 { 244 SELECT * FROM t45 WHERE b='two' AND a>4 245} {5 two x 7 two x 9 two x} 246 247do_execsql_test 5.3 { 248 SELECT * FROM t45 WHERE b='one' AND a<8 249} { 2 one x 4 one x 6 one x } 250 251do_execsql_test 5.4 { 252 CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; 253 WITH r(x) AS ( 254 SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100 255 ) 256 INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r; 257} 258 259set queries { 260 1 2 "c = 5 AND a = 1" {/*i46 (c=? AND a=?)*/} 261 2 6 "c = 4 AND a < 3" {/*i46 (c=? AND a<?)*/} 262 3 4 "c = 2 AND a >= 3" {/*i46 (c=? AND a>?)*/} 263 4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/} 264 5 1 "c = 0 AND a = 0 AND b>5" {/*i46 (c=? AND a=? AND b>?)*/} 265} 266 267foreach {tn cnt where eqp} $queries { 268 do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt 269} 270 271do_execsql_test 5.6 { 272 CREATE INDEX i46 ON t46(c); 273} 274 275foreach {tn cnt where eqp} $queries { 276 do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt 277 do_eqp_test 5.7.$tn.2 "SELECT count(*) FROM t46 WHERE $where" $eqp 278} 279 280#------------------------------------------------------------------------- 281# Check that redundant UNIQUE constraints do not cause a problem. 282# 283do_execsql_test 6.0 { 284 CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID; 285 CREATE INDEX i47 ON t47(a); 286 INSERT INTO t47 VALUES(1, 2); 287 INSERT INTO t47 VALUES(2, 4); 288 INSERT INTO t47 VALUES(3, 6); 289 INSERT INTO t47 VALUES(4, 8); 290 291 VACUUM; 292 PRAGMA integrity_check; 293 SELECT name FROM sqlite_master WHERE tbl_name = 't47'; 294} {ok t47 i47} 295 296do_execsql_test 6.1 { 297 CREATE TABLE t48( 298 a UNIQUE UNIQUE, 299 b UNIQUE, 300 PRIMARY KEY(a), 301 UNIQUE(a) 302 ) WITHOUT ROWID; 303 INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f'); 304 VACUUM; 305 PRAGMA integrity_check; 306 SELECT name FROM sqlite_master WHERE tbl_name = 't48'; 307} { 308 ok t48 sqlite_autoindex_t48_2 309} 310 311# 2015-05-28: CHECK constraints can refer to the rowid in a 312# rowid table, but not in a WITHOUT ROWID table. 313# 314do_execsql_test 7.1 { 315 CREATE TABLE t70a( 316 a INT CHECK( rowid!=33 ), 317 b TEXT PRIMARY KEY 318 ); 319 INSERT INTO t70a(a,b) VALUES(99,'hello'); 320} {} 321do_catchsql_test 7.2 { 322 INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy'); 323} {1 {CHECK constraint failed: t70a}} 324do_catchsql_test 7.3 { 325 CREATE TABLE t70b( 326 a INT CHECK( rowid!=33 ), 327 b TEXT PRIMARY KEY 328 ) WITHOUT ROWID; 329} {1 {no such column: rowid}} 330 331 332finish_test 333