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