wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 September 21 |
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 | # |
||
12 | # This file implements tests to verify that the "testable statements" in |
||
13 | # the lang_delete.html document are correct. |
||
14 | # |
||
15 | set testdir [file dirname $argv0] |
||
16 | source $testdir/tester.tcl |
||
17 | |||
18 | proc do_delete_tests {args} { |
||
19 | uplevel do_select_tests $args |
||
20 | } |
||
21 | |||
22 | do_execsql_test e_delete-0.0 { |
||
23 | CREATE TABLE t1(a, b); |
||
24 | CREATE INDEX i1 ON t1(a); |
||
25 | } {} |
||
26 | |||
27 | # EVIDENCE-OF: R-24177-52883 -- syntax diagram delete-stmt |
||
28 | # |
||
29 | # EVIDENCE-OF: R-12802-60464 -- syntax diagram qualified-table-name |
||
30 | # |
||
31 | do_delete_tests e_delete-0.1 { |
||
32 | 1 "DELETE FROM t1" {} |
||
33 | 2 "DELETE FROM t1 INDEXED BY i1" {} |
||
34 | 3 "DELETE FROM t1 NOT INDEXED" {} |
||
35 | 4 "DELETE FROM main.t1" {} |
||
36 | 5 "DELETE FROM main.t1 INDEXED BY i1" {} |
||
37 | 6 "DELETE FROM main.t1 NOT INDEXED" {} |
||
38 | 7 "DELETE FROM t1 WHERE a>2" {} |
||
39 | 8 "DELETE FROM t1 INDEXED BY i1 WHERE a>2" {} |
||
40 | 9 "DELETE FROM t1 NOT INDEXED WHERE a>2" {} |
||
41 | 10 "DELETE FROM main.t1 WHERE a>2" {} |
||
42 | 11 "DELETE FROM main.t1 INDEXED BY i1 WHERE a>2" {} |
||
43 | 12 "DELETE FROM main.t1 NOT INDEXED WHERE a>2" {} |
||
44 | } |
||
45 | |||
46 | # EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all |
||
47 | # records in the table are deleted. |
||
48 | # |
||
49 | drop_all_tables |
||
50 | do_test e_delete-1.0 { |
||
51 | db transaction { |
||
52 | foreach t {t1 t2 t3 t4 t5 t6} { |
||
53 | execsql [string map [list %T% $t] { |
||
54 | CREATE TABLE %T%(x, y); |
||
55 | INSERT INTO %T% VALUES(1, 'one'); |
||
56 | INSERT INTO %T% VALUES(2, 'two'); |
||
57 | INSERT INTO %T% VALUES(3, 'three'); |
||
58 | INSERT INTO %T% VALUES(4, 'four'); |
||
59 | INSERT INTO %T% VALUES(5, 'five'); |
||
60 | }] |
||
61 | } |
||
62 | } |
||
63 | } {} |
||
64 | do_delete_tests e_delete-1.1 { |
||
65 | 1 "DELETE FROM t1 ; SELECT * FROM t1" {} |
||
66 | 2 "DELETE FROM main.t2 ; SELECT * FROM t2" {} |
||
67 | } |
||
68 | |||
69 | # EVIDENCE-OF: R-30203-16177 If a WHERE clause is supplied, then only |
||
70 | # those rows for which the result of evaluating the WHERE clause as a |
||
71 | # boolean expression is true are deleted. |
||
72 | # |
||
73 | do_delete_tests e_delete-1.2 { |
||
74 | 1 "DELETE FROM t3 WHERE 1 ; SELECT x FROM t3" {} |
||
75 | 2 "DELETE FROM main.t4 WHERE 0 ; SELECT x FROM t4" {1 2 3 4 5} |
||
76 | 3 "DELETE FROM t4 WHERE 0.0 ; SELECT x FROM t4" {1 2 3 4 5} |
||
77 | 4 "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4" {1 2 3 4 5} |
||
78 | 5 "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4" {2} |
||
79 | 6 "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4" {} |
||
80 | 7 "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5" {1 2 3 4} |
||
81 | 8 "DELETE FROM t5 WHERE (SELECT max(x) FROM t4) ;SELECT x FROM t5" {1 2 3 4} |
||
82 | 9 "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) ;SELECT x FROM t5" {} |
||
83 | 10 "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6" {one four five} |
||
84 | } |
||
85 | |||
86 | |||
87 | #------------------------------------------------------------------------- |
||
88 | # Tests for restrictions on DELETE statements that appear within trigger |
||
89 | # programs. |
||
90 | # |
||
91 | forcedelete test.db2 |
||
92 | forcedelete test.db3 |
||
93 | do_execsql_test e_delete-2.0 { |
||
94 | ATTACH 'test.db2' AS aux; |
||
95 | ATTACH 'test.db3' AS aux2; |
||
96 | |||
97 | CREATE TABLE temp.t7(a, b); INSERT INTO temp.t7 VALUES(1, 2); |
||
98 | CREATE TABLE main.t7(a, b); INSERT INTO main.t7 VALUES(3, 4); |
||
99 | CREATE TABLE aux.t7(a, b); INSERT INTO aux.t7 VALUES(5, 6); |
||
100 | CREATE TABLE aux2.t7(a, b); INSERT INTO aux2.t7 VALUES(7, 8); |
||
101 | |||
102 | CREATE TABLE main.t8(a, b); INSERT INTO main.t8 VALUES(1, 2); |
||
103 | CREATE TABLE aux.t8(a, b); INSERT INTO aux.t8 VALUES(3, 4); |
||
104 | CREATE TABLE aux2.t8(a, b); INSERT INTO aux2.t8 VALUES(5, 6); |
||
105 | |||
106 | CREATE TABLE aux.t9(a, b); INSERT INTO aux.t9 VALUES(1, 2); |
||
107 | CREATE TABLE aux2.t9(a, b); INSERT INTO aux2.t9 VALUES(3, 4); |
||
108 | |||
109 | CREATE TABLE aux2.t10(a, b); INSERT INTO aux2.t10 VALUES(1, 2); |
||
110 | } {} |
||
111 | |||
112 | |||
113 | # EVIDENCE-OF: R-09681-58560 The table-name specified as part of a |
||
114 | # DELETE statement within a trigger body must be unqualified. |
||
115 | # |
||
116 | # EVIDENCE-OF: R-36771-43788 In other words, the database-name. prefix |
||
117 | # on the table name is not allowed within triggers. |
||
118 | # |
||
119 | do_delete_tests e_delete-2.1 -error { |
||
120 | qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers |
||
121 | } { |
||
122 | 1 { |
||
123 | CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
||
124 | DELETE FROM main.t2; |
||
125 | END; |
||
126 | } {} |
||
127 | |||
128 | 2 { |
||
129 | CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN |
||
130 | DELETE FROM temp.t7 WHERE a=new.a; |
||
131 | END; |
||
132 | } {} |
||
133 | |||
134 | 3 { |
||
135 | CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN |
||
136 | DELETE FROM aux2.t8 WHERE b!=a; |
||
137 | END; |
||
138 | } {} |
||
139 | } |
||
140 | |||
141 | # EVIDENCE-OF: R-28818-63526 If the table to which the trigger is |
||
142 | # attached is not in the temp database, then DELETE statements within |
||
143 | # the trigger body must operate on tables within the same database as |
||
144 | # it. |
||
145 | # |
||
146 | # This is tested in two parts. First, check that if a table of the |
||
147 | # specified name does not exist, an error is raised. Secondly, test |
||
148 | # that if tables with the specified name exist in multiple databases, |
||
149 | # the local database table is used. |
||
150 | # |
||
151 | do_delete_tests e_delete-2.2.1 -error { no such table: %s } { |
||
152 | 1 { |
||
153 | CREATE TRIGGER main.tr1 AFTER INSERT ON main.t7 BEGIN |
||
154 | DELETE FROM t9; |
||
155 | END; |
||
156 | INSERT INTO main.t7 VALUES(1, 2); |
||
157 | } {main.t9} |
||
158 | |||
159 | 2 { |
||
160 | CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN |
||
161 | DELETE FROM t10; |
||
162 | END; |
||
163 | UPDATE t9 SET a=1; |
||
164 | } {aux.t10} |
||
165 | } |
||
166 | do_execsql_test e_delete-2.2.X { |
||
167 | DROP TRIGGER main.tr1; |
||
168 | DROP TRIGGER aux.tr2; |
||
169 | } {} |
||
170 | |||
171 | do_delete_tests e_delete-2.2.2 { |
||
172 | 1 { |
||
173 | CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN |
||
174 | DELETE FROM t9; |
||
175 | END; |
||
176 | INSERT INTO aux.t8 VALUES(1, 2); |
||
177 | |||
178 | SELECT count(*) FROM aux.t9 |
||
179 | UNION ALL |
||
180 | SELECT count(*) FROM aux2.t9; |
||
181 | } {0 1} |
||
182 | |||
183 | 2 { |
||
184 | CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN |
||
185 | DELETE FROM t7; |
||
186 | END; |
||
187 | INSERT INTO main.t8 VALUES(1, 2); |
||
188 | |||
189 | SELECT count(*) FROM temp.t7 |
||
190 | UNION ALL |
||
191 | SELECT count(*) FROM main.t7 |
||
192 | UNION ALL |
||
193 | SELECT count(*) FROM aux.t7 |
||
194 | UNION ALL |
||
195 | SELECT count(*) FROM aux2.t7; |
||
196 | } {1 0 1 1} |
||
197 | } |
||
198 | |||
199 | # EVIDENCE-OF: R-31567-38587 If the table to which the trigger is |
||
200 | # attached is in the TEMP database, then the unqualified name of the |
||
201 | # table being deleted is resolved in the same way as it is for a |
||
202 | # top-level statement (by searching first the TEMP database, then the |
||
203 | # main database, then any other databases in the order they were |
||
204 | # attached). |
||
205 | # |
||
206 | do_execsql_test e_delete-2.3.0 { |
||
207 | DROP TRIGGER aux.tr1; |
||
208 | DROP TRIGGER main.tr1; |
||
209 | DELETE FROM main.t8 WHERE oid>1; |
||
210 | DELETE FROM aux.t8 WHERE oid>1; |
||
211 | INSERT INTO aux.t9 VALUES(1, 2); |
||
212 | INSERT INTO main.t7 VALUES(3, 4); |
||
213 | } {} |
||
214 | do_execsql_test e_delete-2.3.1 { |
||
215 | SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL |
||
216 | SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7; |
||
217 | |||
218 | SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8 |
||
219 | UNION ALL SELECT count(*) FROM aux2.t8; |
||
220 | |||
221 | SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9; |
||
222 | |||
223 | SELECT count(*) FROM aux2.t10; |
||
224 | } {1 1 1 1 1 1 1 1 1 1} |
||
225 | do_execsql_test e_delete-2.3.2 { |
||
226 | CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN |
||
227 | DELETE FROM t7; |
||
228 | DELETE FROM t8; |
||
229 | DELETE FROM t9; |
||
230 | DELETE FROM t10; |
||
231 | END; |
||
232 | INSERT INTO temp.t7 VALUES('hello', 'world'); |
||
233 | } {} |
||
234 | do_execsql_test e_delete-2.3.3 { |
||
235 | SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL |
||
236 | SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7; |
||
237 | |||
238 | SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8 |
||
239 | UNION ALL SELECT count(*) FROM aux2.t8; |
||
240 | |||
241 | SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9; |
||
242 | |||
243 | SELECT count(*) FROM aux2.t10; |
||
244 | } {0 1 1 1 0 1 1 0 1 0} |
||
245 | |||
246 | # EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED clauses are |
||
247 | # not allowed on DELETE statements within triggers. |
||
248 | # |
||
249 | do_execsql_test e_delete-2.4.0 { |
||
250 | CREATE INDEX i8 ON t8(a, b); |
||
251 | } {} |
||
252 | do_delete_tests e_delete-2.4 -error { |
||
253 | the %s %s clause is not allowed on UPDATE or DELETE statements within triggers |
||
254 | } { |
||
255 | 1 { |
||
256 | CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN |
||
257 | DELETE FROM t8 INDEXED BY i8 WHERE a=5; |
||
258 | END; |
||
259 | } {INDEXED BY} |
||
260 | 2 { |
||
261 | CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN |
||
262 | DELETE FROM t8 NOT INDEXED WHERE a=5; |
||
263 | END; |
||
264 | } {NOT INDEXED} |
||
265 | } |
||
266 | |||
267 | ifcapable update_delete_limit { |
||
268 | |||
269 | # EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described |
||
270 | # below) are unsupported for DELETE statements within triggers. |
||
271 | # |
||
272 | do_delete_tests e_delete-2.5 -error { near "%s": syntax error } { |
||
273 | 1 { |
||
274 | CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN |
||
275 | DELETE FROM t8 LIMIT 10; |
||
276 | END; |
||
277 | } {LIMIT} |
||
278 | 2 { |
||
279 | CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN |
||
280 | DELETE FROM t8 ORDER BY a LIMIT 5; |
||
281 | END; |
||
282 | } {ORDER} |
||
283 | } |
||
284 | |||
285 | # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the |
||
286 | # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax |
||
287 | # of the DELETE statement is extended by the addition of optional ORDER |
||
288 | # BY and LIMIT clauses: |
||
289 | # |
||
290 | # EVIDENCE-OF: R-45897-01670 -- syntax diagram delete-stmt-limited |
||
291 | # |
||
292 | do_delete_tests e_delete-3.1 { |
||
293 | 1 "DELETE FROM t1 LIMIT 5" {} |
||
294 | 2 "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2" {} |
||
295 | 3 "DELETE FROM t1 LIMIT 2+2, 16/4" {} |
||
296 | 4 "DELETE FROM t1 ORDER BY x LIMIT 5" {} |
||
297 | 5 "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2" {} |
||
298 | 6 "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4" {} |
||
299 | 7 "DELETE FROM t1 WHERE x>2 LIMIT 5" {} |
||
300 | 8 "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2" {} |
||
301 | 9 "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4" {} |
||
302 | 10 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5" {} |
||
303 | 11 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2" {} |
||
304 | 12 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4" {} |
||
305 | } |
||
306 | |||
307 | drop_all_tables |
||
308 | proc rebuild_t1 {} { |
||
309 | catchsql { DROP TABLE t1 } |
||
310 | execsql { |
||
311 | CREATE TABLE t1(a, b); |
||
312 | INSERT INTO t1 VALUES(1, 'one'); |
||
313 | INSERT INTO t1 VALUES(2, 'two'); |
||
314 | INSERT INTO t1 VALUES(3, 'three'); |
||
315 | INSERT INTO t1 VALUES(4, 'four'); |
||
316 | INSERT INTO t1 VALUES(5, 'five'); |
||
317 | } |
||
318 | } |
||
319 | |||
320 | # EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause, |
||
321 | # the maximum number of rows that will be deleted is found by evaluating |
||
322 | # the accompanying expression and casting it to an integer value. |
||
323 | # |
||
324 | rebuild_t1 |
||
325 | do_delete_tests e_delete-3.2 -repair rebuild_t1 -query { |
||
326 | SELECT a FROM t1 |
||
327 | } { |
||
328 | 1 "DELETE FROM t1 LIMIT 3" {4 5} |
||
329 | 2 "DELETE FROM t1 LIMIT 1+1" {3 4 5} |
||
330 | 3 "DELETE FROM t1 LIMIT '4'" {5} |
||
331 | 4 "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5} |
||
332 | } |
||
333 | |||
334 | # EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT |
||
335 | # clause cannot be losslessly converted to an integer value, it is an |
||
336 | # error. |
||
337 | # |
||
338 | do_delete_tests e_delete-3.3 -error { datatype mismatch } { |
||
339 | 1 "DELETE FROM t1 LIMIT 'abc'" {} |
||
340 | 2 "DELETE FROM t1 LIMIT NULL" {} |
||
341 | 3 "DELETE FROM t1 LIMIT X'ABCD'" {} |
||
342 | 4 "DELETE FROM t1 LIMIT 1.2" {} |
||
343 | } |
||
344 | |||
345 | # EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as |
||
346 | # "no limit". |
||
347 | # |
||
348 | do_delete_tests e_delete-3.4 -repair rebuild_t1 -query { |
||
349 | SELECT a FROM t1 |
||
350 | } { |
||
351 | 1 "DELETE FROM t1 LIMIT -1" {} |
||
352 | 2 "DELETE FROM t1 LIMIT 2-4" {} |
||
353 | 3 "DELETE FROM t1 LIMIT -4.0" {} |
||
354 | 4 "DELETE FROM t1 LIMIT 5*-1" {} |
||
355 | } |
||
356 | |||
357 | # EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET |
||
358 | # clause, then it is similarly evaluated and cast to an integer value. |
||
359 | # Again, it is an error if the value cannot be losslessly converted to |
||
360 | # an integer. |
||
361 | # |
||
362 | do_delete_tests e_delete-3.5 -error { datatype mismatch } { |
||
363 | 1 "DELETE FROM t1 LIMIT 1 OFFSET 'abc'" {} |
||
364 | 2 "DELETE FROM t1 LIMIT 1 OFFSET NULL" {} |
||
365 | 3 "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'" {} |
||
366 | 4 "DELETE FROM t1 LIMIT 1 OFFSET 1.2" {} |
||
367 | 5 "DELETE FROM t1 LIMIT 'abc', 1" {} |
||
368 | 6 "DELETE FROM t1 LIMIT NULL, 1" {} |
||
369 | 7 "DELETE FROM t1 LIMIT X'ABCD', 1" {} |
||
370 | 8 "DELETE FROM t1 LIMIT 1.2, 1" {} |
||
371 | } |
||
372 | |||
373 | |||
374 | # EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the |
||
375 | # calculated integer value is negative, the effective OFFSET value is |
||
376 | # zero. |
||
377 | # |
||
378 | do_delete_tests e_delete-3.6 -repair rebuild_t1 -query { |
||
379 | SELECT a FROM t1 |
||
380 | } { |
||
381 | 1a "DELETE FROM t1 LIMIT 3 OFFSET 0" {4 5} |
||
382 | 1b "DELETE FROM t1 LIMIT 3" {4 5} |
||
383 | 1c "DELETE FROM t1 LIMIT 3 OFFSET -1" {4 5} |
||
384 | 2a "DELETE FROM t1 LIMIT 1+1 OFFSET 0" {3 4 5} |
||
385 | 2b "DELETE FROM t1 LIMIT 1+1" {3 4 5} |
||
386 | 2c "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5" {3 4 5} |
||
387 | 3a "DELETE FROM t1 LIMIT '4' OFFSET 0" {5} |
||
388 | 3b "DELETE FROM t1 LIMIT '4'" {5} |
||
389 | 3c "DELETE FROM t1 LIMIT '4' OFFSET -1.0" {5} |
||
390 | 4a "DELETE FROM t1 LIMIT '1.0' OFFSET 0" {2 3 4 5} |
||
391 | 4b "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5} |
||
392 | 4c "DELETE FROM t1 LIMIT '1.0' OFFSET -11" {2 3 4 5} |
||
393 | } |
||
394 | |||
395 | # EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY |
||
396 | # clause, then all rows that would be deleted in the absence of the |
||
397 | # LIMIT clause are sorted according to the ORDER BY. The first M rows, |
||
398 | # where M is the value found by evaluating the OFFSET clause expression, |
||
399 | # are skipped, and the following N, where N is the value of the LIMIT |
||
400 | # expression, are deleted. |
||
401 | # |
||
402 | do_delete_tests e_delete-3.7 -repair rebuild_t1 -query { |
||
403 | SELECT a FROM t1 |
||
404 | } { |
||
405 | 1 "DELETE FROM t1 ORDER BY b LIMIT 2" {1 2 3} |
||
406 | 2 "DELETE FROM t1 ORDER BY length(b), a LIMIT 3" {3 5} |
||
407 | 3 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0" {1 2 3 4} |
||
408 | 4 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1" {1 2 3 5} |
||
409 | 5 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2" {1 2 4 5} |
||
410 | } |
||
411 | |||
412 | # EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining |
||
413 | # after taking the OFFSET clause into account, or if the LIMIT clause |
||
414 | # evaluated to a negative value, then all remaining rows are deleted. |
||
415 | # |
||
416 | do_delete_tests e_delete-3.8 -repair rebuild_t1 -query { |
||
417 | SELECT a FROM t1 |
||
418 | } { |
||
419 | 1 "DELETE FROM t1 ORDER BY a ASC LIMIT 10" {} |
||
420 | 2 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {} |
||
421 | 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2" {1 2} |
||
422 | } |
||
423 | |||
424 | # EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY |
||
425 | # clause, then all rows that would be deleted in the absence of the |
||
426 | # LIMIT clause are assembled in an arbitrary order before applying the |
||
427 | # LIMIT and OFFSET clauses to determine the subset that are actually |
||
428 | # deleted. |
||
429 | # |
||
430 | # In practice, the "arbitrary order" is rowid order. |
||
431 | # |
||
432 | do_delete_tests e_delete-3.9 -repair rebuild_t1 -query { |
||
433 | SELECT a FROM t1 |
||
434 | } { |
||
435 | 1 "DELETE FROM t1 LIMIT 2" {3 4 5} |
||
436 | 2 "DELETE FROM t1 LIMIT 3" {4 5} |
||
437 | 3 "DELETE FROM t1 LIMIT 1 OFFSET 0" {2 3 4 5} |
||
438 | 4 "DELETE FROM t1 LIMIT 1 OFFSET 1" {1 3 4 5} |
||
439 | 5 "DELETE FROM t1 LIMIT 1 OFFSET 2" {1 2 4 5} |
||
440 | } |
||
441 | |||
442 | |||
443 | # EVIDENCE-OF: R-07548-13422 The ORDER BY clause on a DELETE statement |
||
444 | # is used only to determine which rows fall within the LIMIT. The order |
||
445 | # in which rows are deleted is arbitrary and is not influenced by the |
||
446 | # ORDER BY clause. |
||
447 | # |
||
448 | # In practice, rows are always deleted in rowid order. |
||
449 | # |
||
450 | do_delete_tests e_delete-3.10 -repair { |
||
451 | rebuild_t1 |
||
452 | catchsql { DROP TABLE t1log } |
||
453 | execsql { |
||
454 | CREATE TABLE t1log(x); |
||
455 | CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN |
||
456 | INSERT INTO t1log VALUES(old.a); |
||
457 | END; |
||
458 | } |
||
459 | } -query { |
||
460 | SELECT x FROM t1log |
||
461 | } { |
||
462 | 1 "DELETE FROM t1 ORDER BY a DESC LIMIT 2" {4 5} |
||
463 | 2 "DELETE FROM t1 ORDER BY a DESC LIMIT -1" {1 2 3 4 5} |
||
464 | 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 2" {1 2} |
||
465 | 4 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {1 2 3 4 5} |
||
466 | } |
||
467 | |||
468 | } |
||
469 | |||
470 | finish_test |