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