xref: /sqlite-3.40.0/test/update.test (revision ef5ecb41) 
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.15 2004/02/10 13:41:53 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#
220execsql {PRAGMA synchronous=FULL}
221do_test update-6.0 {
222  execsql {DROP INDEX idx1}
223  execsql {CREATE INDEX idx1 ON test1(f2)}
224  execsql {SELECT * FROM test1 ORDER BY f1,f2}
225} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
226do_test update-6.1 {
227  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
228  execsql {SELECT * FROM test1 ORDER BY f1,f2}
229} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
230do_test update-6.1.1 {
231  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
232} {8 89 8 257 8 889}
233do_test update-6.1.2 {
234  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
235} {8 89}
236do_test update-6.1.3 {
237  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
238} {}
239do_test update-6.2 {
240  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
241  execsql {SELECT * FROM test1 ORDER BY f1,f2}
242} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
243do_test update-6.3 {
244  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
245  execsql {SELECT * FROM test1 ORDER BY f1,f2}
246} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
247do_test update-6.3.1 {
248  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
249} {8 88 8 256 8 888}
250do_test update-6.3.2 {
251  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
252} {}
253do_test update-6.3.3 {
254  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
255} {8 88}
256do_test update-6.4 {
257  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
258  execsql {SELECT * FROM test1 ORDER BY f1,f2}
259} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
260do_test update-6.4.1 {
261  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
262} {78 128}
263do_test update-6.4.2 {
264  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
265} {778 128}
266do_test update-6.4.3 {
267  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
268} {8 88 8 128 8 256 8 888}
269do_test update-6.5 {
270  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
271  execsql {SELECT * FROM test1 ORDER BY f1,f2}
272} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
273do_test update-6.5.1 {
274  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
275} {78 128}
276do_test update-6.5.2 {
277  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
278} {}
279do_test update-6.5.3 {
280  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
281} {777 128}
282do_test update-6.5.4 {
283  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
284} {8 88 8 128 8 256 8 888}
285do_test update-6.6 {
286  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
287  execsql {SELECT * FROM test1 ORDER BY f1,f2}
288} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
289do_test update-6.6.1 {
290  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
291} {77 128}
292do_test update-6.6.2 {
293  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
294} {}
295do_test update-6.6.3 {
296  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
297} {777 128}
298do_test update-6.6.4 {
299  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
300} {8 88 8 256 8 888}
301
302# Repeat the previous sequence of tests with multiple
303# indices
304#
305do_test update-7.0 {
306  execsql {CREATE INDEX idx2 ON test1(f2)}
307  execsql {CREATE INDEX idx3 ON test1(f1,f2)}
308  execsql {SELECT * FROM test1 ORDER BY f1,f2}
309} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
310do_test update-7.1 {
311  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
312  execsql {SELECT * FROM test1 ORDER BY f1,f2}
313} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
314do_test update-7.1.1 {
315  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
316} {8 89 8 257 8 889}
317do_test update-7.1.2 {
318  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
319} {8 89}
320do_test update-7.1.3 {
321  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
322} {}
323do_test update-7.2 {
324  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
325  execsql {SELECT * FROM test1 ORDER BY f1,f2}
326} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
327do_test update-7.3 {
328  # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
329  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
330  execsql {SELECT * FROM test1 ORDER BY f1,f2}
331} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
332do_test update-7.3.1 {
333  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
334} {8 88 8 256 8 888}
335do_test update-7.3.2 {
336  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
337} {}
338do_test update-7.3.3 {
339  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
340} {8 88}
341do_test update-7.4 {
342  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
343  execsql {SELECT * FROM test1 ORDER BY f1,f2}
344} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
345do_test update-7.4.1 {
346  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
347} {78 128}
348do_test update-7.4.2 {
349  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
350} {778 128}
351do_test update-7.4.3 {
352  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
353} {8 88 8 128 8 256 8 888}
354do_test update-7.5 {
355  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
356  execsql {SELECT * FROM test1 ORDER BY f1,f2}
357} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
358do_test update-7.5.1 {
359  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
360} {78 128}
361do_test update-7.5.2 {
362  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
363} {}
364do_test update-7.5.3 {
365  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
366} {777 128}
367do_test update-7.5.4 {
368  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
369} {8 88 8 128 8 256 8 888}
370do_test update-7.6 {
371  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
372  execsql {SELECT * FROM test1 ORDER BY f1,f2}
373} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
374do_test update-7.6.1 {
375  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
376} {77 128}
377do_test update-7.6.2 {
378  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
379} {}
380do_test update-7.6.3 {
381  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
382} {777 128}
383do_test update-7.6.4 {
384  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
385} {8 88 8 256 8 888}
386
387# Error messages
388#
389do_test update-9.1 {
390  set v [catch {execsql {
391    UPDATE test1 SET x=11 WHERE f1=1025
392  }} msg]
393  lappend v $msg
394} {1 {no such column: x}}
395do_test update-9.2 {
396  set v [catch {execsql {
397    UPDATE test1 SET f1=x(11) WHERE f1=1025
398  }} msg]
399  lappend v $msg
400} {1 {no such function: x}}
401do_test update-9.3 {
402  set v [catch {execsql {
403    UPDATE test1 SET f1=11 WHERE x=1025
404  }} msg]
405  lappend v $msg
406} {1 {no such column: x}}
407do_test update-9.4 {
408  set v [catch {execsql {
409    UPDATE test1 SET f1=11 WHERE x(f1)=1025
410  }} msg]
411  lappend v $msg
412} {1 {no such function: x}}
413
414# Try doing updates on a unique column where the value does not
415# really change.
416#
417do_test update-10.1 {
418  execsql {
419    DROP TABLE test1;
420    CREATE TABLE t1(
421       a integer primary key,
422       b UNIQUE,
423       c, d,
424       e, f,
425       UNIQUE(c,d)
426    );
427    INSERT INTO t1 VALUES(1,2,3,4,5,6);
428    INSERT INTO t1 VALUES(2,3,4,4,6,7);
429    SELECT * FROM t1
430  }
431} {1 2 3 4 5 6 2 3 4 4 6 7}
432do_test update-10.2 {
433  catchsql {
434    UPDATE t1 SET a=1, e=9 WHERE f=6;
435    SELECT * FROM t1;
436  }
437} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
438do_test update-10.3 {
439  catchsql {
440    UPDATE t1 SET a=1, e=10 WHERE f=7;
441    SELECT * FROM t1;
442  }
443} {1 {PRIMARY KEY must be unique}}
444do_test update-10.4 {
445  catchsql {
446    SELECT * FROM t1;
447  }
448} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
449do_test update-10.5 {
450  catchsql {
451    UPDATE t1 SET b=2, e=11 WHERE f=6;
452    SELECT * FROM t1;
453  }
454} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
455do_test update-10.6 {
456  catchsql {
457    UPDATE t1 SET b=2, e=12 WHERE f=7;
458    SELECT * FROM t1;
459  }
460} {1 {column b is not unique}}
461do_test update-10.7 {
462  catchsql {
463    SELECT * FROM t1;
464  }
465} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
466do_test update-10.8 {
467  catchsql {
468    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
469    SELECT * FROM t1;
470  }
471} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
472do_test update-10.9 {
473  catchsql {
474    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
475    SELECT * FROM t1;
476  }
477} {1 {columns c, d are not unique}}
478do_test update-10.10 {
479  catchsql {
480    SELECT * FROM t1;
481  }
482} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
483
484# Make sure we can handle a subquery in the where clause.
485#
486do_test update-11.1 {
487  execsql {
488    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
489    SELECT b,e FROM t1;
490  }
491} {2 14 3 7}
492do_test update-11.2 {
493  execsql {
494    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
495    SELECT a,e FROM t1;
496  }
497} {1 15 2 8}
498
499integrity_check update-12.1
500
501# Ticket 602.  Updates should occur in the same order as the records
502# were discovered in the WHERE clause.
503#
504do_test update-13.1 {
505  execsql {
506    BEGIN;
507    CREATE TABLE t2(a);
508    INSERT INTO t2 VALUES(1);
509    INSERT INTO t2 VALUES(2);
510    INSERT INTO t2 SELECT a+2 FROM t2;
511    INSERT INTO t2 SELECT a+4 FROM t2;
512    INSERT INTO t2 SELECT a+8 FROM t2;
513    INSERT INTO t2 SELECT a+16 FROM t2;
514    INSERT INTO t2 SELECT a+32 FROM t2;
515    INSERT INTO t2 SELECT a+64 FROM t2;
516    INSERT INTO t2 SELECT a+128 FROM t2;
517    INSERT INTO t2 SELECT a+256 FROM t2;
518    INSERT INTO t2 SELECT a+512 FROM t2;
519    INSERT INTO t2 SELECT a+1024 FROM t2;
520    COMMIT;
521    SELECT count(*) FROM t2;
522  }
523} {2048}
524do_test update-13.2 {
525  execsql {
526    SELECT count(*) FROM t2 WHERE a=rowid;
527  }
528} {2048}
529do_test update-13.3 {
530  execsql {
531    UPDATE t2 SET rowid=rowid-1;
532    SELECT count(*) FROM t2 WHERE a=rowid+1;
533  }
534} {2048}
535do_test update-13.3 {
536  execsql {
537    UPDATE t2 SET rowid=rowid+10000;
538    UPDATE t2 SET rowid=rowid-9999;
539    SELECT count(*) FROM t2 WHERE a=rowid;
540  }
541} {2048}
542do_test update-13.4 {
543  execsql {
544    BEGIN;
545    INSERT INTO t2 SELECT a+2048 FROM t2;
546    INSERT INTO t2 SELECT a+4096 FROM t2;
547    INSERT INTO t2 SELECT a+8192 FROM t2;
548    SELECT count(*) FROM t2 WHERE a=rowid;
549    COMMIT;
550  }
551} 16384
552do_test update-13.5 {
553  execsql {
554    UPDATE t2 SET rowid=rowid-1;
555    SELECT count(*) FROM t2 WHERE a=rowid+1;
556  }
557} 16384
558
559integrity_check update-13.6
560
561
562finish_test
563