Annotation of embedaddon/sqlite3/test/update.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to update.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test