/ Check-in [fab3b383]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_delete.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fab3b383bb2c4764a56811f22ff4c783441918e8
User & Date: dan 2010-09-23 18:47:37
Context
2010-09-24
08:00
Modify testable statement ids in a few test files to account for recent docsrc changes. check-in: 7893e525 user: dan tags: trunk
2010-09-23
18:47
Add tests to e_delete.test. check-in: fab3b383 user: dan tags: trunk
2010-09-22
19:06
Further tests and changes to make the r-tree module more robust. check-in: 7ff3574b user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

  2318   2318     return rc;
  2319   2319   }
  2320   2320   
  2321   2321   /*
  2322   2322   ** If node pLeaf is not the root of the r-tree and its pParent pointer is 
  2323   2323   ** still NULL, load all ancestor nodes of pLeaf into memory and populate
  2324   2324   ** the pLeaf->pParent chain all the way up to the root node.
         2325  +**
         2326  +** This operation is required when a row is deleted (or updated - an update
         2327  +** is implemented as a delete followed by an insert). SQLite provides the
         2328  +** rowid of the row to delete, which can be used to find the leaf on which
         2329  +** the entry resides (argument pLeaf). Once the leaf is located, this 
         2330  +** function is called to determine its ancestry.
  2325   2331   */
  2326   2332   static int fixLeafParent(Rtree *pRtree, RtreeNode *pLeaf){
  2327   2333     int rc = SQLITE_OK;
  2328   2334     RtreeNode *pChild = pLeaf;
  2329   2335     while( rc==SQLITE_OK && pChild->iNode!=1 && pChild->pParent==0 ){
  2330   2336       int rc2 = SQLITE_OK;          /* sqlite3_reset() return code */
  2331   2337       sqlite3_bind_int64(pRtree->pReadParent, 1, pChild->iNode);
  2332   2338       rc = sqlite3_step(pRtree->pReadParent);
  2333   2339       if( rc==SQLITE_ROW ){
  2334         -      RtreeNode *pTest;
  2335         -      i64 iNode = sqlite3_column_int64(pRtree->pReadParent, 0);
         2340  +      RtreeNode *pTest;           /* Used to test for reference loops */
         2341  +      i64 iNode;                  /* Node number of parent node */
         2342  +
         2343  +      /* Before setting pChild->pParent, test that we are not creating a
         2344  +      ** loop of references (as we would if, say, pChild==pParent). We don't
         2345  +      ** want to do this as it leads to a memory leak when trying to delete
         2346  +      ** the referenced counted node structures.
         2347  +      */
         2348  +      iNode = sqlite3_column_int64(pRtree->pReadParent, 0);
  2336   2349         for(pTest=pLeaf; pTest && pTest->iNode!=iNode; pTest=pTest->pParent);
  2337   2350         if( !pTest ){
  2338   2351           rc2 = nodeAcquire(pRtree, iNode, 0, &pChild->pParent);
  2339   2352         }
  2340   2353       }
  2341   2354       rc = sqlite3_reset(pRtree->pReadParent);
  2342   2355       if( rc==SQLITE_OK ) rc = rc2;

Changes to ext/rtree/rtreeA.test.

    20     20   ifcapable !rtree { finish_test ; return }
    21     21   
    22     22   proc create_t1 {} {
    23     23     db close
    24     24     forcedelete test.db
    25     25     sqlite3 db test.db
    26     26     execsql {
           27  +    PRAGMA page_size = 1024;
    27     28       CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2);
    28     29     }
    29     30   }
    30     31   proc populate_t1 {} {
    31     32     execsql BEGIN
    32     33     for {set i 0} {$i < 500} {incr i} {
    33     34       set x2 [expr $i+5]
................................................................................
    67     68   
    68     69     set blob [binary format a*Sua* \
    69     70       [string range $blob 0 1] $newvalue [string range $blob 4 end]
    70     71     ]
    71     72     db eval "UPDATE ${tbl}_node SET data = \$blob WHERE nodeno=$nodeno"
    72     73     return [set_entry_count $tbl $nodeno]
    73     74   }
    74         -
    75     75   
    76     76   
    77     77   proc do_corruption_tests {prefix args} {
    78     78     set testarray [lindex $args end]
    79     79     set errormsg {database disk image is malformed}
    80     80   
    81     81     foreach {z value} [lrange $args 0 end-1] {
................................................................................
   209    209   create_t1
   210    210   populate_t1
   211    211   do_execsql_test rtreeA-6.1.0 { 
   212    212     UPDATE t1_parent set parentnode = parentnode+1
   213    213   } {}
   214    214   do_corruption_tests rtreeA-6.1 {
   215    215     1   "DELETE FROM t1 WHERE rowid = 5"
          216  +  2   "UPDATE t1 SET x1=x1+1, x2=x2+1"
   216    217   }
   217    218   
   218    219   
   219    220   finish_test

Changes to test/e_delete.test.

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

Changes to test/tester.tcl.

   347    347   #   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
   348    348   #
   349    349   # Where switches are:
   350    350   #
   351    351   #   -errorformat FMTSTRING
   352    352   #   -count
   353    353   #   -query SQL
          354  +#   -repair TCL
   354    355   #
   355    356   proc do_select_tests {prefix args} {
   356    357   
   357    358     set testlist [lindex $args end]
   358    359     set switches [lrange $args 0 end-1]
   359    360   
   360    361     set errfmt ""
   361    362     set countonly 0
   362    363     set query ""
          364  +  set repair ""
   363    365   
   364    366     for {set i 0} {$i < [llength $switches]} {incr i} {
   365    367       set s [lindex $switches $i]
   366    368       set n [string length $s]
   367    369       if {$n>=2 && [string equal -length $n $s "-query"]} {
   368    370         set query [lindex $switches [incr i]]
   369    371       } elseif {$n>=2 && [string equal -length $n $s "-errorformat"]} {
   370    372         set errfmt [lindex $switches [incr i]]
          373  +    } elseif {$n>=2 && [string equal -length $n $s "-repair"]} {
          374  +      set repair [lindex $switches [incr i]]
   371    375       } elseif {$n>=2 && [string equal -length $n $s "-count"]} {
   372    376         set countonly 1
   373    377       } else {
   374    378         error "unknown switch: $s"
   375    379       }
   376    380     }
   377    381   
................................................................................
   379    383       error "Cannot use -count and -errorformat together"
   380    384     }
   381    385     set nTestlist [llength $testlist]
   382    386     if {$nTestlist%3 || $nTestlist==0 } {
   383    387       error "SELECT test list contains [llength $testlist] elements"
   384    388     }
   385    389   
          390  +  eval $repair
   386    391     foreach {tn sql res} $testlist {
   387    392       if {$query != ""} {
   388    393         execsql $sql
   389    394         set sql $query
   390    395       }
   391    396   
   392    397       if {$countonly} {
................................................................................
   395    400         uplevel do_test ${prefix}.$tn [list [list set {} $nRow]] [list $res]
   396    401       } elseif {$errfmt==""} {
   397    402         uplevel do_execsql_test ${prefix}.${tn} [list $sql] [list [list {*}$res]]
   398    403       } else {
   399    404         set res [list 1 [string trim [format $errfmt {*}$res]]]
   400    405         uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
   401    406       }
          407  +    eval $repair
   402    408     }
          409  +
   403    410   }
   404    411   
   405    412   proc delete_all_data {} {
   406    413     db eval {SELECT tbl_name AS t FROM sqlite_master WHERE type = 'table'} {
   407    414       db eval "DELETE FROM '[string map {' ''} $t]'"
   408    415     }
   409    416   }