gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/delete.test (about) 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 # Semantic errors in the WHERE clause 72 # 73 do_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 79 do_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}} 83 integrity_check delete-4.3 84 85 # Lots of deletes 86 # 87 do_test delete-5.1.1 { 88 execsql {DELETE FROM table1} 89 } {2} 90 do_test delete-5.1.2 { 91 execsql {SELECT count(*) FROM table1} 92 } {0} 93 do_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} 101 do_test delete-5.2.2 { 102 execsql {DELETE FROM table1} 103 } {200} 104 do_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} 112 do_test delete-5.2.4 { 113 execsql {PRAGMA count_changes=off} 114 execsql {DELETE FROM table1} 115 } {} 116 do_test delete-5.2.5 { 117 execsql {SELECT count(*) FROM table1} 118 } {0} 119 do_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} 127 do_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} 133 do_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}] 137 do_test delete-5.4.2 { 138 execsql {SELECT count(*) FROM table1} 139 } {37} 140 do_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} 146 do_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} 152 do_test delete-5.7 { 153 execsql "DELETE FROM table1 WHERE f1!=48" 154 execsql {SELECT f1 FROM table1 ORDER BY f1} 155 } {48} 156 integrity_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 # 162 do_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} 174 do_test delete-6.2 { 175 execsql {SELECT count(*) FROM table2} 176 } {3000} 177 do_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} 180 do_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} 183 do_test delete-6.5.1 { 184 execsql {DELETE FROM table1 WHERE f1>7} 185 db changes 186 } {2993} 187 do_test delete-6.5.2 { 188 execsql {SELECT f1 FROM table1 ORDER BY f1} 189 } {1 2 3 4 5 6 7} 190 do_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} 194 do_test delete-6.7 { 195 execsql {DELETE FROM table1} 196 execsql {SELECT f1 FROM table1} 197 } {} 198 do_test delete-6.8 { 199 execsql {INSERT INTO table1 VALUES(2,3)} 200 execsql {SELECT f1 FROM table1} 201 } {2} 202 do_test delete-6.9 { 203 execsql {DELETE FROM table2} 204 execsql {SELECT f1 FROM table2} 205 } {} 206 do_test delete-6.10 { 207 execsql {INSERT INTO table2 VALUES(2,3)} 208 execsql {SELECT f1 FROM table2} 209 } {2} 210 integrity_check delete-6.11 211 212 do_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} 221 ifcapable {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 261 ifcapable {!trigger} { 262 execsql {DELETE FROM t3} 263 } 264 integrity_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 # 269 do_test delete-8.0 { 270 execsql { 271 PRAGMA count_changes=OFF; 272 INSERT INTO t3 VALUES(123); 273 SELECT * FROM t3; 274 } 275 } {123} 276 db close 277 catch {forcedelete test.db-journal} 278 catch {file attributes test.db -permissions 0444} 279 catch {file attributes test.db -readonly 1} 280 sqlite3 db test.db 281 set ::DB [sqlite3_connection_pointer db] 282 do_test delete-8.1 { 283 catchsql { 284 DELETE FROM t3; 285 } 286 } {1 {attempt to write a readonly database}} 287 do_test delete-8.2 { 288 execsql {SELECT * FROM t3} 289 } {123} 290 do_test delete-8.3 { 291 catchsql { 292 DELETE FROM t3 WHERE 1; 293 } 294 } {1 {attempt to write a readonly database}} 295 do_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. 302 do_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}} 308 do_test delete-8.6 { 309 execsql {SELECT * FROM t3} 310 } {123} 311 integrity_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 # 317 catch {file attributes test.db -readonly 0} 318 db close 319 forcedelete 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 # 328 do_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 } {} 342 do_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} 350 do_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} 363 do_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} 377 do_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 392 do_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 398 do_execsql_test delete-10.1 { 399 DELETE FROM t1 WHERE a='1' AND b='2'; 400 } 401 402 do_execsql_test delete-10.2 { 403 SELECT * FROM t1 WHERE a='1' AND b='2'; 404 } 405 406 do_execsql_test delete-11.0 { 407 CREATE TABLE t11(a INTEGER PRIMARY KEY, b INT); 408 WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<20) 409 INSERT INTO t11(a,b) SELECT x, (x*17)%100 FROM cnt; 410 SELECT * FROM t11; 411 } {1 17 2 34 3 51 4 68 5 85 6 2 7 19 8 36 9 53 10 70 11 87 12 4 13 21 14 38 15 55 16 72 17 89 18 6 19 23 20 40} 412 do_execsql_test delete-11.1 { 413 DELETE FROM t11 AS xyz 414 WHERE EXISTS(SELECT 1 FROM t11 WHERE t11.a>xyz.a AND t11.b<=xyz.b); 415 SELECT * FROM t11; 416 } {6 2 12 4 18 6 19 23 20 40} 417 418 419 finish_test