wasCSharpSQLite – Blame information for rev 4

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