third_party/sqlite/src/test/e_update.test

   1 # 2010 September 20
   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 #
  12 # This file implements tests to verify that the "testable statements" in
  13 # the lang_update.html document are correct.
  14 #
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 #--------------------
  19 # Test organization:
  20 #
  21 #   e_update-1.*: Test statements describing the workings of UPDATE statements.
  22 #
  23 #   e_update-2.*: Test the restrictions on the UPDATE statement syntax that
  24 #                 can be used within triggers.
  25 #
  26 #   e_update-3.*: Test the special LIMIT/OFFSET and ORDER BY clauses that can
  27 #                 be used with UPDATE when SQLite is compiled with
  28 #                 SQLITE_ENABLE_UPDATE_DELETE_LIMIT.
  29 #
  30
  31 forcedelete test.db2
  32
  33 do_execsql_test e_update-0.0 {
  34   ATTACH 'test.db2' AS aux;
  35   CREATE TABLE t1(a, b);
  36   CREATE TABLE t2(a, b, c);
  37   CREATE TABLE t3(a, b UNIQUE);
  38   CREATE TABLE t6(x, y);
  39   CREATE INDEX i1 ON t1(a);
  40
  41   CREATE TEMP TABLE t4(x, y);
  42   CREATE TEMP TABLE t6(x, y);
  43
  44   CREATE TABLE aux.t1(a, b);
  45   CREATE TABLE aux.t5(a, b);
  46 } {}
  47
  48 proc do_update_tests {args} {
  49   uplevel do_select_tests $args
  50 }
  51
  52 # EVIDENCE-OF: R-05685-44205 -- syntax diagram update-stmt
  53 #
  54 do_update_tests e_update-0 {
  55   1    "UPDATE t1 SET a=10" {}
  56   2    "UPDATE t1 SET a=10, b=5" {}
  57   3    "UPDATE t1 SET a=10 WHERE b=5" {}
  58   4    "UPDATE t1 SET b=5,a=10 WHERE 1" {}
  59   5    "UPDATE main.t1 SET a=10" {}
  60   6    "UPDATE main.t1 SET a=10, b=5" {}
  61   7    "UPDATE main.t1 SET a=10 WHERE b=5" {}
  62   9    "UPDATE OR ROLLBACK t1 SET a=10" {}
  63   10   "UPDATE OR ROLLBACK t1 SET a=10, b=5" {}
  64   11   "UPDATE OR ROLLBACK t1 SET a=10 WHERE b=5" {}
  65   12   "UPDATE OR ROLLBACK t1 SET b=5,a=10 WHERE 1" {}
  66   13   "UPDATE OR ROLLBACK main.t1 SET a=10" {}
  67   14   "UPDATE OR ROLLBACK main.t1 SET a=10, b=5" {}
  68   15   "UPDATE OR ROLLBACK main.t1 SET a=10 WHERE b=5" {}
  69   16   "UPDATE OR ROLLBACK main.t1 SET b=5,a=10 WHERE 1" {}
  70   17   "UPDATE OR ABORT t1 SET a=10" {}
  71   18   "UPDATE OR ABORT t1 SET a=10, b=5" {}
  72   19   "UPDATE OR ABORT t1 SET a=10 WHERE b=5" {}
  73   20   "UPDATE OR ABORT t1 SET b=5,a=10 WHERE 1" {}
  74   21   "UPDATE OR ABORT main.t1 SET a=10" {}
  75   22   "UPDATE OR ABORT main.t1 SET a=10, b=5" {}
  76   23   "UPDATE OR ABORT main.t1 SET a=10 WHERE b=5" {}
  77   24   "UPDATE OR ABORT main.t1 SET b=5,a=10 WHERE 1" {}
  78   25   "UPDATE OR REPLACE t1 SET a=10" {}
  79   26   "UPDATE OR REPLACE t1 SET a=10, b=5" {}
  80   27   "UPDATE OR REPLACE t1 SET a=10 WHERE b=5" {}
  81   28   "UPDATE OR REPLACE t1 SET b=5,a=10 WHERE 1" {}
  82   29   "UPDATE OR REPLACE main.t1 SET a=10" {}
  83   30   "UPDATE OR REPLACE main.t1 SET a=10, b=5" {}
  84   31   "UPDATE OR REPLACE main.t1 SET a=10 WHERE b=5" {}
  85   32   "UPDATE OR REPLACE main.t1 SET b=5,a=10 WHERE 1" {}
  86   33   "UPDATE OR FAIL t1 SET a=10" {}
  87   34   "UPDATE OR FAIL t1 SET a=10, b=5" {}
  88   35   "UPDATE OR FAIL t1 SET a=10 WHERE b=5" {}
  89   36   "UPDATE OR FAIL t1 SET b=5,a=10 WHERE 1" {}
  90   37   "UPDATE OR FAIL main.t1 SET a=10" {}
  91   38   "UPDATE OR FAIL main.t1 SET a=10, b=5" {}
  92   39   "UPDATE OR FAIL main.t1 SET a=10 WHERE b=5" {}
  93   40   "UPDATE OR FAIL main.t1 SET b=5,a=10 WHERE 1" {}
  94   41   "UPDATE OR IGNORE t1 SET a=10" {}
  95   42   "UPDATE OR IGNORE t1 SET a=10, b=5" {}
  96   43   "UPDATE OR IGNORE t1 SET a=10 WHERE b=5" {}
  97   44   "UPDATE OR IGNORE t1 SET b=5,a=10 WHERE 1" {}
  98   45   "UPDATE OR IGNORE main.t1 SET a=10" {}
  99   46   "UPDATE OR IGNORE main.t1 SET a=10, b=5" {}
 100   47   "UPDATE OR IGNORE main.t1 SET a=10 WHERE b=5" {}
 101   48   "UPDATE OR IGNORE main.t1 SET b=5,a=10 WHERE 1" {}
 102 }
 103
 104 # EVIDENCE-OF: R-38515-45264 An UPDATE statement is used to modify a
 105 # subset of the values stored in zero or more rows of the database table
 106 # identified by the qualified-table-name specified as part of the UPDATE
 107 # statement.
 108 #
 109 #     Test cases e_update-1.1.1.* test the "identified by the
 110 #     qualified-table-name" part of the statement above. Tests
 111 #     e_update-1.1.2.* show that the "zero or more rows" part is
 112 #     accurate.
 113 #
 114 do_execsql_test e_update-1.1.0 {
 115   INSERT INTO main.t1 VALUES(1, 'i');
 116   INSERT INTO main.t1 VALUES(2, 'ii');
 117   INSERT INTO main.t1 VALUES(3, 'iii');
 118
 119   INSERT INTO aux.t1 VALUES(1, 'I');
 120   INSERT INTO aux.t1 VALUES(2, 'II');
 121   INSERT INTO aux.t1 VALUES(3, 'III');
 122 } {}
 123 do_update_tests e_update-1.1 {
 124   1.1  "UPDATE t1 SET a = a+1; SELECT * FROM t1"             {2 i  3 ii  4 iii}
 125   1.2  "UPDATE main.t1 SET a = a+1; SELECT * FROM main.t1"   {3 i  4 ii  5 iii}
 126   1.3  "UPDATE aux.t1 SET a = a+1; SELECT * FROM aux.t1"     {2 I  3 II  4 III}
 127
 128   2.1  "UPDATE t1 SET a = a+1 WHERE a = 1; SELECT * FROM t1" {3 i  4 ii  5 iii}
 129   2.2  "UPDATE t1 SET a = a+1 WHERE a = 4; SELECT * FROM t1" {3 i  5 ii  5 iii}
 130 }
 131
 132 # EVIDENCE-OF: R-55869-30521 If the UPDATE statement does not have a
 133 # WHERE clause, all rows in the table are modified by the UPDATE.
 134 #
 135 do_execsql_test e_update-1.2.0 {
 136   DELETE FROM main.t1;
 137   INSERT INTO main.t1 VALUES(1, 'i');
 138   INSERT INTO main.t1 VALUES(2, 'ii');
 139   INSERT INTO main.t1 VALUES(3, 'iii');
 140 } {}
 141 do_update_tests e_update-1.2 {
 142   1  "UPDATE t1 SET b = 'roman' ; SELECT * FROM t1"
 143      {1 roman  2 roman  3 roman}
 144
 145   2  "UPDATE t1 SET a = 'greek' ; SELECT * FROM t1"
 146      {greek roman  greek roman  greek roman}
 147 }
 148
 149 # EVIDENCE-OF: R-42117-40023 Otherwise, the UPDATE affects only those
 150 # rows for which the result of evaluating the WHERE clause expression as
 151 # a boolean expression is true.
 152 #
 153 do_execsql_test e_update-1.3.0 {
 154   DELETE FROM main.t1;
 155   INSERT INTO main.t1 VALUES(NULL, '');
 156   INSERT INTO main.t1 VALUES(1, 'i');
 157   INSERT INTO main.t1 VALUES(2, 'ii');
 158   INSERT INTO main.t1 VALUES(3, 'iii');
 159 } {}
 160 do_update_tests e_update-1.3 {
 161   1  "UPDATE t1 SET b = 'roman' WHERE a<2 ; SELECT * FROM t1"
 162      {{} {}  1 roman  2 ii  3 iii}
 163
 164   2  "UPDATE t1 SET b = 'egyptian' WHERE (a-3)/10.0 ; SELECT * FROM t1"
 165      {{} {}  1 egyptian  2 egyptian  3 iii}
 166
 167   3  "UPDATE t1 SET b = 'macedonian' WHERE a; SELECT * FROM t1"
 168      {{} {}  1 macedonian  2 macedonian  3 macedonian}
 169
 170   4  "UPDATE t1 SET b = 'lithuanian' WHERE a IS NULL; SELECT * FROM t1"
 171      {{} lithuanian  1 macedonian  2 macedonian  3 macedonian}
 172 }
 173
 174 # EVIDENCE-OF: R-58129-20729 It is not an error if the WHERE clause does
 175 # not evaluate to true for any row in the table - this just means that
 176 # the UPDATE statement affects zero rows.
 177 #
 178 do_execsql_test e_update-1.4.0 {
 179   DELETE FROM main.t1;
 180   INSERT INTO main.t1 VALUES(NULL, '');
 181   INSERT INTO main.t1 VALUES(1, 'i');
 182   INSERT INTO main.t1 VALUES(2, 'ii');
 183   INSERT INTO main.t1 VALUES(3, 'iii');
 184 } {}
 185 do_update_tests e_update-1.4 -query {
 186   SELECT * FROM t1
 187 } {
 188   1  "UPDATE t1 SET b = 'burmese' WHERE a=5" {{} {}  1 i  2 ii  3 iii}
 189
 190   2  "UPDATE t1 SET b = 'burmese' WHERE length(b)<1 AND a IS NOT NULL"
 191      {{} {}  1 i  2 ii  3 iii}
 192
 193   3  "UPDATE t1 SET b = 'burmese' WHERE 0" {{} {}  1 i  2 ii  3 iii}
 194
 195   4  "UPDATE t1 SET b = 'burmese' WHERE (SELECT a FROM t1 WHERE rowid=1)"
 196      {{} {}  1 i  2 ii  3 iii}
 197 }
 198
 199 # EVIDENCE-OF: R-40598-36595 For each affected row, the named columns
 200 # are set to the values found by evaluating the corresponding scalar
 201 # expressions.
 202 #
 203 # EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
 204 # assignments are left unmodified.
 205 #
 206 do_execsql_test e_update-1.5.0 {
 207   INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
 208   INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
 209   INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
 210 } {}
 211 do_update_tests e_update-1.5 -query {
 212   SELECT * FROM t2
 213 } {
 214   1   "UPDATE t2 SET c = 1+1 WHERE a=2"
 215       {3 1 4   1 5 9   2 6 2}
 216
 217   2   "UPDATE t2 SET b = 4/2, c=CAST((0.4*5) AS INTEGER) WHERE a<3"
 218       {3 1 4   1 2 2   2 2 2}
 219
 220   3   "UPDATE t2 SET a = 1"
 221       {1 1 4   1 2 2   1 2 2}
 222
 223   4   "UPDATE t2 SET b = (SELECT count(*)+2 FROM t2), c = 24/3+1 WHERE rowid=2"
 224       {1 1 4   1 5 9   1 2 2}
 225
 226   5   "UPDATE t2 SET a = 3 WHERE c = 4"
 227       {3 1 4   1 5 9   1 2 2}
 228
 229   6   "UPDATE t2 SET a = b WHERE rowid>2"
 230       {3 1 4   1 5 9   2 2 2}
 231
 232   6   "UPDATE t2 SET b=6, c=5 WHERE a=b AND b=c"
 233       {3 1 4   1 5 9   2 6 5}
 234 }
 235
 236 # EVIDENCE-OF: R-09060-20018 If a single column-name appears more than
 237 # once in the list of assignment expressions, all but the rightmost
 238 # occurence is ignored.
 239 #
 240 do_update_tests e_update-1.6 -query {
 241   SELECT * FROM t2
 242 } {
 243   1   "UPDATE t2 SET c=5, c=6, c=7 WHERE rowid=1" {3 1 7   1 5 9   2 6 5}
 244   2   "UPDATE t2 SET c=7, c=6, c=5 WHERE rowid=1" {3 1 5   1 5 9   2 6 5}
 245   3   "UPDATE t2 SET c=5, b=6, c=7 WHERE rowid=1" {3 6 7   1 5 9   2 6 5}
 246 }
 247
 248 # EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns
 249 # of the row being updated.
 250 #
 251 # EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
 252 # evaluated before any assignments are made.
 253 #
 254 do_execsql_test e_update-1.7.0 {
 255   DELETE FROM t2;
 256   INSERT INTO t2(rowid, a, b, c) VALUES(1,  3, 1, 4);
 257   INSERT INTO t2(rowid, a, b, c) VALUES(2,  1, 5, 9);
 258   INSERT INTO t2(rowid, a, b, c) VALUES(3,  2, 6, 5);
 259 } {}
 260 do_update_tests e_update-1.7 -query {
 261   SELECT * FROM t2
 262 } {
 263   1   "UPDATE t2 SET a=b+c"          {5 1 4     14 5 9   11  6 5}
 264   2   "UPDATE t2 SET a=b, b=a"       {1 5 4     5 14 9    6 11 5}
 265   3   "UPDATE t2 SET a=c||c, c=NULL" {44 5 {}  99 14 {}  55 11 {}}
 266 }
 267
 268 # EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
 269 # user to nominate a specific constraint conflict resolution algorithm
 270 # to use during this one UPDATE command.
 271 #
 272 do_execsql_test e_update-1.8.0 {
 273   DELETE FROM t3;
 274   INSERT INTO t3 VALUES(1, 'one');
 275   INSERT INTO t3 VALUES(2, 'two');
 276   INSERT INTO t3 VALUES(3, 'three');
 277   INSERT INTO t3 VALUES(4, 'four');
 278 } {}
 279 foreach {tn sql error ac data } {
 280   1  "UPDATE t3 SET b='one' WHERE a=3"
 281      {column b is not unique} 1 {1 one 2 two 3 three 4 four}
 282
 283   2  "UPDATE OR REPLACE t3 SET b='one' WHERE a=3"
 284      {} 1 {2 two 3 one 4 four}
 285
 286   3  "UPDATE OR FAIL t3 SET b='three'"
 287      {column b is not unique} 1 {2 three 3 one 4 four}
 288
 289   4  "UPDATE OR IGNORE t3 SET b='three' WHERE a=3"
 290      {} 1 {2 three 3 one 4 four}
 291
 292   5  "UPDATE OR ABORT t3 SET b='three' WHERE a=3"
 293      {column b is not unique} 1 {2 three 3 one 4 four}
 294
 295   6  "BEGIN" {} 0 {2 three 3 one 4 four}
 296
 297   7  "UPDATE t3 SET b='three' WHERE a=3"
 298      {column b is not unique} 0 {2 three 3 one 4 four}
 299
 300   8  "UPDATE OR ABORT t3 SET b='three' WHERE a=3"
 301      {column b is not unique} 0 {2 three 3 one 4 four}
 302
 303   9  "UPDATE OR FAIL t3 SET b='two'"
 304      {column b is not unique} 0 {2 two 3 one 4 four}
 305
 306   10 "UPDATE OR IGNORE t3 SET b='four' WHERE a=3"
 307      {} 0 {2 two 3 one 4 four}
 308
 309   11 "UPDATE OR REPLACE t3 SET b='four' WHERE a=3"
 310      {} 0 {2 two 3 four}
 311
 312   12 "UPDATE OR ROLLBACK t3 SET b='four'"
 313      {column b is not unique} 1 {2 three 3 one 4 four}
 314 } {
 315   do_catchsql_test e_update-1.8.$tn.1 $sql [list [expr {$error!=""}] $error]
 316   do_execsql_test  e_update-1.8.$tn.2 {SELECT * FROM t3} [list {*}$data]
 317   do_test          e_update-1.8.$tn.3 {sqlite3_get_autocommit db} $ac
 318 }
 319
 320
 321
 322 # EVIDENCE-OF: R-12123-54095 The table-name specified as part of an
 323 # UPDATE statement within a trigger body must be unqualified.
 324 #
 325 # EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
 326 # on the table name of the UPDATE is not allowed within triggers.
 327 #
 328 do_update_tests e_update-2.1 -error {
 329   qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers
 330 } {
 331   1 {
 332       CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 333         UPDATE main.t2 SET a=1, b=2, c=3;
 334       END;
 335   } {}
 336
 337   2 {
 338       CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN
 339         UPDATE aux.t1 SET a=1, b=2;
 340       END;
 341   } {}
 342
 343   3 {
 344       CREATE TRIGGER tr1 AFTER DELETE ON t4 BEGIN
 345         UPDATE main.t1 SET a=1, b=2;
 346       END;
 347   } {}
 348 }
 349
 350 # EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is
 351 # attached is in the TEMP database, the table being updated by the
 352 # trigger program must reside in the same database as it.
 353 #
 354 do_update_tests e_update-2.2 -error {
 355   no such table: %s
 356 } {
 357   1 {
 358       CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 359         UPDATE t4 SET x=x+1;
 360       END;
 361       INSERT INTO t1 VALUES(1, 2);
 362   } "main.t4"
 363
 364   2 {
 365       CREATE TRIGGER aux.tr1 AFTER INSERT ON t5 BEGIN
 366         UPDATE t4 SET x=x+1;
 367       END;
 368       INSERT INTO t5 VALUES(1, 2);
 369   } "aux.t4"
 370 }
 371 do_execsql_test e_update-2.2.X {
 372   DROP TRIGGER tr1;
 373   DROP TRIGGER aux.tr1;
 374 } {}
 375
 376 # EVIDENCE-OF: R-29512-54644 If the table to which the trigger is
 377 # attached is in the TEMP database, then the unqualified name of the
 378 # table being updated is resolved in the same way as it is for a
 379 # top-level statement (by searching first the TEMP database, then the
 380 # main database, then any other databases in the order they were
 381 # attached).
 382 #
 383 do_execsql_test e_update-2.3.0 {
 384   SELECT 'main', tbl_name FROM main.sqlite_master WHERE type = 'table'
 385     UNION ALL
 386   SELECT 'temp', tbl_name FROM sqlite_temp_master WHERE type = 'table'
 387     UNION ALL
 388   SELECT 'aux', tbl_name FROM aux.sqlite_master WHERE type = 'table'
 389 } [list {*}{
 390     main t1
 391     main t2
 392     main t3
 393     main t6
 394     temp t4
 395     temp t6
 396     aux  t1
 397     aux  t5
 398 }]
 399 do_execsql_test e_update-2.3.1 {
 400   DELETE FROM main.t6;
 401   DELETE FROM temp.t6;
 402   INSERT INTO main.t6 VALUES(1, 2);
 403   INSERT INTO temp.t6 VALUES(1, 2);
 404
 405   CREATE TRIGGER temp.tr1 AFTER INSERT ON t4 BEGIN
 406     UPDATE t6 SET x=x+1;
 407   END;
 408
 409   INSERT INTO t4 VALUES(1, 2);
 410   SELECT * FROM main.t6;
 411   SELECT * FROM temp.t6;
 412 } {1 2 2 2}
 413 do_execsql_test e_update-2.3.2 {
 414   DELETE FROM main.t1;
 415   DELETE FROM aux.t1;
 416   INSERT INTO main.t1 VALUES(1, 2);
 417   INSERT INTO aux.t1 VALUES(1, 2);
 418
 419   CREATE TRIGGER temp.tr2 AFTER DELETE ON t4 BEGIN
 420     UPDATE t1 SET a=a+1;
 421   END;
 422
 423   DELETE FROM t4;
 424   SELECT * FROM main.t1;
 425   SELECT * FROM aux.t1;
 426 } {2 2 1 2}
 427 do_execsql_test e_update-2.3.3 {
 428   DELETE FROM aux.t5;
 429   INSERT INTO aux.t5 VALUES(1, 2);
 430
 431   INSERT INTO t4 VALUES('x', 'y');
 432   CREATE TRIGGER temp.tr3 AFTER UPDATE ON t4 BEGIN
 433     UPDATE t5 SET a=a+1;
 434   END;
 435
 436   UPDATE t4 SET x=10;
 437   SELECT * FROM aux.t5;
 438 } {2 2}
 439
 440 # EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
 441 # not allowed on UPDATE statements within triggers.
 442 #
 443 do_update_tests e_update-2.4 -error {
 444   the %s %s clause is not allowed on UPDATE or DELETE statements within triggers
 445 } {
 446   1 {
 447       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 448         UPDATE t1 INDEXED BY i1 SET a=a+1;
 449       END;
 450   } {INDEXED BY}
 451
 452   2 {
 453       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 454         UPDATE t1 NOT INDEXED SET a=a+1;
 455       END;
 456   } {NOT INDEXED}
 457 }
 458
 459 ifcapable update_delete_limit {
 460
 461 # EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE
 462 # are unsupported within triggers, regardless of the compilation options
 463 # used to build SQLite.
 464 #
 465 do_update_tests e_update-2.5 -error {
 466   near "%s": syntax error
 467 } {
 468   1 {
 469       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 470         UPDATE t1 SET a=a+1 LIMIT 10;
 471       END;
 472   } {LIMIT}
 473
 474   2 {
 475       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 476         UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10;
 477       END;
 478   } {ORDER}
 479
 480   3 {
 481       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 482         UPDATE t1 SET a=a+1 ORDER BY a LIMIT 10 OFFSET 2;
 483       END;
 484   } {ORDER}
 485
 486   4 {
 487       CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
 488         UPDATE t1 SET a=a+1 LIMIT 10 OFFSET 2;
 489       END;
 490   } {LIMIT}
 491 }
 492
 493 # EVIDENCE-OF: R-59581-44104 If SQLite is built with the
 494 # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
 495 # of the UPDATE statement is extended with optional ORDER BY and LIMIT
 496 # clauses
 497 #
 498 # EVIDENCE-OF: R-08948-01887 -- syntax diagram update-stmt-limited
 499 #
 500 do_update_tests e_update-3.0 {
 501   1   "UPDATE t1 SET a=b LIMIT 5"                                    {}
 502   2   "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2"                       {}
 503   3   "UPDATE t1 SET a=b LIMIT 2+2, 16/4"                            {}
 504   4   "UPDATE t1 SET a=b ORDER BY a LIMIT 5"                         {}
 505   5   "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2"            {}
 506   6   "UPDATE t1 SET a=b ORDER BY a LIMIT 2+2, 16/4"                 {}
 507   7   "UPDATE t1 SET a=b WHERE a>2 LIMIT 5"                          {}
 508   8   "UPDATE t1 SET a=b WHERE a>2 LIMIT 5-1 OFFSET 2+2"             {}
 509   9   "UPDATE t1 SET a=b WHERE a>2 LIMIT 2+2, 16/4"                  {}
 510   10  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5"               {}
 511   11  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 5-1 OFFSET 2+2"  {}
 512   12  "UPDATE t1 SET a=b WHERE a>2 ORDER BY a LIMIT 2+2, 16/4"       {}
 513 }
 514
 515 do_execsql_test e_update-3.1.0 {
 516   CREATE TABLE t7(q, r, s);
 517   INSERT INTO t7 VALUES(1, 'one',   'X');
 518   INSERT INTO t7 VALUES(2, 'two',   'X');
 519   INSERT INTO t7 VALUES(3, 'three', 'X');
 520   INSERT INTO t7 VALUES(4, 'four',  'X');
 521   INSERT INTO t7 VALUES(5, 'five',  'X');
 522   INSERT INTO t7 VALUES(6, 'six',   'X');
 523   INSERT INTO t7 VALUES(7, 'seven', 'X');
 524   INSERT INTO t7 VALUES(8, 'eight', 'X');
 525   INSERT INTO t7 VALUES(9, 'nine',  'X');
 526   INSERT INTO t7 VALUES(10, 'ten',  'X');
 527 } {}
 528
 529 # EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause,
 530 # the maximum number of rows that will be updated is found by evaluating
 531 # the accompanying expression and casting it to an integer value.
 532 #
 533 do_update_tests e_update-3.1 -query { SELECT s FROM t7 } {
 534   1   "UPDATE t7 SET s = q LIMIT 5"            {1 2 3 4 5 X X X X X}
 535   2   "UPDATE t7 SET s = r WHERE q>2 LIMIT 4"  {1 2 three four five six X X X X}
 536   3   "UPDATE t7 SET s = q LIMIT 0"            {1 2 three four five six X X X X}
 537 }
 538
 539 # EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no limit".
 540 #
 541 do_update_tests e_update-3.2 -query { SELECT s FROM t7 } {
 542   1   "UPDATE t7 SET s = q LIMIT -1"              {1 2 3 4 5 6 7 8 9 10}
 543   2   "UPDATE t7 SET s = r WHERE q>4 LIMIT -1"
 544       {1 2 3 4 five six seven eight nine ten}
 545   3   "UPDATE t7 SET s = 'X' LIMIT -1"            {X X X X X X X X X X}
 546 }
 547
 548 # EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to
 549 # non-negative value N and the UPDATE statement has an ORDER BY clause,
 550 # then all rows that would be updated in the absence of the LIMIT clause
 551 # are sorted according to the ORDER BY and the first N updated.
 552 #
 553 do_update_tests e_update-3.3 -query { SELECT s FROM t7 } {
 554   1   "UPDATE t7 SET s = q ORDER BY r LIMIT 3"      {X X X 4 5 X X 8 X X}
 555   2   "UPDATE t7 SET s = r ORDER BY r DESC LIMIT 2" {X two three 4 5 X X 8 X X}
 556   3   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5" {X two three 4 5 6 7 8 9 10}
 557
 558   X   "UPDATE t7 SET s = 'X'"                       {X X X X X X X X X X}
 559 }
 560
 561 # EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET
 562 # clause, then it is similarly evaluated and cast to an integer value.
 563 # If the OFFSET expression evaluates to a non-negative value M, then the
 564 # first M rows are skipped and the following N rows updated instead.
 565 #
 566 do_update_tests e_update-3.3 -query { SELECT s FROM t7 } {
 567   1   "UPDATE t7 SET s = q ORDER BY q LIMIT 3 OFFSET 2"  {X X 3 4 5 X X X X X}
 568   2   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 2, 3 "  {X X 3 4 5 6 7 8 X X}
 569
 570   X   "UPDATE t7 SET s = 'X'"                       {X X X X X X X X X X}
 571 }
 572
 573 # EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY
 574 # clause, then all rows that would be updated in the absence of the
 575 # LIMIT clause are assembled in an arbitrary order before applying the
 576 # LIMIT and OFFSET clauses to determine which are actually updated.
 577 #
 578 #     In practice, "arbitrary order" is rowid order. This is also tested
 579 #     by e_update-3.2.* above.
 580 #
 581 do_update_tests e_update-3.4 -query { SELECT s FROM t7 } {
 582   1   "UPDATE t7 SET s = q LIMIT 4, 2"        {X X X X 5 6 X X X X}
 583   2   "UPDATE t7 SET s = q LIMIT 2 OFFSET 7"  {X X X X 5 6 X 8 9 X}
 584 }
 585
 586 # EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement
 587 # is used only to determine which rows fall within the LIMIT. The order
 588 # in which rows are modified is arbitrary and is not influenced by the
 589 # ORDER BY clause.
 590 #
 591 do_execsql_test e_update-3.5.0 {
 592   CREATE TABLE t8(x);
 593   CREATE TRIGGER tr7 BEFORE UPDATE ON t7 BEGIN
 594     INSERT INTO t8 VALUES(old.q);
 595   END;
 596 } {}
 597 do_update_tests e_update-3.5 -query { SELECT x FROM t8 ; DELETE FROM t8 } {
 598   1   "UPDATE t7 SET s = q ORDER BY r LIMIT -1"        {1 2 3 4 5 6 7 8 9 10}
 599   2   "UPDATE t7 SET s = q ORDER BY r ASC LIMIT -1"    {1 2 3 4 5 6 7 8 9 10}
 600   3   "UPDATE t7 SET s = q ORDER BY r DESC LIMIT -1"   {1 2 3 4 5 6 7 8 9 10}
 601   4   "UPDATE t7 SET s = q ORDER BY q DESC LIMIT 5"    {6 7 8 9 10}
 602 }
 603
 604
 605 } ;# ifcapable update_delete_limit
 606
 607 finish_test
 608