diff options
| -rw-r--r-- | ext/rtree/rtree.c | 17 | ||||
| -rw-r--r-- | ext/rtree/rtreeA.test | 3 | ||||
| -rw-r--r-- | manifest | 18 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | test/e_delete.test | 426 | ||||
| -rw-r--r-- | test/tester.tcl | 7 |
6 files changed, 459 insertions, 14 deletions
diff --git a/ext/rtree/rtree.c b/ext/rtree/rtree.c index 8f67c801b..b805676b7 100644 --- a/ext/rtree/rtree.c +++ b/ext/rtree/rtree.c @@ -2322,6 +2322,12 @@ splitnode_out: ** If node pLeaf is not the root of the r-tree and its pParent pointer is ** still NULL, load all ancestor nodes of pLeaf into memory and populate ** the pLeaf->pParent chain all the way up to the root node. +** +** This operation is required when a row is deleted (or updated - an update +** is implemented as a delete followed by an insert). SQLite provides the +** rowid of the row to delete, which can be used to find the leaf on which +** the entry resides (argument pLeaf). Once the leaf is located, this +** function is called to determine its ancestry. */ static int fixLeafParent(Rtree *pRtree, RtreeNode *pLeaf){ int rc = SQLITE_OK; @@ -2331,8 +2337,15 @@ static int fixLeafParent(Rtree *pRtree, RtreeNode *pLeaf){ sqlite3_bind_int64(pRtree->pReadParent, 1, pChild->iNode); rc = sqlite3_step(pRtree->pReadParent); if( rc==SQLITE_ROW ){ - RtreeNode *pTest; - i64 iNode = sqlite3_column_int64(pRtree->pReadParent, 0); + RtreeNode *pTest; /* Used to test for reference loops */ + i64 iNode; /* Node number of parent node */ + + /* Before setting pChild->pParent, test that we are not creating a + ** loop of references (as we would if, say, pChild==pParent). We don't + ** want to do this as it leads to a memory leak when trying to delete + ** the referenced counted node structures. + */ + iNode = sqlite3_column_int64(pRtree->pReadParent, 0); for(pTest=pLeaf; pTest && pTest->iNode!=iNode; pTest=pTest->pParent); if( !pTest ){ rc2 = nodeAcquire(pRtree, iNode, 0, &pChild->pParent); diff --git a/ext/rtree/rtreeA.test b/ext/rtree/rtreeA.test index d456bf6e2..e377b013c 100644 --- a/ext/rtree/rtreeA.test +++ b/ext/rtree/rtreeA.test @@ -24,6 +24,7 @@ proc create_t1 {} { forcedelete test.db sqlite3 db test.db execsql { + PRAGMA page_size = 1024; CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2); } } @@ -73,7 +74,6 @@ proc set_entry_count {tbl nodeno {newvalue ""}} { } - proc do_corruption_tests {prefix args} { set testarray [lindex $args end] set errormsg {database disk image is malformed} @@ -213,6 +213,7 @@ do_execsql_test rtreeA-6.1.0 { } {} do_corruption_tests rtreeA-6.1 { 1 "DELETE FROM t1 WHERE rowid = 5" + 2 "UPDATE t1 SET x1=x1+1, x2=x2+1" } @@ -1,5 +1,5 @@ -C Further\stests\sand\schanges\sto\smake\sthe\sr-tree\smodule\smore\srobust. -D 2010-09-22T19:06:03 +C Add\stests\sto\se_delete.test. +D 2010-09-23T18:47:37 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in c599a15d268b1db2aeadea19df2adc3bf2eb6bee F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -79,7 +79,7 @@ F ext/icu/README.txt bf8461d8cdc6b8f514c080e4e10dc3b2bbdfefa9 F ext/icu/icu.c 850e9a36567bbcce6bd85a4b68243cad8e3c2de2 F ext/icu/sqliteicu.h 728867a802baa5a96de7495e9689a8e01715ef37 F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761 -F ext/rtree/rtree.c 4e2bda4a99794d1e618486c13a76987acd547132 +F ext/rtree/rtree.c 7dffd6d90dac2e504b7315572f49c754772525dc F ext/rtree/rtree.h 834dbcb82dc85b2481cde6a07cdadfddc99e9b9e F ext/rtree/rtree1.test dbd4250ac0ad367a262eb9676f7e3080b0368206 F ext/rtree/rtree2.test acbb3a4ce0f4fbc2c304d2b4b784cfa161856bba @@ -90,7 +90,7 @@ F ext/rtree/rtree6.test 1ebe0d632a7501cc80ba5a225f028fd4f0fdda08 F ext/rtree/rtree7.test bcb647b42920b3b5d025846689147778485cc318 F ext/rtree/rtree8.test 9772e16da71e17e02bdebf0a5188590f289ab37d F ext/rtree/rtree9.test df9843d1a9195249c8d3b4ea6aedda2d5c73e9c2 -F ext/rtree/rtreeA.test ebd9dcff5eaa90d0359e9a30787f5d8da5e013ac +F ext/rtree/rtreeA.test ace05e729a36e342d40cf94e9efc7b4723d9dcdf F ext/rtree/rtree_perf.tcl 6c18c1f23cd48e0f948930c98dfdd37dfccb5195 F ext/rtree/rtree_util.tcl 06aab2ed5b826545bf215fff90ecb9255a8647ea F ext/rtree/sqlite3rtree.h 1af0899c63a688e272d69d8e746f24e76f10a3f0 @@ -348,7 +348,7 @@ F test/descidx2.test 9f1a0c83fd57f8667c82310ca21b30a350888b5d F test/descidx3.test fe720e8b37d59f4cef808b0bf4e1b391c2e56b6f F test/diskfull.test 0cede7ef9d8f415d9d3944005c76be7589bb5ebb F test/distinctagg.test 1a6ef9c87a58669438fc771450d7a72577417376 -F test/e_delete.test f01ec0855765eb3ad05d20e9a0b2a9e5e04aa585 +F test/e_delete.test d0c2be2fa1a7dd6648a4b5a2fdcbe4c802756728 F test/e_expr.test 164e87c1d7b40ceb47c57c3bffa384c81d009aa7 F test/e_fkey.test 6721a741c6499b3ab7e5385923233343c8f1ad05 F test/e_fts3.test 75bb0aee26384ef586165e21018a17f7cd843469 @@ -638,7 +638,7 @@ F test/tclsqlite.test 8c154101e704170c2be10f137a5499ac2c6da8d3 F test/tempdb.test 19d0f66e2e3eeffd68661a11c83ba5e6ace9128c F test/temptable.test f42121a0d29a62f00f93274464164177ab1cc24a F test/temptrigger.test b0273db072ce5f37cf19140ceb1f0d524bbe9f05 -F test/tester.tcl 12043a198a939c6076f1caca34dcb435c547047b +F test/tester.tcl 5a9b8727747f76c18e3a1bf3f0ed16f57a546265 F test/thread001.test a3e6a7254d1cb057836cb3145b60c10bf5b7e60f F test/thread002.test afd20095e6e845b405df4f2c920cb93301ca69db F test/thread003.test b824d4f52b870ae39fc5bae4d8070eca73085dca @@ -863,7 +863,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P 68a305fd5ac917317fee2ef6670ac389a120e502 -R 4dc37794fd753db61a2e814773c65213 +P 7ff3574b9c581b5e1f2b6f98028106c638e59bb7 +R 1b59fd312b3a99059f83ad2bf3ea3829 U dan -Z f587531c441ccd1803517229a507ab8c +Z 491ffce3dab8151bf3f81e7028f9463e diff --git a/manifest.uuid b/manifest.uuid index ce56bca3a..285c54bdb 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -7ff3574b9c581b5e1f2b6f98028106c638e59bb7
\ No newline at end of file +fab3b383bb2c4764a56811f22ff4c783441918e8
\ No newline at end of file diff --git a/test/e_delete.test b/test/e_delete.test index 08e5690f3..18e4bcbe2 100644 --- a/test/e_delete.test +++ b/test/e_delete.test @@ -42,6 +42,430 @@ do_delete_tests e_delete-0.1 { 11 "DELETE FROM main.t1 INDEXED BY i1 WHERE a>2" {} 12 "DELETE FROM main.t1 NOT INDEXED WHERE a>2" {} } + +# EVIDENCE-OF: R-20205-17349 If the WHERE clause is not present, all +# records in the table are deleted. +# +drop_all_tables +do_test e_delete-1.0 { + db transaction { + foreach t {t1 t2 t3 t4 t5 t6} { + execsql [string map [list %T% $t] { + CREATE TABLE %T%(x, y); + INSERT INTO %T% VALUES(1, 'one'); + INSERT INTO %T% VALUES(2, 'two'); + INSERT INTO %T% VALUES(3, 'three'); + INSERT INTO %T% VALUES(4, 'four'); + INSERT INTO %T% VALUES(5, 'five'); + }] + } + } +} {} +do_delete_tests e_delete-1.1 { + 1 "DELETE FROM t1 ; SELECT * FROM t1" {} + 2 "DELETE FROM main.t2 ; SELECT * FROM t2" {} +} + +# EVIDENCE-OF: R-25092-63878 If a WHERE clause is supplied, then only +# those rows for which evaluating the WHERE clause and casting the +# result to a NUMERIC value produces a result other than NULL or zero +# (integer value 0 or real value 0.0). +# +do_delete_tests e_delete-1.2 { + 1 "DELETE FROM t3 WHERE 1 ; SELECT x FROM t3" {} + 2 "DELETE FROM main.t4 WHERE 0 ; SELECT x FROM t4" {1 2 3 4 5} + 3 "DELETE FROM t4 WHERE 0.0 ; SELECT x FROM t4" {1 2 3 4 5} + 4 "DELETE FROM t4 WHERE NULL ; SELECT x FROM t4" {1 2 3 4 5} + 5 "DELETE FROM t4 WHERE y!='two'; SELECT x FROM t4" {2} + 6 "DELETE FROM t4 WHERE y='two' ; SELECT x FROM t4" {} + 7 "DELETE FROM t5 WHERE x=(SELECT max(x) FROM t5);SELECT x FROM t5" {1 2 3 4} + 8 "DELETE FROM t5 WHERE (SELECT max(x) FROM t4) ;SELECT x FROM t5" {1 2 3 4} + 9 "DELETE FROM t5 WHERE (SELECT max(x) FROM t6) ;SELECT x FROM t5" {} + 10 "DELETE FROM t6 WHERE y>'seven' ; SELECT y FROM t6" {one four five} +} + + +#------------------------------------------------------------------------- +# Tests for restrictions on DELETE statements that appear within trigger +# programs. +# +forcedelete test.db2 +forcedelete test.db3 +do_execsql_test e_delete-2.0 { + ATTACH 'test.db2' AS aux; + ATTACH 'test.db3' AS aux2; + + CREATE TABLE temp.t7(a, b); INSERT INTO temp.t7 VALUES(1, 2); + CREATE TABLE main.t7(a, b); INSERT INTO main.t7 VALUES(3, 4); + CREATE TABLE aux.t7(a, b); INSERT INTO aux.t7 VALUES(5, 6); + CREATE TABLE aux2.t7(a, b); INSERT INTO aux2.t7 VALUES(7, 8); + + CREATE TABLE main.t8(a, b); INSERT INTO main.t8 VALUES(1, 2); + CREATE TABLE aux.t8(a, b); INSERT INTO aux.t8 VALUES(3, 4); + CREATE TABLE aux2.t8(a, b); INSERT INTO aux2.t8 VALUES(5, 6); + + CREATE TABLE aux.t9(a, b); INSERT INTO aux.t9 VALUES(1, 2); + CREATE TABLE aux2.t9(a, b); INSERT INTO aux2.t9 VALUES(3, 4); + + CREATE TABLE aux2.t10(a, b); INSERT INTO aux2.t10 VALUES(1, 2); +} {} + + +# EVIDENCE-OF: R-09681-58560 The table-name specified as part of a +# DELETE statement within a trigger body must be unqualified. +# +# EVIDENCE-OF: R-36771-43788 In other words, the database-name. prefix +# on the table name is not allowed within triggers. +# +do_delete_tests e_delete-2.1 -error { + qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers +} { + 1 { + CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN + DELETE FROM main.t2; + END; + } {} + + 2 { + CREATE TRIGGER tr1 BEFORE UPDATE ON t2 BEGIN + DELETE FROM temp.t7 WHERE a=new.a; + END; + } {} + + 3 { + CREATE TRIGGER tr1 AFTER UPDATE ON t8 BEGIN + DELETE FROM aux2.t8 WHERE b!=a; + END; + } {} +} + +# EVIDENCE-OF: R-28818-63526 If the table to which the trigger is +# attached is not in the temp database, then DELETE statements within +# the trigger body must operate on tables within the same database as +# it. +# +# This is tested in two parts. First, check that if a table of the +# specified name does not exist, an error is raised. Secondly, test +# that if tables with the specified name exist in multiple databases, +# the local database table is used. +# +do_delete_tests e_delete-2.2.1 -error { no such table: %s } { + 1 { + CREATE TRIGGER main.tr1 AFTER INSERT ON main.t7 BEGIN + DELETE FROM t9; + END; + INSERT INTO main.t7 VALUES(1, 2); + } {main.t9} + + 2 { + CREATE TRIGGER aux.tr2 BEFORE UPDATE ON t9 BEGIN + DELETE FROM t10; + END; + UPDATE t9 SET a=1; + } {aux.t10} +} +do_execsql_test e_delete-2.2.X { + DROP TRIGGER main.tr1; + DROP TRIGGER aux.tr2; +} {} + +do_delete_tests e_delete-2.2.2 { + 1 { + CREATE TRIGGER aux.tr1 AFTER INSERT ON t8 BEGIN + DELETE FROM t9; + END; + INSERT INTO aux.t8 VALUES(1, 2); + + SELECT count(*) FROM aux.t9 + UNION ALL + SELECT count(*) FROM aux2.t9; + } {0 1} + + 2 { + CREATE TRIGGER main.tr1 AFTER INSERT ON t8 BEGIN + DELETE FROM t7; + END; + INSERT INTO main.t8 VALUES(1, 2); + + SELECT count(*) FROM temp.t7 + UNION ALL + SELECT count(*) FROM main.t7 + UNION ALL + SELECT count(*) FROM aux.t7 + UNION ALL + SELECT count(*) FROM aux2.t7; + } {1 0 1 1} +} + +# EVIDENCE-OF: R-31567-38587 If the table to which the trigger is +# attached is in the TEMP database, then the unqualified name of the +# table being deleted is resolved in the same way as it is for a +# top-level statement (by searching first the TEMP database, then the +# main database, then any other databases in the order they were +# attached). +# +do_execsql_test e_delete-2.3.0 { + DROP TRIGGER aux.tr1; + DROP TRIGGER main.tr1; + DELETE FROM main.t8 WHERE oid>1; + DELETE FROM aux.t8 WHERE oid>1; + INSERT INTO aux.t9 VALUES(1, 2); + INSERT INTO main.t7 VALUES(3, 4); +} {} +do_execsql_test e_delete-2.3.1 { + SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL + SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7; + + SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8 + UNION ALL SELECT count(*) FROM aux2.t8; + + SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9; + + SELECT count(*) FROM aux2.t10; +} {1 1 1 1 1 1 1 1 1 1} +do_execsql_test e_delete-2.3.2 { + CREATE TRIGGER temp.tr1 AFTER INSERT ON t7 BEGIN + DELETE FROM t7; + DELETE FROM t8; + DELETE FROM t9; + DELETE FROM t10; + END; + INSERT INTO temp.t7 VALUES('hello', 'world'); +} {} +do_execsql_test e_delete-2.3.3 { + SELECT count(*) FROM temp.t7 UNION ALL SELECT count(*) FROM main.t7 UNION ALL + SELECT count(*) FROM aux.t7 UNION ALL SELECT count(*) FROM aux2.t7; + + SELECT count(*) FROM main.t8 UNION ALL SELECT count(*) FROM aux.t8 + UNION ALL SELECT count(*) FROM aux2.t8; + + SELECT count(*) FROM aux.t9 UNION ALL SELECT count(*) FROM aux2.t9; + + SELECT count(*) FROM aux2.t10; +} {0 1 1 1 0 1 1 0 1 0} + +# EVIDENCE-OF: R-28691-49464 The INDEXED BY and NOT INDEXED clauses are +# not allowed on DELETE statements within triggers. +# +do_execsql_test e_delete-2.4.0 { + CREATE INDEX i8 ON t8(a, b); +} {} +do_delete_tests e_delete-2.4 -error { + the %s %s clause is not allowed on UPDATE or DELETE statements within triggers +} { + 1 { + CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN + DELETE FROM t8 INDEXED BY i8 WHERE a=5; + END; + } {INDEXED BY} + 2 { + CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN + DELETE FROM t8 NOT INDEXED WHERE a=5; + END; + } {NOT INDEXED} +} + +ifcapable update_delete_limit { + +# EVIDENCE-OF: R-64942-06615 The LIMIT and ORDER BY clauses (described +# below) are unsupported for DELETE statements within triggers. +# +do_delete_tests e_delete-2.5 -error { near "%s": syntax error } { + 1 { + CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN + DELETE FROM t8 LIMIT 10; + END; + } {LIMIT} + 2 { + CREATE TRIGGER tr3 AFTER INSERT ON t8 BEGIN + DELETE FROM t8 ORDER BY a LIMIT 5; + END; + } {ORDER} +} + +# EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the +# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax +# of the DELETE statement is extended by the addition of optional ORDER +# BY and LIMIT clauses: +# +# EVIDENCE-OF: R-49959-20251 -- syntax diagram delete-stmt-limited +# +do_delete_tests e_delete-3.1 { + 1 "DELETE FROM t1 LIMIT 5" {} + 2 "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2" {} + 3 "DELETE FROM t1 LIMIT 2+2, 16/4" {} + 4 "DELETE FROM t1 ORDER BY x LIMIT 5" {} + 5 "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2" {} + 6 "DELETE FROM t1 ORDER BY x LIMIT 2+2, 16/4" {} + 7 "DELETE FROM t1 WHERE x>2 LIMIT 5" {} + 8 "DELETE FROM t1 WHERE x>2 LIMIT 5-1 OFFSET 2+2" {} + 9 "DELETE FROM t1 WHERE x>2 LIMIT 2+2, 16/4" {} + 10 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5" {} + 11 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 5-1 OFFSET 2+2" {} + 12 "DELETE FROM t1 WHERE x>2 ORDER BY x LIMIT 2+2, 16/4" {} +} + +drop_all_tables +proc rebuild_t1 {} { + catchsql { DROP TABLE t1 } + execsql { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 'one'); + INSERT INTO t1 VALUES(2, 'two'); + INSERT INTO t1 VALUES(3, 'three'); + INSERT INTO t1 VALUES(4, 'four'); + INSERT INTO t1 VALUES(5, 'five'); + } +} + +# EVIDENCE-OF: R-44062-08550 If a DELETE statement has a LIMIT clause, +# the maximum number of rows that will be deleted is found by evaluating +# the accompanying expression and casting it to an integer value. +# +rebuild_t1 +do_delete_tests e_delete-3.2 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1 "DELETE FROM t1 LIMIT 3" {4 5} + 2 "DELETE FROM t1 LIMIT 1+1" {3 4 5} + 3 "DELETE FROM t1 LIMIT '4'" {5} + 4 "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5} +} + +# EVIDENCE-OF: R-02661-56399 If the result of the evaluating the LIMIT +# clause cannot be losslessly converted to an integer value, it is an +# error. +# +do_delete_tests e_delete-3.3 -error { datatype mismatch } { + 1 "DELETE FROM t1 LIMIT 'abc'" {} + 2 "DELETE FROM t1 LIMIT NULL" {} + 3 "DELETE FROM t1 LIMIT X'ABCD'" {} + 4 "DELETE FROM t1 LIMIT 1.2" {} +} + +# EVIDENCE-OF: R-00598-03741 A negative LIMIT value is interpreted as +# "no limit". +# +do_delete_tests e_delete-3.4 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1 "DELETE FROM t1 LIMIT -1" {} + 2 "DELETE FROM t1 LIMIT 2-4" {} + 3 "DELETE FROM t1 LIMIT -4.0" {} + 4 "DELETE FROM t1 LIMIT 5*-1" {} +} + +# EVIDENCE-OF: R-26377-49195 If the DELETE statement also has an OFFSET +# clause, then it is similarly evaluated and cast to an integer value. +# Again, it is an error if the value cannot be losslessly converted to +# an integer. +# +do_delete_tests e_delete-3.5 -error { datatype mismatch } { + 1 "DELETE FROM t1 LIMIT 1 OFFSET 'abc'" {} + 2 "DELETE FROM t1 LIMIT 1 OFFSET NULL" {} + 3 "DELETE FROM t1 LIMIT 1 OFFSET X'ABCD'" {} + 4 "DELETE FROM t1 LIMIT 1 OFFSET 1.2" {} + 5 "DELETE FROM t1 LIMIT 'abc', 1" {} + 6 "DELETE FROM t1 LIMIT NULL, 1" {} + 7 "DELETE FROM t1 LIMIT X'ABCD', 1" {} + 8 "DELETE FROM t1 LIMIT 1.2, 1" {} +} + + +# EVIDENCE-OF: R-64004-53814 If there is no OFFSET clause, or the +# calculated integer value is negative, the effective OFFSET value is +# zero. +# +do_delete_tests e_delete-3.6 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1a "DELETE FROM t1 LIMIT 3 OFFSET 0" {4 5} + 1b "DELETE FROM t1 LIMIT 3" {4 5} + 1c "DELETE FROM t1 LIMIT 3 OFFSET -1" {4 5} + 2a "DELETE FROM t1 LIMIT 1+1 OFFSET 0" {3 4 5} + 2b "DELETE FROM t1 LIMIT 1+1" {3 4 5} + 2c "DELETE FROM t1 LIMIT 1+1 OFFSET 2-5" {3 4 5} + 3a "DELETE FROM t1 LIMIT '4' OFFSET 0" {5} + 3b "DELETE FROM t1 LIMIT '4'" {5} + 3c "DELETE FROM t1 LIMIT '4' OFFSET -1.0" {5} + 4a "DELETE FROM t1 LIMIT '1.0' OFFSET 0" {2 3 4 5} + 4b "DELETE FROM t1 LIMIT '1.0'" {2 3 4 5} + 4c "DELETE FROM t1 LIMIT '1.0' OFFSET -11" {2 3 4 5} +} + +# EVIDENCE-OF: R-48141-52334 If the DELETE statement has an ORDER BY +# clause, then all rows that would be deleted in the absence of the +# LIMIT clause are sorted according to the ORDER BY. The first M rows, +# where M is the value found by evaluating the OFFSET clause expression, +# are skipped, and the following N, where N is the value of the LIMIT +# expression, are deleted. +# +do_delete_tests e_delete-3.7 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1 "DELETE FROM t1 ORDER BY b LIMIT 2" {1 2 3} + 2 "DELETE FROM t1 ORDER BY length(b), a LIMIT 3" {3 5} + 3 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 0" {1 2 3 4} + 4 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 1" {1 2 3 5} + 5 "DELETE FROM t1 ORDER BY a DESC LIMIT 1 OFFSET 2" {1 2 4 5} +} + +# EVIDENCE-OF: R-64535-08414 If there are less than N rows remaining +# after taking the OFFSET clause into account, or if the LIMIT clause +# evaluated to a negative value, then all remaining rows are deleted. +# +do_delete_tests e_delete-3.8 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1 "DELETE FROM t1 ORDER BY a ASC LIMIT 10" {} + 2 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {} + 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 4 OFFSET 2" {1 2} +} + +# EVIDENCE-OF: R-37284-06965 If the DELETE statement has no ORDER BY +# clause, then all rows that would be deleted in the absence of the +# LIMIT clause are assembled in an arbitrary order before applying the +# LIMIT and OFFSET clauses to determine the subset that are actually +# deleted. +# +# In practice, the "arbitrary order" is rowid order. +# +do_delete_tests e_delete-3.9 -repair rebuild_t1 -query { + SELECT a FROM t1 +} { + 1 "DELETE FROM t1 LIMIT 2" {3 4 5} + 2 "DELETE FROM t1 LIMIT 3" {4 5} + 3 "DELETE FROM t1 LIMIT 1 OFFSET 0" {2 3 4 5} + 4 "DELETE FROM t1 LIMIT 1 OFFSET 1" {1 3 4 5} + 5 "DELETE FROM t1 LIMIT 1 OFFSET 2" {1 2 4 5} +} + + +# EVIDENCE-OF: R-26627-30313 The ORDER BY clause on an DELETE statement +# is used only to determine which rows fall within the LIMIT. The order +# in which rows are deleted is arbitrary and is not influenced by the +# ORDER BY clause. +# +# In practice, rows are always deleted in rowid order. +# +do_delete_tests e_delete-3.10 -repair { + rebuild_t1 + catchsql { DROP TABLE t1log } + execsql { + CREATE TABLE t1log(x); + CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN + INSERT INTO t1log VALUES(old.a); + END; + } +} -query { + SELECT x FROM t1log +} { + 1 "DELETE FROM t1 ORDER BY a DESC LIMIT 2" {4 5} + 2 "DELETE FROM t1 ORDER BY a DESC LIMIT -1" {1 2 3 4 5} + 3 "DELETE FROM t1 ORDER BY a ASC LIMIT 2" {1 2} + 4 "DELETE FROM t1 ORDER BY a ASC LIMIT -1" {1 2 3 4 5} +} + +} finish_test - diff --git a/test/tester.tcl b/test/tester.tcl index 14e0ff849..c88181c65 100644 --- a/test/tester.tcl +++ b/test/tester.tcl @@ -351,6 +351,7 @@ proc do_catchsql_test {testname sql result} { # -errorformat FMTSTRING # -count # -query SQL +# -repair TCL # proc do_select_tests {prefix args} { @@ -360,6 +361,7 @@ proc do_select_tests {prefix args} { set errfmt "" set countonly 0 set query "" + set repair "" for {set i 0} {$i < [llength $switches]} {incr i} { set s [lindex $switches $i] @@ -368,6 +370,8 @@ proc do_select_tests {prefix args} { set query [lindex $switches [incr i]] } elseif {$n>=2 && [string equal -length $n $s "-errorformat"]} { set errfmt [lindex $switches [incr i]] + } elseif {$n>=2 && [string equal -length $n $s "-repair"]} { + set repair [lindex $switches [incr i]] } elseif {$n>=2 && [string equal -length $n $s "-count"]} { set countonly 1 } else { @@ -383,6 +387,7 @@ proc do_select_tests {prefix args} { error "SELECT test list contains [llength $testlist] elements" } + eval $repair foreach {tn sql res} $testlist { if {$query != ""} { execsql $sql @@ -399,7 +404,9 @@ proc do_select_tests {prefix args} { set res [list 1 [string trim [format $errfmt {*}$res]]] uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res] } + eval $repair } + } proc delete_all_data {} { |