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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Try to delete from a non-existant table. 20# 21do_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# 28do_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# 35do_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} 43do_test delete-3.1.2 { 44 execsql {DELETE FROM table1 WHERE f1=3} 45} {} 46do_test delete-3.1.3 { 47 execsql {SELECT * FROM table1 ORDER BY f1} 48} {1 2 2 4 4 16} 49do_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} 57do_test delete-3.1.5 { 58 execsql {SELECT * FROM table1 ORDER BY f1} 59} {1 2 2 4 4 16} 60do_test delete-3.1.6.1 { 61 execsql {DELETE FROM table1 WHERE f1=2} 62} {1} 63do_test delete-3.1.6.2 { 64 db changes 65} 1 66do_test delete-3.1.7 { 67 execsql {SELECT * FROM table1 ORDER BY f1} 68} {1 2 4 16} 69integrity_check delete-3.2 70 71 72# Semantic errors in the WHERE clause 73# 74do_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 80do_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}} 84integrity_check delete-4.3 85 86# Lots of deletes 87# 88do_test delete-5.1.1 { 89 execsql {DELETE FROM table1} 90} {2} 91do_test delete-5.1.2 { 92 execsql {SELECT count(*) FROM table1} 93} {0} 94do_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} 102do_test delete-5.2.2 { 103 execsql {DELETE FROM table1} 104} {200} 105do_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} 113do_test delete-5.2.4 { 114 execsql {PRAGMA count_changes=off} 115 execsql {DELETE FROM table1} 116} {} 117do_test delete-5.2.5 { 118 execsql {SELECT count(*) FROM table1} 119} {0} 120do_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} 128do_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} 134do_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}] 138do_test delete-5.4.2 { 139 execsql {SELECT count(*) FROM table1} 140} {37} 141do_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} 147do_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} 153do_test delete-5.7 { 154 execsql "DELETE FROM table1 WHERE f1!=48" 155 execsql {SELECT f1 FROM table1 ORDER BY f1} 156} {48} 157integrity_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# 163do_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} 175do_test delete-6.2 { 176 execsql {SELECT count(*) FROM table2} 177} {3000} 178do_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} 181do_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} 184do_test delete-6.5.1 { 185 execsql {DELETE FROM table1 WHERE f1>7} 186 db changes 187} {2993} 188do_test delete-6.5.2 { 189 execsql {SELECT f1 FROM table1 ORDER BY f1} 190} {1 2 3 4 5 6 7} 191do_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} 195do_test delete-6.7 { 196 execsql {DELETE FROM table1} 197 execsql {SELECT f1 FROM table1} 198} {} 199do_test delete-6.8 { 200 execsql {INSERT INTO table1 VALUES(2,3)} 201 execsql {SELECT f1 FROM table1} 202} {2} 203do_test delete-6.9 { 204 execsql {DELETE FROM table2} 205 execsql {SELECT f1 FROM table2} 206} {} 207do_test delete-6.10 { 208 execsql {INSERT INTO table2 VALUES(2,3)} 209 execsql {SELECT f1 FROM table2} 210} {2} 211integrity_check delete-6.11 212 213do_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} 222ifcapable {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 262ifcapable {!trigger} { 263 execsql {DELETE FROM t3} 264} 265integrity_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# 270do_test delete-8.0 { 271 execsql { 272 PRAGMA count_changes=OFF; 273 INSERT INTO t3 VALUES(123); 274 SELECT * FROM t3; 275 } 276} {123} 277db close 278catch {file delete -force test.db-journal} 279catch {file attributes test.db -permissions 0444} 280catch {file attributes test.db -readonly 1} 281sqlite3 db test.db 282set ::DB [sqlite3_connection_pointer db] 283do_test delete-8.1 { 284 catchsql { 285 DELETE FROM t3; 286 } 287} {1 {attempt to write a readonly database}} 288do_test delete-8.2 { 289 execsql {SELECT * FROM t3} 290} {123} 291do_test delete-8.3 { 292 catchsql { 293 DELETE FROM t3 WHERE 1; 294 } 295} {1 {attempt to write a readonly database}} 296do_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. 303do_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}} 309do_test delete-8.6 { 310 execsql {SELECT * FROM t3} 311} {123} 312integrity_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# 318catch {file attributes test.db -readonly 0} 319db close 320file 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# 329do_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} {} 343do_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} 351do_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} 364do_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} 378do_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 394finish_test 395