3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the UPDATE statement.
14 # $Id: update.test,v 1.19 2008/04/10 18:44:36 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Try to update an non-existent table
22 set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg]
24 } {1 {no such table: test1}}
26 # Try to update a read-only table
30 {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg]
32 } {1 {table sqlite_master may not be modified}}
34 # Create a table to work with
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}])"
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}
45 # Unknown column name in an expression
48 set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
50 } {1 {no such column: f3}}
52 set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
54 } {1 {no such column: test2.f1}}
56 set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
58 } {1 {no such column: f3}}
60 # Actually do some updates
63 execsql {UPDATE test1 SET f2=f2*3}
65 do_test update-3.5.1 {
69 # verify that SELECT does not reset the change counter
70 do_test update-3.5.2 {
71 db eval {SELECT count(*) FROM test1}
73 do_test update-3.5.3 {
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}
81 execsql {PRAGMA count_changes=on}
82 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
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}
88 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
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}
94 # Swap the values of f1 and f2 for all elements
97 execsql {UPDATE test1 SET F2=f1, F1=f2}
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}
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}
110 # Create duplicate entries and make sure updating still
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);
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}
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}
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}
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}
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}
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}
145 PRAGMA count_changes=on;
146 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
151 PRAGMA count_changes=off;
152 SELECT * FROM test1 ORDER BY f1,f2
154 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
156 # Repeat the previous sequence of tests with an index.
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}
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}
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}
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}
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}
181 do_test update-5.4.2 {
182 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
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}
188 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
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}
196 do_test update-5.5.3 {
197 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
199 do_test update-5.5.4 {
200 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
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}
207 PRAGMA count_changes=on;
208 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
211 do_test update-5.6.1 {
213 PRAGMA count_changes=off;
214 SELECT * FROM test1 ORDER BY f1,f2
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}
220 do_test update-5.6.3 {
221 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
223 do_test update-5.6.4 {
224 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
226 do_test update-5.6.5 {
227 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
230 # Repeat the previous sequence of tests with a different index.
232 execsql {PRAGMA synchronous=FULL}
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}
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}
245 do_test update-6.1.2 {
246 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
248 do_test update-6.1.3 {
249 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
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}
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}
262 do_test update-6.3.2 {
263 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
265 do_test update-6.3.3 {
266 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
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}
275 do_test update-6.4.2 {
276 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
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}
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}
288 do_test update-6.5.2 {
289 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
291 do_test update-6.5.3 {
292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
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}
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}
304 do_test update-6.6.2 {
305 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
307 do_test update-6.6.3 {
308 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
310 do_test update-6.6.4 {
311 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
314 # Repeat the previous sequence of tests with multiple
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}
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}
329 do_test update-7.1.2 {
330 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
332 do_test update-7.1.3 {
333 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
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}
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}
347 do_test update-7.3.2 {
348 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
350 do_test update-7.3.3 {
351 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
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}
360 do_test update-7.4.2 {
361 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
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}
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}
373 do_test update-7.5.2 {
374 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
376 do_test update-7.5.3 {
377 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
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}
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}
389 do_test update-7.6.2 {
390 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
392 do_test update-7.6.3 {
393 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
395 do_test update-7.6.4 {
396 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
402 set v [catch {execsql {
403 UPDATE test1 SET x=11 WHERE f1=1025
406 } {1 {no such column: x}}
408 set v [catch {execsql {
409 UPDATE test1 SET f1=x(11) WHERE f1=1025
412 } {1 {no such function: x}}
414 set v [catch {execsql {
415 UPDATE test1 SET f1=11 WHERE x=1025
418 } {1 {no such column: x}}
420 set v [catch {execsql {
421 UPDATE test1 SET f1=11 WHERE x(f1)=1025
424 } {1 {no such function: x}}
426 # Try doing updates on a unique column where the value does not
429 do_test update-10.1 {
433 a integer primary key,
439 INSERT INTO t1 VALUES(1,2,3,4,5,6);
440 INSERT INTO t1 VALUES(2,3,4,4,6,7);
443 } {1 2 3 4 5 6 2 3 4 4 6 7}
444 do_test update-10.2 {
446 UPDATE t1 SET a=1, e=9 WHERE f=6;
449 } {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
450 do_test update-10.3 {
452 UPDATE t1 SET a=1, e=10 WHERE f=7;
455 } {1 {UNIQUE constraint failed: t1.a}}
456 do_test update-10.4 {
460 } {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
461 do_test update-10.5 {
463 UPDATE t1 SET b=2, e=11 WHERE f=6;
466 } {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
467 do_test update-10.6 {
469 UPDATE t1 SET b=2, e=12 WHERE f=7;
472 } {1 {UNIQUE constraint failed: t1.b}}
473 do_test update-10.7 {
477 } {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
478 do_test update-10.8 {
480 UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
483 } {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
484 do_test update-10.9 {
486 UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
489 } {1 {UNIQUE constraint failed: t1.c, t1.d}}
490 do_test update-10.10 {
494 } {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
496 # Make sure we can handle a subquery in the where clause.
499 do_test update-11.1 {
501 UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
505 do_test update-11.2 {
507 UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
513 integrity_check update-12.1
515 # Ticket 602. Updates should occur in the same order as the records
516 # were discovered in the WHERE clause.
518 do_test update-13.1 {
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;
535 SELECT count(*) FROM t2;
538 do_test update-13.2 {
540 SELECT count(*) FROM t2 WHERE a=rowid;
543 do_test update-13.3 {
545 UPDATE t2 SET rowid=rowid-1;
546 SELECT count(*) FROM t2 WHERE a=rowid+1;
549 do_test update-13.3 {
551 UPDATE t2 SET rowid=rowid+10000;
552 UPDATE t2 SET rowid=rowid-9999;
553 SELECT count(*) FROM t2 WHERE a=rowid;
556 do_test update-13.4 {
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;
566 do_test update-13.5 {
568 UPDATE t2 SET rowid=rowid-1;
569 SELECT count(*) FROM t2 WHERE a=rowid+1;
573 integrity_check update-13.6
575 ifcapable {trigger} {
576 # Test for proper detection of malformed WHEN clauses on UPDATE triggers.
578 do_test update-14.1 {
580 CREATE TABLE t3(a,b,c);
581 CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN
582 SELECT 'illegal WHEN clause';
586 do_test update-14.2 {
590 } {1 {no such column: nosuchcol}}
591 do_test update-14.3 {
593 CREATE TABLE t4(a,b,c);
594 CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN
595 SELECT 'illegal WHEN clause';
599 do_test update-14.4 {
603 } {1 {no such column: nosuchcol}}
605 } ;# ifcapable {trigger}