third_party/sqlite/src/test/e_delete.test

   1 # 2010 September 21
   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_delete.html document are correct.
  14 #
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 ifcapable !compound {
  19   finish_test
  20   return
  21 }
  22
  23 proc do_delete_tests {args} {
  24   uplevel do_select_tests $args
  25 }
  26
  27 do_execsql_test e_delete-0.0 {
  28   CREATE TABLE t1(a, b);
  29   CREATE INDEX i1 ON t1(a);
  30 } {}
  31
  32 # -- syntax diagram delete-stmt
  33 # -- syntax diagram qualified-table-name
  34 #
  35 do_delete_tests e_delete-0.1 {
  36   1  "DELETE FROM t1"                              {}
  37   2  "DELETE FROM t1 INDEXED BY i1"                {}
  38   3  "DELETE FROM t1 NOT INDEXED"                  {}
  39   4  "DELETE FROM main.t1"                         {}
  40   5  "DELETE FROM main.t1 INDEXED BY i1"           {}
  41   6  "DELETE FROM main.t1 NOT INDEXED"             {}
  42   7  "DELETE FROM t1 WHERE a>2"                    {}
  43   8  "DELETE FROM t1 INDEXED BY i1 WHERE a>2"      {}
  44   9  "DELETE FROM t1 NOT INDEXED WHERE a>2"        {}
  45   10 "DELETE FROM main.t1 WHERE a>2"               {}
  46   11 "DELETE FROM main.t1 INDEXED BY i1 WHERE a>2" {}
  47   12 "DELETE FROM main.t1 NOT INDEXED WHERE a>2"   {}
  48 }
  49
  50 # EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all
  51 # records in the table are deleted.
  52 #
  53 drop_all_tables
  54 do_test e_delete-1.0 {
  55   db transaction {
  56     foreach t {t1 t2 t3 t4 t5 t6} {
  57       execsql [string map [list %T% $t] {
  58         CREATE TABLE %T%(x, y);
  59         INSERT INTO %T% VALUES(1, 'one');
  60         INSERT INTO %T% VALUES(2, 'two');
  61         INSERT INTO %T% VALUES(3, 'three');
  62         INSERT INTO %T% VALUES(4, 'four');
  63         INSERT INTO %T% VALUES(5, 'five');
  64       }]
  65     }
  66   }
  67 } {}
  68 do_delete_tests e_delete-1.1 {
  69   1  "DELETE FROM t1       ; SELECT * FROM t1"       {}
  70   2  "DELETE FROM main.t2  ; SELECT * FROM t2"       {}
  71 }
  72
  73 # EVIDENCE-OF: R-30203-16177 If a WHERE clause is supplied, then only
  74 # those rows for which the result of evaluating the WHERE clause as a
  75 # boolean expression is true are deleted.
  76 #
  77 do_delete_tests e_delete-1.2 {
  78   1  "DELETE FROM t3 WHERE 1       ; SELECT x FROM t3"       {}
  79   2  "DELETE FROM main.t4 WHERE 0  ; SELECT x FROM t4"       {1 2 3 4 5}
  80   3  "DELETE FROM t4 WHERE 0.0     ; SELECT x FROM t4"       {1 2 3 4 5}
  81   4  "DELETE FROM t4 WHERE NULL    ; SELECT x FROM t4"       {1 2 3 4 5}
  82   5  "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4"       {2}
  83   6  "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4"       {}
  84   7  "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5" {1 2 3 4}
  85   8  "DELETE FROM t5 WHERE (SELECT max(x) FROM t4)  ;SELECT x FROM t5" {1 2 3 4}
  86   9  "DELETE FROM t5 WHERE (SELECT max(x) FROM t6)  ;SELECT x FROM t5" {}
  87   10 "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6"     {one four five}
  88 }
  89
  90
  91 #-------------------------------------------------------------------------
  92 # Tests for restrictions on DELETE statements that appear within trigger
  93 # programs.
  94 #
  95 forcedelete test.db2
  96 forcedelete test.db3
  97 do_execsql_test e_delete-2.0 {
  98   ATTACH 'test.db2' AS aux;
  99   ATTACH 'test.db3' AS aux2;
 100
 101   CREATE TABLE temp.t7(a, b);   INSERT INTO temp.t7 VALUES(1, 2);
 102   CREATE TABLE main.t7(a, b);   INSERT INTO main.t7 VALUES(3, 4);
 103   CREATE TABLE aux.t7(a, b);    INSERT INTO aux.t7 VALUES(5, 6);
 104   CREATE TABLE aux2.t7(a, b);   INSERT INTO aux2.t7 VALUES(7, 8);
 105
 106   CREATE TABLE main.t8(a, b);   INSERT INTO main.t8 VALUES(1, 2);
 107   CREATE TABLE aux.t8(a, b);    INSERT INTO aux.t8 VALUES(3, 4);
 108   CREATE TABLE aux2.t8(a, b);   INSERT INTO aux2.t8 VALUES(5, 6);
 109
 110   CREATE TABLE aux.t9(a, b);    INSERT INTO aux.t9 VALUES(1, 2);
 111   CREATE TABLE aux2.t9(a, b);   INSERT INTO aux2.t9 VALUES(3, 4);
 112
 113   CREATE TABLE aux2.t10(a, b);  INSERT INTO aux2.t10 VALUES(1, 2);
 114 } {}
 115
 116
 117 # EVIDENCE-OF: R-09681-58560 The table-name specified as part of a
 118 # DELETE statement within a trigger body must be unqualified.
 119 #
 120 # EVIDENCE-OF: R-36771-43788 In other words, the database-name. prefix
 121 # on the table name is not allowed within triggers.
 122 #
 123 do_delete_tests e_delete-2.1 -error {
 124   qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers
 125 } {
 126   1 {
 127       CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 128         DELETE FROM main.t2;
 129       END;
 130   } {}
 131
 132   2 {
 133       CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN
 134         DELETE FROM temp.t7 WHERE a=new.a;
 135       END;
 136   } {}
 137
 138   3 {
 139       CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN
 140         DELETE FROM aux2.t8 WHERE b!=a;
 141       END;
 142   } {}
 143 }
 144
 145 # EVIDENCE-OF: R-28818-63526 If the table to which the trigger is
 146 # attached is not in the temp database, then DELETE statements within
 147 # the trigger body must operate on tables within the same database as
 148 # it.
 149 #
 150 #   This is tested in two parts. First, check that if a table of the
 151 #   specified name does not exist, an error is raised. Secondly, test
 152 #   that if tables with the specified name exist in multiple databases,
 153 #   the local database table is used.
 154 #
 155 do_delete_tests e_delete-2.2.1 -error { no such table: %s } {
 156   1 {
 157       CREATE TRIGGER main.tr1 AFTER INSERT ON main.t7 BEGIN
 158         DELETE FROM t9;
 159       END;
 160       INSERT INTO main.t7 VALUES(1, 2);
 161   } {main.t9}
 162
 163   2 {
 164       CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN
 165         DELETE FROM t10;
 166       END;
 167       UPDATE t9 SET a=1;
 168   } {aux.t10}
 169 }
 170 do_execsql_test e_delete-2.2.X {
 171   DROP TRIGGER main.tr1;
 172   DROP TRIGGER aux.tr2;
 173 } {}
 174
 175 do_delete_tests e_delete-2.2.2 {
 176   1 {
 177       CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN
 178         DELETE FROM t9;
 179       END;
 180       INSERT INTO aux.t8 VALUES(1, 2);
 181
 182       SELECT count(*) FROM aux.t9
 183         UNION ALL
 184       SELECT count(*) FROM aux2.t9;
 185   } {0 1}
 186
 187   2 {
 188       CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN
 189         DELETE FROM t7;
 190       END;
 191       INSERT INTO main.t8 VALUES(1, 2);
 192
 193       SELECT count(*) FROM temp.t7
 194         UNION ALL
 195       SELECT count(*) FROM main.t7
 196         UNION ALL
 197       SELECT count(*) FROM aux.t7
 198         UNION ALL
 199       SELECT count(*) FROM aux2.t7;
 200   } {1 0 1 1}
 201 }
 202
 203 # EVIDENCE-OF: R-31567-38587 If the table to which the trigger is
 204 # attached is in the TEMP database, then the unqualified name of the
 205 # table being deleted is resolved in the same way as it is for a
 206 # top-level statement (by searching first the TEMP database, then the
 207 # main database, then any other databases in the order they were
 208 # attached).
 209 #
 210 do_execsql_test e_delete-2.3.0 {
 211   DROP TRIGGER aux.tr1;
 212   DROP TRIGGER main.tr1;
 213   DELETE FROM main.t8 WHERE oid>1;
 214   DELETE FROM aux.t8 WHERE oid>1;
 215   INSERT INTO aux.t9 VALUES(1, 2);
 216   INSERT INTO main.t7 VALUES(3, 4);
 217 } {}
 218 do_execsql_test e_delete-2.3.1 {
 219   SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
 220   SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;
 221
 222   SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8
 223   UNION ALL SELECT count(*) FROM aux2.t8;
 224
 225   SELECT count(*) FROM aux.t9  UNION ALL SELECT count(*) FROM aux2.t9;
 226
 227   SELECT count(*) FROM aux2.t10;
 228 } {1 1 1 1 1 1 1 1 1 1}
 229 do_execsql_test e_delete-2.3.2 {
 230   CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN
 231     DELETE FROM t7;
 232     DELETE FROM t8;
 233     DELETE FROM t9;
 234     DELETE FROM t10;
 235   END;
 236   INSERT INTO temp.t7 VALUES('hello', 'world');
 237 } {}
 238 do_execsql_test e_delete-2.3.3 {
 239   SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
 240   SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;
 241
 242   SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8
 243   UNION ALL SELECT count(*) FROM aux2.t8;
 244
 245   SELECT count(*) FROM aux.t9  UNION ALL SELECT count(*) FROM aux2.t9;
 246
 247   SELECT count(*) FROM aux2.t10;
 248 } {0 1 1 1 0 1 1 0 1 0}
 249
 250 # EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED clauses are
 251 # not allowed on DELETE statements within triggers.
 252 #
 253 do_execsql_test e_delete-2.4.0 {
 254   CREATE INDEX i8 ON t8(a, b);
 255 } {}
 256 do_delete_tests e_delete-2.4 -error {
 257   the %s %s clause is not allowed on UPDATE or DELETE statements within triggers
 258 } {
 259   1 {
 260     CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
 261       DELETE FROM t8 INDEXED BY i8 WHERE a=5;
 262     END;
 263   } {INDEXED BY}
 264   2 {
 265     CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
 266       DELETE FROM t8 NOT INDEXED WHERE a=5;
 267     END;
 268   } {NOT INDEXED}
 269 }
 270
 271 ifcapable update_delete_limit {
 272
 273 # EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described
 274 # below) are unsupported for DELETE statements within triggers.
 275 #
 276 do_delete_tests e_delete-2.5 -error { near "%s": syntax error } {
 277   1 {
 278     CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
 279       DELETE FROM t8 LIMIT 10;
 280     END;
 281   } {LIMIT}
 282   2 {
 283     CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
 284       DELETE FROM t8 ORDER BY a LIMIT 5;
 285     END;
 286   } {ORDER}
 287 }
 288
 289 # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
 290 # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
 291 # of the DELETE statement is extended by the addition of optional ORDER
 292 # BY and LIMIT clauses:
 293 #
 294 # -- syntax diagram delete-stmt-limited
 295 #
 296 do_delete_tests e_delete-3.1 {
 297   1   "DELETE FROM t1 LIMIT 5"                                    {}
 298   2   "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2"                       {}
 299   3   "DELETE FROM t1 LIMIT 2+2, 16/4"                            {}
 300   4   "DELETE FROM t1 ORDER BY x LIMIT 5"                         {}
 301   5   "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2"            {}
 302   6   "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4"                 {}
 303   7   "DELETE FROM t1 WHERE x>2 LIMIT 5"                          {}
 304   8   "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2"             {}
 305   9   "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4"                  {}
 306   10  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5"               {}
 307   11  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2"  {}
 308   12  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4"       {}
 309 }
 310
 311 drop_all_tables
 312 proc rebuild_t1 {} {
 313   catchsql { DROP TABLE t1 }
 314   execsql {
 315     CREATE TABLE t1(a, b);
 316     INSERT INTO t1 VALUES(1, 'one');
 317     INSERT INTO t1 VALUES(2, 'two');
 318     INSERT INTO t1 VALUES(3, 'three');
 319     INSERT INTO t1 VALUES(4, 'four');
 320     INSERT INTO t1 VALUES(5, 'five');
 321   }
 322 }
 323
 324 # EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause,
 325 # the maximum number of rows that will be deleted is found by evaluating
 326 # the accompanying expression and casting it to an integer value.
 327 #
 328 rebuild_t1
 329 do_delete_tests e_delete-3.2 -repair rebuild_t1 -query {
 330   SELECT a FROM t1
 331 } {
 332   1   "DELETE FROM t1 LIMIT 3"       {4 5}
 333   2   "DELETE FROM t1 LIMIT 1+1"     {3 4 5}
 334   3   "DELETE FROM t1 LIMIT '4'"     {5}
 335   4   "DELETE FROM t1 LIMIT '1.0'"   {2 3 4 5}
 336 }
 337
 338 # EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT
 339 # clause cannot be losslessly converted to an integer value, it is an
 340 # error.
 341 #
 342 do_delete_tests e_delete-3.3 -error { datatype mismatch } {
 343   1   "DELETE FROM t1 LIMIT 'abc'"   {}
 344   2   "DELETE FROM t1 LIMIT NULL"    {}
 345   3   "DELETE FROM t1 LIMIT X'ABCD'" {}
 346   4   "DELETE FROM t1 LIMIT 1.2"     {}
 347 }
 348
 349 # EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as
 350 # "no limit".
 351 #
 352 do_delete_tests e_delete-3.4 -repair rebuild_t1 -query {
 353   SELECT a FROM t1
 354 } {
 355   1   "DELETE FROM t1 LIMIT -1"       {}
 356   2   "DELETE FROM t1 LIMIT 2-4"      {}
 357   3   "DELETE FROM t1 LIMIT -4.0"     {}
 358   4   "DELETE FROM t1 LIMIT 5*-1"     {}
 359 }
 360
 361 # EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET
 362 # clause, then it is similarly evaluated and cast to an integer value.
 363 # Again, it is an error if the value cannot be losslessly converted to
 364 # an integer.
 365 #
 366 do_delete_tests e_delete-3.5 -error { datatype mismatch } {
 367   1   "DELETE FROM t1 LIMIT 1 OFFSET 'abc'"   {}
 368   2   "DELETE FROM t1 LIMIT 1 OFFSET NULL"    {}
 369   3   "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'" {}
 370   4   "DELETE FROM t1 LIMIT 1 OFFSET 1.2"     {}
 371   5   "DELETE FROM t1 LIMIT 'abc', 1"         {}
 372   6   "DELETE FROM t1 LIMIT NULL, 1"          {}
 373   7   "DELETE FROM t1 LIMIT X'ABCD', 1"       {}
 374   8   "DELETE FROM t1 LIMIT 1.2, 1"           {}
 375 }
 376
 377
 378 # EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the
 379 # calculated integer value is negative, the effective OFFSET value is
 380 # zero.
 381 #
 382 do_delete_tests e_delete-3.6 -repair rebuild_t1 -query {
 383   SELECT a FROM t1
 384 } {
 385   1a  "DELETE FROM t1 LIMIT 3 OFFSET 0"        {4 5}
 386   1b  "DELETE FROM t1 LIMIT 3"                 {4 5}
 387   1c  "DELETE FROM t1 LIMIT 3 OFFSET -1"       {4 5}
 388   2a  "DELETE FROM t1 LIMIT 1+1 OFFSET 0"      {3 4 5}
 389   2b  "DELETE FROM t1 LIMIT 1+1"               {3 4 5}
 390   2c  "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5"    {3 4 5}
 391   3a  "DELETE FROM t1 LIMIT '4' OFFSET 0"      {5}
 392   3b  "DELETE FROM t1 LIMIT '4'"               {5}
 393   3c  "DELETE FROM t1 LIMIT '4' OFFSET -1.0"   {5}
 394   4a  "DELETE FROM t1 LIMIT '1.0' OFFSET 0"    {2 3 4 5}
 395   4b  "DELETE FROM t1 LIMIT '1.0'"             {2 3 4 5}
 396   4c  "DELETE FROM t1 LIMIT '1.0' OFFSET -11"  {2 3 4 5}
 397 }
 398
 399 # EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY
 400 # clause, then all rows that would be deleted in the absence of the
 401 # LIMIT clause are sorted according to the ORDER BY. The first M rows,
 402 # where M is the value found by evaluating the OFFSET clause expression,
 403 # are skipped, and the following N, where N is the value of the LIMIT
 404 # expression, are deleted.
 405 #
 406 do_delete_tests e_delete-3.7 -repair rebuild_t1 -query {
 407   SELECT a FROM t1
 408 } {
 409   1   "DELETE FROM t1 ORDER BY b LIMIT 2"               {1 2 3}
 410   2   "DELETE FROM t1 ORDER BY length(b), a LIMIT 3"    {3 5}
 411   3   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0"  {1 2 3 4}
 412   4   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1"  {1 2 3 5}
 413   5   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2"  {1 2 4 5}
 414 }
 415
 416 # EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining
 417 # after taking the OFFSET clause into account, or if the LIMIT clause
 418 # evaluated to a negative value, then all remaining rows are deleted.
 419 #
 420 do_delete_tests e_delete-3.8 -repair rebuild_t1 -query {
 421   SELECT a FROM t1
 422 } {
 423   1   "DELETE FROM t1 ORDER BY a ASC LIMIT 10"           {}
 424   2   "DELETE FROM t1 ORDER BY a ASC LIMIT -1"           {}
 425   3   "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2"   {1 2}
 426 }
 427
 428 # EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY
 429 # clause, then all rows that would be deleted in the absence of the
 430 # LIMIT clause are assembled in an arbitrary order before applying the
 431 # LIMIT and OFFSET clauses to determine the subset that are actually
 432 # deleted.
 433 #
 434 #     In practice, the "arbitrary order" is rowid order.
 435 #
 436 do_delete_tests e_delete-3.9 -repair rebuild_t1 -query {
 437   SELECT a FROM t1
 438 } {
 439   1   "DELETE FROM t1 LIMIT 2"               {3 4 5}
 440   2   "DELETE FROM t1 LIMIT 3"               {4 5}
 441   3   "DELETE FROM t1 LIMIT 1 OFFSET 0"      {2 3 4 5}
 442   4   "DELETE FROM t1 LIMIT 1 OFFSET 1"      {1 3 4 5}
 443   5   "DELETE FROM t1 LIMIT 1 OFFSET 2"      {1 2 4 5}
 444 }
 445
 446
 447 # EVIDENCE-OF: R-07548-13422 The ORDER BY clause on a DELETE statement
 448 # is used only to determine which rows fall within the LIMIT. The order
 449 # in which rows are deleted is arbitrary and is not influenced by the
 450 # ORDER BY clause.
 451 #
 452 #     In practice, rows are always deleted in rowid order.
 453 #
 454 do_delete_tests e_delete-3.10 -repair {
 455   rebuild_t1
 456   catchsql { DROP TABLE t1log }
 457   execsql {
 458     CREATE TABLE t1log(x);
 459     CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
 460       INSERT INTO t1log VALUES(old.a);
 461     END;
 462   }
 463 } -query {
 464   SELECT x FROM t1log
 465 } {
 466   1   "DELETE FROM t1 ORDER BY a DESC LIMIT 2"   {4 5}
 467   2   "DELETE FROM t1 ORDER BY a DESC LIMIT -1"  {1 2 3 4 5}
 468   3   "DELETE FROM t1 ORDER BY a ASC LIMIT 2"    {1 2}
 469   4   "DELETE FROM t1 ORDER BY a ASC LIMIT -1"   {1 2 3 4 5}
 470 }
 471
 472 }
 473
 474 finish_test