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: |
fab3b383bb2c4764a56811f22ff4c783 |
| 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
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 }