PageRenderTime 39ms CodeModel.GetById 31ms app.highlight 4ms RepoModel.GetById 1ms app.codeStats 0ms

/trunk/src/sqlite/test/update.test

#
Unknown | 608 lines | 585 code | 23 blank | 0 comment | 0 complexity | d2c21796ce95f467c807098622cc0553 MD5 | raw file
Possible License(s): BSD-3-Clause
  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.19 2008/04/10 18: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 {1<<$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.5.1 {
 66  db changes
 67} {10}
 68
 69# verify that SELECT does not reset the change counter
 70do_test update-3.5.2 {
 71  db eval {SELECT count(*) FROM test1}
 72} {10}
 73do_test update-3.5.3 {
 74  db changes
 75} {10}
 76
 77do_test update-3.6 {
 78  execsql {SELECT * FROM test1 ORDER BY f1}
 79} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
 80do_test update-3.7 {
 81  execsql {PRAGMA count_changes=on}
 82  execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
 83} {5}
 84do_test update-3.8 {
 85  execsql {SELECT * FROM test1 ORDER BY f1}
 86} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072}
 87do_test update-3.9 {
 88  execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
 89} {5}
 90do_test update-3.10 {
 91  execsql {SELECT * FROM test1 ORDER BY f1}
 92} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
 93
 94# Swap the values of f1 and f2 for all elements
 95#
 96do_test update-3.11 {
 97  execsql {UPDATE test1 SET F2=f1, F1=f2}
 98} {10}
 99do_test update-3.12 {
100  execsql {SELECT * FROM test1 ORDER BY F1}
101} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}
102do_test update-3.13 {
103  execsql {PRAGMA count_changes=off}
104  execsql {UPDATE test1 SET F2=f1, F1=f2}
105} {}
106do_test update-3.14 {
107  execsql {SELECT * FROM test1 ORDER BY F1}
108} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
109
110# Create duplicate entries and make sure updating still
111# works.
112#
113do_test update-4.0 {
114  execsql {
115    DELETE FROM test1 WHERE f1<=5;
116    INSERT INTO test1(f1,f2) VALUES(8,88);
117    INSERT INTO test1(f1,f2) VALUES(8,888);
118    INSERT INTO test1(f1,f2) VALUES(77,128);
119    INSERT INTO test1(f1,f2) VALUES(777,128);
120  }
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.1 {
124  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
125  execsql {SELECT * FROM test1 ORDER BY f1,f2}
126} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
127do_test update-4.2 {
128  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
129  execsql {SELECT * FROM test1 ORDER BY f1,f2}
130} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
131do_test update-4.3 {
132  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
133  execsql {SELECT * FROM test1 ORDER BY f1,f2}
134} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
135do_test update-4.4 {
136  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
137  execsql {SELECT * FROM test1 ORDER BY f1,f2}
138} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
139do_test update-4.5 {
140  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
141  execsql {SELECT * FROM test1 ORDER BY f1,f2}
142} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
143do_test update-4.6 {
144  execsql {
145    PRAGMA count_changes=on;
146    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
147  }
148} {2}
149do_test update-4.7 {
150  execsql {
151    PRAGMA count_changes=off;
152    SELECT * FROM test1 ORDER BY f1,f2
153  }
154} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
155
156# Repeat the previous sequence of tests with an index.
157#
158do_test update-5.0 {
159  execsql {CREATE INDEX idx1 ON test1(f1)}
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.1 {
163  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
164  execsql {SELECT * FROM test1 ORDER BY f1,f2}
165} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
166do_test update-5.2 {
167  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
168  execsql {SELECT * FROM test1 ORDER BY f1,f2}
169} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
170do_test update-5.3 {
171  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
172  execsql {SELECT * FROM test1 ORDER BY f1,f2}
173} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
174do_test update-5.4 {
175  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
176  execsql {SELECT * FROM test1 ORDER BY f1,f2}
177} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
178do_test update-5.4.1 {
179  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
180} {78 128}
181do_test update-5.4.2 {
182  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
183} {778 128}
184do_test update-5.4.3 {
185  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
186} {8 88 8 128 8 256 8 888}
187do_test update-5.5 {
188  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
189} {}
190do_test update-5.5.1 {
191  execsql {SELECT * FROM test1 ORDER BY f1,f2}
192} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
193do_test update-5.5.2 {
194  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
195} {78 128}
196do_test update-5.5.3 {
197  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
198} {}
199do_test update-5.5.4 {
200  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
201} {777 128}
202do_test update-5.5.5 {
203  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
204} {8 88 8 128 8 256 8 888}
205do_test update-5.6 {
206  execsql {
207    PRAGMA count_changes=on;
208    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
209  }
210} {2}
211do_test update-5.6.1 {
212  execsql {
213    PRAGMA count_changes=off;
214    SELECT * FROM test1 ORDER BY f1,f2
215  }
216} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
217do_test update-5.6.2 {
218  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
219} {77 128}
220do_test update-5.6.3 {
221  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
222} {}
223do_test update-5.6.4 {
224  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
225} {777 128}
226do_test update-5.6.5 {
227  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
228} {8 88 8 256 8 888}
229
230# Repeat the previous sequence of tests with a different index.
231#
232execsql {PRAGMA synchronous=FULL}
233do_test update-6.0 {
234  execsql {DROP INDEX idx1}
235  execsql {CREATE INDEX idx1 ON test1(f2)}
236  execsql {SELECT * FROM test1 ORDER BY f1,f2}
237} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
238do_test update-6.1 {
239  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
240  execsql {SELECT * FROM test1 ORDER BY f1,f2}
241} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
242do_test update-6.1.1 {
243  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
244} {8 89 8 257 8 889}
245do_test update-6.1.2 {
246  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
247} {8 89}
248do_test update-6.1.3 {
249  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
250} {}
251do_test update-6.2 {
252  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
253  execsql {SELECT * FROM test1 ORDER BY f1,f2}
254} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
255do_test update-6.3 {
256  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
257  execsql {SELECT * FROM test1 ORDER BY f1,f2}
258} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
259do_test update-6.3.1 {
260  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
261} {8 88 8 256 8 888}
262do_test update-6.3.2 {
263  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
264} {}
265do_test update-6.3.3 {
266  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
267} {8 88}
268do_test update-6.4 {
269  execsql {UPDATE test1 SET f1=f1+1 WHERE 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 778 128}
272do_test update-6.4.1 {
273  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
274} {78 128}
275do_test update-6.4.2 {
276  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
277} {778 128}
278do_test update-6.4.3 {
279  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
280} {8 88 8 128 8 256 8 888}
281do_test update-6.5 {
282  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
283  execsql {SELECT * FROM test1 ORDER BY f1,f2}
284} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
285do_test update-6.5.1 {
286  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
287} {78 128}
288do_test update-6.5.2 {
289  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
290} {}
291do_test update-6.5.3 {
292  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
293} {777 128}
294do_test update-6.5.4 {
295  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
296} {8 88 8 128 8 256 8 888}
297do_test update-6.6 {
298  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
299  execsql {SELECT * FROM test1 ORDER BY f1,f2}
300} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
301do_test update-6.6.1 {
302  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
303} {77 128}
304do_test update-6.6.2 {
305  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
306} {}
307do_test update-6.6.3 {
308  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
309} {777 128}
310do_test update-6.6.4 {
311  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
312} {8 88 8 256 8 888}
313
314# Repeat the previous sequence of tests with multiple
315# indices
316#
317do_test update-7.0 {
318  execsql {CREATE INDEX idx2 ON test1(f2)}
319  execsql {CREATE INDEX idx3 ON test1(f1,f2)}
320  execsql {SELECT * FROM test1 ORDER BY f1,f2}
321} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
322do_test update-7.1 {
323  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
324  execsql {SELECT * FROM test1 ORDER BY f1,f2}
325} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
326do_test update-7.1.1 {
327  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
328} {8 89 8 257 8 889}
329do_test update-7.1.2 {
330  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
331} {8 89}
332do_test update-7.1.3 {
333  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
334} {}
335do_test update-7.2 {
336  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
337  execsql {SELECT * FROM test1 ORDER BY f1,f2}
338} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
339do_test update-7.3 {
340  # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
341  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
342  execsql {SELECT * FROM test1 ORDER BY f1,f2}
343} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
344do_test update-7.3.1 {
345  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
346} {8 88 8 256 8 888}
347do_test update-7.3.2 {
348  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
349} {}
350do_test update-7.3.3 {
351  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
352} {8 88}
353do_test update-7.4 {
354  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
355  execsql {SELECT * FROM test1 ORDER BY f1,f2}
356} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
357do_test update-7.4.1 {
358  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
359} {78 128}
360do_test update-7.4.2 {
361  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
362} {778 128}
363do_test update-7.4.3 {
364  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
365} {8 88 8 128 8 256 8 888}
366do_test update-7.5 {
367  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
368  execsql {SELECT * FROM test1 ORDER BY f1,f2}
369} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
370do_test update-7.5.1 {
371  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
372} {78 128}
373do_test update-7.5.2 {
374  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
375} {}
376do_test update-7.5.3 {
377  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
378} {777 128}
379do_test update-7.5.4 {
380  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
381} {8 88 8 128 8 256 8 888}
382do_test update-7.6 {
383  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
384  execsql {SELECT * FROM test1 ORDER BY f1,f2}
385} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
386do_test update-7.6.1 {
387  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
388} {77 128}
389do_test update-7.6.2 {
390  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
391} {}
392do_test update-7.6.3 {
393  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
394} {777 128}
395do_test update-7.6.4 {
396  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
397} {8 88 8 256 8 888}
398
399# Error messages
400#
401do_test update-9.1 {
402  set v [catch {execsql {
403    UPDATE test1 SET x=11 WHERE f1=1025
404  }} msg]
405  lappend v $msg
406} {1 {no such column: x}}
407do_test update-9.2 {
408  set v [catch {execsql {
409    UPDATE test1 SET f1=x(11) WHERE f1=1025
410  }} msg]
411  lappend v $msg
412} {1 {no such function: x}}
413do_test update-9.3 {
414  set v [catch {execsql {
415    UPDATE test1 SET f1=11 WHERE x=1025
416  }} msg]
417  lappend v $msg
418} {1 {no such column: x}}
419do_test update-9.4 {
420  set v [catch {execsql {
421    UPDATE test1 SET f1=11 WHERE x(f1)=1025
422  }} msg]
423  lappend v $msg
424} {1 {no such function: x}}
425
426# Try doing updates on a unique column where the value does not
427# really change.
428#
429do_test update-10.1 {
430  execsql {
431    DROP TABLE test1;
432    CREATE TABLE t1(
433       a integer primary key,
434       b UNIQUE, 
435       c, d,
436       e, f,
437       UNIQUE(c,d)
438    );
439    INSERT INTO t1 VALUES(1,2,3,4,5,6);
440    INSERT INTO t1 VALUES(2,3,4,4,6,7);
441    SELECT * FROM t1
442  }
443} {1 2 3 4 5 6 2 3 4 4 6 7}
444do_test update-10.2 {
445  catchsql {
446    UPDATE t1 SET a=1, e=9 WHERE f=6;
447    SELECT * FROM t1;
448  }
449} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
450do_test update-10.3 {
451  catchsql {
452    UPDATE t1 SET a=1, e=10 WHERE f=7;
453    SELECT * FROM t1;
454  }
455} {1 {PRIMARY KEY must be unique}}
456do_test update-10.4 {
457  catchsql {
458    SELECT * FROM t1;
459  }
460} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
461do_test update-10.5 {
462  catchsql {
463    UPDATE t1 SET b=2, e=11 WHERE f=6;
464    SELECT * FROM t1;
465  }
466} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
467do_test update-10.6 {
468  catchsql {
469    UPDATE t1 SET b=2, e=12 WHERE f=7;
470    SELECT * FROM t1;
471  }
472} {1 {column b is not unique}}
473do_test update-10.7 {
474  catchsql {
475    SELECT * FROM t1;
476  }
477} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
478do_test update-10.8 {
479  catchsql {
480    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
481    SELECT * FROM t1;
482  }
483} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
484do_test update-10.9 {
485  catchsql {
486    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
487    SELECT * FROM t1;
488  }
489} {1 {columns c, d are not unique}}
490do_test update-10.10 {
491  catchsql {
492    SELECT * FROM t1;
493  }
494} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
495
496# Make sure we can handle a subquery in the where clause.
497#
498ifcapable subquery {
499  do_test update-11.1 {
500    execsql {
501      UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
502      SELECT b,e FROM t1;
503    }
504  } {2 14 3 7}
505  do_test update-11.2 {
506    execsql {
507      UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
508      SELECT a,e FROM t1;
509    }
510  } {1 15 2 8}
511}
512
513integrity_check update-12.1
514
515# Ticket 602.  Updates should occur in the same order as the records
516# were discovered in the WHERE clause.
517#
518do_test update-13.1 {
519  execsql {
520    BEGIN;
521    CREATE TABLE t2(a);
522    INSERT INTO t2 VALUES(1);
523    INSERT INTO t2 VALUES(2);
524    INSERT INTO t2 SELECT a+2 FROM t2;
525    INSERT INTO t2 SELECT a+4 FROM t2;
526    INSERT INTO t2 SELECT a+8 FROM t2;
527    INSERT INTO t2 SELECT a+16 FROM t2;
528    INSERT INTO t2 SELECT a+32 FROM t2;
529    INSERT INTO t2 SELECT a+64 FROM t2;
530    INSERT INTO t2 SELECT a+128 FROM t2;
531    INSERT INTO t2 SELECT a+256 FROM t2;
532    INSERT INTO t2 SELECT a+512 FROM t2;
533    INSERT INTO t2 SELECT a+1024 FROM t2;
534    COMMIT;
535    SELECT count(*) FROM t2;
536  }
537} {2048}
538do_test update-13.2 {
539  execsql {
540    SELECT count(*) FROM t2 WHERE a=rowid;
541  }
542} {2048}
543do_test update-13.3 {
544  execsql {
545    UPDATE t2 SET rowid=rowid-1;
546    SELECT count(*) FROM t2 WHERE a=rowid+1;
547  }
548} {2048}
549do_test update-13.3 {
550  execsql {
551    UPDATE t2 SET rowid=rowid+10000;
552    UPDATE t2 SET rowid=rowid-9999;
553    SELECT count(*) FROM t2 WHERE a=rowid;
554  }
555} {2048}
556do_test update-13.4 {
557  execsql {
558    BEGIN;
559    INSERT INTO t2 SELECT a+2048 FROM t2;
560    INSERT INTO t2 SELECT a+4096 FROM t2;
561    INSERT INTO t2 SELECT a+8192 FROM t2;
562    SELECT count(*) FROM t2 WHERE a=rowid;
563    COMMIT;
564  }
565} 16384
566do_test update-13.5 {
567  execsql {
568    UPDATE t2 SET rowid=rowid-1;
569    SELECT count(*) FROM t2 WHERE a=rowid+1;
570  }
571} 16384
572
573integrity_check update-13.6
574
575ifcapable {trigger} {
576# Test for proper detection of malformed WHEN clauses on UPDATE triggers.
577#
578do_test update-14.1 {
579  execsql {
580    CREATE TABLE t3(a,b,c);
581    CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN
582      SELECT 'illegal WHEN clause';
583    END;
584  }
585} {}
586do_test update-14.2 {
587  catchsql {
588    UPDATE t3 SET a=1;
589  }
590} {1 {no such column: nosuchcol}}
591do_test update-14.3 {
592  execsql {
593    CREATE TABLE t4(a,b,c);
594    CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN
595      SELECT 'illegal WHEN clause';
596    END;
597  }
598} {}
599do_test update-14.4 {
600  catchsql {
601    UPDATE t4 SET a=1;
602  }
603} {1 {no such column: nosuchcol}}
604
605} ;# ifcapable {trigger}
606
607
608finish_test