gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/update.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Try to update an non-existent table 20 # 21 do_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 # 28 do_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 # 36 do_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 # 47 do_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}} 51 do_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}} 55 do_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 # 62 do_test update-3.5 { 63 execsql {UPDATE test1 SET f2=f2*3} 64 } {} 65 do_test update-3.5.1 { 66 db changes 67 } {10} 68 69 # verify that SELECT does not reset the change counter 70 do_test update-3.5.2 { 71 db eval {SELECT count(*) FROM test1} 72 } {10} 73 do_test update-3.5.3 { 74 db changes 75 } {10} 76 77 do_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} 80 do_test update-3.7 { 81 execsql {PRAGMA count_changes=on} 82 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5} 83 } {5} 84 do_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} 87 do_test update-3.9 { 88 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5} 89 } {5} 90 do_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 # 96 do_test update-3.11 { 97 execsql {UPDATE test1 SET F2=f1, F1=f2} 98 } {10} 99 do_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} 102 do_test update-3.13 { 103 execsql {PRAGMA count_changes=off} 104 execsql {UPDATE test1 SET F2=f1, F1=f2} 105 } {} 106 do_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 # 113 do_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} 123 do_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} 127 do_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} 131 do_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} 135 do_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} 139 do_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} 143 do_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} 149 do_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 # 158 do_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} 162 do_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} 166 do_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} 170 do_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} 174 do_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} 178 do_test update-5.4.1 { 179 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 180 } {78 128} 181 do_test update-5.4.2 { 182 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 183 } {778 128} 184 do_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} 187 do_test update-5.5 { 188 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} 189 } {} 190 do_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} 193 do_test update-5.5.2 { 194 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 195 } {78 128} 196 do_test update-5.5.3 { 197 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 198 } {} 199 do_test update-5.5.4 { 200 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 201 } {777 128} 202 do_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} 205 do_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} 211 do_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} 217 do_test update-5.6.2 { 218 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 219 } {77 128} 220 do_test update-5.6.3 { 221 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 222 } {} 223 do_test update-5.6.4 { 224 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 225 } {777 128} 226 do_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 # 232 execsql {PRAGMA synchronous=FULL} 233 do_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} 238 do_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} 242 do_test update-6.1.1 { 243 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 244 } {8 89 8 257 8 889} 245 do_test update-6.1.2 { 246 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 247 } {8 89} 248 do_test update-6.1.3 { 249 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 250 } {} 251 do_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} 255 do_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} 259 do_test update-6.3.1 { 260 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 261 } {8 88 8 256 8 888} 262 do_test update-6.3.2 { 263 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 264 } {} 265 do_test update-6.3.3 { 266 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 267 } {8 88} 268 do_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} 272 do_test update-6.4.1 { 273 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 274 } {78 128} 275 do_test update-6.4.2 { 276 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 277 } {778 128} 278 do_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} 281 do_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} 285 do_test update-6.5.1 { 286 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 287 } {78 128} 288 do_test update-6.5.2 { 289 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 290 } {} 291 do_test update-6.5.3 { 292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 293 } {777 128} 294 do_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} 297 do_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} 301 do_test update-6.6.1 { 302 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 303 } {77 128} 304 do_test update-6.6.2 { 305 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 306 } {} 307 do_test update-6.6.3 { 308 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 309 } {777 128} 310 do_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 # 317 do_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} 322 do_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} 326 do_test update-7.1.1 { 327 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 328 } {8 89 8 257 8 889} 329 do_test update-7.1.2 { 330 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 331 } {8 89} 332 do_test update-7.1.3 { 333 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} 334 } {} 335 do_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} 339 do_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} 344 do_test update-7.3.1 { 345 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} 346 } {8 88 8 256 8 888} 347 do_test update-7.3.2 { 348 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} 349 } {} 350 do_test update-7.3.3 { 351 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} 352 } {8 88} 353 do_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} 357 do_test update-7.4.1 { 358 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 359 } {78 128} 360 do_test update-7.4.2 { 361 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 362 } {778 128} 363 do_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} 366 do_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} 370 do_test update-7.5.1 { 371 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} 372 } {78 128} 373 do_test update-7.5.2 { 374 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 375 } {} 376 do_test update-7.5.3 { 377 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 378 } {777 128} 379 do_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} 382 do_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} 386 do_test update-7.6.1 { 387 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} 388 } {77 128} 389 do_test update-7.6.2 { 390 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} 391 } {} 392 do_test update-7.6.3 { 393 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} 394 } {777 128} 395 do_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 # 401 do_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}} 407 do_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}} 413 do_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}} 419 do_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 # 429 do_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} 444 do_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}} 450 do_test update-10.3 { 451 catchsql { 452 UPDATE t1 SET a=1, e=10 WHERE f=7; 453 SELECT * FROM t1; 454 } 455 } {1 {UNIQUE constraint failed: t1.a}} 456 do_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}} 461 do_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}} 467 do_test update-10.6 { 468 catchsql { 469 UPDATE t1 SET b=2, e=12 WHERE f=7; 470 SELECT * FROM t1; 471 } 472 } {1 {UNIQUE constraint failed: t1.b}} 473 do_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}} 478 do_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}} 484 do_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 {UNIQUE constraint failed: t1.c, t1.d}} 490 do_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 # 498 ifcapable 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 do_test update-11.3 { 512 execsql { 513 UPDATE t1 AS xyz SET e=e+1 WHERE xyz.a IN (SELECT a FROM t1); 514 SELECT a,e FROM t1; 515 } 516 } {1 16 2 9} 517 do_test update-11.4 { 518 execsql { 519 UPDATE t1 AS xyz SET e=e+1 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.a<xyz.a); 520 SELECT a,e FROM t1; 521 } 522 } {1 16 2 10} 523 } 524 525 integrity_check update-12.1 526 527 # Ticket 602. Updates should occur in the same order as the records 528 # were discovered in the WHERE clause. 529 # 530 do_test update-13.1 { 531 execsql { 532 BEGIN; 533 CREATE TABLE t2(a); 534 INSERT INTO t2 VALUES(1); 535 INSERT INTO t2 VALUES(2); 536 INSERT INTO t2 SELECT a+2 FROM t2; 537 INSERT INTO t2 SELECT a+4 FROM t2; 538 INSERT INTO t2 SELECT a+8 FROM t2; 539 INSERT INTO t2 SELECT a+16 FROM t2; 540 INSERT INTO t2 SELECT a+32 FROM t2; 541 INSERT INTO t2 SELECT a+64 FROM t2; 542 INSERT INTO t2 SELECT a+128 FROM t2; 543 INSERT INTO t2 SELECT a+256 FROM t2; 544 INSERT INTO t2 SELECT a+512 FROM t2; 545 INSERT INTO t2 SELECT a+1024 FROM t2; 546 COMMIT; 547 SELECT count(*) FROM t2; 548 } 549 } {2048} 550 do_test update-13.2 { 551 execsql { 552 SELECT count(*) FROM t2 WHERE a=rowid; 553 } 554 } {2048} 555 do_test update-13.3 { 556 execsql { 557 UPDATE t2 SET rowid=rowid-1; 558 SELECT count(*) FROM t2 WHERE a=rowid+1; 559 } 560 } {2048} 561 do_test update-13.3 { 562 execsql { 563 UPDATE t2 SET rowid=rowid+10000; 564 UPDATE t2 SET rowid=rowid-9999; 565 SELECT count(*) FROM t2 WHERE a=rowid; 566 } 567 } {2048} 568 do_test update-13.4 { 569 execsql { 570 BEGIN; 571 INSERT INTO t2 SELECT a+2048 FROM t2; 572 INSERT INTO t2 SELECT a+4096 FROM t2; 573 INSERT INTO t2 SELECT a+8192 FROM t2; 574 SELECT count(*) FROM t2 WHERE a=rowid; 575 COMMIT; 576 } 577 } 16384 578 do_test update-13.5 { 579 execsql { 580 UPDATE t2 SET rowid=rowid-1; 581 SELECT count(*) FROM t2 WHERE a=rowid+1; 582 } 583 } 16384 584 585 integrity_check update-13.6 586 587 ifcapable {trigger} { 588 # Test for proper detection of malformed WHEN clauses on UPDATE triggers. 589 # 590 do_test update-14.1 { 591 execsql { 592 CREATE TABLE t3(a,b,c); 593 CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN 594 SELECT 'illegal WHEN clause'; 595 END; 596 } 597 } {} 598 do_test update-14.2 { 599 catchsql { 600 UPDATE t3 SET a=1; 601 } 602 } {1 {no such column: nosuchcol}} 603 do_test update-14.3 { 604 execsql { 605 CREATE TABLE t4(a,b,c); 606 CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN 607 SELECT 'illegal WHEN clause'; 608 END; 609 } 610 } {} 611 do_test update-14.4 { 612 catchsql { 613 UPDATE t4 SET a=1; 614 } 615 } {1 {no such column: nosuchcol}} 616 617 } ;# ifcapable {trigger} 618 619 # Ticket [https://www.sqlite.org/src/tktview/43107840f1c02] on 2014-10-29 620 # An assertion fault on UPDATE 621 # 622 ifcapable altertable { 623 do_execsql_test update-15.1 { 624 CREATE TABLE t15(a INTEGER PRIMARY KEY, b); 625 INSERT INTO t15(a,b) VALUES(10,'abc'),(20,'def'),(30,'ghi'); 626 ALTER TABLE t15 ADD COLUMN c; 627 CREATE INDEX t15c ON t15(c); 628 INSERT INTO t15(a,b) 629 VALUES(5,'zyx'),(15,'wvu'),(25,'tsr'),(35,'qpo'); 630 UPDATE t15 SET c=printf('y%d',a) WHERE c IS NULL; 631 SELECT a,b,c,'|' FROM t15 ORDER BY a; 632 } {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |} 633 } 634 635 # Unreleased bug in UPDATE caused by the UPSERT changes. 636 # Found by OSSFuzz as soon as the UPSERT changes landed on trunk. 637 # Never released into the wild. 2018-04-19. 638 # 639 do_execsql_test update-16.1 { 640 CREATE TABLE t16(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE); 641 INSERT INTO t16(a,b) VALUES(1,2),(3,4),(5,6); 642 UPDATE t16 SET a=a; 643 SELECT * FROM t16 ORDER BY +a; 644 } {1 2 3 4 5 6} 645 646 # 2019-12-09 gramfuzz find 647 # If a partial index that does not reference any column of its table (which is you 648 # must admit is a very strange index, but one that is allowed) is used by an UPDATE 649 # statement, void the use of OP_DeferredSeek on the main loop, as the seek will not 650 # be resolved prior to the OP_Delete. 651 # 652 do_execsql_test update-17.10 { 653 DROP TABLE IF EXISTS t1; 654 CREATE TABLE t1(x,y); 655 INSERT INTO t1(x) VALUES(1); 656 CREATE INDEX t1x1 ON t1(1) WHERE 3; 657 UPDATE t1 SET x=2, y=3 WHERE 3; 658 SELECT * FROM t1; 659 } {2 3} 660 661 # 2019-12-22 ticket 5ad2aa6921faa1ee 662 # Make a hard-copy of values that need to be run through OP_RealAffinity 663 # rather than a soft-copy. This is not strictly necessary, but it avoids 664 # a memory-accounting assert(). 665 # 666 reset_db 667 do_execsql_test update-18.10 { 668 PRAGMA encoding = 'UTF16'; 669 CREATE TABLE t0(c0 REAL, c1); 670 INSERT INTO t0(c0,c1) VALUES('xyz',11),('uvw',22); 671 CREATE INDEX i0 ON t0(c1) WHERE c0 GLOB 3; 672 CREATE INDEX i1 ON t0(c0,c1) WHERE typeof(c0)='text' AND typeof(c1)='integer'; 673 UPDATE t0 SET c1=345; 674 SELECT * FROM t0; 675 } {xyz 345 uvw 345} 676 677 # 2019-12-22 ticket c62c5e58524b204d 678 # This is really the same underlying problem as 5ad2aa6921faa1ee 679 # 680 reset_db 681 do_execsql_test update-18.20 { 682 PRAGMA encoding = 'utf16'; 683 CREATE TABLE t0(c0 TEXT); 684 CREATE INDEX i0 ON t0(0 LIKE COALESCE(c0, 0)); 685 INSERT INTO t0(c0) VALUES (0), (0); 686 SELECT * FROM t0; 687 } {0 0} 688 689 # 2019-12-28 assertion fault reported by Yongheng 690 # Similar to ticket ec8abb025e78f40c 691 # An UPDATE was reaching the OP_Delete after running OP_DeferredSeek 692 # without ever hitting an OP_Column. The enhanced solution is to 693 # fix OP_Delete so that it can do the seek itself. 694 # 695 reset_db 696 do_execsql_test update-19.10 { 697 CREATE TABLE t1( 698 a TEXT, 699 b INTEGER PRIMARY KEY UNIQUE 700 ); 701 INSERT INTO t1 VALUES(1,2); 702 UPDATE t1 SET a = quote(b) WHERE b>=2; 703 SELECT * FROM t1; 704 } {2 2} 705 706 # 2019-12-29 ticket https://www.sqlite.org/src/info/314cc133e5ada126 707 # REPLACE conflict resolution during an UPDATE causes a DELETE trigger 708 # to fire. If that DELETE trigger subsequently modifies the row 709 # being updated, bad things can happen. Prevent this by prohibiting 710 # triggers from making changes to the table being updated while doing 711 # REPLACE conflict resolution on the UPDATE. 712 # 713 # See also tickets: 714 # https://www.sqlite.org/src/info/c1e19e12046d23fe 2019-10-25 715 # https://www.sqlite.org/src/info/a8a4847a2d96f5de 2019-10-16 716 # 717 reset_db 718 do_execsql_test update-20.10 { 719 PRAGMA recursive_triggers = true; 720 CREATE TABLE t1(a UNIQUE ON CONFLICT REPLACE, b); 721 INSERT INTO t1(a,b) VALUES(4,12),(9,13); 722 CREATE INDEX i0 ON t1(b); 723 CREATE TRIGGER tr0 DELETE ON t1 BEGIN 724 UPDATE t1 SET b = a; 725 END; 726 PRAGMA integrity_check; 727 } {ok} 728 do_catchsql_test update-20.20 { 729 UPDATE t1 SET a=0; 730 } {1 {constraint failed}} 731 do_execsql_test update-20.30 { 732 PRAGMA integrity_check; 733 } {ok} 734 735 finish_test