gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/delete4.test (about) 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 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix delete4 18 19 do_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 } 30 do_execsql_test 1.2 { 31 DELETE FROM t1 WHERE y=1; 32 } 33 do_execsql_test 1.3 { 34 SELECT x FROM t1; 35 } {1 3 5 7} 36 37 #------------------------------------------------------------------------- 38 # 39 reset_db 40 do_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 } 51 do_execsql_test 2.2 { 52 DELETE FROM t1 WHERE y=1; 53 } 54 do_execsql_test 2.3 { 55 SELECT x FROM t1; 56 } {1 3 5 7} 57 58 59 #------------------------------------------------------------------------- 60 # 61 reset_db 62 do_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} 71 do_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 # 82 reset_db 83 do_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 97 do_execsql_test 3.2 { 98 DELETE FROM t1 WHERE a='two' OR b='iv'; 99 } 100 101 do_execsql_test 3.3 { 102 SELECT i FROM t1 ORDER BY i; 103 } {1 3 5 7} 104 105 do_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 # 113 do_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} 122 do_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} 131 do_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} 140 do_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 # 155 db close 156 forcedelete test.db 157 sqlite3 db test.db 158 do_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 # 177 db close 178 forcedelete test.db 179 sqlite3 db test.db 180 do_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} 186 do_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 # 200 do_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 } 210 do_execsql_test 7.1.1 { 211 DELETE FROM t3 WHERE a=4 OR b=1; 212 } 213 do_execsql_test 7.1.2 { 214 SELECT * FROM t3; 215 } { 2 2 2 3 3 3 } 216 217 do_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 234 db close 235 sqlite3 db test.db 236 do_execsql_test 7.2.1 { 237 DELETE FROM t4 WHERE b='world' 238 } 239 reset_db 240 241 do_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 251 do_execsql_test 7.3.1 { 252 DELETE FROM t3 WHERE a IN(2, 5, 8); 253 SELECT * FROM t3; 254 } {} 255 256 do_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 266 do_execsql_test 7.3.3 { 267 DELETE FROM t3 WHERE a IN(2, 5, 8); 268 SELECT * FROM t3; 269 } {} 270 271 272 finish_test