1 # 2001 September 15 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the DELETE FROM statement. 13 # 14 # $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Try to delete from a non-existant table. 20 # 21 do_test delete-1.1 { 22 set v [catch {execsql {DELETE FROM test1}} msg] 23 lappend v $msg 24 } {1 {no such table: test1}} 25 26 # Try to delete from sqlite_master 27 # 28 do_test delete-2.1 { 29 set v [catch {execsql {DELETE FROM sqlite_master}} msg] 30 lappend v $msg 31 } {1 {table sqlite_master may not be modified}} 32 33 # Delete selected entries from a table with and without an index. 34 # 35 do_test delete-3.1.1 { 36 execsql {CREATE TABLE table1(f1 int, f2 int)} 37 execsql {INSERT INTO table1 VALUES(1,2)} 38 execsql {INSERT INTO table1 VALUES(2,4)} 39 execsql {INSERT INTO table1 VALUES(3,8)} 40 execsql {INSERT INTO table1 VALUES(4,16)} 41 execsql {SELECT * FROM table1 ORDER BY f1} 42 } {1 2 2 4 3 8 4 16} 43 do_test delete-3.1.2 { 44 execsql {DELETE FROM table1 WHERE f1=3} 45 } {} 46 do_test delete-3.1.3 { 47 execsql {SELECT * FROM table1 ORDER BY f1} 48 } {1 2 2 4 4 16} 49 do_test delete-3.1.4 { 50 execsql {CREATE INDEX index1 ON table1(f1)} 51 execsql {PRAGMA count_changes=on} 52 ifcapable explain { 53 execsql {EXPLAIN DELETE FROM table1 WHERE f1=3} 54 } 55 execsql {DELETE FROM 'table1' WHERE f1=3} 56 } {0} 57 do_test delete-3.1.5 { 58 execsql {SELECT * FROM table1 ORDER BY f1} 59 } {1 2 2 4 4 16} 60 do_test delete-3.1.6.1 { 61 execsql {DELETE FROM table1 WHERE f1=2} 62 } {1} 63 do_test delete-3.1.6.2 { 64 db changes 65 } 1 66 do_test delete-3.1.7 { 67 execsql {SELECT * FROM table1 ORDER BY f1} 68 } {1 2 4 16} 69 integrity_check delete-3.2 70 71 72 # Semantic errors in the WHERE clause 73 # 74 do_test delete-4.1 { 75 execsql {CREATE TABLE table2(f1 int, f2 int)} 76 set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg] 77 lappend v $msg 78 } {1 {no such column: f3}} 79 80 do_test delete-4.2 { 81 set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg] 82 lappend v $msg 83 } {1 {no such function: xyzzy}} 84 integrity_check delete-4.3 85 86 # Lots of deletes 87 # 88 do_test delete-5.1.1 { 89 execsql {DELETE FROM table1} 90 } {2} 91 do_test delete-5.1.2 { 92 execsql {SELECT count(*) FROM table1} 93 } {0} 94 do_test delete-5.2.1 { 95 execsql {BEGIN TRANSACTION} 96 for {set i 1} {$i<=200} {incr i} { 97 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" 98 } 99 execsql {COMMIT} 100 execsql {SELECT count(*) FROM table1} 101 } {200} 102 do_test delete-5.2.2 { 103 execsql {DELETE FROM table1} 104 } {200} 105 do_test delete-5.2.3 { 106 execsql {BEGIN TRANSACTION} 107 for {set i 1} {$i<=200} {incr i} { 108 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" 109 } 110 execsql {COMMIT} 111 execsql {SELECT count(*) FROM table1} 112 } {200} 113 do_test delete-5.2.4 { 114 execsql {PRAGMA count_changes=off} 115 execsql {DELETE FROM table1} 116 } {} 117 do_test delete-5.2.5 { 118 execsql {SELECT count(*) FROM table1} 119 } {0} 120 do_test delete-5.2.6 { 121 execsql {BEGIN TRANSACTION} 122 for {set i 1} {$i<=200} {incr i} { 123 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" 124 } 125 execsql {COMMIT} 126 execsql {SELECT count(*) FROM table1} 127 } {200} 128 do_test delete-5.3 { 129 for {set i 1} {$i<=200} {incr i 4} { 130 execsql "DELETE FROM table1 WHERE f1==$i" 131 } 132 execsql {SELECT count(*) FROM table1} 133 } {150} 134 do_test delete-5.4.1 { 135 execsql "DELETE FROM table1 WHERE f1>50" 136 db changes 137 } [db one {SELECT count(*) FROM table1 WHERE f1>50}] 138 do_test delete-5.4.2 { 139 execsql {SELECT count(*) FROM table1} 140 } {37} 141 do_test delete-5.5 { 142 for {set i 1} {$i<=70} {incr i 3} { 143 execsql "DELETE FROM table1 WHERE f1==$i" 144 } 145 execsql {SELECT f1 FROM table1 ORDER BY f1} 146 } {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50} 147 do_test delete-5.6 { 148 for {set i 1} {$i<40} {incr i} { 149 execsql "DELETE FROM table1 WHERE f1==$i" 150 } 151 execsql {SELECT f1 FROM table1 ORDER BY f1} 152 } {42 44 47 48 50} 153 do_test delete-5.7 { 154 execsql "DELETE FROM table1 WHERE f1!=48" 155 execsql {SELECT f1 FROM table1 ORDER BY f1} 156 } {48} 157 integrity_check delete-5.8 158 159 160 # Delete large quantities of data. We want to test the List overflow 161 # mechanism in the vdbe. 162 # 163 do_test delete-6.1 { 164 execsql {BEGIN; DELETE FROM table1} 165 for {set i 1} {$i<=3000} {incr i} { 166 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" 167 } 168 execsql {DELETE FROM table2} 169 for {set i 1} {$i<=3000} {incr i} { 170 execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])" 171 } 172 execsql {COMMIT} 173 execsql {SELECT count(*) FROM table1} 174 } {3000} 175 do_test delete-6.2 { 176 execsql {SELECT count(*) FROM table2} 177 } {3000} 178 do_test delete-6.3 { 179 execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1} 180 } {1 2 3 4 5 6 7 8 9} 181 do_test delete-6.4 { 182 execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1} 183 } {1 2 3 4 5 6 7 8 9} 184 do_test delete-6.5.1 { 185 execsql {DELETE FROM table1 WHERE f1>7} 186 db changes 187 } {2993} 188 do_test delete-6.5.2 { 189 execsql {SELECT f1 FROM table1 ORDER BY f1} 190 } {1 2 3 4 5 6 7} 191 do_test delete-6.6 { 192 execsql {DELETE FROM table2 WHERE f1>7} 193 execsql {SELECT f1 FROM table2 ORDER BY f1} 194 } {1 2 3 4 5 6 7} 195 do_test delete-6.7 { 196 execsql {DELETE FROM table1} 197 execsql {SELECT f1 FROM table1} 198 } {} 199 do_test delete-6.8 { 200 execsql {INSERT INTO table1 VALUES(2,3)} 201 execsql {SELECT f1 FROM table1} 202 } {2} 203 do_test delete-6.9 { 204 execsql {DELETE FROM table2} 205 execsql {SELECT f1 FROM table2} 206 } {} 207 do_test delete-6.10 { 208 execsql {INSERT INTO table2 VALUES(2,3)} 209 execsql {SELECT f1 FROM table2} 210 } {2} 211 integrity_check delete-6.11 212 213 do_test delete-7.1 { 214 execsql { 215 CREATE TABLE t3(a); 216 INSERT INTO t3 VALUES(1); 217 INSERT INTO t3 SELECT a+1 FROM t3; 218 INSERT INTO t3 SELECT a+2 FROM t3; 219 SELECT * FROM t3; 220 } 221 } {1 2 3 4} 222 ifcapable {trigger} { 223 do_test delete-7.2 { 224 execsql { 225 CREATE TABLE cnt(del); 226 INSERT INTO cnt VALUES(0); 227 CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN 228 UPDATE cnt SET del=del+1; 229 END; 230 DELETE FROM t3 WHERE a<2; 231 SELECT * FROM t3; 232 } 233 } {2 3 4} 234 do_test delete-7.3 { 235 execsql { 236 SELECT * FROM cnt; 237 } 238 } {1} 239 do_test delete-7.4 { 240 execsql { 241 DELETE FROM t3; 242 SELECT * FROM t3; 243 } 244 } {} 245 do_test delete-7.5 { 246 execsql { 247 SELECT * FROM cnt; 248 } 249 } {4} 250 do_test delete-7.6 { 251 execsql { 252 INSERT INTO t3 VALUES(1); 253 INSERT INTO t3 SELECT a+1 FROM t3; 254 INSERT INTO t3 SELECT a+2 FROM t3; 255 CREATE TABLE t4 AS SELECT * FROM t3; 256 PRAGMA count_changes=ON; 257 DELETE FROM t3; 258 DELETE FROM t4; 259 } 260 } {4 4} 261 } ;# endif trigger 262 ifcapable {!trigger} { 263 execsql {DELETE FROM t3} 264 } 265 integrity_check delete-7.7 266 267 # Make sure error messages are consistent when attempting to delete 268 # from a read-only database. Ticket #304. 269 # 270 do_test delete-8.0 { 271 execsql { 272 PRAGMA count_changes=OFF; 273 INSERT INTO t3 VALUES(123); 274 SELECT * FROM t3; 275 } 276 } {123} 277 db close 278 catch {file delete -force test.db-journal} 279 catch {file attributes test.db -permissions 0444} 280 catch {file attributes test.db -readonly 1} 281 sqlite3 db test.db 282 set ::DB [sqlite3_connection_pointer db] 283 do_test delete-8.1 { 284 catchsql { 285 DELETE FROM t3; 286 } 287 } {1 {attempt to write a readonly database}} 288 do_test delete-8.2 { 289 execsql {SELECT * FROM t3} 290 } {123} 291 do_test delete-8.3 { 292 catchsql { 293 DELETE FROM t3 WHERE 1; 294 } 295 } {1 {attempt to write a readonly database}} 296 do_test delete-8.4 { 297 execsql {SELECT * FROM t3} 298 } {123} 299 300 # Update for v3: In v2 the DELETE statement would succeed because no 301 # database writes actually occur. Version 3 refuses to open a transaction 302 # on a read-only file, so the statement fails. 303 do_test delete-8.5 { 304 catchsql { 305 DELETE FROM t3 WHERE a<100; 306 } 307 # v2 result: {0 {}} 308 } {1 {attempt to write a readonly database}} 309 do_test delete-8.6 { 310 execsql {SELECT * FROM t3} 311 } {123} 312 integrity_check delete-8.7 313 314 # Need to do the following for tcl 8.5 on mac. On that configuration, the 315 # -readonly flag is taken so seriously that a subsequent [file delete -force] 316 # (required before the next test file can be executed) will fail. 317 # 318 catch {file attributes test.db -readonly 0} 319 db close 320 file delete -force test.db test.db-journal 321 322 # The following tests verify that SQLite correctly handles the case 323 # where an index B-Tree is being scanned, the rowid column being read 324 # from each index entry and another statement deletes some rows from 325 # the index B-Tree. At one point this (obscure) scenario was causing 326 # SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect 327 # query results. 328 # 329 do_test delete-9.1 { 330 sqlite3 db test.db 331 execsql { 332 CREATE TABLE t5(a, b); 333 CREATE TABLE t6(c, d); 334 INSERT INTO t5 VALUES(1, 2); 335 INSERT INTO t5 VALUES(3, 4); 336 INSERT INTO t5 VALUES(5, 6); 337 INSERT INTO t6 VALUES('a', 'b'); 338 INSERT INTO t6 VALUES('c', 'd'); 339 CREATE INDEX i5 ON t5(a); 340 CREATE INDEX i6 ON t6(c); 341 } 342 } {} 343 do_test delete-9.2 { 344 set res [list] 345 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { 346 if {$r==2} { db eval { DELETE FROM t5 } } 347 lappend res $r $c $d 348 } 349 set res 350 } {1 a b 1 c d 2 a b {} c d} 351 do_test delete-9.3 { 352 execsql { 353 INSERT INTO t5 VALUES(1, 2); 354 INSERT INTO t5 VALUES(3, 4); 355 INSERT INTO t5 VALUES(5, 6); 356 } 357 set res [list] 358 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { 359 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } } 360 lappend res $r $c $d 361 } 362 set res 363 } {1 a b 1 c d 2 a b {} c d 3 a b 3 c d} 364 do_test delete-9.4 { 365 execsql { 366 DELETE FROM t5; 367 INSERT INTO t5 VALUES(1, 2); 368 INSERT INTO t5 VALUES(3, 4); 369 INSERT INTO t5 VALUES(5, 6); 370 } 371 set res [list] 372 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { 373 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } } 374 lappend res $r $c $d 375 } 376 set res 377 } {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d} 378 do_test delete-9.5 { 379 execsql { 380 DELETE FROM t5; 381 INSERT INTO t5 VALUES(1, 2); 382 INSERT INTO t5 VALUES(3, 4); 383 INSERT INTO t5 VALUES(5, 6); 384 } 385 set res [list] 386 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { 387 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } } 388 lappend res $r $c $d 389 } 390 set res 391 } {1 a b 1 c d 2 a b 2 c d} 392 393 394 finish_test 395
