test/delete.test

   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 # 2023-03-15
 420 # https://sqlite.org/forum/forumpost/e61252062c9d286d
 421 #
 422 # When the WHERE clause of a DELETE statement contains a subquery
 423 # which uses the table that is being deleted from and there is a
 424 # short-circuit operator of some kind in the WHERE clause such that
 425 # the subquery might not run right away, then the subquery might
 426 # run after one or more rows have been deleted, which can change
 427 # the result of the subquery, and result in the wrong answer.
 428 #
 429 # Similar problem for UPDATE tested by update-21.4
 430 # https://sqlite.org/forum/forumpost/0007d1fdb1
 431 #
 432 reset_db
 433 do_execsql_test delete-12.0 {
 434   CREATE TABLE t0(vkey INTEGER, pkey INTEGER,c1 INTEGER);
 435   INSERT INTO t0 VALUES(2,1,-20),(2,2,NULL),(2,3,0),(8,4,95);
 436   DELETE FROM t0 WHERE NOT (
 437     (t0.vkey <= t0.c1) AND
 438     (t0.vkey <> (SELECT vkey FROM t0 ORDER BY vkey LIMIT 1 OFFSET 2))
 439   );
 440   SELECT * FROM t0;
 441 } {8 4 95}
 442
 443 finish_test