test/update.test

   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 # 2023-03-16 https://sqlite.org/forum/forumpost/0007d1fdb1
 736 # A subquery in the WHERE clause of an UPDATE and behind a
 737 # short-circuit evaluation caused problems because multi-row
 738 # single-pass was selected.
 739 #
 740 # Similar problem for DELETE tested by delete-12.0.
 741 # https://sqlite.org/src/info/73f0036f045bf371
 742 #
 743 reset_db
 744 do_execsql_test update-21.1 {
 745   CREATE TABLE t1 (vkey INTEGER, c5 INTEGER);
 746   INSERT INTO t1 VALUES(3,NULL),(6,-54);
 747 }
 748 db null NULL
 749 do_execsql_test update-21.2 {
 750   BEGIN;
 751   UPDATE t1 SET vkey = 100 WHERE c5 is null;
 752   SELECT * FROM t1 ORDER BY vkey, c5;
 753   ROLLBACK;
 754 } {6 -54 100 NULL}
 755 do_execsql_test update-21.3 {
 756   BEGIN;
 757   UPDATE t1 SET vkey = 100 WHERE NOT (-10*(select min(vkey) from t1) >= c5);
 758   SELECT * FROM t1 ORDER BY vkey, c5;
 759   ROLLBACK;
 760 } {3 NULL 6 -54}
 761 do_execsql_test update-21.4 {
 762   BEGIN;
 763   UPDATE t1 SET vkey = 100 WHERE c5 is null OR NOT (-10*(select min(vkey) from t1) >= c5);
 764   SELECT * FROM t1 ORDER BY vkey, c5;
 765   ROLLBACK;
 766 } {6 -54 100 NULL}
 767 # Follow-up on 2023-07-31 (forum post https://sqlite.org/forum/forumpost/8ab195fd44e75ed0):
 768 # Only disable one-pass if the subquery is in the WHERE clause.  The SET expressions
 769 # do not count.
 770 do_execsql_test update-21.11 {
 771   DROP TABLE t1;
 772   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
 773   CREATE TABLE t2(d INT);
 774 }
 775 do_eqp_test update-21.12 {
 776   WITH t3(x,y) AS (SELECT d, row_number()OVER() FROM t2)
 777     UPDATE t1 SET b=(SELECT y FROM t3 WHERE t1.a=t3.x);
 778 } {
 779   QUERY PLAN
 780   |--SCAN t1
 781   `--CORRELATED SCALAR SUBQUERY xxxxxx
 782      |--CO-ROUTINE t3
 783      |  |--CO-ROUTINE (subquery-xxxxxx)
 784      |  |  `--SCAN t2
 785      |  `--SCAN (subquery-xxxxxx)
 786      |--BLOOM FILTER ON t3 (x=?)
 787      `--SEARCH t3 USING AUTOMATIC COVERING INDEX (x=?)
 788 }
 789
 790 finish_test