diff options
Diffstat (limited to '')
-rw-r--r-- | test/delete.test | 443 |
1 files changed, 443 insertions, 0 deletions
diff --git a/test/delete.test b/test/delete.test new file mode 100644 index 0000000..214bae6 --- /dev/null +++ b/test/delete.test @@ -0,0 +1,443 @@ +# 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 |