xref: /sqlite-3.40.0/test/delete.test (revision 66b4ebaf) 
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.12 2003/05/04 20:42:56 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Try to delete from a non-existant table.
20#
21do_test delete-1.1 {
22  set v [catch {execsql {DELETE FROM test1}} msg]
23  lappend v $msg
24} {1 {no such table: test1}}
25
26# Try to delete from sqlite_master
27#
28do_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}}
32
33# Delete selected entries from a table with and without an index.
34#
35do_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}
43do_test delete-3.1.2 {
44  execsql {DELETE FROM table1 WHERE f1=3}
45} {}
46do_test delete-3.1.3 {
47  execsql {SELECT * FROM table1 ORDER BY f1}
48} {1 2 2 4 4 16}
49do_test delete-3.1.4 {
50  execsql {CREATE INDEX index1 ON table1(f1)}
51  execsql {PRAGMA count_changes=on}
52  execsql {DELETE FROM 'table1' WHERE f1=3}
53} {0}
54do_test delete-3.1.5 {
55  execsql {SELECT * FROM table1 ORDER BY f1}
56} {1 2 2 4 4 16}
57do_test delete-3.1.6 {
58  execsql {DELETE FROM table1 WHERE f1=2}
59} {1}
60do_test delete-3.1.7 {
61  execsql {SELECT * FROM table1 ORDER BY f1}
62} {1 2 4 16}
63
64# Semantic errors in the WHERE clause
65#
66do_test delete-4.1 {
67  execsql {CREATE TABLE table2(f1 int, f2 int)}
68  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
69  lappend v $msg
70} {1 {no such column: f3}}
71
72do_test delete-4.2 {
73  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
74  lappend v $msg
75} {1 {no such function: xyzzy}}
76
77# Lots of deletes
78#
79do_test delete-5.1.1 {
80  execsql {DELETE FROM table1}
81} {2}
82do_test delete-5.1.2 {
83  execsql {SELECT count(*) FROM table1}
84} {0}
85do_test delete-5.2.1 {
86  execsql {BEGIN TRANSACTION}
87  for {set i 1} {$i<=200} {incr i} {
88     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
89  }
90  execsql {COMMIT}
91  execsql {SELECT count(*) FROM table1}
92} {200}
93do_test delete-5.2.2 {
94  execsql {DELETE FROM table1}
95} {200}
96do_test delete-5.2.3 {
97  execsql {BEGIN TRANSACTION}
98  for {set i 1} {$i<=200} {incr i} {
99     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
100  }
101  execsql {COMMIT}
102  execsql {SELECT count(*) FROM table1}
103} {200}
104do_test delete-5.2.4 {
105  execsql {PRAGMA count_changes=off}
106  execsql {DELETE FROM table1}
107} {}
108do_test delete-5.2.5 {
109  execsql {SELECT count(*) FROM table1}
110} {0}
111do_test delete-5.2.6 {
112  execsql {BEGIN TRANSACTION}
113  for {set i 1} {$i<=200} {incr i} {
114     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
115  }
116  execsql {COMMIT}
117  execsql {SELECT count(*) FROM table1}
118} {200}
119do_test delete-5.3 {
120  for {set i 1} {$i<=200} {incr i 4} {
121     execsql "DELETE FROM table1 WHERE f1==$i"
122  }
123  execsql {SELECT count(*) FROM table1}
124} {150}
125do_test delete-5.4 {
126  execsql "DELETE FROM table1 WHERE f1>50"
127  execsql {SELECT count(*) FROM table1}
128} {37}
129do_test delete-5.5 {
130  for {set i 1} {$i<=70} {incr i 3} {
131     execsql "DELETE FROM table1 WHERE f1==$i"
132  }
133  execsql {SELECT f1 FROM table1 ORDER BY f1}
134} {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}
135do_test delete-5.6 {
136  for {set i 1} {$i<40} {incr i} {
137     execsql "DELETE FROM table1 WHERE f1==$i"
138  }
139  execsql {SELECT f1 FROM table1 ORDER BY f1}
140} {42 44 47 48 50}
141do_test delete-5.7 {
142  execsql "DELETE FROM table1 WHERE f1!=48"
143  execsql {SELECT f1 FROM table1 ORDER BY f1}
144} {48}
145
146# Delete large quantities of data.  We want to test the List overflow
147# mechanism in the vdbe.
148#
149do_test delete-6.1 {
150  set fd [open data1.txt w]
151  for {set i 1} {$i<=3000} {incr i} {
152    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
153  }
154  close $fd
155  execsql {DELETE FROM table1}
156  execsql {COPY table1 FROM 'data1.txt'}
157  execsql {DELETE FROM table2}
158  execsql {COPY table2 FROM 'data1.txt'}
159  file delete data1.txt
160  execsql {SELECT count(*) FROM table1}
161} {3000}
162do_test delete-6.2 {
163  execsql {SELECT count(*) FROM table2}
164} {3000}
165do_test delete-6.3 {
166  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
167} {1 2 3 4 5 6 7 8 9}
168do_test delete-6.4 {
169  execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
170} {1 2 3 4 5 6 7 8 9}
171do_test delete-6.5 {
172  execsql {DELETE FROM table1 WHERE f1>7}
173  execsql {SELECT f1 FROM table1 ORDER BY f1}
174} {1 2 3 4 5 6 7}
175do_test delete-6.6 {
176  execsql {DELETE FROM table2 WHERE f1>7}
177  execsql {SELECT f1 FROM table2 ORDER BY f1}
178} {1 2 3 4 5 6 7}
179do_test delete-6.7 {
180  execsql {DELETE FROM table1}
181  execsql {SELECT f1 FROM table1}
182} {}
183do_test delete-6.8 {
184  execsql {INSERT INTO table1 VALUES(2,3)}
185  execsql {SELECT f1 FROM table1}
186} {2}
187do_test delete-6.9 {
188  execsql {DELETE FROM table2}
189  execsql {SELECT f1 FROM table2}
190} {}
191do_test delete-6.10 {
192  execsql {INSERT INTO table2 VALUES(2,3)}
193  execsql {SELECT f1 FROM table2}
194} {2}
195
196do_test delete-7.1 {
197  execsql {
198    CREATE TABLE t3(a);
199    INSERT INTO t3 VALUES(1);
200    INSERT INTO t3 SELECT a+1 FROM t3;
201    INSERT INTO t3 SELECT a+2 FROM t3;
202    SELECT * FROM t3;
203  }
204} {1 2 3 4}
205do_test delete-7.2 {
206  execsql {
207    CREATE TABLE cnt(del);
208    INSERT INTO cnt VALUES(0);
209    CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
210      UPDATE cnt SET del=del+1;
211    END;
212    DELETE FROM t3 WHERE a<2;
213    SELECT * FROM t3;
214  }
215} {2 3 4}
216do_test delete-7.3 {
217  execsql {
218    SELECT * FROM cnt;
219  }
220} {1}
221do_test delete-7.4 {
222  execsql {
223    DELETE FROM t3;
224    SELECT * FROM t3;
225  }
226} {}
227do_test delete-7.5 {
228  execsql {
229    SELECT * FROM cnt;
230  }
231} {4}
232do_test delete-7.6 {
233  execsql {
234    INSERT INTO t3 VALUES(1);
235    INSERT INTO t3 SELECT a+1 FROM t3;
236    INSERT INTO t3 SELECT a+2 FROM t3;
237    CREATE TABLE t4 AS SELECT * FROM t3;
238    PRAGMA count_changes=ON;
239    DELETE FROM t3;
240    DELETE FROM t4;
241  }
242} {4 4}
243
244# Make sure error messages are consistent when attempting to delete
245# from a read-only database.  Ticket #304.
246#
247do_test delete-8.0 {
248  execsql {
249    PRAGMA count_changes=OFF;
250    INSERT INTO t3 VALUES(123);
251    SELECT * FROM t3;
252  }
253} {123}
254db close
255catch {file attributes test.db -permissions 0444}
256catch {file attributes test.db -readonly 1}
257sqlite db test.db
258do_test delete-8.1 {
259  catchsql {
260    DELETE FROM t3;
261  }
262} {1 {attempt to write a readonly database}}
263do_test delete-8.2 {
264  execsql {SELECT * FROM t3}
265} {123}
266do_test delete-8.3 {
267  catchsql {
268    DELETE FROM t3 WHERE 1;
269  }
270} {1 {attempt to write a readonly database}}
271do_test delete-8.4 {
272  execsql {SELECT * FROM t3}
273} {123}
274do_test delete-8.5 {
275  catchsql {
276    DELETE FROM t3 WHERE a<100;
277  }
278} {0 {}}
279do_test delete-8.6 {
280  execsql {SELECT * FROM t3}
281} {123}
282
283
284finish_test
285