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