aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/test/update.test
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/test/update.test')
-rw-r--r--libraries/sqlite/unix/sqlite-3.5.1/test/update.test596
1 files changed, 596 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/update.test b/libraries/sqlite/unix/sqlite-3.5.1/test/update.test
new file mode 100644
index 0000000..d56c342
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/update.test
@@ -0,0 +1,596 @@
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 UPDATE statement.
13#
14# $Id: update.test,v 1.17 2005/01/21 03:12:16 danielk1977 Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Try to update an non-existent table
20#
21do_test update-1.1 {
22 set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg]
23 lappend v $msg
24} {1 {no such table: test1}}
25
26# Try to update a read-only table
27#
28do_test update-2.1 {
29 set v [catch \
30 {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg]
31 lappend v $msg
32} {1 {table sqlite_master may not be modified}}
33
34# Create a table to work with
35#
36do_test update-3.1 {
37 execsql {CREATE TABLE test1(f1 int,f2 int)}
38 for {set i 1} {$i<=10} {incr i} {
39 set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
40 execsql $sql
41 }
42 execsql {SELECT * FROM test1 ORDER BY f1}
43} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
44
45# Unknown column name in an expression
46#
47do_test update-3.2 {
48 set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
49 lappend v $msg
50} {1 {no such column: f3}}
51do_test update-3.3 {
52 set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
53 lappend v $msg
54} {1 {no such column: test2.f1}}
55do_test update-3.4 {
56 set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
57 lappend v $msg
58} {1 {no such column: f3}}
59
60# Actually do some updates
61#
62do_test update-3.5 {
63 execsql {UPDATE test1 SET f2=f2*3}
64} {}
65do_test update-3.6 {
66 execsql {SELECT * FROM test1 ORDER BY f1}
67} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
68do_test update-3.7 {
69 execsql {PRAGMA count_changes=on}
70 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
71} {5}
72do_test update-3.8 {
73 execsql {SELECT * FROM test1 ORDER BY f1}
74} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072}
75do_test update-3.9 {
76 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
77} {5}
78do_test update-3.10 {
79 execsql {SELECT * FROM test1 ORDER BY f1}
80} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
81
82# Swap the values of f1 and f2 for all elements
83#
84do_test update-3.11 {
85 execsql {UPDATE test1 SET F2=f1, F1=f2}
86} {10}
87do_test update-3.12 {
88 execsql {SELECT * FROM test1 ORDER BY F1}
89} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}
90do_test update-3.13 {
91 execsql {PRAGMA count_changes=off}
92 execsql {UPDATE test1 SET F2=f1, F1=f2}
93} {}
94do_test update-3.14 {
95 execsql {SELECT * FROM test1 ORDER BY F1}
96} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
97
98# Create duplicate entries and make sure updating still
99# works.
100#
101do_test update-4.0 {
102 execsql {
103 DELETE FROM test1 WHERE f1<=5;
104 INSERT INTO test1(f1,f2) VALUES(8,88);
105 INSERT INTO test1(f1,f2) VALUES(8,888);
106 INSERT INTO test1(f1,f2) VALUES(77,128);
107 INSERT INTO test1(f1,f2) VALUES(777,128);
108 }
109 execsql {SELECT * FROM test1 ORDER BY f1,f2}
110} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
111do_test update-4.1 {
112 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
113 execsql {SELECT * FROM test1 ORDER BY f1,f2}
114} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
115do_test update-4.2 {
116 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
117 execsql {SELECT * FROM test1 ORDER BY f1,f2}
118} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
119do_test update-4.3 {
120 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
121 execsql {SELECT * FROM test1 ORDER BY f1,f2}
122} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
123do_test update-4.4 {
124 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
125 execsql {SELECT * FROM test1 ORDER BY f1,f2}
126} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
127do_test update-4.5 {
128 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
129 execsql {SELECT * FROM test1 ORDER BY f1,f2}
130} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
131do_test update-4.6 {
132 execsql {
133 PRAGMA count_changes=on;
134 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
135 }
136} {2}
137do_test update-4.7 {
138 execsql {
139 PRAGMA count_changes=off;
140 SELECT * FROM test1 ORDER BY f1,f2
141 }
142} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
143
144# Repeat the previous sequence of tests with an index.
145#
146do_test update-5.0 {
147 execsql {CREATE INDEX idx1 ON test1(f1)}
148 execsql {SELECT * FROM test1 ORDER BY f1,f2}
149} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
150do_test update-5.1 {
151 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
152 execsql {SELECT * FROM test1 ORDER BY f1,f2}
153} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
154do_test update-5.2 {
155 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
156 execsql {SELECT * FROM test1 ORDER BY f1,f2}
157} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
158do_test update-5.3 {
159 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
160 execsql {SELECT * FROM test1 ORDER BY f1,f2}
161} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
162do_test update-5.4 {
163 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
164 execsql {SELECT * FROM test1 ORDER BY f1,f2}
165} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
166do_test update-5.4.1 {
167 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
168} {78 128}
169do_test update-5.4.2 {
170 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
171} {778 128}
172do_test update-5.4.3 {
173 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
174} {8 88 8 128 8 256 8 888}
175do_test update-5.5 {
176 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
177} {}
178do_test update-5.5.1 {
179 execsql {SELECT * FROM test1 ORDER BY f1,f2}
180} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
181do_test update-5.5.2 {
182 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
183} {78 128}
184do_test update-5.5.3 {
185 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
186} {}
187do_test update-5.5.4 {
188 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
189} {777 128}
190do_test update-5.5.5 {
191 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
192} {8 88 8 128 8 256 8 888}
193do_test update-5.6 {
194 execsql {
195 PRAGMA count_changes=on;
196 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
197 }
198} {2}
199do_test update-5.6.1 {
200 execsql {
201 PRAGMA count_changes=off;
202 SELECT * FROM test1 ORDER BY f1,f2
203 }
204} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
205do_test update-5.6.2 {
206 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
207} {77 128}
208do_test update-5.6.3 {
209 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
210} {}
211do_test update-5.6.4 {
212 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
213} {777 128}
214do_test update-5.6.5 {
215 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
216} {8 88 8 256 8 888}
217
218# Repeat the previous sequence of tests with a different index.
219#
220execsql {PRAGMA synchronous=FULL}
221do_test update-6.0 {
222 execsql {DROP INDEX idx1}
223 execsql {CREATE INDEX idx1 ON test1(f2)}
224 execsql {SELECT * FROM test1 ORDER BY f1,f2}
225} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
226do_test update-6.1 {
227 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
228 execsql {SELECT * FROM test1 ORDER BY f1,f2}
229} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
230do_test update-6.1.1 {
231 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
232} {8 89 8 257 8 889}
233do_test update-6.1.2 {
234 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
235} {8 89}
236do_test update-6.1.3 {
237 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
238} {}
239do_test update-6.2 {
240 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
241 execsql {SELECT * FROM test1 ORDER BY f1,f2}
242} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
243do_test update-6.3 {
244 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
245 execsql {SELECT * FROM test1 ORDER BY f1,f2}
246} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
247do_test update-6.3.1 {
248 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
249} {8 88 8 256 8 888}
250do_test update-6.3.2 {
251 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
252} {}
253do_test update-6.3.3 {
254 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
255} {8 88}
256do_test update-6.4 {
257 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
258 execsql {SELECT * FROM test1 ORDER BY f1,f2}
259} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
260do_test update-6.4.1 {
261 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
262} {78 128}
263do_test update-6.4.2 {
264 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
265} {778 128}
266do_test update-6.4.3 {
267 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
268} {8 88 8 128 8 256 8 888}
269do_test update-6.5 {
270 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
271 execsql {SELECT * FROM test1 ORDER BY f1,f2}
272} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
273do_test update-6.5.1 {
274 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
275} {78 128}
276do_test update-6.5.2 {
277 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
278} {}
279do_test update-6.5.3 {
280 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
281} {777 128}
282do_test update-6.5.4 {
283 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
284} {8 88 8 128 8 256 8 888}
285do_test update-6.6 {
286 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
287 execsql {SELECT * FROM test1 ORDER BY f1,f2}
288} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
289do_test update-6.6.1 {
290 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
291} {77 128}
292do_test update-6.6.2 {
293 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
294} {}
295do_test update-6.6.3 {
296 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
297} {777 128}
298do_test update-6.6.4 {
299 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
300} {8 88 8 256 8 888}
301
302# Repeat the previous sequence of tests with multiple
303# indices
304#
305do_test update-7.0 {
306 execsql {CREATE INDEX idx2 ON test1(f2)}
307 execsql {CREATE INDEX idx3 ON test1(f1,f2)}
308 execsql {SELECT * FROM test1 ORDER BY f1,f2}
309} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
310do_test update-7.1 {
311 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
312 execsql {SELECT * FROM test1 ORDER BY f1,f2}
313} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
314do_test update-7.1.1 {
315 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
316} {8 89 8 257 8 889}
317do_test update-7.1.2 {
318 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
319} {8 89}
320do_test update-7.1.3 {
321 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
322} {}
323do_test update-7.2 {
324 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
325 execsql {SELECT * FROM test1 ORDER BY f1,f2}
326} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
327do_test update-7.3 {
328 # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
329 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
330 execsql {SELECT * FROM test1 ORDER BY f1,f2}
331} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
332do_test update-7.3.1 {
333 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
334} {8 88 8 256 8 888}
335do_test update-7.3.2 {
336 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
337} {}
338do_test update-7.3.3 {
339 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
340} {8 88}
341do_test update-7.4 {
342 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
343 execsql {SELECT * FROM test1 ORDER BY f1,f2}
344} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
345do_test update-7.4.1 {
346 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
347} {78 128}
348do_test update-7.4.2 {
349 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
350} {778 128}
351do_test update-7.4.3 {
352 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
353} {8 88 8 128 8 256 8 888}
354do_test update-7.5 {
355 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
356 execsql {SELECT * FROM test1 ORDER BY f1,f2}
357} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
358do_test update-7.5.1 {
359 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
360} {78 128}
361do_test update-7.5.2 {
362 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
363} {}
364do_test update-7.5.3 {
365 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
366} {777 128}
367do_test update-7.5.4 {
368 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
369} {8 88 8 128 8 256 8 888}
370do_test update-7.6 {
371 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
372 execsql {SELECT * FROM test1 ORDER BY f1,f2}
373} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
374do_test update-7.6.1 {
375 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
376} {77 128}
377do_test update-7.6.2 {
378 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
379} {}
380do_test update-7.6.3 {
381 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
382} {777 128}
383do_test update-7.6.4 {
384 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
385} {8 88 8 256 8 888}
386
387# Error messages
388#
389do_test update-9.1 {
390 set v [catch {execsql {
391 UPDATE test1 SET x=11 WHERE f1=1025
392 }} msg]
393 lappend v $msg
394} {1 {no such column: x}}
395do_test update-9.2 {
396 set v [catch {execsql {
397 UPDATE test1 SET f1=x(11) WHERE f1=1025
398 }} msg]
399 lappend v $msg
400} {1 {no such function: x}}
401do_test update-9.3 {
402 set v [catch {execsql {
403 UPDATE test1 SET f1=11 WHERE x=1025
404 }} msg]
405 lappend v $msg
406} {1 {no such column: x}}
407do_test update-9.4 {
408 set v [catch {execsql {
409 UPDATE test1 SET f1=11 WHERE x(f1)=1025
410 }} msg]
411 lappend v $msg
412} {1 {no such function: x}}
413
414# Try doing updates on a unique column where the value does not
415# really change.
416#
417do_test update-10.1 {
418 execsql {
419 DROP TABLE test1;
420 CREATE TABLE t1(
421 a integer primary key,
422 b UNIQUE,
423 c, d,
424 e, f,
425 UNIQUE(c,d)
426 );
427 INSERT INTO t1 VALUES(1,2,3,4,5,6);
428 INSERT INTO t1 VALUES(2,3,4,4,6,7);
429 SELECT * FROM t1
430 }
431} {1 2 3 4 5 6 2 3 4 4 6 7}
432do_test update-10.2 {
433 catchsql {
434 UPDATE t1 SET a=1, e=9 WHERE f=6;
435 SELECT * FROM t1;
436 }
437} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
438do_test update-10.3 {
439 catchsql {
440 UPDATE t1 SET a=1, e=10 WHERE f=7;
441 SELECT * FROM t1;
442 }
443} {1 {PRIMARY KEY must be unique}}
444do_test update-10.4 {
445 catchsql {
446 SELECT * FROM t1;
447 }
448} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
449do_test update-10.5 {
450 catchsql {
451 UPDATE t1 SET b=2, e=11 WHERE f=6;
452 SELECT * FROM t1;
453 }
454} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
455do_test update-10.6 {
456 catchsql {
457 UPDATE t1 SET b=2, e=12 WHERE f=7;
458 SELECT * FROM t1;
459 }
460} {1 {column b is not unique}}
461do_test update-10.7 {
462 catchsql {
463 SELECT * FROM t1;
464 }
465} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
466do_test update-10.8 {
467 catchsql {
468 UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
469 SELECT * FROM t1;
470 }
471} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
472do_test update-10.9 {
473 catchsql {
474 UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
475 SELECT * FROM t1;
476 }
477} {1 {columns c, d are not unique}}
478do_test update-10.10 {
479 catchsql {
480 SELECT * FROM t1;
481 }
482} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
483
484# Make sure we can handle a subquery in the where clause.
485#
486ifcapable subquery {
487 do_test update-11.1 {
488 execsql {
489 UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
490 SELECT b,e FROM t1;
491 }
492 } {2 14 3 7}
493 do_test update-11.2 {
494 execsql {
495 UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
496 SELECT a,e FROM t1;
497 }
498 } {1 15 2 8}
499}
500
501integrity_check update-12.1
502
503# Ticket 602. Updates should occur in the same order as the records
504# were discovered in the WHERE clause.
505#
506do_test update-13.1 {
507 execsql {
508 BEGIN;
509 CREATE TABLE t2(a);
510 INSERT INTO t2 VALUES(1);
511 INSERT INTO t2 VALUES(2);
512 INSERT INTO t2 SELECT a+2 FROM t2;
513 INSERT INTO t2 SELECT a+4 FROM t2;
514 INSERT INTO t2 SELECT a+8 FROM t2;
515 INSERT INTO t2 SELECT a+16 FROM t2;
516 INSERT INTO t2 SELECT a+32 FROM t2;
517 INSERT INTO t2 SELECT a+64 FROM t2;
518 INSERT INTO t2 SELECT a+128 FROM t2;
519 INSERT INTO t2 SELECT a+256 FROM t2;
520 INSERT INTO t2 SELECT a+512 FROM t2;
521 INSERT INTO t2 SELECT a+1024 FROM t2;
522 COMMIT;
523 SELECT count(*) FROM t2;
524 }
525} {2048}
526do_test update-13.2 {
527 execsql {
528 SELECT count(*) FROM t2 WHERE a=rowid;
529 }
530} {2048}
531do_test update-13.3 {
532 execsql {
533 UPDATE t2 SET rowid=rowid-1;
534 SELECT count(*) FROM t2 WHERE a=rowid+1;
535 }
536} {2048}
537do_test update-13.3 {
538 execsql {
539 UPDATE t2 SET rowid=rowid+10000;
540 UPDATE t2 SET rowid=rowid-9999;
541 SELECT count(*) FROM t2 WHERE a=rowid;
542 }
543} {2048}
544do_test update-13.4 {
545 execsql {
546 BEGIN;
547 INSERT INTO t2 SELECT a+2048 FROM t2;
548 INSERT INTO t2 SELECT a+4096 FROM t2;
549 INSERT INTO t2 SELECT a+8192 FROM t2;
550 SELECT count(*) FROM t2 WHERE a=rowid;
551 COMMIT;
552 }
553} 16384
554do_test update-13.5 {
555 execsql {
556 UPDATE t2 SET rowid=rowid-1;
557 SELECT count(*) FROM t2 WHERE a=rowid+1;
558 }
559} 16384
560
561integrity_check update-13.6
562
563ifcapable {trigger} {
564# Test for proper detection of malformed WHEN clauses on UPDATE triggers.
565#
566do_test update-14.1 {
567 execsql {
568 CREATE TABLE t3(a,b,c);
569 CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN
570 SELECT 'illegal WHEN clause';
571 END;
572 }
573} {}
574do_test update-14.2 {
575 catchsql {
576 UPDATE t3 SET a=1;
577 }
578} {1 {no such column: nosuchcol}}
579do_test update-14.3 {
580 execsql {
581 CREATE TABLE t4(a,b,c);
582 CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN
583 SELECT 'illegal WHEN clause';
584 END;
585 }
586} {}
587do_test update-14.4 {
588 catchsql {
589 UPDATE t4 SET a=1;
590 }
591} {1 {no such column: nosuchcol}}
592
593} ;# ifcapable {trigger}
594
595
596finish_test
generated by cgit v1.1 at 2025-04-25 04:39:06 +0000