xref: /titanic_51/usr/src/lib/libsqlite/test/update.test (revision 6a72db4a7fa12c3e0d1c1cf91a07390739fa0fbf) 
1
2#pragma ident	"%Z%%M%	%I%	%E% SMI"
3
4# 2001 September 15
5#
6# The author disclaims copyright to this source code.  In place of
7# a legal notice, here is a blessing:
8#
9#    May you do good and not evil.
10#    May you find forgiveness for yourself and forgive others.
11#    May you share freely, never taking more than you give.
12#
13#***********************************************************************
14# This file implements regression tests for SQLite library.  The
15# focus of this file is testing the UPDATE statement.
16#
17# $Id: update.test,v 1.15 2004/02/10 13:41:53 drh Exp $
18
19set testdir [file dirname $argv0]
20source $testdir/tester.tcl
21
22# Try to update an non-existent table
23#
24do_test update-1.1 {
25  set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg]
26  lappend v $msg
27} {1 {no such table: test1}}
28
29# Try to update a read-only table
30#
31do_test update-2.1 {
32  set v [catch \
33       {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg]
34  lappend v $msg
35} {1 {table sqlite_master may not be modified}}
36
37# Create a table to work with
38#
39do_test update-3.1 {
40  execsql {CREATE TABLE test1(f1 int,f2 int)}
41  for {set i 1} {$i<=10} {incr i} {
42    set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
43    execsql $sql
44  }
45  execsql {SELECT * FROM test1 ORDER BY f1}
46} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
47
48# Unknown column name in an expression
49#
50do_test update-3.2 {
51  set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
52  lappend v $msg
53} {1 {no such column: f3}}
54do_test update-3.3 {
55  set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
56  lappend v $msg
57} {1 {no such column: test2.f1}}
58do_test update-3.4 {
59  set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
60  lappend v $msg
61} {1 {no such column: f3}}
62
63# Actually do some updates
64#
65do_test update-3.5 {
66  execsql {UPDATE test1 SET f2=f2*3}
67} {}
68do_test update-3.6 {
69  execsql {SELECT * FROM test1 ORDER BY f1}
70} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
71do_test update-3.7 {
72  execsql {PRAGMA count_changes=on}
73  execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
74} {5}
75do_test update-3.8 {
76  execsql {SELECT * FROM test1 ORDER BY f1}
77} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072}
78do_test update-3.9 {
79  execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
80} {5}
81do_test update-3.10 {
82  execsql {SELECT * FROM test1 ORDER BY f1}
83} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
84
85# Swap the values of f1 and f2 for all elements
86#
87do_test update-3.11 {
88  execsql {UPDATE test1 SET F2=f1, F1=f2}
89} {10}
90do_test update-3.12 {
91  execsql {SELECT * FROM test1 ORDER BY F1}
92} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}
93do_test update-3.13 {
94  execsql {PRAGMA count_changes=off}
95  execsql {UPDATE test1 SET F2=f1, F1=f2}
96} {}
97do_test update-3.14 {
98  execsql {SELECT * FROM test1 ORDER BY F1}
99} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
100
101# Create duplicate entries and make sure updating still
102# works.
103#
104do_test update-4.0 {
105  execsql {
106    DELETE FROM test1 WHERE f1<=5;
107    INSERT INTO test1(f1,f2) VALUES(8,88);
108    INSERT INTO test1(f1,f2) VALUES(8,888);
109    INSERT INTO test1(f1,f2) VALUES(77,128);
110    INSERT INTO test1(f1,f2) VALUES(777,128);
111  }
112  execsql {SELECT * FROM test1 ORDER BY f1,f2}
113} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
114do_test update-4.1 {
115  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
116  execsql {SELECT * FROM test1 ORDER BY f1,f2}
117} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
118do_test update-4.2 {
119  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
120  execsql {SELECT * FROM test1 ORDER BY f1,f2}
121} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
122do_test update-4.3 {
123  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
124  execsql {SELECT * FROM test1 ORDER BY f1,f2}
125} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
126do_test update-4.4 {
127  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
128  execsql {SELECT * FROM test1 ORDER BY f1,f2}
129} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
130do_test update-4.5 {
131  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
132  execsql {SELECT * FROM test1 ORDER BY f1,f2}
133} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
134do_test update-4.6 {
135  execsql {
136    PRAGMA count_changes=on;
137    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
138  }
139} {2}
140do_test update-4.7 {
141  execsql {
142    PRAGMA count_changes=off;
143    SELECT * FROM test1 ORDER BY f1,f2
144  }
145} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
146
147# Repeat the previous sequence of tests with an index.
148#
149do_test update-5.0 {
150  execsql {CREATE INDEX idx1 ON test1(f1)}
151  execsql {SELECT * FROM test1 ORDER BY f1,f2}
152} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
153do_test update-5.1 {
154  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
155  execsql {SELECT * FROM test1 ORDER BY f1,f2}
156} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
157do_test update-5.2 {
158  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
159  execsql {SELECT * FROM test1 ORDER BY f1,f2}
160} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
161do_test update-5.3 {
162  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
163  execsql {SELECT * FROM test1 ORDER BY f1,f2}
164} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
165do_test update-5.4 {
166  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
167  execsql {SELECT * FROM test1 ORDER BY f1,f2}
168} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
169do_test update-5.4.1 {
170  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
171} {78 128}
172do_test update-5.4.2 {
173  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
174} {778 128}
175do_test update-5.4.3 {
176  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
177} {8 88 8 128 8 256 8 888}
178do_test update-5.5 {
179  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
180} {}
181do_test update-5.5.1 {
182  execsql {SELECT * FROM test1 ORDER BY f1,f2}
183} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
184do_test update-5.5.2 {
185  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
186} {78 128}
187do_test update-5.5.3 {
188  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
189} {}
190do_test update-5.5.4 {
191  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
192} {777 128}
193do_test update-5.5.5 {
194  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
195} {8 88 8 128 8 256 8 888}
196do_test update-5.6 {
197  execsql {
198    PRAGMA count_changes=on;
199    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
200  }
201} {2}
202do_test update-5.6.1 {
203  execsql {
204    PRAGMA count_changes=off;
205    SELECT * FROM test1 ORDER BY f1,f2
206  }
207} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
208do_test update-5.6.2 {
209  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
210} {77 128}
211do_test update-5.6.3 {
212  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
213} {}
214do_test update-5.6.4 {
215  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
216} {777 128}
217do_test update-5.6.5 {
218  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
219} {8 88 8 256 8 888}
220
221# Repeat the previous sequence of tests with a different index.
222#
223execsql {PRAGMA synchronous=FULL}
224do_test update-6.0 {
225  execsql {DROP INDEX idx1}
226  execsql {CREATE INDEX idx1 ON test1(f2)}
227  execsql {SELECT * FROM test1 ORDER BY f1,f2}
228} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
229do_test update-6.1 {
230  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
231  execsql {SELECT * FROM test1 ORDER BY f1,f2}
232} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
233do_test update-6.1.1 {
234  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
235} {8 89 8 257 8 889}
236do_test update-6.1.2 {
237  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
238} {8 89}
239do_test update-6.1.3 {
240  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
241} {}
242do_test update-6.2 {
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 89 8 257 8 888 9 512 10 1024 77 128 777 128}
246do_test update-6.3 {
247  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
248  execsql {SELECT * FROM test1 ORDER BY f1,f2}
249} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
250do_test update-6.3.1 {
251  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
252} {8 88 8 256 8 888}
253do_test update-6.3.2 {
254  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
255} {}
256do_test update-6.3.3 {
257  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
258} {8 88}
259do_test update-6.4 {
260  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
261  execsql {SELECT * FROM test1 ORDER BY f1,f2}
262} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
263do_test update-6.4.1 {
264  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
265} {78 128}
266do_test update-6.4.2 {
267  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
268} {778 128}
269do_test update-6.4.3 {
270  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
271} {8 88 8 128 8 256 8 888}
272do_test update-6.5 {
273  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
274  execsql {SELECT * FROM test1 ORDER BY f1,f2}
275} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
276do_test update-6.5.1 {
277  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
278} {78 128}
279do_test update-6.5.2 {
280  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
281} {}
282do_test update-6.5.3 {
283  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
284} {777 128}
285do_test update-6.5.4 {
286  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
287} {8 88 8 128 8 256 8 888}
288do_test update-6.6 {
289  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
290  execsql {SELECT * FROM test1 ORDER BY f1,f2}
291} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
292do_test update-6.6.1 {
293  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
294} {77 128}
295do_test update-6.6.2 {
296  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
297} {}
298do_test update-6.6.3 {
299  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
300} {777 128}
301do_test update-6.6.4 {
302  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
303} {8 88 8 256 8 888}
304
305# Repeat the previous sequence of tests with multiple
306# indices
307#
308do_test update-7.0 {
309  execsql {CREATE INDEX idx2 ON test1(f2)}
310  execsql {CREATE INDEX idx3 ON test1(f1,f2)}
311  execsql {SELECT * FROM test1 ORDER BY f1,f2}
312} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
313do_test update-7.1 {
314  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
315  execsql {SELECT * FROM test1 ORDER BY f1,f2}
316} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
317do_test update-7.1.1 {
318  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
319} {8 89 8 257 8 889}
320do_test update-7.1.2 {
321  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
322} {8 89}
323do_test update-7.1.3 {
324  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
325} {}
326do_test update-7.2 {
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 89 8 257 8 888 9 512 10 1024 77 128 777 128}
330do_test update-7.3 {
331  # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
332  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
333  execsql {SELECT * FROM test1 ORDER BY f1,f2}
334} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
335do_test update-7.3.1 {
336  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
337} {8 88 8 256 8 888}
338do_test update-7.3.2 {
339  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
340} {}
341do_test update-7.3.3 {
342  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
343} {8 88}
344do_test update-7.4 {
345  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
346  execsql {SELECT * FROM test1 ORDER BY f1,f2}
347} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
348do_test update-7.4.1 {
349  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
350} {78 128}
351do_test update-7.4.2 {
352  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
353} {778 128}
354do_test update-7.4.3 {
355  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
356} {8 88 8 128 8 256 8 888}
357do_test update-7.5 {
358  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
359  execsql {SELECT * FROM test1 ORDER BY f1,f2}
360} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
361do_test update-7.5.1 {
362  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
363} {78 128}
364do_test update-7.5.2 {
365  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
366} {}
367do_test update-7.5.3 {
368  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
369} {777 128}
370do_test update-7.5.4 {
371  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
372} {8 88 8 128 8 256 8 888}
373do_test update-7.6 {
374  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
375  execsql {SELECT * FROM test1 ORDER BY f1,f2}
376} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
377do_test update-7.6.1 {
378  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
379} {77 128}
380do_test update-7.6.2 {
381  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
382} {}
383do_test update-7.6.3 {
384  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
385} {777 128}
386do_test update-7.6.4 {
387  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
388} {8 88 8 256 8 888}
389
390# Error messages
391#
392do_test update-9.1 {
393  set v [catch {execsql {
394    UPDATE test1 SET x=11 WHERE f1=1025
395  }} msg]
396  lappend v $msg
397} {1 {no such column: x}}
398do_test update-9.2 {
399  set v [catch {execsql {
400    UPDATE test1 SET f1=x(11) WHERE f1=1025
401  }} msg]
402  lappend v $msg
403} {1 {no such function: x}}
404do_test update-9.3 {
405  set v [catch {execsql {
406    UPDATE test1 SET f1=11 WHERE x=1025
407  }} msg]
408  lappend v $msg
409} {1 {no such column: x}}
410do_test update-9.4 {
411  set v [catch {execsql {
412    UPDATE test1 SET f1=11 WHERE x(f1)=1025
413  }} msg]
414  lappend v $msg
415} {1 {no such function: x}}
416
417# Try doing updates on a unique column where the value does not
418# really change.
419#
420do_test update-10.1 {
421  execsql {
422    DROP TABLE test1;
423    CREATE TABLE t1(
424       a integer primary key,
425       b UNIQUE,
426       c, d,
427       e, f,
428       UNIQUE(c,d)
429    );
430    INSERT INTO t1 VALUES(1,2,3,4,5,6);
431    INSERT INTO t1 VALUES(2,3,4,4,6,7);
432    SELECT * FROM t1
433  }
434} {1 2 3 4 5 6 2 3 4 4 6 7}
435do_test update-10.2 {
436  catchsql {
437    UPDATE t1 SET a=1, e=9 WHERE f=6;
438    SELECT * FROM t1;
439  }
440} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
441do_test update-10.3 {
442  catchsql {
443    UPDATE t1 SET a=1, e=10 WHERE f=7;
444    SELECT * FROM t1;
445  }
446} {1 {PRIMARY KEY must be unique}}
447do_test update-10.4 {
448  catchsql {
449    SELECT * FROM t1;
450  }
451} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
452do_test update-10.5 {
453  catchsql {
454    UPDATE t1 SET b=2, e=11 WHERE f=6;
455    SELECT * FROM t1;
456  }
457} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
458do_test update-10.6 {
459  catchsql {
460    UPDATE t1 SET b=2, e=12 WHERE f=7;
461    SELECT * FROM t1;
462  }
463} {1 {column b is not unique}}
464do_test update-10.7 {
465  catchsql {
466    SELECT * FROM t1;
467  }
468} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
469do_test update-10.8 {
470  catchsql {
471    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
472    SELECT * FROM t1;
473  }
474} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
475do_test update-10.9 {
476  catchsql {
477    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
478    SELECT * FROM t1;
479  }
480} {1 {columns c, d are not unique}}
481do_test update-10.10 {
482  catchsql {
483    SELECT * FROM t1;
484  }
485} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
486
487# Make sure we can handle a subquery in the where clause.
488#
489do_test update-11.1 {
490  execsql {
491    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
492    SELECT b,e FROM t1;
493  }
494} {2 14 3 7}
495do_test update-11.2 {
496  execsql {
497    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
498    SELECT a,e FROM t1;
499  }
500} {1 15 2 8}
501
502integrity_check update-12.1
503
504# Ticket 602.  Updates should occur in the same order as the records
505# were discovered in the WHERE clause.
506#
507do_test update-13.1 {
508  execsql {
509    BEGIN;
510    CREATE TABLE t2(a);
511    INSERT INTO t2 VALUES(1);
512    INSERT INTO t2 VALUES(2);
513    INSERT INTO t2 SELECT a+2 FROM t2;
514    INSERT INTO t2 SELECT a+4 FROM t2;
515    INSERT INTO t2 SELECT a+8 FROM t2;
516    INSERT INTO t2 SELECT a+16 FROM t2;
517    INSERT INTO t2 SELECT a+32 FROM t2;
518    INSERT INTO t2 SELECT a+64 FROM t2;
519    INSERT INTO t2 SELECT a+128 FROM t2;
520    INSERT INTO t2 SELECT a+256 FROM t2;
521    INSERT INTO t2 SELECT a+512 FROM t2;
522    INSERT INTO t2 SELECT a+1024 FROM t2;
523    COMMIT;
524    SELECT count(*) FROM t2;
525  }
526} {2048}
527do_test update-13.2 {
528  execsql {
529    SELECT count(*) FROM t2 WHERE a=rowid;
530  }
531} {2048}
532do_test update-13.3 {
533  execsql {
534    UPDATE t2 SET rowid=rowid-1;
535    SELECT count(*) FROM t2 WHERE a=rowid+1;
536  }
537} {2048}
538do_test update-13.3 {
539  execsql {
540    UPDATE t2 SET rowid=rowid+10000;
541    UPDATE t2 SET rowid=rowid-9999;
542    SELECT count(*) FROM t2 WHERE a=rowid;
543  }
544} {2048}
545do_test update-13.4 {
546  execsql {
547    BEGIN;
548    INSERT INTO t2 SELECT a+2048 FROM t2;
549    INSERT INTO t2 SELECT a+4096 FROM t2;
550    INSERT INTO t2 SELECT a+8192 FROM t2;
551    SELECT count(*) FROM t2 WHERE a=rowid;
552    COMMIT;
553  }
554} 16384
555do_test update-13.5 {
556  execsql {
557    UPDATE t2 SET rowid=rowid-1;
558    SELECT count(*) FROM t2 WHERE a=rowid+1;
559  }
560} 16384
561
562integrity_check update-13.6
563
564
565finish_test
566