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.10 2002/07/16 17:22:51 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 {int(pow(2,$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.6 { 66 execsql {SELECT * FROM test1 ORDER BY f1} 67} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072} 68do_test update-3.7 { 69 execsql {PRAGMA count_changes=on} 70 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5} 71} {5} 72do_test update-3.8 { 73 execsql {SELECT * FROM test1 ORDER BY f1} 74} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072} 75do_test update-3.9 { 76 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5} 77} {5} 78do_test update-3.10 { 79 execsql {SELECT * FROM test1 ORDER BY f1} 80} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 81 82# Swap the values of f1 and f2 for all elements 83# 84do_test update-3.11 { 85 execsql {UPDATE test1 SET F2=f1, F1=f2} 86} {10} 87do_test update-3.12 { 88 execsql {SELECT * FROM test1 ORDER BY F1} 89} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10} 90do_test update-3.13 { 91 execsql {PRAGMA count_changes=off} 92 execsql {UPDATE test1 SET F2=f1, F1=f2} 93} {} 94do_test update-3.14 { 95 execsql {SELECT * FROM test1 ORDER BY F1} 96} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} 97 98# Create duplicate entries and make sure updating still 99# works. 100# 101do_test update-4.0 { 102 execsql { 103 DELETE FROM test1 WHERE f1<=5; 104 INSERT INTO test1(f1,f2) VALUES(8,88); 105 INSERT INTO test1(f1,f2) VALUES(8,888); 106 INSERT INTO test1(f1,f2) VALUES(77,128); 107 INSERT INTO test1(f1,f2) VALUES(777,128); 108 } 109 execsql {SELECT * FROM test1 ORDER BY f1,f2} 110} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 111do_test update-4.1 { 112 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 113 execsql {SELECT * FROM test1 ORDER BY f1,f2} 114} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 115do_test update-4.2 { 116 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 117 execsql {SELECT * FROM test1 ORDER BY f1,f2} 118} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 119do_test update-4.3 { 120 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 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.4 { 124 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 125 execsql {SELECT * FROM test1 ORDER BY f1,f2} 126} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 127do_test update-4.5 { 128 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 129 execsql {SELECT * FROM test1 ORDER BY f1,f2} 130} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 131do_test update-4.6 { 132 execsql { 133 PRAGMA count_changes=on; 134 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; 135 } 136} {2} 137do_test update-4.7 { 138 execsql { 139 PRAGMA count_changes=off; 140 SELECT * FROM test1 ORDER BY f1,f2 141 } 142} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 143 144# Repeat the previous sequence of tests with an index. 145# 146do_test update-5.0 { 147 execsql {CREATE INDEX idx1 ON test1(f1)} 148 execsql {SELECT * FROM test1 ORDER BY f1,f2} 149} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 150do_test update-5.1 { 151 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 152 execsql {SELECT * FROM test1 ORDER BY f1,f2} 153} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 154do_test update-5.2 { 155 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 156 execsql {SELECT * FROM test1 ORDER BY f1,f2} 157} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 158do_test update-5.3 { 159 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 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.4 { 163 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 164 execsql {SELECT * FROM test1 ORDER BY f1,f2} 165} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 166do_test update-5.4.1 { 167 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 168} {78 128} 169do_test update-5.4.2 { 170 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 171} {778 128} 172do_test update-5.4.3 { 173 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 174} {8 88 8 128 8 256 8 888} 175do_test update-5.5 { 176 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 177} {} 178do_test update-5.5.1 { 179 execsql {SELECT * FROM test1 ORDER BY f1,f2} 180} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} 181do_test update-5.5.2 { 182 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 183} {78 128} 184do_test update-5.5.3 { 185 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 186} {} 187do_test update-5.5.4 { 188 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 189} {777 128} 190do_test update-5.5.5 { 191 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 192} {8 88 8 128 8 256 8 888} 193do_test update-5.6 { 194 execsql { 195 PRAGMA count_changes=on; 196 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; 197 } 198} {2} 199do_test update-5.6.1 { 200 execsql { 201 PRAGMA count_changes=off; 202 SELECT * FROM test1 ORDER BY f1,f2 203 } 204} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 205do_test update-5.6.2 { 206 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 207} {77 128} 208do_test update-5.6.3 { 209 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 210} {} 211do_test update-5.6.4 { 212 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 213} {777 128} 214do_test update-5.6.5 { 215 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 216} {8 88 8 256 8 888} 217 218# Repeat the previous sequence of tests with a different index. 219# 220do_test update-6.0 { 221 execsql {DROP INDEX idx1} 222 execsql {CREATE INDEX idx1 ON test1(f2)} 223 execsql {SELECT * FROM test1 ORDER BY f1,f2} 224} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 225do_test update-6.1 { 226 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 227 execsql {SELECT * FROM test1 ORDER BY f1,f2} 228} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 229do_test update-6.1.1 { 230 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 231} {8 89 8 257 8 889} 232do_test update-6.1.2 { 233 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 234} {8 89} 235do_test update-6.1.3 { 236 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 237} {} 238do_test update-6.2 { 239 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 240 execsql {SELECT * FROM test1 ORDER BY f1,f2} 241} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 242do_test update-6.3 { 243 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 244 execsql {SELECT * FROM test1 ORDER BY f1,f2} 245} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 246do_test update-6.3.1 { 247 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 248} {8 88 8 256 8 888} 249do_test update-6.3.2 { 250 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 251} {} 252do_test update-6.3.3 { 253 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 254} {8 88} 255do_test update-6.4 { 256 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 257 execsql {SELECT * FROM test1 ORDER BY f1,f2} 258} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 259do_test update-6.4.1 { 260 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 261} {78 128} 262do_test update-6.4.2 { 263 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 264} {778 128} 265do_test update-6.4.3 { 266 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 267} {8 88 8 128 8 256 8 888} 268do_test update-6.5 { 269 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and 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 777 128} 272do_test update-6.5.1 { 273 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 274} {78 128} 275do_test update-6.5.2 { 276 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 277} {} 278do_test update-6.5.3 { 279 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 280} {777 128} 281do_test update-6.5.4 { 282 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 283} {8 88 8 128 8 256 8 888} 284do_test update-6.6 { 285 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} 286 execsql {SELECT * FROM test1 ORDER BY f1,f2} 287} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 288do_test update-6.6.1 { 289 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 290} {77 128} 291do_test update-6.6.2 { 292 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 293} {} 294do_test update-6.6.3 { 295 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 296} {777 128} 297do_test update-6.6.4 { 298 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 299} {8 88 8 256 8 888} 300 301# Repeat the previous sequence of tests with multiple 302# indices 303# 304do_test update-7.0 { 305 execsql {CREATE INDEX idx2 ON test1(f2)} 306 execsql {CREATE INDEX idx3 ON test1(f1,f2)} 307 execsql {SELECT * FROM test1 ORDER BY f1,f2} 308} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 309do_test update-7.1 { 310 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} 311 execsql {SELECT * FROM test1 ORDER BY f1,f2} 312} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} 313do_test update-7.1.1 { 314 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 315} {8 89 8 257 8 889} 316do_test update-7.1.2 { 317 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 318} {8 89} 319do_test update-7.1.3 { 320 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 321} {} 322do_test update-7.2 { 323 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} 324 execsql {SELECT * FROM test1 ORDER BY f1,f2} 325} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} 326do_test update-7.3 { 327 # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300} 328 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} 329 execsql {SELECT * FROM test1 ORDER BY f1,f2} 330} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 331do_test update-7.3.1 { 332 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 333} {8 88 8 256 8 888} 334do_test update-7.3.2 { 335 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 336} {} 337do_test update-7.3.3 { 338 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 339} {8 88} 340do_test update-7.4 { 341 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} 342 execsql {SELECT * FROM test1 ORDER BY f1,f2} 343} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} 344do_test update-7.4.1 { 345 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 346} {78 128} 347do_test update-7.4.2 { 348 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 349} {778 128} 350do_test update-7.4.3 { 351 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 352} {8 88 8 128 8 256 8 888} 353do_test update-7.5 { 354 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and 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 777 128} 357do_test update-7.5.1 { 358 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 359} {78 128} 360do_test update-7.5.2 { 361 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 362} {} 363do_test update-7.5.3 { 364 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 365} {777 128} 366do_test update-7.5.4 { 367 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 368} {8 88 8 128 8 256 8 888} 369do_test update-7.6 { 370 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} 371 execsql {SELECT * FROM test1 ORDER BY f1,f2} 372} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} 373do_test update-7.6.1 { 374 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 375} {77 128} 376do_test update-7.6.2 { 377 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 378} {} 379do_test update-7.6.3 { 380 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 381} {777 128} 382do_test update-7.6.4 { 383 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 384} {8 88 8 256 8 888} 385 386# Error messages 387# 388do_test update-9.1 { 389 set v [catch {execsql { 390 UPDATE test1 SET x=11 WHERE f1=1025 391 }} msg] 392 lappend v $msg 393} {1 {no such column: x}} 394do_test update-9.2 { 395 set v [catch {execsql { 396 UPDATE test1 SET f1=x(11) WHERE f1=1025 397 }} msg] 398 lappend v $msg 399} {1 {no such function: x}} 400do_test update-9.3 { 401 set v [catch {execsql { 402 UPDATE test1 SET f1=11 WHERE x=1025 403 }} msg] 404 lappend v $msg 405} {1 {no such column: x}} 406do_test update-9.4 { 407 set v [catch {execsql { 408 UPDATE test1 SET f1=11 WHERE x(f1)=1025 409 }} msg] 410 lappend v $msg 411} {1 {no such function: x}} 412 413# Try doing updates on a unique column where the value does not 414# really change. 415# 416do_test update-10.1 { 417 execsql { 418 DROP TABLE test1; 419 CREATE TABLE t1( 420 a integer primary key, 421 b UNIQUE, 422 c, d, 423 e, f, 424 UNIQUE(c,d) 425 ); 426 INSERT INTO t1 VALUES(1,2,3,4,5,6); 427 INSERT INTO t1 VALUES(2,3,4,4,6,7); 428 SELECT * FROM t1 429 } 430} {1 2 3 4 5 6 2 3 4 4 6 7} 431do_test update-10.2 { 432 catchsql { 433 UPDATE t1 SET a=1, e=9 WHERE f=6; 434 SELECT * FROM t1; 435 } 436} {0 {1 2 3 4 9 6 2 3 4 4 6 7}} 437do_test update-10.3 { 438 catchsql { 439 UPDATE t1 SET a=1, e=10 WHERE f=7; 440 SELECT * FROM t1; 441 } 442} {1 {constraint failed}} 443do_test update-10.4 { 444 catchsql { 445 SELECT * FROM t1; 446 } 447} {0 {1 2 3 4 9 6 2 3 4 4 6 7}} 448do_test update-10.5 { 449 catchsql { 450 UPDATE t1 SET b=2, e=11 WHERE f=6; 451 SELECT * FROM t1; 452 } 453} {0 {1 2 3 4 11 6 2 3 4 4 6 7}} 454do_test update-10.6 { 455 catchsql { 456 UPDATE t1 SET b=2, e=12 WHERE f=7; 457 SELECT * FROM t1; 458 } 459} {1 {constraint failed}} 460do_test update-10.7 { 461 catchsql { 462 SELECT * FROM t1; 463 } 464} {0 {1 2 3 4 11 6 2 3 4 4 6 7}} 465do_test update-10.8 { 466 catchsql { 467 UPDATE t1 SET c=3, d=4, e=13 WHERE f=6; 468 SELECT * FROM t1; 469 } 470} {0 {1 2 3 4 13 6 2 3 4 4 6 7}} 471do_test update-10.9 { 472 catchsql { 473 UPDATE t1 SET c=3, d=4, e=14 WHERE f=7; 474 SELECT * FROM t1; 475 } 476} {1 {constraint failed}} 477do_test update-10.10 { 478 catchsql { 479 SELECT * FROM t1; 480 } 481} {0 {1 2 3 4 13 6 2 3 4 4 6 7}} 482 483# Make sure we can handle a subquery in the where clause. 484# 485do_test update-11.1 { 486 execsql { 487 UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1); 488 SELECT b,e FROM t1; 489 } 490} {2 14 3 7} 491do_test update-11.2 { 492 execsql { 493 UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1); 494 SELECT a,e FROM t1; 495 } 496} {1 15 2 8} 497 498finish_test 499