1*1da57d55SToomas Soome#
27c478bd9Sstevel@tonic-gate# 2001 September 15
37c478bd9Sstevel@tonic-gate#
47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code.  In place of
57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing:
67c478bd9Sstevel@tonic-gate#
77c478bd9Sstevel@tonic-gate#    May you do good and not evil.
87c478bd9Sstevel@tonic-gate#    May you find forgiveness for yourself and forgive others.
97c478bd9Sstevel@tonic-gate#    May you share freely, never taking more than you give.
107c478bd9Sstevel@tonic-gate#
117c478bd9Sstevel@tonic-gate#***********************************************************************
127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library.  The
137c478bd9Sstevel@tonic-gate# focus of this file is testing the UPDATE statement.
147c478bd9Sstevel@tonic-gate#
157c478bd9Sstevel@tonic-gate# $Id: update.test,v 1.15 2004/02/10 13:41:53 drh Exp $
167c478bd9Sstevel@tonic-gate
177c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0]
187c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl
197c478bd9Sstevel@tonic-gate
207c478bd9Sstevel@tonic-gate# Try to update an non-existent table
217c478bd9Sstevel@tonic-gate#
227c478bd9Sstevel@tonic-gatedo_test update-1.1 {
237c478bd9Sstevel@tonic-gate  set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg]
247c478bd9Sstevel@tonic-gate  lappend v $msg
257c478bd9Sstevel@tonic-gate} {1 {no such table: test1}}
267c478bd9Sstevel@tonic-gate
277c478bd9Sstevel@tonic-gate# Try to update a read-only table
287c478bd9Sstevel@tonic-gate#
297c478bd9Sstevel@tonic-gatedo_test update-2.1 {
307c478bd9Sstevel@tonic-gate  set v [catch \
317c478bd9Sstevel@tonic-gate       {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg]
327c478bd9Sstevel@tonic-gate  lappend v $msg
337c478bd9Sstevel@tonic-gate} {1 {table sqlite_master may not be modified}}
347c478bd9Sstevel@tonic-gate
357c478bd9Sstevel@tonic-gate# Create a table to work with
367c478bd9Sstevel@tonic-gate#
377c478bd9Sstevel@tonic-gatedo_test update-3.1 {
387c478bd9Sstevel@tonic-gate  execsql {CREATE TABLE test1(f1 int,f2 int)}
397c478bd9Sstevel@tonic-gate  for {set i 1} {$i<=10} {incr i} {
407c478bd9Sstevel@tonic-gate    set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
417c478bd9Sstevel@tonic-gate    execsql $sql
427c478bd9Sstevel@tonic-gate  }
437c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1}
447c478bd9Sstevel@tonic-gate} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
457c478bd9Sstevel@tonic-gate
467c478bd9Sstevel@tonic-gate# Unknown column name in an expression
477c478bd9Sstevel@tonic-gate#
487c478bd9Sstevel@tonic-gatedo_test update-3.2 {
497c478bd9Sstevel@tonic-gate  set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
507c478bd9Sstevel@tonic-gate  lappend v $msg
517c478bd9Sstevel@tonic-gate} {1 {no such column: f3}}
527c478bd9Sstevel@tonic-gatedo_test update-3.3 {
537c478bd9Sstevel@tonic-gate  set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
547c478bd9Sstevel@tonic-gate  lappend v $msg
557c478bd9Sstevel@tonic-gate} {1 {no such column: test2.f1}}
567c478bd9Sstevel@tonic-gatedo_test update-3.4 {
577c478bd9Sstevel@tonic-gate  set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
587c478bd9Sstevel@tonic-gate  lappend v $msg
597c478bd9Sstevel@tonic-gate} {1 {no such column: f3}}
607c478bd9Sstevel@tonic-gate
617c478bd9Sstevel@tonic-gate# Actually do some updates
627c478bd9Sstevel@tonic-gate#
637c478bd9Sstevel@tonic-gatedo_test update-3.5 {
647c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2*3}
657c478bd9Sstevel@tonic-gate} {}
667c478bd9Sstevel@tonic-gatedo_test update-3.6 {
677c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1}
687c478bd9Sstevel@tonic-gate} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
697c478bd9Sstevel@tonic-gatedo_test update-3.7 {
707c478bd9Sstevel@tonic-gate  execsql {PRAGMA count_changes=on}
717c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
727c478bd9Sstevel@tonic-gate} {5}
737c478bd9Sstevel@tonic-gatedo_test update-3.8 {
747c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1}
757c478bd9Sstevel@tonic-gate} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072}
767c478bd9Sstevel@tonic-gatedo_test update-3.9 {
777c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
787c478bd9Sstevel@tonic-gate} {5}
797c478bd9Sstevel@tonic-gatedo_test update-3.10 {
807c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1}
817c478bd9Sstevel@tonic-gate} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
827c478bd9Sstevel@tonic-gate
837c478bd9Sstevel@tonic-gate# Swap the values of f1 and f2 for all elements
847c478bd9Sstevel@tonic-gate#
857c478bd9Sstevel@tonic-gatedo_test update-3.11 {
867c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET F2=f1, F1=f2}
877c478bd9Sstevel@tonic-gate} {10}
887c478bd9Sstevel@tonic-gatedo_test update-3.12 {
897c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY F1}
907c478bd9Sstevel@tonic-gate} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}
917c478bd9Sstevel@tonic-gatedo_test update-3.13 {
927c478bd9Sstevel@tonic-gate  execsql {PRAGMA count_changes=off}
937c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET F2=f1, F1=f2}
947c478bd9Sstevel@tonic-gate} {}
957c478bd9Sstevel@tonic-gatedo_test update-3.14 {
967c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY F1}
977c478bd9Sstevel@tonic-gate} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
987c478bd9Sstevel@tonic-gate
997c478bd9Sstevel@tonic-gate# Create duplicate entries and make sure updating still
1007c478bd9Sstevel@tonic-gate# works.
1017c478bd9Sstevel@tonic-gate#
1027c478bd9Sstevel@tonic-gatedo_test update-4.0 {
1037c478bd9Sstevel@tonic-gate  execsql {
1047c478bd9Sstevel@tonic-gate    DELETE FROM test1 WHERE f1<=5;
1057c478bd9Sstevel@tonic-gate    INSERT INTO test1(f1,f2) VALUES(8,88);
1067c478bd9Sstevel@tonic-gate    INSERT INTO test1(f1,f2) VALUES(8,888);
1077c478bd9Sstevel@tonic-gate    INSERT INTO test1(f1,f2) VALUES(77,128);
1087c478bd9Sstevel@tonic-gate    INSERT INTO test1(f1,f2) VALUES(777,128);
1097c478bd9Sstevel@tonic-gate  }
1107c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1117c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1127c478bd9Sstevel@tonic-gatedo_test update-4.1 {
1137c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1147c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1157c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1167c478bd9Sstevel@tonic-gatedo_test update-4.2 {
1177c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1187c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1197c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1207c478bd9Sstevel@tonic-gatedo_test update-4.3 {
1217c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1227c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1237c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1247c478bd9Sstevel@tonic-gatedo_test update-4.4 {
1257c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1267c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1277c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1287c478bd9Sstevel@tonic-gatedo_test update-4.5 {
1297c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1307c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1317c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1327c478bd9Sstevel@tonic-gatedo_test update-4.6 {
1337c478bd9Sstevel@tonic-gate  execsql {
1347c478bd9Sstevel@tonic-gate    PRAGMA count_changes=on;
1357c478bd9Sstevel@tonic-gate    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
1367c478bd9Sstevel@tonic-gate  }
1377c478bd9Sstevel@tonic-gate} {2}
1387c478bd9Sstevel@tonic-gatedo_test update-4.7 {
1397c478bd9Sstevel@tonic-gate  execsql {
1407c478bd9Sstevel@tonic-gate    PRAGMA count_changes=off;
1417c478bd9Sstevel@tonic-gate    SELECT * FROM test1 ORDER BY f1,f2
1427c478bd9Sstevel@tonic-gate  }
1437c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1447c478bd9Sstevel@tonic-gate
1457c478bd9Sstevel@tonic-gate# Repeat the previous sequence of tests with an index.
1467c478bd9Sstevel@tonic-gate#
1477c478bd9Sstevel@tonic-gatedo_test update-5.0 {
1487c478bd9Sstevel@tonic-gate  execsql {CREATE INDEX idx1 ON test1(f1)}
1497c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1507c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1517c478bd9Sstevel@tonic-gatedo_test update-5.1 {
1527c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1537c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1547c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1557c478bd9Sstevel@tonic-gatedo_test update-5.2 {
1567c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1577c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1587c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1597c478bd9Sstevel@tonic-gatedo_test update-5.3 {
1607c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1617c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1627c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1637c478bd9Sstevel@tonic-gatedo_test update-5.4 {
1647c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1657c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1667c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1677c478bd9Sstevel@tonic-gatedo_test update-5.4.1 {
1687c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1697c478bd9Sstevel@tonic-gate} {78 128}
1707c478bd9Sstevel@tonic-gatedo_test update-5.4.2 {
1717c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1727c478bd9Sstevel@tonic-gate} {778 128}
1737c478bd9Sstevel@tonic-gatedo_test update-5.4.3 {
1747c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1757c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
1767c478bd9Sstevel@tonic-gatedo_test update-5.5 {
1777c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1787c478bd9Sstevel@tonic-gate} {}
1797c478bd9Sstevel@tonic-gatedo_test update-5.5.1 {
1807c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
1817c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1827c478bd9Sstevel@tonic-gatedo_test update-5.5.2 {
1837c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1847c478bd9Sstevel@tonic-gate} {78 128}
1857c478bd9Sstevel@tonic-gatedo_test update-5.5.3 {
1867c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1877c478bd9Sstevel@tonic-gate} {}
1887c478bd9Sstevel@tonic-gatedo_test update-5.5.4 {
1897c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1907c478bd9Sstevel@tonic-gate} {777 128}
1917c478bd9Sstevel@tonic-gatedo_test update-5.5.5 {
1927c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1937c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
1947c478bd9Sstevel@tonic-gatedo_test update-5.6 {
1957c478bd9Sstevel@tonic-gate  execsql {
1967c478bd9Sstevel@tonic-gate    PRAGMA count_changes=on;
1977c478bd9Sstevel@tonic-gate    UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
1987c478bd9Sstevel@tonic-gate  }
1997c478bd9Sstevel@tonic-gate} {2}
2007c478bd9Sstevel@tonic-gatedo_test update-5.6.1 {
2017c478bd9Sstevel@tonic-gate  execsql {
2027c478bd9Sstevel@tonic-gate    PRAGMA count_changes=off;
2037c478bd9Sstevel@tonic-gate    SELECT * FROM test1 ORDER BY f1,f2
2047c478bd9Sstevel@tonic-gate  }
2057c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
2067c478bd9Sstevel@tonic-gatedo_test update-5.6.2 {
2077c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
2087c478bd9Sstevel@tonic-gate} {77 128}
2097c478bd9Sstevel@tonic-gatedo_test update-5.6.3 {
2107c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
2117c478bd9Sstevel@tonic-gate} {}
2127c478bd9Sstevel@tonic-gatedo_test update-5.6.4 {
2137c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
2147c478bd9Sstevel@tonic-gate} {777 128}
2157c478bd9Sstevel@tonic-gatedo_test update-5.6.5 {
2167c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
2177c478bd9Sstevel@tonic-gate} {8 88 8 256 8 888}
2187c478bd9Sstevel@tonic-gate
2197c478bd9Sstevel@tonic-gate# Repeat the previous sequence of tests with a different index.
2207c478bd9Sstevel@tonic-gate#
2217c478bd9Sstevel@tonic-gateexecsql {PRAGMA synchronous=FULL}
2227c478bd9Sstevel@tonic-gatedo_test update-6.0 {
2237c478bd9Sstevel@tonic-gate  execsql {DROP INDEX idx1}
2247c478bd9Sstevel@tonic-gate  execsql {CREATE INDEX idx1 ON test1(f2)}
2257c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2267c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
2277c478bd9Sstevel@tonic-gatedo_test update-6.1 {
2287c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
2297c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2307c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
2317c478bd9Sstevel@tonic-gatedo_test update-6.1.1 {
2327c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
2337c478bd9Sstevel@tonic-gate} {8 89 8 257 8 889}
2347c478bd9Sstevel@tonic-gatedo_test update-6.1.2 {
2357c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
2367c478bd9Sstevel@tonic-gate} {8 89}
2377c478bd9Sstevel@tonic-gatedo_test update-6.1.3 {
2387c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
2397c478bd9Sstevel@tonic-gate} {}
2407c478bd9Sstevel@tonic-gatedo_test update-6.2 {
2417c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
2427c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2437c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
2447c478bd9Sstevel@tonic-gatedo_test update-6.3 {
2457c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
2467c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2477c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
2487c478bd9Sstevel@tonic-gatedo_test update-6.3.1 {
2497c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
2507c478bd9Sstevel@tonic-gate} {8 88 8 256 8 888}
2517c478bd9Sstevel@tonic-gatedo_test update-6.3.2 {
2527c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
2537c478bd9Sstevel@tonic-gate} {}
2547c478bd9Sstevel@tonic-gatedo_test update-6.3.3 {
2557c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
2567c478bd9Sstevel@tonic-gate} {8 88}
2577c478bd9Sstevel@tonic-gatedo_test update-6.4 {
2587c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
2597c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2607c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
2617c478bd9Sstevel@tonic-gatedo_test update-6.4.1 {
2627c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
2637c478bd9Sstevel@tonic-gate} {78 128}
2647c478bd9Sstevel@tonic-gatedo_test update-6.4.2 {
2657c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
2667c478bd9Sstevel@tonic-gate} {778 128}
2677c478bd9Sstevel@tonic-gatedo_test update-6.4.3 {
2687c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
2697c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
2707c478bd9Sstevel@tonic-gatedo_test update-6.5 {
2717c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
2727c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2737c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
2747c478bd9Sstevel@tonic-gatedo_test update-6.5.1 {
2757c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
2767c478bd9Sstevel@tonic-gate} {78 128}
2777c478bd9Sstevel@tonic-gatedo_test update-6.5.2 {
2787c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
2797c478bd9Sstevel@tonic-gate} {}
2807c478bd9Sstevel@tonic-gatedo_test update-6.5.3 {
2817c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
2827c478bd9Sstevel@tonic-gate} {777 128}
2837c478bd9Sstevel@tonic-gatedo_test update-6.5.4 {
2847c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
2857c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
2867c478bd9Sstevel@tonic-gatedo_test update-6.6 {
2877c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
2887c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
2897c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
2907c478bd9Sstevel@tonic-gatedo_test update-6.6.1 {
2917c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
2927c478bd9Sstevel@tonic-gate} {77 128}
2937c478bd9Sstevel@tonic-gatedo_test update-6.6.2 {
2947c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
2957c478bd9Sstevel@tonic-gate} {}
2967c478bd9Sstevel@tonic-gatedo_test update-6.6.3 {
2977c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
2987c478bd9Sstevel@tonic-gate} {777 128}
2997c478bd9Sstevel@tonic-gatedo_test update-6.6.4 {
3007c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3017c478bd9Sstevel@tonic-gate} {8 88 8 256 8 888}
3027c478bd9Sstevel@tonic-gate
3037c478bd9Sstevel@tonic-gate# Repeat the previous sequence of tests with multiple
3047c478bd9Sstevel@tonic-gate# indices
3057c478bd9Sstevel@tonic-gate#
3067c478bd9Sstevel@tonic-gatedo_test update-7.0 {
3077c478bd9Sstevel@tonic-gate  execsql {CREATE INDEX idx2 ON test1(f2)}
3087c478bd9Sstevel@tonic-gate  execsql {CREATE INDEX idx3 ON test1(f1,f2)}
3097c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3107c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
3117c478bd9Sstevel@tonic-gatedo_test update-7.1 {
3127c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
3137c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3147c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
3157c478bd9Sstevel@tonic-gatedo_test update-7.1.1 {
3167c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3177c478bd9Sstevel@tonic-gate} {8 89 8 257 8 889}
3187c478bd9Sstevel@tonic-gatedo_test update-7.1.2 {
3197c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
3207c478bd9Sstevel@tonic-gate} {8 89}
3217c478bd9Sstevel@tonic-gatedo_test update-7.1.3 {
3227c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
3237c478bd9Sstevel@tonic-gate} {}
3247c478bd9Sstevel@tonic-gatedo_test update-7.2 {
3257c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
3267c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3277c478bd9Sstevel@tonic-gate} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
3287c478bd9Sstevel@tonic-gatedo_test update-7.3 {
3297c478bd9Sstevel@tonic-gate  # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
3307c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
3317c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3327c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
3337c478bd9Sstevel@tonic-gatedo_test update-7.3.1 {
3347c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3357c478bd9Sstevel@tonic-gate} {8 88 8 256 8 888}
3367c478bd9Sstevel@tonic-gatedo_test update-7.3.2 {
3377c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
3387c478bd9Sstevel@tonic-gate} {}
3397c478bd9Sstevel@tonic-gatedo_test update-7.3.3 {
3407c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
3417c478bd9Sstevel@tonic-gate} {8 88}
3427c478bd9Sstevel@tonic-gatedo_test update-7.4 {
3437c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
3447c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3457c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
3467c478bd9Sstevel@tonic-gatedo_test update-7.4.1 {
3477c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
3487c478bd9Sstevel@tonic-gate} {78 128}
3497c478bd9Sstevel@tonic-gatedo_test update-7.4.2 {
3507c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
3517c478bd9Sstevel@tonic-gate} {778 128}
3527c478bd9Sstevel@tonic-gatedo_test update-7.4.3 {
3537c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3547c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
3557c478bd9Sstevel@tonic-gatedo_test update-7.5 {
3567c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
3577c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3587c478bd9Sstevel@tonic-gate} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
3597c478bd9Sstevel@tonic-gatedo_test update-7.5.1 {
3607c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
3617c478bd9Sstevel@tonic-gate} {78 128}
3627c478bd9Sstevel@tonic-gatedo_test update-7.5.2 {
3637c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
3647c478bd9Sstevel@tonic-gate} {}
3657c478bd9Sstevel@tonic-gatedo_test update-7.5.3 {
3667c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
3677c478bd9Sstevel@tonic-gate} {777 128}
3687c478bd9Sstevel@tonic-gatedo_test update-7.5.4 {
3697c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3707c478bd9Sstevel@tonic-gate} {8 88 8 128 8 256 8 888}
3717c478bd9Sstevel@tonic-gatedo_test update-7.6 {
3727c478bd9Sstevel@tonic-gate  execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
3737c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 ORDER BY f1,f2}
3747c478bd9Sstevel@tonic-gate} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
3757c478bd9Sstevel@tonic-gatedo_test update-7.6.1 {
3767c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
3777c478bd9Sstevel@tonic-gate} {77 128}
3787c478bd9Sstevel@tonic-gatedo_test update-7.6.2 {
3797c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
3807c478bd9Sstevel@tonic-gate} {}
3817c478bd9Sstevel@tonic-gatedo_test update-7.6.3 {
3827c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
3837c478bd9Sstevel@tonic-gate} {777 128}
3847c478bd9Sstevel@tonic-gatedo_test update-7.6.4 {
3857c478bd9Sstevel@tonic-gate  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
3867c478bd9Sstevel@tonic-gate} {8 88 8 256 8 888}
3877c478bd9Sstevel@tonic-gate
3887c478bd9Sstevel@tonic-gate# Error messages
3897c478bd9Sstevel@tonic-gate#
3907c478bd9Sstevel@tonic-gatedo_test update-9.1 {
3917c478bd9Sstevel@tonic-gate  set v [catch {execsql {
3927c478bd9Sstevel@tonic-gate    UPDATE test1 SET x=11 WHERE f1=1025
3937c478bd9Sstevel@tonic-gate  }} msg]
3947c478bd9Sstevel@tonic-gate  lappend v $msg
3957c478bd9Sstevel@tonic-gate} {1 {no such column: x}}
3967c478bd9Sstevel@tonic-gatedo_test update-9.2 {
3977c478bd9Sstevel@tonic-gate  set v [catch {execsql {
3987c478bd9Sstevel@tonic-gate    UPDATE test1 SET f1=x(11) WHERE f1=1025
3997c478bd9Sstevel@tonic-gate  }} msg]
4007c478bd9Sstevel@tonic-gate  lappend v $msg
4017c478bd9Sstevel@tonic-gate} {1 {no such function: x}}
4027c478bd9Sstevel@tonic-gatedo_test update-9.3 {
4037c478bd9Sstevel@tonic-gate  set v [catch {execsql {
4047c478bd9Sstevel@tonic-gate    UPDATE test1 SET f1=11 WHERE x=1025
4057c478bd9Sstevel@tonic-gate  }} msg]
4067c478bd9Sstevel@tonic-gate  lappend v $msg
4077c478bd9Sstevel@tonic-gate} {1 {no such column: x}}
4087c478bd9Sstevel@tonic-gatedo_test update-9.4 {
4097c478bd9Sstevel@tonic-gate  set v [catch {execsql {
4107c478bd9Sstevel@tonic-gate    UPDATE test1 SET f1=11 WHERE x(f1)=1025
4117c478bd9Sstevel@tonic-gate  }} msg]
4127c478bd9Sstevel@tonic-gate  lappend v $msg
4137c478bd9Sstevel@tonic-gate} {1 {no such function: x}}
4147c478bd9Sstevel@tonic-gate
4157c478bd9Sstevel@tonic-gate# Try doing updates on a unique column where the value does not
4167c478bd9Sstevel@tonic-gate# really change.
4177c478bd9Sstevel@tonic-gate#
4187c478bd9Sstevel@tonic-gatedo_test update-10.1 {
4197c478bd9Sstevel@tonic-gate  execsql {
4207c478bd9Sstevel@tonic-gate    DROP TABLE test1;
4217c478bd9Sstevel@tonic-gate    CREATE TABLE t1(
4227c478bd9Sstevel@tonic-gate       a integer primary key,
423*1da57d55SToomas Soome       b UNIQUE,
4247c478bd9Sstevel@tonic-gate       c, d,
4257c478bd9Sstevel@tonic-gate       e, f,
4267c478bd9Sstevel@tonic-gate       UNIQUE(c,d)
4277c478bd9Sstevel@tonic-gate    );
4287c478bd9Sstevel@tonic-gate    INSERT INTO t1 VALUES(1,2,3,4,5,6);
4297c478bd9Sstevel@tonic-gate    INSERT INTO t1 VALUES(2,3,4,4,6,7);
4307c478bd9Sstevel@tonic-gate    SELECT * FROM t1
4317c478bd9Sstevel@tonic-gate  }
4327c478bd9Sstevel@tonic-gate} {1 2 3 4 5 6 2 3 4 4 6 7}
4337c478bd9Sstevel@tonic-gatedo_test update-10.2 {
4347c478bd9Sstevel@tonic-gate  catchsql {
4357c478bd9Sstevel@tonic-gate    UPDATE t1 SET a=1, e=9 WHERE f=6;
4367c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4377c478bd9Sstevel@tonic-gate  }
4387c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
4397c478bd9Sstevel@tonic-gatedo_test update-10.3 {
4407c478bd9Sstevel@tonic-gate  catchsql {
4417c478bd9Sstevel@tonic-gate    UPDATE t1 SET a=1, e=10 WHERE f=7;
4427c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4437c478bd9Sstevel@tonic-gate  }
4447c478bd9Sstevel@tonic-gate} {1 {PRIMARY KEY must be unique}}
4457c478bd9Sstevel@tonic-gatedo_test update-10.4 {
4467c478bd9Sstevel@tonic-gate  catchsql {
4477c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4487c478bd9Sstevel@tonic-gate  }
4497c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
4507c478bd9Sstevel@tonic-gatedo_test update-10.5 {
4517c478bd9Sstevel@tonic-gate  catchsql {
4527c478bd9Sstevel@tonic-gate    UPDATE t1 SET b=2, e=11 WHERE f=6;
4537c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4547c478bd9Sstevel@tonic-gate  }
4557c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
4567c478bd9Sstevel@tonic-gatedo_test update-10.6 {
4577c478bd9Sstevel@tonic-gate  catchsql {
4587c478bd9Sstevel@tonic-gate    UPDATE t1 SET b=2, e=12 WHERE f=7;
4597c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4607c478bd9Sstevel@tonic-gate  }
4617c478bd9Sstevel@tonic-gate} {1 {column b is not unique}}
4627c478bd9Sstevel@tonic-gatedo_test update-10.7 {
4637c478bd9Sstevel@tonic-gate  catchsql {
4647c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4657c478bd9Sstevel@tonic-gate  }
4667c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
4677c478bd9Sstevel@tonic-gatedo_test update-10.8 {
4687c478bd9Sstevel@tonic-gate  catchsql {
4697c478bd9Sstevel@tonic-gate    UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
4707c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4717c478bd9Sstevel@tonic-gate  }
4727c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
4737c478bd9Sstevel@tonic-gatedo_test update-10.9 {
4747c478bd9Sstevel@tonic-gate  catchsql {
4757c478bd9Sstevel@tonic-gate    UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
4767c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4777c478bd9Sstevel@tonic-gate  }
4787c478bd9Sstevel@tonic-gate} {1 {columns c, d are not unique}}
4797c478bd9Sstevel@tonic-gatedo_test update-10.10 {
4807c478bd9Sstevel@tonic-gate  catchsql {
4817c478bd9Sstevel@tonic-gate    SELECT * FROM t1;
4827c478bd9Sstevel@tonic-gate  }
4837c478bd9Sstevel@tonic-gate} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
4847c478bd9Sstevel@tonic-gate
4857c478bd9Sstevel@tonic-gate# Make sure we can handle a subquery in the where clause.
4867c478bd9Sstevel@tonic-gate#
4877c478bd9Sstevel@tonic-gatedo_test update-11.1 {
4887c478bd9Sstevel@tonic-gate  execsql {
4897c478bd9Sstevel@tonic-gate    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
4907c478bd9Sstevel@tonic-gate    SELECT b,e FROM t1;
4917c478bd9Sstevel@tonic-gate  }
4927c478bd9Sstevel@tonic-gate} {2 14 3 7}
4937c478bd9Sstevel@tonic-gatedo_test update-11.2 {
4947c478bd9Sstevel@tonic-gate  execsql {
4957c478bd9Sstevel@tonic-gate    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
4967c478bd9Sstevel@tonic-gate    SELECT a,e FROM t1;
4977c478bd9Sstevel@tonic-gate  }
4987c478bd9Sstevel@tonic-gate} {1 15 2 8}
4997c478bd9Sstevel@tonic-gate
5007c478bd9Sstevel@tonic-gateintegrity_check update-12.1
5017c478bd9Sstevel@tonic-gate
5027c478bd9Sstevel@tonic-gate# Ticket 602.  Updates should occur in the same order as the records
5037c478bd9Sstevel@tonic-gate# were discovered in the WHERE clause.
5047c478bd9Sstevel@tonic-gate#
5057c478bd9Sstevel@tonic-gatedo_test update-13.1 {
5067c478bd9Sstevel@tonic-gate  execsql {
5077c478bd9Sstevel@tonic-gate    BEGIN;
5087c478bd9Sstevel@tonic-gate    CREATE TABLE t2(a);
5097c478bd9Sstevel@tonic-gate    INSERT INTO t2 VALUES(1);
5107c478bd9Sstevel@tonic-gate    INSERT INTO t2 VALUES(2);
5117c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+2 FROM t2;
5127c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+4 FROM t2;
5137c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+8 FROM t2;
5147c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+16 FROM t2;
5157c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+32 FROM t2;
5167c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+64 FROM t2;
5177c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+128 FROM t2;
5187c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+256 FROM t2;
5197c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+512 FROM t2;
5207c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+1024 FROM t2;
5217c478bd9Sstevel@tonic-gate    COMMIT;
5227c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2;
5237c478bd9Sstevel@tonic-gate  }
5247c478bd9Sstevel@tonic-gate} {2048}
5257c478bd9Sstevel@tonic-gatedo_test update-13.2 {
5267c478bd9Sstevel@tonic-gate  execsql {
5277c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2 WHERE a=rowid;
5287c478bd9Sstevel@tonic-gate  }
5297c478bd9Sstevel@tonic-gate} {2048}
5307c478bd9Sstevel@tonic-gatedo_test update-13.3 {
5317c478bd9Sstevel@tonic-gate  execsql {
5327c478bd9Sstevel@tonic-gate    UPDATE t2 SET rowid=rowid-1;
5337c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2 WHERE a=rowid+1;
5347c478bd9Sstevel@tonic-gate  }
5357c478bd9Sstevel@tonic-gate} {2048}
5367c478bd9Sstevel@tonic-gatedo_test update-13.3 {
5377c478bd9Sstevel@tonic-gate  execsql {
5387c478bd9Sstevel@tonic-gate    UPDATE t2 SET rowid=rowid+10000;
5397c478bd9Sstevel@tonic-gate    UPDATE t2 SET rowid=rowid-9999;
5407c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2 WHERE a=rowid;
5417c478bd9Sstevel@tonic-gate  }
5427c478bd9Sstevel@tonic-gate} {2048}
5437c478bd9Sstevel@tonic-gatedo_test update-13.4 {
5447c478bd9Sstevel@tonic-gate  execsql {
5457c478bd9Sstevel@tonic-gate    BEGIN;
5467c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+2048 FROM t2;
5477c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+4096 FROM t2;
5487c478bd9Sstevel@tonic-gate    INSERT INTO t2 SELECT a+8192 FROM t2;
5497c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2 WHERE a=rowid;
5507c478bd9Sstevel@tonic-gate    COMMIT;
5517c478bd9Sstevel@tonic-gate  }
5527c478bd9Sstevel@tonic-gate} 16384
5537c478bd9Sstevel@tonic-gatedo_test update-13.5 {
5547c478bd9Sstevel@tonic-gate  execsql {
5557c478bd9Sstevel@tonic-gate    UPDATE t2 SET rowid=rowid-1;
5567c478bd9Sstevel@tonic-gate    SELECT count(*) FROM t2 WHERE a=rowid+1;
5577c478bd9Sstevel@tonic-gate  }
5587c478bd9Sstevel@tonic-gate} 16384
5597c478bd9Sstevel@tonic-gate
5607c478bd9Sstevel@tonic-gateintegrity_check update-13.6
5617c478bd9Sstevel@tonic-gate
5627c478bd9Sstevel@tonic-gate
5637c478bd9Sstevel@tonic-gatefinish_test
564