| blob | 61e979ef1153429c8af9eb245b6320f079e0e398 |
1 # 2001 September 15
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 file is testing the DELETE FROM statement.
13 #
14 # $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Try to delete from a non-existant table.
20 #
21 do_test delete-1.1 {
22 set v [catch {execsql {DELETE FROM test1}} msg]
23 lappend v $msg
24 } {1 {no such table: test1}}
26 # Try to delete from sqlite_master
27 #
28 do_test delete-2.1 {
29 set v [catch {execsql {DELETE FROM sqlite_master}} msg]
30 lappend v $msg
31 } {1 {table sqlite_master may not be modified}}
33 # Delete selected entries from a table with and without an index.
34 #
35 do_test delete-3.1.1 {
36 execsql {CREATE TABLE table1(f1 int, f2 int)}
37 execsql {INSERT INTO table1 VALUES(1,2)}
38 execsql {INSERT INTO table1 VALUES(2,4)}
39 execsql {INSERT INTO table1 VALUES(3,8)}
40 execsql {INSERT INTO table1 VALUES(4,16)}
41 execsql {SELECT * FROM table1 ORDER BY f1}
42 } {1 2 2 4 3 8 4 16}
43 do_test delete-3.1.2 {
44 execsql {DELETE FROM table1 WHERE f1=3}
45 } {}
46 do_test delete-3.1.3 {
47 execsql {SELECT * FROM table1 ORDER BY f1}
48 } {1 2 2 4 4 16}
49 do_test delete-3.1.4 {
50 execsql {CREATE INDEX index1 ON table1(f1)}
51 execsql {PRAGMA count_changes=on}
52 ifcapable explain {
53 execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
54 }
55 execsql {DELETE FROM 'table1' WHERE f1=3}
56 } {0}
57 do_test delete-3.1.5 {
58 execsql {SELECT * FROM table1 ORDER BY f1}
59 } {1 2 2 4 4 16}
60 do_test delete-3.1.6.1 {
61 execsql {DELETE FROM table1 WHERE f1=2}
62 } {1}
63 do_test delete-3.1.6.2 {
64 db changes
65 } 1
66 do_test delete-3.1.7 {
67 execsql {SELECT * FROM table1 ORDER BY f1}
68 } {1 2 4 16}
69 integrity_check delete-3.2
72 # Semantic errors in the WHERE clause
73 #
74 do_test delete-4.1 {
75 execsql {CREATE TABLE table2(f1 int, f2 int)}
76 set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
77 lappend v $msg
78 } {1 {no such column: f3}}
80 do_test delete-4.2 {
81 set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
82 lappend v $msg
83 } {1 {no such function: xyzzy}}
84 integrity_check delete-4.3
86 # Lots of deletes
87 #
88 do_test delete-5.1.1 {
89 execsql {DELETE FROM table1}
90 } {2}
91 do_test delete-5.1.2 {
92 execsql {SELECT count(*) FROM table1}
93 } {0}
94 do_test delete-5.2.1 {
95 execsql {BEGIN TRANSACTION}
96 for {set i 1} {$i<=200} {incr i} {
97 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
98 }
99 execsql {COMMIT}
100 execsql {SELECT count(*) FROM table1}
101 } {200}
102 do_test delete-5.2.2 {
103 execsql {DELETE FROM table1}
104 } {200}
105 do_test delete-5.2.3 {
106 execsql {BEGIN TRANSACTION}
107 for {set i 1} {$i<=200} {incr i} {
108 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
109 }
110 execsql {COMMIT}
111 execsql {SELECT count(*) FROM table1}
112 } {200}
113 do_test delete-5.2.4 {
114 execsql {PRAGMA count_changes=off}
115 execsql {DELETE FROM table1}
116 } {}
117 do_test delete-5.2.5 {
118 execsql {SELECT count(*) FROM table1}
119 } {0}
120 do_test delete-5.2.6 {
121 execsql {BEGIN TRANSACTION}
122 for {set i 1} {$i<=200} {incr i} {
123 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
124 }
125 execsql {COMMIT}
126 execsql {SELECT count(*) FROM table1}
127 } {200}
128 do_test delete-5.3 {
129 for {set i 1} {$i<=200} {incr i 4} {
130 execsql "DELETE FROM table1 WHERE f1==$i"
131 }
132 execsql {SELECT count(*) FROM table1}
133 } {150}
134 do_test delete-5.4.1 {
135 execsql "DELETE FROM table1 WHERE f1>50"
136 db changes
137 } [db one {SELECT count(*) FROM table1 WHERE f1>50}]
138 do_test delete-5.4.2 {
139 execsql {SELECT count(*) FROM table1}
140 } {37}
141 do_test delete-5.5 {
142 for {set i 1} {$i<=70} {incr i 3} {
143 execsql "DELETE FROM table1 WHERE f1==$i"
144 }
145 execsql {SELECT f1 FROM table1 ORDER BY f1}
146 } {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}
147 do_test delete-5.6 {
148 for {set i 1} {$i<40} {incr i} {
149 execsql "DELETE FROM table1 WHERE f1==$i"
150 }
151 execsql {SELECT f1 FROM table1 ORDER BY f1}
152 } {42 44 47 48 50}
153 do_test delete-5.7 {
154 execsql "DELETE FROM table1 WHERE f1!=48"
155 execsql {SELECT f1 FROM table1 ORDER BY f1}
156 } {48}
157 integrity_check delete-5.8
160 # Delete large quantities of data. We want to test the List overflow
161 # mechanism in the vdbe.
162 #
163 do_test delete-6.1 {
164 execsql {BEGIN; DELETE FROM table1}
165 for {set i 1} {$i<=3000} {incr i} {
166 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
167 }
168 execsql {DELETE FROM table2}
169 for {set i 1} {$i<=3000} {incr i} {
170 execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
171 }
172 execsql {COMMIT}
173 execsql {SELECT count(*) FROM table1}
174 } {3000}
175 do_test delete-6.2 {
176 execsql {SELECT count(*) FROM table2}
177 } {3000}
178 do_test delete-6.3 {
179 execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
180 } {1 2 3 4 5 6 7 8 9}
181 do_test delete-6.4 {
182 execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
183 } {1 2 3 4 5 6 7 8 9}
184 do_test delete-6.5.1 {
185 execsql {DELETE FROM table1 WHERE f1>7}
186 db changes
187 } {2993}
188 do_test delete-6.5.2 {
189 execsql {SELECT f1 FROM table1 ORDER BY f1}
190 } {1 2 3 4 5 6 7}
191 do_test delete-6.6 {
192 execsql {DELETE FROM table2 WHERE f1>7}
193 execsql {SELECT f1 FROM table2 ORDER BY f1}
194 } {1 2 3 4 5 6 7}
195 do_test delete-6.7 {
196 execsql {DELETE FROM table1}
197 execsql {SELECT f1 FROM table1}
198 } {}
199 do_test delete-6.8 {
200 execsql {INSERT INTO table1 VALUES(2,3)}
201 execsql {SELECT f1 FROM table1}
202 } {2}
203 do_test delete-6.9 {
204 execsql {DELETE FROM table2}
205 execsql {SELECT f1 FROM table2}
206 } {}
207 do_test delete-6.10 {
208 execsql {INSERT INTO table2 VALUES(2,3)}
209 execsql {SELECT f1 FROM table2}
210 } {2}
211 integrity_check delete-6.11
213 do_test delete-7.1 {
214 execsql {
215 CREATE TABLE t3(a);
216 INSERT INTO t3 VALUES(1);
217 INSERT INTO t3 SELECT a+1 FROM t3;
218 INSERT INTO t3 SELECT a+2 FROM t3;
219 SELECT * FROM t3;
220 }
221 } {1 2 3 4}
222 ifcapable {trigger} {
223 do_test delete-7.2 {
224 execsql {
225 CREATE TABLE cnt(del);
226 INSERT INTO cnt VALUES(0);
227 CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
228 UPDATE cnt SET del=del+1;
229 END;
230 DELETE FROM t3 WHERE a<2;
231 SELECT * FROM t3;
232 }
233 } {2 3 4}
234 do_test delete-7.3 {
235 execsql {
236 SELECT * FROM cnt;
237 }
238 } {1}
239 do_test delete-7.4 {
240 execsql {
241 DELETE FROM t3;
242 SELECT * FROM t3;
243 }
244 } {}
245 do_test delete-7.5 {
246 execsql {
247 SELECT * FROM cnt;
248 }
249 } {4}
250 do_test delete-7.6 {
251 execsql {
252 INSERT INTO t3 VALUES(1);
253 INSERT INTO t3 SELECT a+1 FROM t3;
254 INSERT INTO t3 SELECT a+2 FROM t3;
255 CREATE TABLE t4 AS SELECT * FROM t3;
256 PRAGMA count_changes=ON;
257 DELETE FROM t3;
258 DELETE FROM t4;
259 }
260 } {4 4}
261 } ;# endif trigger
262 ifcapable {!trigger} {
263 execsql {DELETE FROM t3}
264 }
265 integrity_check delete-7.7
267 # Make sure error messages are consistent when attempting to delete
268 # from a read-only database. Ticket #304.
269 #
270 do_test delete-8.0 {
271 execsql {
272 PRAGMA count_changes=OFF;
273 INSERT INTO t3 VALUES(123);
274 SELECT * FROM t3;
275 }
276 } {123}
277 db close
278 catch {file delete -force test.db-journal}
279 catch {file attributes test.db -permissions 0444}
280 catch {file attributes test.db -readonly 1}
281 sqlite3 db test.db
282 set ::DB [sqlite3_connection_pointer db]
283 do_test delete-8.1 {
284 catchsql {
285 DELETE FROM t3;
286 }
287 } {1 {attempt to write a readonly database}}
288 do_test delete-8.2 {
289 execsql {SELECT * FROM t3}
290 } {123}
291 do_test delete-8.3 {
292 catchsql {
293 DELETE FROM t3 WHERE 1;
294 }
295 } {1 {attempt to write a readonly database}}
296 do_test delete-8.4 {
297 execsql {SELECT * FROM t3}
298 } {123}
300 # Update for v3: In v2 the DELETE statement would succeed because no
301 # database writes actually occur. Version 3 refuses to open a transaction
302 # on a read-only file, so the statement fails.
303 do_test delete-8.5 {
304 catchsql {
305 DELETE FROM t3 WHERE a<100;
306 }
307 # v2 result: {0 {}}
308 } {1 {attempt to write a readonly database}}
309 do_test delete-8.6 {
310 execsql {SELECT * FROM t3}
311 } {123}
312 integrity_check delete-8.7
314 # Need to do the following for tcl 8.5 on mac. On that configuration, the
315 # -readonly flag is taken so seriously that a subsequent [file delete -force]
316 # (required before the next test file can be executed) will fail.
317 #
318 catch {file attributes test.db -readonly 0}
319 db close
320 file delete -force test.db test.db-journal
322 # The following tests verify that SQLite correctly handles the case
323 # where an index B-Tree is being scanned, the rowid column being read
324 # from each index entry and another statement deletes some rows from
325 # the index B-Tree. At one point this (obscure) scenario was causing
326 # SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect
327 # query results.
328 #
329 do_test delete-9.1 {
330 sqlite3 db test.db
331 execsql {
332 CREATE TABLE t5(a, b);
333 CREATE TABLE t6(c, d);
334 INSERT INTO t5 VALUES(1, 2);
335 INSERT INTO t5 VALUES(3, 4);
336 INSERT INTO t5 VALUES(5, 6);
337 INSERT INTO t6 VALUES('a', 'b');
338 INSERT INTO t6 VALUES('c', 'd');
339 CREATE INDEX i5 ON t5(a);
340 CREATE INDEX i6 ON t6(c);
341 }
342 } {}
343 do_test delete-9.2 {
344 set res [list]
345 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
346 if {$r==2} { db eval { DELETE FROM t5 } }
347 lappend res $r $c $d
348 }
349 set res
350 } {1 a b 1 c d 2 a b {} c d}
351 do_test delete-9.3 {
352 execsql {
353 INSERT INTO t5 VALUES(1, 2);
354 INSERT INTO t5 VALUES(3, 4);
355 INSERT INTO t5 VALUES(5, 6);
356 }
357 set res [list]
358 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
359 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } }
360 lappend res $r $c $d
361 }
362 set res
363 } {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}
364 do_test delete-9.4 {
365 execsql {
366 DELETE FROM t5;
367 INSERT INTO t5 VALUES(1, 2);
368 INSERT INTO t5 VALUES(3, 4);
369 INSERT INTO t5 VALUES(5, 6);
370 }
371 set res [list]
372 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
373 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } }
374 lappend res $r $c $d
375 }
376 set res
377 } {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}
378 do_test delete-9.5 {
379 execsql {
380 DELETE FROM t5;
381 INSERT INTO t5 VALUES(1, 2);
382 INSERT INTO t5 VALUES(3, 4);
383 INSERT INTO t5 VALUES(5, 6);
384 }
385 set res [list]
386 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
387 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } }
388 lappend res $r $c $d
389 }
390 set res
391 } {1 a b 1 c d 2 a b 2 c d}
394 finish_test