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