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