000001  # 2010 September 21
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_delete.html document are correct.
000014  #
000015  set testdir [file dirname $argv0]
000016  source $testdir/tester.tcl
000017  
000018  ifcapable !compound {
000019    finish_test
000020    return
000021  }
000022  
000023  proc do_delete_tests {args} {
000024    uplevel do_select_tests $args
000025  }
000026  
000027  do_execsql_test e_delete-0.0 {
000028    CREATE TABLE t1(a, b);
000029    CREATE INDEX i1 ON t1(a);
000030  } {}
000031  
000032  # -- syntax diagram delete-stmt
000033  # -- syntax diagram qualified-table-name
000034  #
000035  do_delete_tests e_delete-0.1 {
000036    1  "DELETE FROM t1"                              {}
000037    2  "DELETE FROM t1 INDEXED BY i1"                {}
000038    3  "DELETE FROM t1 NOT INDEXED"                  {}
000039    4  "DELETE FROM main.t1"                         {}
000040    5  "DELETE FROM main.t1 INDEXED BY i1"           {}
000041    6  "DELETE FROM main.t1 NOT INDEXED"             {}
000042    7  "DELETE FROM t1 WHERE a>2"                    {}
000043    8  "DELETE FROM t1 INDEXED BY i1 WHERE a>2"      {}
000044    9  "DELETE FROM t1 NOT INDEXED WHERE a>2"        {}
000045    10 "DELETE FROM main.t1 WHERE a>2"               {}
000046    11 "DELETE FROM main.t1 INDEXED BY i1 WHERE a>2" {}
000047    12 "DELETE FROM main.t1 NOT INDEXED WHERE a>2"   {}
000048  }
000049  
000050  # EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all
000051  # records in the table are deleted.
000052  #
000053  drop_all_tables
000054  do_test e_delete-1.0 {
000055    db transaction {
000056      foreach t {t1 t2 t3 t4 t5 t6} {
000057        execsql [string map [list %T% $t] {
000058          CREATE TABLE %T%(x, y);
000059          INSERT INTO %T% VALUES(1, 'one');
000060          INSERT INTO %T% VALUES(2, 'two');
000061          INSERT INTO %T% VALUES(3, 'three');
000062          INSERT INTO %T% VALUES(4, 'four');
000063          INSERT INTO %T% VALUES(5, 'five');
000064        }]
000065      }
000066    }
000067  } {}
000068  do_delete_tests e_delete-1.1 {
000069    1  "DELETE FROM t1       ; SELECT * FROM t1"       {}
000070    2  "DELETE FROM main.t2  ; SELECT * FROM t2"       {}
000071  }
000072  
000073  # EVIDENCE-OF: R-26300-50198 If a WHERE clause is supplied, then only
000074  # those rows for which the WHERE clause boolean expression is true are
000075  # deleted.
000076  #
000077  # EVIDENCE-OF: R-23360-48280 Rows for which the expression is false or
000078  # NULL are retained.
000079  #
000080  do_delete_tests e_delete-1.2 {
000081    1  "DELETE FROM t3 WHERE 1       ; SELECT x FROM t3"       {}
000082    2  "DELETE FROM main.t4 WHERE 0  ; SELECT x FROM t4"       {1 2 3 4 5}
000083    3  "DELETE FROM t4 WHERE 0.0     ; SELECT x FROM t4"       {1 2 3 4 5}
000084    4  "DELETE FROM t4 WHERE NULL    ; SELECT x FROM t4"       {1 2 3 4 5}
000085    5  "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4"       {2}
000086    6  "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4"       {}
000087    7  "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5" {1 2 3 4}
000088    8  "DELETE FROM t5 WHERE (SELECT max(x) FROM t4)  ;SELECT x FROM t5" {1 2 3 4}
000089    9  "DELETE FROM t5 WHERE (SELECT max(x) FROM t6)  ;SELECT x FROM t5" {}
000090    10 "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6"     {one four five}
000091  }
000092  
000093  
000094  #-------------------------------------------------------------------------
000095  # Tests for restrictions on DELETE statements that appear within trigger
000096  # programs.
000097  #
000098  forcedelete test.db2
000099  forcedelete test.db3
000100  do_execsql_test e_delete-2.0 {
000101    ATTACH 'test.db2' AS aux;
000102    ATTACH 'test.db3' AS aux2;
000103  
000104    CREATE TABLE temp.t7(a, b);   INSERT INTO temp.t7 VALUES(1, 2);
000105    CREATE TABLE main.t7(a, b);   INSERT INTO main.t7 VALUES(3, 4);
000106    CREATE TABLE aux.t7(a, b);    INSERT INTO aux.t7 VALUES(5, 6);
000107    CREATE TABLE aux2.t7(a, b);   INSERT INTO aux2.t7 VALUES(7, 8);
000108  
000109    CREATE TABLE main.t8(a, b);   INSERT INTO main.t8 VALUES(1, 2);
000110    CREATE TABLE aux.t8(a, b);    INSERT INTO aux.t8 VALUES(3, 4);
000111    CREATE TABLE aux2.t8(a, b);   INSERT INTO aux2.t8 VALUES(5, 6);
000112  
000113    CREATE TABLE aux.t9(a, b);    INSERT INTO aux.t9 VALUES(1, 2);
000114    CREATE TABLE aux2.t9(a, b);   INSERT INTO aux2.t9 VALUES(3, 4);
000115  
000116    CREATE TABLE aux2.t10(a, b);  INSERT INTO aux2.t10 VALUES(1, 2);
000117  } {}
000118  
000119  
000120  # EVIDENCE-OF: R-09681-58560 The table-name specified as part of a
000121  # DELETE statement within a trigger body must be unqualified.
000122  #
000123  # EVIDENCE-OF: R-12275-20298 In other words, the schema-name. prefix on
000124  # the table name is not allowed within triggers.
000125  #
000126  do_delete_tests e_delete-2.1 -error {
000127    qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers
000128  } {
000129    1 {
000130        CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
000131          DELETE FROM main.t2;
000132        END;
000133    } {}
000134  
000135    2 {
000136        CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN
000137          DELETE FROM temp.t7 WHERE a=new.a;
000138        END;
000139    } {}
000140  
000141    3 {
000142        CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN
000143          DELETE FROM aux2.t8 WHERE b!=a;
000144        END;
000145    } {}
000146  }
000147  
000148  # EVIDENCE-OF: R-28818-63526 If the table to which the trigger is
000149  # attached is not in the temp database, then DELETE statements within
000150  # the trigger body must operate on tables within the same database as
000151  # it.
000152  # 
000153  #   This is tested in two parts. First, check that if a table of the
000154  #   specified name does not exist, an error is raised. Secondly, test
000155  #   that if tables with the specified name exist in multiple databases,
000156  #   the local database table is used.
000157  #
000158  do_delete_tests e_delete-2.2.1 -error { no such table: %s } {
000159    1 {
000160        CREATE TRIGGER main.tr1 AFTER INSERT ON main.t7 BEGIN
000161          DELETE FROM t9;
000162        END;
000163        INSERT INTO main.t7 VALUES(1, 2);
000164    } {main.t9}
000165  
000166    2 {
000167        CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN
000168          DELETE FROM t10;
000169        END;
000170        UPDATE t9 SET a=1;
000171    } {aux.t10}
000172  }
000173  do_execsql_test e_delete-2.2.X {
000174    DROP TRIGGER main.tr1;
000175    DROP TRIGGER aux.tr2;
000176  } {}
000177  
000178  do_delete_tests e_delete-2.2.2 {
000179    1 {
000180        CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN
000181          DELETE FROM t9;
000182        END;
000183        INSERT INTO aux.t8 VALUES(1, 2);
000184  
000185        SELECT count(*) FROM aux.t9 
000186          UNION ALL
000187        SELECT count(*) FROM aux2.t9;
000188    } {0 1}
000189  
000190    2 {
000191        CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN
000192          DELETE FROM t7;
000193        END;
000194        INSERT INTO main.t8 VALUES(1, 2);
000195  
000196        SELECT count(*) FROM temp.t7 
000197          UNION ALL
000198        SELECT count(*) FROM main.t7
000199          UNION ALL
000200        SELECT count(*) FROM aux.t7
000201          UNION ALL
000202        SELECT count(*) FROM aux2.t7;
000203    } {1 0 1 1}
000204  }
000205  
000206  # EVIDENCE-OF: R-31567-38587 If the table to which the trigger is
000207  # attached is in the TEMP database, then the unqualified name of the
000208  # table being deleted is resolved in the same way as it is for a
000209  # top-level statement (by searching first the TEMP database, then the
000210  # main database, then any other databases in the order they were
000211  # attached).
000212  #
000213  do_execsql_test e_delete-2.3.0 {
000214    DROP TRIGGER aux.tr1;
000215    DROP TRIGGER main.tr1;
000216    DELETE FROM main.t8 WHERE oid>1;
000217    DELETE FROM aux.t8 WHERE oid>1;
000218    INSERT INTO aux.t9 VALUES(1, 2);
000219    INSERT INTO main.t7 VALUES(3, 4);
000220  } {}
000221  do_execsql_test e_delete-2.3.1 {
000222    SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
000223    SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;
000224  
000225    SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8  
000226    UNION ALL SELECT count(*) FROM aux2.t8;
000227  
000228    SELECT count(*) FROM aux.t9  UNION ALL SELECT count(*) FROM aux2.t9;
000229  
000230    SELECT count(*) FROM aux2.t10;
000231  } {1 1 1 1 1 1 1 1 1 1}
000232  do_execsql_test e_delete-2.3.2 {
000233    CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN
000234      DELETE FROM t7;
000235      DELETE FROM t8;
000236      DELETE FROM t9;
000237      DELETE FROM t10;
000238    END;
000239    INSERT INTO temp.t7 VALUES('hello', 'world');
000240  } {}
000241  do_execsql_test e_delete-2.3.3 {
000242    SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL
000243    SELECT count(*) FROM aux.t7  UNION ALL SELECT count(*) FROM aux2.t7;
000244  
000245    SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8  
000246    UNION ALL SELECT count(*) FROM aux2.t8;
000247  
000248    SELECT count(*) FROM aux.t9  UNION ALL SELECT count(*) FROM aux2.t9;
000249  
000250    SELECT count(*) FROM aux2.t10;
000251  } {0 1 1 1 0 1 1 0 1 0}
000252  
000253  # EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED clauses are
000254  # not allowed on DELETE statements within triggers.
000255  #
000256  do_execsql_test e_delete-2.4.0 {
000257    CREATE INDEX i8 ON t8(a, b);
000258  } {}
000259  do_delete_tests e_delete-2.4 -error {
000260    the %s %s clause is not allowed on UPDATE or DELETE statements within triggers
000261  } {
000262    1 {
000263      CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
000264        DELETE FROM t8 INDEXED BY i8 WHERE a=5;
000265      END;
000266    } {INDEXED BY}
000267    2 {
000268      CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
000269        DELETE FROM t8 NOT INDEXED WHERE a=5;
000270      END;
000271    } {NOT INDEXED}
000272  }
000273  
000274  ifcapable update_delete_limit {
000275  
000276  # EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described
000277  # below) are unsupported for DELETE statements within triggers.
000278  #
000279  do_delete_tests e_delete-2.5 -error { near "%s": syntax error } {
000280    1 {
000281      CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
000282        DELETE FROM t8 LIMIT 10;
000283      END;
000284    } {LIMIT}
000285    2 {
000286      CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN
000287        DELETE FROM t8 ORDER BY a LIMIT 5;
000288      END;
000289    } {ORDER}
000290  }
000291  
000292  # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the
000293  # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax
000294  # of the DELETE statement is extended by the addition of optional ORDER
000295  # BY and LIMIT clauses:
000296  #
000297  # -- syntax diagram delete-stmt-limited
000298  #
000299  do_delete_tests e_delete-3.1 {
000300    1   "DELETE FROM t1 LIMIT 5"                                    {}
000301    2   "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2"                       {}
000302    3   "DELETE FROM t1 LIMIT 2+2, 16/4"                            {}
000303    4   "DELETE FROM t1 ORDER BY x LIMIT 5"                         {}
000304    5   "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2"            {}
000305    6   "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4"                 {}
000306    7   "DELETE FROM t1 WHERE x>2 LIMIT 5"                          {}
000307    8   "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2"             {}
000308    9   "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4"                  {}
000309    10  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5"               {}
000310    11  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2"  {}
000311    12  "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4"       {}
000312  }
000313  
000314  drop_all_tables
000315  proc rebuild_t1 {} {
000316    catchsql { DROP TABLE t1 }
000317    execsql {
000318      CREATE TABLE t1(a, b);
000319      INSERT INTO t1 VALUES(1, 'one');
000320      INSERT INTO t1 VALUES(2, 'two');
000321      INSERT INTO t1 VALUES(3, 'three');
000322      INSERT INTO t1 VALUES(4, 'four');
000323      INSERT INTO t1 VALUES(5, 'five');
000324    }
000325  }
000326  
000327  # EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause,
000328  # the maximum number of rows that will be deleted is found by evaluating
000329  # the accompanying expression and casting it to an integer value.
000330  #
000331  rebuild_t1
000332  do_delete_tests e_delete-3.2 -repair rebuild_t1 -query {
000333    SELECT a FROM t1
000334  } {
000335    1   "DELETE FROM t1 LIMIT 3"       {4 5}
000336    2   "DELETE FROM t1 LIMIT 1+1"     {3 4 5}
000337    3   "DELETE FROM t1 LIMIT '4'"     {5}
000338    4   "DELETE FROM t1 LIMIT '1.0'"   {2 3 4 5}
000339  }
000340  
000341  # EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT
000342  # clause cannot be losslessly converted to an integer value, it is an
000343  # error.
000344  #
000345  do_delete_tests e_delete-3.3 -error { datatype mismatch } {
000346    1   "DELETE FROM t1 LIMIT 'abc'"   {}
000347    2   "DELETE FROM t1 LIMIT NULL"    {}
000348    3   "DELETE FROM t1 LIMIT X'ABCD'" {}
000349    4   "DELETE FROM t1 LIMIT 1.2"     {}
000350  }
000351  
000352  # EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as
000353  # "no limit".
000354  #
000355  do_delete_tests e_delete-3.4 -repair rebuild_t1 -query {
000356    SELECT a FROM t1
000357  } {
000358    1   "DELETE FROM t1 LIMIT -1"       {}
000359    2   "DELETE FROM t1 LIMIT 2-4"      {}
000360    3   "DELETE FROM t1 LIMIT -4.0"     {}
000361    4   "DELETE FROM t1 LIMIT 5*-1"     {}
000362  }
000363  
000364  # EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET
000365  # clause, then it is similarly evaluated and cast to an integer value.
000366  # Again, it is an error if the value cannot be losslessly converted to
000367  # an integer.
000368  #
000369  do_delete_tests e_delete-3.5 -error { datatype mismatch } {
000370    1   "DELETE FROM t1 LIMIT 1 OFFSET 'abc'"   {}
000371    2   "DELETE FROM t1 LIMIT 1 OFFSET NULL"    {}
000372    3   "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'" {}
000373    4   "DELETE FROM t1 LIMIT 1 OFFSET 1.2"     {}
000374    5   "DELETE FROM t1 LIMIT 'abc', 1"         {}
000375    6   "DELETE FROM t1 LIMIT NULL, 1"          {}
000376    7   "DELETE FROM t1 LIMIT X'ABCD', 1"       {}
000377    8   "DELETE FROM t1 LIMIT 1.2, 1"           {}
000378  }
000379  
000380  
000381  # EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the
000382  # calculated integer value is negative, the effective OFFSET value is
000383  # zero.
000384  #
000385  do_delete_tests e_delete-3.6 -repair rebuild_t1 -query {
000386    SELECT a FROM t1
000387  } {
000388    1a  "DELETE FROM t1 LIMIT 3 OFFSET 0"        {4 5}
000389    1b  "DELETE FROM t1 LIMIT 3"                 {4 5}
000390    1c  "DELETE FROM t1 LIMIT 3 OFFSET -1"       {4 5}
000391    2a  "DELETE FROM t1 LIMIT 1+1 OFFSET 0"      {3 4 5}
000392    2b  "DELETE FROM t1 LIMIT 1+1"               {3 4 5}
000393    2c  "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5"    {3 4 5}
000394    3a  "DELETE FROM t1 LIMIT '4' OFFSET 0"      {5}
000395    3b  "DELETE FROM t1 LIMIT '4'"               {5}
000396    3c  "DELETE FROM t1 LIMIT '4' OFFSET -1.0"   {5}
000397    4a  "DELETE FROM t1 LIMIT '1.0' OFFSET 0"    {2 3 4 5}
000398    4b  "DELETE FROM t1 LIMIT '1.0'"             {2 3 4 5}
000399    4c  "DELETE FROM t1 LIMIT '1.0' OFFSET -11"  {2 3 4 5}
000400  }
000401  
000402  # EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY
000403  # clause, then all rows that would be deleted in the absence of the
000404  # LIMIT clause are sorted according to the ORDER BY. The first M rows,
000405  # where M is the value found by evaluating the OFFSET clause expression,
000406  # are skipped, and the following N, where N is the value of the LIMIT
000407  # expression, are deleted.
000408  #
000409  do_delete_tests e_delete-3.7 -repair rebuild_t1 -query {
000410    SELECT a FROM t1
000411  } {
000412    1   "DELETE FROM t1 ORDER BY b LIMIT 2"               {1 2 3}
000413    2   "DELETE FROM t1 ORDER BY length(b), a LIMIT 3"    {3 5}
000414    3   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0"  {1 2 3 4}
000415    4   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1"  {1 2 3 5}
000416    5   "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2"  {1 2 4 5}
000417  }
000418  
000419  # EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining
000420  # after taking the OFFSET clause into account, or if the LIMIT clause
000421  # evaluated to a negative value, then all remaining rows are deleted.
000422  #
000423  do_delete_tests e_delete-3.8 -repair rebuild_t1 -query {
000424    SELECT a FROM t1
000425  } {
000426    1   "DELETE FROM t1 ORDER BY a ASC LIMIT 10"           {}
000427    2   "DELETE FROM t1 ORDER BY a ASC LIMIT -1"           {}
000428    3   "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2"   {1 2}
000429  }
000430  
000431  # EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY
000432  # clause, then all rows that would be deleted in the absence of the
000433  # LIMIT clause are assembled in an arbitrary order before applying the
000434  # LIMIT and OFFSET clauses to determine the subset that are actually
000435  # deleted.
000436  #
000437  #     In practice, the "arbitrary order" is rowid order.
000438  #
000439  do_delete_tests e_delete-3.9 -repair rebuild_t1 -query {
000440    SELECT a FROM t1
000441  } {
000442    1   "DELETE FROM t1 LIMIT 2"               {3 4 5}
000443    2   "DELETE FROM t1 LIMIT 3"               {4 5}
000444    3   "DELETE FROM t1 LIMIT 1 OFFSET 0"      {2 3 4 5}
000445    4   "DELETE FROM t1 LIMIT 1 OFFSET 1"      {1 3 4 5}
000446    5   "DELETE FROM t1 LIMIT 1 OFFSET 2"      {1 2 4 5}
000447  }
000448  
000449  
000450  # EVIDENCE-OF: R-07548-13422 The ORDER BY clause on a DELETE statement
000451  # is used only to determine which rows fall within the LIMIT. The order
000452  # in which rows are deleted is arbitrary and is not influenced by the
000453  # ORDER BY clause.
000454  #
000455  #     In practice, rows are always deleted in rowid order.
000456  #
000457  do_delete_tests e_delete-3.10 -repair {
000458    rebuild_t1 
000459    catchsql { DROP TABLE t1log }
000460    execsql {
000461      CREATE TABLE t1log(x);
000462      CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
000463        INSERT INTO t1log VALUES(old.a);
000464      END;
000465    }
000466  } -query {
000467    SELECT x FROM t1log
000468  } {
000469    1   "DELETE FROM t1 ORDER BY a DESC LIMIT 2"   {4 5}
000470    2   "DELETE FROM t1 ORDER BY a DESC LIMIT -1"  {1 2 3 4 5}
000471    3   "DELETE FROM t1 ORDER BY a ASC LIMIT 2"    {1 2}
000472    4   "DELETE FROM t1 ORDER BY a ASC LIMIT -1"   {1 2 3 4 5}
000473  }
000474  
000475  }
000476   
000477  finish_test