# 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the DELETE FROM statement. # # $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to delete from a non-existant table. # do_test delete-1.1 { set v [catch {execsql {DELETE FROM test1}} msg] lappend v $msg } {1 {no such table: test1}} # Try to delete from sqlite_master # do_test delete-2.1 { set v [catch {execsql {DELETE FROM sqlite_master}} msg] lappend v $msg } {1 {table sqlite_master may not be modified}} # Delete selected entries from a table with and without an index. # do_test delete-3.1.1 { execsql {CREATE TABLE table1(f1 int, f2 int)} execsql {INSERT INTO table1 VALUES(1,2)} execsql {INSERT INTO table1 VALUES(2,4)} execsql {INSERT INTO table1 VALUES(3,8)} execsql {INSERT INTO table1 VALUES(4,16)} execsql {SELECT * FROM table1 ORDER BY f1} } {1 2 2 4 3 8 4 16} do_test delete-3.1.2 { execsql {DELETE FROM table1 WHERE f1=3} } {} do_test delete-3.1.3 { execsql {SELECT * FROM table1 ORDER BY f1} } {1 2 2 4 4 16} do_test delete-3.1.4 { execsql {CREATE INDEX index1 ON table1(f1)} execsql {PRAGMA count_changes=on} ifcapable explain { execsql {EXPLAIN DELETE FROM table1 WHERE f1=3} } execsql {DELETE FROM 'table1' WHERE f1=3} } {0} do_test delete-3.1.5 { execsql {SELECT * FROM table1 ORDER BY f1} } {1 2 2 4 4 16} do_test delete-3.1.6.1 { execsql {DELETE FROM table1 WHERE f1=2} } {1} do_test delete-3.1.6.2 { db changes } 1 do_test delete-3.1.7 { execsql {SELECT * FROM table1 ORDER BY f1} } {1 2 4 16} integrity_check delete-3.2 # Semantic errors in the WHERE clause # do_test delete-4.1 { execsql {CREATE TABLE table2(f1 int, f2 int)} set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg] lappend v $msg } {1 {no such column: f3}} do_test delete-4.2 { set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg] lappend v $msg } {1 {no such function: xyzzy}} integrity_check delete-4.3 # Lots of deletes # do_test delete-5.1.1 { execsql {DELETE FROM table1} } {2} do_test delete-5.1.2 { execsql {SELECT count(*) FROM table1} } {0} do_test delete-5.2.1 { execsql {BEGIN TRANSACTION} for {set i 1} {$i<=200} {incr i} { execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" } execsql {COMMIT} execsql {SELECT count(*) FROM table1} } {200} do_test delete-5.2.2 { execsql {DELETE FROM table1} } {200} do_test delete-5.2.3 { execsql {BEGIN TRANSACTION} for {set i 1} {$i<=200} {incr i} { execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" } execsql {COMMIT} execsql {SELECT count(*) FROM table1} } {200} do_test delete-5.2.4 { execsql {PRAGMA count_changes=off} execsql {DELETE FROM table1} } {} do_test delete-5.2.5 { execsql {SELECT count(*) FROM table1} } {0} do_test delete-5.2.6 { execsql {BEGIN TRANSACTION} for {set i 1} {$i<=200} {incr i} { execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" } execsql {COMMIT} execsql {SELECT count(*) FROM table1} } {200} do_test delete-5.3 { for {set i 1} {$i<=200} {incr i 4} { execsql "DELETE FROM table1 WHERE f1==$i" } execsql {SELECT count(*) FROM table1} } {150} do_test delete-5.4.1 { execsql "DELETE FROM table1 WHERE f1>50" db changes } [db one {SELECT count(*) FROM table1 WHERE f1>50}] do_test delete-5.4.2 { execsql {SELECT count(*) FROM table1} } {37} do_test delete-5.5 { for {set i 1} {$i<=70} {incr i 3} { execsql "DELETE FROM table1 WHERE f1==$i" } execsql {SELECT f1 FROM table1 ORDER BY f1} } {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} do_test delete-5.6 { for {set i 1} {$i<40} {incr i} { execsql "DELETE FROM table1 WHERE f1==$i" } execsql {SELECT f1 FROM table1 ORDER BY f1} } {42 44 47 48 50} do_test delete-5.7 { execsql "DELETE FROM table1 WHERE f1!=48" execsql {SELECT f1 FROM table1 ORDER BY f1} } {48} integrity_check delete-5.8 # Delete large quantities of data. We want to test the List overflow # mechanism in the vdbe. # do_test delete-6.1 { execsql {BEGIN; DELETE FROM table1} for {set i 1} {$i<=3000} {incr i} { execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])" } execsql {DELETE FROM table2} for {set i 1} {$i<=3000} {incr i} { execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])" } execsql {COMMIT} execsql {SELECT count(*) FROM table1} } {3000} do_test delete-6.2 { execsql {SELECT count(*) FROM table2} } {3000} do_test delete-6.3 { execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1} } {1 2 3 4 5 6 7 8 9} do_test delete-6.4 { execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1} } {1 2 3 4 5 6 7 8 9} do_test delete-6.5.1 { execsql {DELETE FROM table1 WHERE f1>7} db changes } {2993} do_test delete-6.5.2 { execsql {SELECT f1 FROM table1 ORDER BY f1} } {1 2 3 4 5 6 7} do_test delete-6.6 { execsql {DELETE FROM table2 WHERE f1>7} execsql {SELECT f1 FROM table2 ORDER BY f1} } {1 2 3 4 5 6 7} do_test delete-6.7 { execsql {DELETE FROM table1} execsql {SELECT f1 FROM table1} } {} do_test delete-6.8 { execsql {INSERT INTO table1 VALUES(2,3)} execsql {SELECT f1 FROM table1} } {2} do_test delete-6.9 { execsql {DELETE FROM table2} execsql {SELECT f1 FROM table2} } {} do_test delete-6.10 { execsql {INSERT INTO table2 VALUES(2,3)} execsql {SELECT f1 FROM table2} } {2} integrity_check delete-6.11 do_test delete-7.1 { execsql { CREATE TABLE t3(a); INSERT INTO t3 VALUES(1); INSERT INTO t3 SELECT a+1 FROM t3; INSERT INTO t3 SELECT a+2 FROM t3; SELECT * FROM t3; } } {1 2 3 4} ifcapable {trigger} { do_test delete-7.2 { execsql { CREATE TABLE cnt(del); INSERT INTO cnt VALUES(0); CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN UPDATE cnt SET del=del+1; END; DELETE FROM t3 WHERE a<2; SELECT * FROM t3; } } {2 3 4} do_test delete-7.3 { execsql { SELECT * FROM cnt; } } {1} do_test delete-7.4 { execsql { DELETE FROM t3; SELECT * FROM t3; } } {} do_test delete-7.5 { execsql { SELECT * FROM cnt; } } {4} do_test delete-7.6 { execsql { INSERT INTO t3 VALUES(1); INSERT INTO t3 SELECT a+1 FROM t3; INSERT INTO t3 SELECT a+2 FROM t3; CREATE TABLE t4 AS SELECT * FROM t3; PRAGMA count_changes=ON; DELETE FROM t3; DELETE FROM t4; } } {4 4} } ;# endif trigger ifcapable {!trigger} { execsql {DELETE FROM t3} } integrity_check delete-7.7 # Make sure error messages are consistent when attempting to delete # from a read-only database. Ticket #304. # do_test delete-8.0 { execsql { PRAGMA count_changes=OFF; INSERT INTO t3 VALUES(123); SELECT * FROM t3; } } {123} db close catch {forcedelete test.db-journal} catch {file attributes test.db -permissions 0444} catch {file attributes test.db -readonly 1} sqlite3 db test.db set ::DB [sqlite3_connection_pointer db] do_test delete-8.1 { catchsql { DELETE FROM t3; } } {1 {attempt to write a readonly database}} do_test delete-8.2 { execsql {SELECT * FROM t3} } {123} do_test delete-8.3 { catchsql { DELETE FROM t3 WHERE 1; } } {1 {attempt to write a readonly database}} do_test delete-8.4 { execsql {SELECT * FROM t3} } {123} # Update for v3: In v2 the DELETE statement would succeed because no # database writes actually occur. Version 3 refuses to open a transaction # on a read-only file, so the statement fails. do_test delete-8.5 { catchsql { DELETE FROM t3 WHERE a<100; } # v2 result: {0 {}} } {1 {attempt to write a readonly database}} do_test delete-8.6 { execsql {SELECT * FROM t3} } {123} integrity_check delete-8.7 # Need to do the following for tcl 8.5 on mac. On that configuration, the # -readonly flag is taken so seriously that a subsequent [forcedelete] # (required before the next test file can be executed) will fail. # catch {file attributes test.db -readonly 0} db close forcedelete test.db test.db-journal # The following tests verify that SQLite correctly handles the case # where an index B-Tree is being scanned, the rowid column being read # from each index entry and another statement deletes some rows from # the index B-Tree. At one point this (obscure) scenario was causing # SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect # query results. # do_test delete-9.1 { sqlite3 db test.db execsql { CREATE TABLE t5(a, b); CREATE TABLE t6(c, d); INSERT INTO t5 VALUES(1, 2); INSERT INTO t5 VALUES(3, 4); INSERT INTO t5 VALUES(5, 6); INSERT INTO t6 VALUES('a', 'b'); INSERT INTO t6 VALUES('c', 'd'); CREATE INDEX i5 ON t5(a); CREATE INDEX i6 ON t6(c); } } {} do_test delete-9.2 { set res [list] db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { if {$r==2} { db eval { DELETE FROM t5 } } lappend res $r $c $d } set res } {1 a b 1 c d 2 a b {} c d} do_test delete-9.3 { execsql { INSERT INTO t5 VALUES(1, 2); INSERT INTO t5 VALUES(3, 4); INSERT INTO t5 VALUES(5, 6); } set res [list] db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } } lappend res $r $c $d } set res } {1 a b 1 c d 2 a b {} c d 3 a b 3 c d} do_test delete-9.4 { execsql { DELETE FROM t5; INSERT INTO t5 VALUES(1, 2); INSERT INTO t5 VALUES(3, 4); INSERT INTO t5 VALUES(5, 6); } set res [list] db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } } lappend res $r $c $d } set res } {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d} do_test delete-9.5 { execsql { DELETE FROM t5; INSERT INTO t5 VALUES(1, 2); INSERT INTO t5 VALUES(3, 4); INSERT INTO t5 VALUES(5, 6); } set res [list] db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } { if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } } lappend res $r $c $d } set res } {1 a b 1 c d 2 a b 2 c d} do_execsql_test delete-10.0 { CREATE TABLE t1(a INT UNIQUE, b INT); INSERT INTO t1(a,b) VALUES('1','2'); SELECT * FROM t1 WHERE a='1' AND b='2'; } {1 2} do_execsql_test delete-10.1 { DELETE FROM t1 WHERE a='1' AND b='2'; } do_execsql_test delete-10.2 { SELECT * FROM t1 WHERE a='1' AND b='2'; } do_execsql_test delete-11.0 { CREATE TABLE t11(a INTEGER PRIMARY KEY, b INT); WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<20) INSERT INTO t11(a,b) SELECT x, (x*17)%100 FROM cnt; SELECT * FROM t11; } {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} do_execsql_test delete-11.1 { DELETE FROM t11 AS xyz WHERE EXISTS(SELECT 1 FROM t11 WHERE t11.a>xyz.a AND t11.b<=xyz.b); SELECT * FROM t11; } {6 2 12 4 18 6 19 23 20 40} # 2023-03-15 # https://sqlite.org/forum/forumpost/e61252062c9d286d # # When the WHERE clause of a DELETE statement contains a subquery # which uses the table that is being deleted from and there is a # short-circuit operator of some kind in the WHERE clause such that # the subquery might not run right away, then the subquery might # run after one or more rows have been deleted, which can change # the result of the subquery, and result in the wrong answer. # # Similar problem for UPDATE tested by update-21.4 # https://sqlite.org/forum/forumpost/0007d1fdb1 # reset_db do_execsql_test delete-12.0 { CREATE TABLE t0(vkey INTEGER, pkey INTEGER,c1 INTEGER); INSERT INTO t0 VALUES(2,1,-20),(2,2,NULL),(2,3,0),(8,4,95); DELETE FROM t0 WHERE NOT ( (t0.vkey <= t0.c1) AND (t0.vkey <> (SELECT vkey FROM t0 ORDER BY vkey LIMIT 1 OFFSET 2)) ); SELECT * FROM t0; } {8 4 95} finish_test