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