wasCSharpSQLite – Blame information for rev 1
?pathlinks?
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 |