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