xref: /sqlite-3.40.0/test/update.test (revision 1bee3d7b) 
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.7 2001/10/15 00:44:36 drh 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#
220do_test update-6.0 {
221  execsql {DROP INDEX idx1}
222  execsql {CREATE INDEX idx1 ON test1(f2)}
223  execsql {SELECT * FROM test1 ORDER BY f1,f2}
224} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
225do_test update-6.1 {
226  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
227  execsql {SELECT * FROM test1 ORDER BY f1,f2}
228} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
229do_test update-6.1.1 {
230  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
231} {8 89 8 257 8 889}
232do_test update-6.1.2 {
233  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
234} {8 89}
235do_test update-6.1.3 {
236  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
237} {}
238do_test update-6.2 {
239  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
240  execsql {SELECT * FROM test1 ORDER BY f1,f2}
241} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
242do_test update-6.3 {
243  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
244  execsql {SELECT * FROM test1 ORDER BY f1,f2}
245} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
246do_test update-6.3.1 {
247  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
248} {8 88 8 256 8 888}
249do_test update-6.3.2 {
250  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
251} {}
252do_test update-6.3.3 {
253  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
254} {8 88}
255do_test update-6.4 {
256  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
257  execsql {SELECT * FROM test1 ORDER BY f1,f2}
258} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
259do_test update-6.4.1 {
260  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
261} {78 128}
262do_test update-6.4.2 {
263  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
264} {778 128}
265do_test update-6.4.3 {
266  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
267} {8 88 8 128 8 256 8 888}
268do_test update-6.5 {
269  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and 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 777 128}
272do_test update-6.5.1 {
273  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
274} {78 128}
275do_test update-6.5.2 {
276  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
277} {}
278do_test update-6.5.3 {
279  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
280} {777 128}
281do_test update-6.5.4 {
282  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
283} {8 88 8 128 8 256 8 888}
284do_test update-6.6 {
285  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
286  execsql {SELECT * FROM test1 ORDER BY f1,f2}
287} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
288do_test update-6.6.1 {
289  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
290} {77 128}
291do_test update-6.6.2 {
292  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
293} {}
294do_test update-6.6.3 {
295  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
296} {777 128}
297do_test update-6.6.4 {
298  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
299} {8 88 8 256 8 888}
300
301# Repeat the previous sequence of tests with multiple
302# indices
303#
304do_test update-7.0 {
305  execsql {CREATE INDEX idx2 ON test1(f2)}
306  execsql {CREATE INDEX idx3 ON test1(f1,f2)}
307  execsql {SELECT * FROM test1 ORDER BY f1,f2}
308} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
309do_test update-7.1 {
310  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
311  execsql {SELECT * FROM test1 ORDER BY f1,f2}
312} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
313do_test update-7.1.1 {
314  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
315} {8 89 8 257 8 889}
316do_test update-7.1.2 {
317  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
318} {8 89}
319do_test update-7.1.3 {
320  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
321} {}
322do_test update-7.2 {
323  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
324  execsql {SELECT * FROM test1 ORDER BY f1,f2}
325} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
326do_test update-7.3 {
327  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
328  execsql {SELECT * FROM test1 ORDER BY f1,f2}
329} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
330do_test update-7.3.1 {
331  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
332} {8 88 8 256 8 888}
333do_test update-7.3.2 {
334  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
335} {}
336do_test update-7.3.3 {
337  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
338} {8 88}
339do_test update-7.4 {
340  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
341  execsql {SELECT * FROM test1 ORDER BY f1,f2}
342} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
343do_test update-7.4.1 {
344  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
345} {78 128}
346do_test update-7.4.2 {
347  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
348} {778 128}
349do_test update-7.4.3 {
350  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
351} {8 88 8 128 8 256 8 888}
352do_test update-7.5 {
353  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
354  execsql {SELECT * FROM test1 ORDER BY f1,f2}
355} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
356do_test update-7.5.1 {
357  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
358} {78 128}
359do_test update-7.5.2 {
360  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
361} {}
362do_test update-7.5.3 {
363  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
364} {777 128}
365do_test update-7.5.4 {
366  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
367} {8 88 8 128 8 256 8 888}
368do_test update-7.6 {
369  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
370  execsql {SELECT * FROM test1 ORDER BY f1,f2}
371} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
372do_test update-7.6.1 {
373  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
374} {77 128}
375do_test update-7.6.2 {
376  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
377} {}
378do_test update-7.6.3 {
379  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
380} {777 128}
381do_test update-7.6.4 {
382  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
383} {8 88 8 256 8 888}
384
385# Error messages
386#
387do_test update-9.1 {
388  set v [catch {execsql {
389    UPDATE test1 SET x=11 WHERE f1=1025
390  }} msg]
391  lappend v $msg
392} {1 {no such column: x}}
393do_test update-9.2 {
394  set v [catch {execsql {
395    UPDATE test1 SET f1=x(11) WHERE f1=1025
396  }} msg]
397  lappend v $msg
398} {1 {no such function: x}}
399do_test update-9.3 {
400  set v [catch {execsql {
401    UPDATE test1 SET f1=11 WHERE x=1025
402  }} msg]
403  lappend v $msg
404} {1 {no such column: x}}
405do_test update-9.4 {
406  set v [catch {execsql {
407    UPDATE test1 SET f1=11 WHERE x(f1)=1025
408  }} msg]
409  lappend v $msg
410} {1 {no such function: x}}
411
412
413
414
415
416
417finish_test
418