1# 2005 August 24 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 script is a test of the DELETE command. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix delete4 18 19do_execsql_test 1.1 { 20 CREATE TABLE t1(x INTEGER PRIMARY KEY, y); 21 INSERT INTO t1 VALUES(1, 0); 22 INSERT INTO t1 VALUES(2, 1); 23 INSERT INTO t1 VALUES(3, 0); 24 INSERT INTO t1 VALUES(4, 1); 25 INSERT INTO t1 VALUES(5, 0); 26 INSERT INTO t1 VALUES(6, 1); 27 INSERT INTO t1 VALUES(7, 0); 28 INSERT INTO t1 VALUES(8, 1); 29} 30do_execsql_test 1.2 { 31 DELETE FROM t1 WHERE y=1; 32} 33do_execsql_test 1.3 { 34 SELECT x FROM t1; 35} {1 3 5 7} 36 37#------------------------------------------------------------------------- 38# 39reset_db 40do_execsql_test 2.1 { 41 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z); 42 INSERT INTO t1 VALUES(1, 0, randomblob(200)); 43 INSERT INTO t1 VALUES(2, 1, randomblob(200)); 44 INSERT INTO t1 VALUES(3, 0, randomblob(200)); 45 INSERT INTO t1 VALUES(4, 1, randomblob(200)); 46 INSERT INTO t1 VALUES(5, 0, randomblob(200)); 47 INSERT INTO t1 VALUES(6, 1, randomblob(200)); 48 INSERT INTO t1 VALUES(7, 0, randomblob(200)); 49 INSERT INTO t1 VALUES(8, 1, randomblob(200)); 50} 51do_execsql_test 2.2 { 52 DELETE FROM t1 WHERE y=1; 53} 54do_execsql_test 2.3 { 55 SELECT x FROM t1; 56} {1 3 5 7} 57 58 59#------------------------------------------------------------------------- 60# 61reset_db 62do_execsql_test 3.1 { 63 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; 64 INSERT INTO t1 VALUES(1, 2); 65 INSERT INTO t1 VALUES(2, 4); 66 INSERT INTO t1 VALUES(1, 5); 67 DELETE FROM t1 WHERE a=1; 68 SELECT * FROM t1; 69} {2 4} 70 71#------------------------------------------------------------------------- 72# DELETE statement that uses the OR optimization 73# 74reset_db 75do_execsql_test 3.1 { 76 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b); 77 CREATE INDEX i1a ON t1(a); 78 CREATE INDEX i1b ON t1(b); 79 INSERT INTO t1 VALUES(1, 'one', 'i'); 80 INSERT INTO t1 VALUES(2, 'two', 'ii'); 81 INSERT INTO t1 VALUES(3, 'three', 'iii'); 82 INSERT INTO t1 VALUES(4, 'four', 'iv'); 83 INSERT INTO t1 VALUES(5, 'one', 'i'); 84 INSERT INTO t1 VALUES(6, 'two', 'ii'); 85 INSERT INTO t1 VALUES(7, 'three', 'iii'); 86 INSERT INTO t1 VALUES(8, 'four', 'iv'); 87} {} 88 89do_execsql_test 3.2 { 90 DELETE FROM t1 WHERE a='two' OR b='iv'; 91} 92 93do_execsql_test 3.3 { 94 SELECT i FROM t1 ORDER BY i; 95} {1 3 5 7} 96 97do_execsql_test 3.4 { 98 PRAGMA integrity_check; 99} {ok} 100 101# Between 2015-09-14 and 2015-09-28, the following test cases would result 102# in corruption (wrong # of entries in index) due to a bug in the ONEPASS 103# optimization. 104# 105do_execsql_test 4.1 { 106 DROP TABLE IF EXISTS t4; 107 CREATE TABLE t4(col0, col1); 108 INSERT INTO "t4" VALUES(14, 'abcde'); 109 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 110 CREATE INDEX idx_t4_3 ON t4 (col0); 111 DELETE FROM t4 WHERE col0=69 OR col0>7; 112 PRAGMA integrity_check; 113} {ok} 114do_execsql_test 4.2 { 115 DROP TABLE IF EXISTS t4; 116 CREATE TABLE t4(col0, col1); 117 INSERT INTO "t4" VALUES(14, 'abcde'); 118 CREATE INDEX idx_t4_3 ON t4 (col0); 119 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 120 DELETE FROM t4 WHERE col0=69 OR col0>7; 121 PRAGMA integrity_check; 122} {ok} 123do_execsql_test 4.11 { 124 DROP TABLE IF EXISTS t4; 125 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID; 126 INSERT INTO t4 VALUES(14, 'abcde','xyzzy'); 127 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 128 CREATE INDEX idx_t4_3 ON t4 (col0); 129 DELETE FROM t4 WHERE col0=69 OR col0>7; 130 PRAGMA integrity_check; 131} {ok} 132do_execsql_test 4.12 { 133 DROP TABLE IF EXISTS t4; 134 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID; 135 INSERT INTO t4 VALUES(14, 'abcde','xyzzy'); 136 CREATE INDEX idx_t4_3 ON t4 (col0); 137 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 138 DELETE FROM t4 WHERE col0=69 OR col0>7; 139 PRAGMA integrity_check; 140} {ok} 141 142# 2016-04-09 143# Ticket https://sqlite.org/src/info/a306e56ff68b8fa5 144# Failure to completely delete when reverse_unordered_selects is 145# engaged. 146# 147db close 148forcedelete test.db 149sqlite3 db test.db 150do_execsql_test 5.0 { 151 PRAGMA page_size=1024; 152 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 153 CREATE INDEX x1 ON t1(b, c); 154 INSERT INTO t1(a,b,c) VALUES(1, 1, zeroblob(80)); 155 INSERT INTO t1(a,b,c) SELECT a+1, 1, c FROM t1; 156 INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1; 157 INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1; 158 INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1; 159 PRAGMA reverse_unordered_selects = ON; 160 DELETE FROM t1 WHERE b=2; 161 SELECT a FROM t1 WHERE b=2; 162} {} 163 164# 2016-05-02 165# Ticket https://www.sqlite.org/src/tktview/dc6ebeda93960877 166# A subquery in the WHERE clause of a one-pass DELETE can cause an 167# incorrect answer. 168# 169db close 170forcedelete test.db 171sqlite3 db test.db 172do_execsql_test 6.0 { 173 CREATE TABLE t2(x INT); 174 INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5); 175 DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x-1); 176 SELECT x FROM t2; 177} {1} 178do_execsql_test 6.1 { 179 DROP TABLE IF EXISTS t2; 180 CREATE TABLE t2(x INT); 181 INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5); 182 DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1); 183 SELECT x FROM t2; 184} {5} 185 186#------------------------------------------------------------------------- 187# Test the effect of failing to find a table row based on an index key 188# within a DELETE. Either because the db is corrupt, or a trigger on another 189# row already deleted the entry, or because a BEFORE trigger on the current 190# row has already deleted it. 191# 192do_execsql_test 7.1.0 { 193 CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID; 194 CREATE INDEX t3a ON t3(a); 195 CREATE INDEX t3b ON t3(b); 196 197 INSERT INTO t3 VALUES(1, 1, 1); 198 INSERT INTO t3 VALUES(2, 2, 2); 199 INSERT INTO t3 VALUES(3, 3, 3); 200 INSERT INTO t3 VALUES(4, 4, 1); 201} 202do_execsql_test 7.1.1 { 203 DELETE FROM t3 WHERE a=4 OR b=1; 204} 205do_execsql_test 7.1.2 { 206 SELECT * FROM t3; 207} { 2 2 2 3 3 3 } 208 209do_execsql_test 7.2.0 { 210 CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID; 211 CREATE INDEX t4i ON t4(b); 212 INSERT INTO t4 VALUES(1, 'hello'); 213 INSERT INTO t4 VALUES(2, 'world'); 214 215 CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID; 216 CREATE INDEX t5i ON t5(b); 217 INSERT INTO t5 VALUES(1, 'hello'); 218 INSERT INTO t5 VALUES(3, 'world'); 219 220 PRAGMA writable_schema = 1; 221 UPDATE sqlite_master SET rootpage = ( 222 SELECT rootpage FROM sqlite_master WHERE name = 't5' 223 ) WHERE name = 't4'; 224} 225 226db close 227sqlite3 db test.db 228do_execsql_test 7.2.1 { 229 DELETE FROM t4 WHERE b='world' 230} 231reset_db 232 233do_execsql_test 7.3.0 { 234 CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID; 235 INSERT INTO t3 VALUES(1, 2, 3); 236 INSERT INTO t3 VALUES(4, 5, 6); 237 INSERT INTO t3 VALUES(7, 8, 9); 238 CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN 239 DELETE FROM t3 WHERE id=old.id+3; 240 END; 241} 242 243do_execsql_test 7.3.1 { 244 DELETE FROM t3 WHERE a IN(2, 5, 8); 245 SELECT * FROM t3; 246} {} 247 248do_execsql_test 7.3.2 { 249 DROP TRIGGER t3t; 250 INSERT INTO t3 VALUES(1, 2, 3); 251 INSERT INTO t3 VALUES(4, 5, 6); 252 INSERT INTO t3 VALUES(7, 8, 9); 253 CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN 254 DELETE FROM t3 WHERE id=old.id; 255 END; 256} 257 258do_execsql_test 7.3.3 { 259 DELETE FROM t3 WHERE a IN(2, 5, 8); 260 SELECT * FROM t3; 261} {} 262 263 264finish_test 265