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