modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/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 } 512 513 integrity_check update-12.1 514 515 # Ticket 602. Updates should occur in the same order as the records 516 # were discovered in the WHERE clause. 517 # 518 do_test update-13.1 { 519 execsql { 520 BEGIN; 521 CREATE TABLE t2(a); 522 INSERT INTO t2 VALUES(1); 523 INSERT INTO t2 VALUES(2); 524 INSERT INTO t2 SELECT a+2 FROM t2; 525 INSERT INTO t2 SELECT a+4 FROM t2; 526 INSERT INTO t2 SELECT a+8 FROM t2; 527 INSERT INTO t2 SELECT a+16 FROM t2; 528 INSERT INTO t2 SELECT a+32 FROM t2; 529 INSERT INTO t2 SELECT a+64 FROM t2; 530 INSERT INTO t2 SELECT a+128 FROM t2; 531 INSERT INTO t2 SELECT a+256 FROM t2; 532 INSERT INTO t2 SELECT a+512 FROM t2; 533 INSERT INTO t2 SELECT a+1024 FROM t2; 534 COMMIT; 535 SELECT count(*) FROM t2; 536 } 537 } {2048} 538 do_test update-13.2 { 539 execsql { 540 SELECT count(*) FROM t2 WHERE a=rowid; 541 } 542 } {2048} 543 do_test update-13.3 { 544 execsql { 545 UPDATE t2 SET rowid=rowid-1; 546 SELECT count(*) FROM t2 WHERE a=rowid+1; 547 } 548 } {2048} 549 do_test update-13.3 { 550 execsql { 551 UPDATE t2 SET rowid=rowid+10000; 552 UPDATE t2 SET rowid=rowid-9999; 553 SELECT count(*) FROM t2 WHERE a=rowid; 554 } 555 } {2048} 556 do_test update-13.4 { 557 execsql { 558 BEGIN; 559 INSERT INTO t2 SELECT a+2048 FROM t2; 560 INSERT INTO t2 SELECT a+4096 FROM t2; 561 INSERT INTO t2 SELECT a+8192 FROM t2; 562 SELECT count(*) FROM t2 WHERE a=rowid; 563 COMMIT; 564 } 565 } 16384 566 do_test update-13.5 { 567 execsql { 568 UPDATE t2 SET rowid=rowid-1; 569 SELECT count(*) FROM t2 WHERE a=rowid+1; 570 } 571 } 16384 572 573 integrity_check update-13.6 574 575 ifcapable {trigger} { 576 # Test for proper detection of malformed WHEN clauses on UPDATE triggers. 577 # 578 do_test update-14.1 { 579 execsql { 580 CREATE TABLE t3(a,b,c); 581 CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN 582 SELECT 'illegal WHEN clause'; 583 END; 584 } 585 } {} 586 do_test update-14.2 { 587 catchsql { 588 UPDATE t3 SET a=1; 589 } 590 } {1 {no such column: nosuchcol}} 591 do_test update-14.3 { 592 execsql { 593 CREATE TABLE t4(a,b,c); 594 CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN 595 SELECT 'illegal WHEN clause'; 596 END; 597 } 598 } {} 599 do_test update-14.4 { 600 catchsql { 601 UPDATE t4 SET a=1; 602 } 603 } {1 {no such column: nosuchcol}} 604 605 } ;# ifcapable {trigger} 606 607 # Ticket [https://www.sqlite.org/src/tktview/43107840f1c02] on 2014-10-29 608 # An assertion fault on UPDATE 609 # 610 do_execsql_test update-15.1 { 611 CREATE TABLE t15(a INTEGER PRIMARY KEY, b); 612 INSERT INTO t15(a,b) VALUES(10,'abc'),(20,'def'),(30,'ghi'); 613 ALTER TABLE t15 ADD COLUMN c; 614 CREATE INDEX t15c ON t15(c); 615 INSERT INTO t15(a,b) 616 VALUES(5,'zyx'),(15,'wvu'),(25,'tsr'),(35,'qpo'); 617 UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL; 618 SELECT a,b,c,'|' FROM t15 ORDER BY a; 619 } {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |} 620 621 622 finish_test