AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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# This file implements regression tests for SQLite library. The12# focus of this file is testing the DELETE FROM statement.13#14# $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Try to delete from a non-existant table.20#21do_test delete-1.1 {22 set v [catch {execsql {DELETE FROM test1}} msg]23 lappend v $msg24} {1 {no such table: test1}}25 26# Try to delete from sqlite_master27#28do_test delete-2.1 {29 set v [catch {execsql {DELETE FROM sqlite_master}} msg]30 lappend v $msg31} {1 {table sqlite_master may not be modified}}32 33# Delete selected entries from a table with and without an index.34#35do_test delete-3.1.1 {36 execsql {CREATE TABLE table1(f1 int, f2 int)}37 execsql {INSERT INTO table1 VALUES(1,2)}38 execsql {INSERT INTO table1 VALUES(2,4)}39 execsql {INSERT INTO table1 VALUES(3,8)}40 execsql {INSERT INTO table1 VALUES(4,16)}41 execsql {SELECT * FROM table1 ORDER BY f1}42} {1 2 2 4 3 8 4 16}43do_test delete-3.1.2 {44 execsql {DELETE FROM table1 WHERE f1=3}45} {}46do_test delete-3.1.3 {47 execsql {SELECT * FROM table1 ORDER BY f1}48} {1 2 2 4 4 16}49do_test delete-3.1.4 {50 execsql {CREATE INDEX index1 ON table1(f1)}51 execsql {PRAGMA count_changes=on}52 ifcapable explain {53 execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}54 }55 execsql {DELETE FROM 'table1' WHERE f1=3}56} {0}57do_test delete-3.1.5 {58 execsql {SELECT * FROM table1 ORDER BY f1}59} {1 2 2 4 4 16}60do_test delete-3.1.6.1 {61 execsql {DELETE FROM table1 WHERE f1=2}62} {1}63do_test delete-3.1.6.2 {64 db changes65} 166do_test delete-3.1.7 {67 execsql {SELECT * FROM table1 ORDER BY f1}68} {1 2 4 16}69integrity_check delete-3.270 71# Semantic errors in the WHERE clause72#73do_test delete-4.1 {74 execsql {CREATE TABLE table2(f1 int, f2 int)}75 set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]76 lappend v $msg77} {1 {no such column: f3}}78 79do_test delete-4.2 {80 set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]81 lappend v $msg82} {1 {no such function: xyzzy}}83integrity_check delete-4.384 85# Lots of deletes86#87do_test delete-5.1.1 {88 execsql {DELETE FROM table1}89} {2}90do_test delete-5.1.2 {91 execsql {SELECT count(*) FROM table1}92} {0}93do_test delete-5.2.1 {94 execsql {BEGIN TRANSACTION}95 for {set i 1} {$i<=200} {incr i} {96 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"97 }98 execsql {COMMIT}99 execsql {SELECT count(*) FROM table1}100} {200}101do_test delete-5.2.2 {102 execsql {DELETE FROM table1}103} {200}104do_test delete-5.2.3 {105 execsql {BEGIN TRANSACTION}106 for {set i 1} {$i<=200} {incr i} {107 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"108 }109 execsql {COMMIT}110 execsql {SELECT count(*) FROM table1}111} {200}112do_test delete-5.2.4 {113 execsql {PRAGMA count_changes=off}114 execsql {DELETE FROM table1}115} {}116do_test delete-5.2.5 {117 execsql {SELECT count(*) FROM table1}118} {0}119do_test delete-5.2.6 {120 execsql {BEGIN TRANSACTION}121 for {set i 1} {$i<=200} {incr i} {122 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"123 }124 execsql {COMMIT}125 execsql {SELECT count(*) FROM table1}126} {200}127do_test delete-5.3 {128 for {set i 1} {$i<=200} {incr i 4} {129 execsql "DELETE FROM table1 WHERE f1==$i"130 }131 execsql {SELECT count(*) FROM table1}132} {150}133do_test delete-5.4.1 {134 execsql "DELETE FROM table1 WHERE f1>50"135 db changes136} [db one {SELECT count(*) FROM table1 WHERE f1>50}]137do_test delete-5.4.2 {138 execsql {SELECT count(*) FROM table1}139} {37}140do_test delete-5.5 {141 for {set i 1} {$i<=70} {incr i 3} {142 execsql "DELETE FROM table1 WHERE f1==$i"143 }144 execsql {SELECT f1 FROM table1 ORDER BY f1}145} {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50}146do_test delete-5.6 {147 for {set i 1} {$i<40} {incr i} {148 execsql "DELETE FROM table1 WHERE f1==$i"149 }150 execsql {SELECT f1 FROM table1 ORDER BY f1}151} {42 44 47 48 50}152do_test delete-5.7 {153 execsql "DELETE FROM table1 WHERE f1!=48"154 execsql {SELECT f1 FROM table1 ORDER BY f1}155} {48}156integrity_check delete-5.8157 158 159# Delete large quantities of data. We want to test the List overflow160# mechanism in the vdbe.161#162do_test delete-6.1 {163 execsql {BEGIN; DELETE FROM table1}164 for {set i 1} {$i<=3000} {incr i} {165 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"166 }167 execsql {DELETE FROM table2}168 for {set i 1} {$i<=3000} {incr i} {169 execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"170 }171 execsql {COMMIT}172 execsql {SELECT count(*) FROM table1}173} {3000}174do_test delete-6.2 {175 execsql {SELECT count(*) FROM table2}176} {3000}177do_test delete-6.3 {178 execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}179} {1 2 3 4 5 6 7 8 9}180do_test delete-6.4 {181 execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}182} {1 2 3 4 5 6 7 8 9}183do_test delete-6.5.1 {184 execsql {DELETE FROM table1 WHERE f1>7}185 db changes186} {2993}187do_test delete-6.5.2 {188 execsql {SELECT f1 FROM table1 ORDER BY f1}189} {1 2 3 4 5 6 7}190do_test delete-6.6 {191 execsql {DELETE FROM table2 WHERE f1>7}192 execsql {SELECT f1 FROM table2 ORDER BY f1}193} {1 2 3 4 5 6 7}194do_test delete-6.7 {195 execsql {DELETE FROM table1}196 execsql {SELECT f1 FROM table1}197} {}198do_test delete-6.8 {199 execsql {INSERT INTO table1 VALUES(2,3)}200 execsql {SELECT f1 FROM table1}201} {2}202do_test delete-6.9 {203 execsql {DELETE FROM table2}204 execsql {SELECT f1 FROM table2}205} {}206do_test delete-6.10 {207 execsql {INSERT INTO table2 VALUES(2,3)}208 execsql {SELECT f1 FROM table2}209} {2}210integrity_check delete-6.11211 212do_test delete-7.1 {213 execsql {214 CREATE TABLE t3(a);215 INSERT INTO t3 VALUES(1);216 INSERT INTO t3 SELECT a+1 FROM t3;217 INSERT INTO t3 SELECT a+2 FROM t3;218 SELECT * FROM t3;219 }220} {1 2 3 4}221ifcapable {trigger} {222 do_test delete-7.2 {223 execsql {224 CREATE TABLE cnt(del);225 INSERT INTO cnt VALUES(0);226 CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN227 UPDATE cnt SET del=del+1;228 END;229 DELETE FROM t3 WHERE a<2;230 SELECT * FROM t3;231 }232 } {2 3 4}233 do_test delete-7.3 {234 execsql {235 SELECT * FROM cnt;236 }237 } {1}238 do_test delete-7.4 {239 execsql {240 DELETE FROM t3;241 SELECT * FROM t3;242 }243 } {}244 do_test delete-7.5 {245 execsql {246 SELECT * FROM cnt;247 }248 } {4}249 do_test delete-7.6 {250 execsql {251 INSERT INTO t3 VALUES(1);252 INSERT INTO t3 SELECT a+1 FROM t3;253 INSERT INTO t3 SELECT a+2 FROM t3;254 CREATE TABLE t4 AS SELECT * FROM t3;255 PRAGMA count_changes=ON;256 DELETE FROM t3;257 DELETE FROM t4;258 }259 } {4 4}260} ;# endif trigger261ifcapable {!trigger} {262 execsql {DELETE FROM t3}263}264integrity_check delete-7.7265 266# Make sure error messages are consistent when attempting to delete267# from a read-only database. Ticket #304.268#269do_test delete-8.0 {270 execsql {271 PRAGMA count_changes=OFF;272 INSERT INTO t3 VALUES(123);273 SELECT * FROM t3;274 }275} {123}276db close277catch {forcedelete test.db-journal}278catch {file attributes test.db -permissions 0444}279catch {file attributes test.db -readonly 1}280sqlite3 db test.db281set ::DB [sqlite3_connection_pointer db]282do_test delete-8.1 {283 catchsql {284 DELETE FROM t3;285 }286} {1 {attempt to write a readonly database}}287do_test delete-8.2 {288 execsql {SELECT * FROM t3} 289} {123}290do_test delete-8.3 {291 catchsql {292 DELETE FROM t3 WHERE 1;293 }294} {1 {attempt to write a readonly database}}295do_test delete-8.4 {296 execsql {SELECT * FROM t3} 297} {123}298 299# Update for v3: In v2 the DELETE statement would succeed because no300# database writes actually occur. Version 3 refuses to open a transaction301# on a read-only file, so the statement fails.302do_test delete-8.5 {303 catchsql {304 DELETE FROM t3 WHERE a<100;305 }306# v2 result: {0 {}}307} {1 {attempt to write a readonly database}}308do_test delete-8.6 {309 execsql {SELECT * FROM t3}310} {123}311integrity_check delete-8.7312 313# Need to do the following for tcl 8.5 on mac. On that configuration, the314# -readonly flag is taken so seriously that a subsequent [forcedelete]315# (required before the next test file can be executed) will fail.316#317catch {file attributes test.db -readonly 0}318db close319forcedelete test.db test.db-journal320 321# The following tests verify that SQLite correctly handles the case322# where an index B-Tree is being scanned, the rowid column being read323# from each index entry and another statement deletes some rows from324# the index B-Tree. At one point this (obscure) scenario was causing 325# SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect326# query results. 327#328do_test delete-9.1 {329 sqlite3 db test.db330 execsql {331 CREATE TABLE t5(a, b);332 CREATE TABLE t6(c, d);333 INSERT INTO t5 VALUES(1, 2);334 INSERT INTO t5 VALUES(3, 4);335 INSERT INTO t5 VALUES(5, 6);336 INSERT INTO t6 VALUES('a', 'b');337 INSERT INTO t6 VALUES('c', 'd');338 CREATE INDEX i5 ON t5(a);339 CREATE INDEX i6 ON t6(c);340 }341} {}342do_test delete-9.2 {343 set res [list]344 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {345 if {$r==2} { db eval { DELETE FROM t5 } }346 lappend res $r $c $d347 }348 set res349} {1 a b 1 c d 2 a b {} c d}350do_test delete-9.3 {351 execsql {352 INSERT INTO t5 VALUES(1, 2);353 INSERT INTO t5 VALUES(3, 4);354 INSERT INTO t5 VALUES(5, 6);355 }356 set res [list]357 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {358 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } }359 lappend res $r $c $d360 }361 set res362} {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}363do_test delete-9.4 {364 execsql {365 DELETE FROM t5;366 INSERT INTO t5 VALUES(1, 2);367 INSERT INTO t5 VALUES(3, 4);368 INSERT INTO t5 VALUES(5, 6);369 }370 set res [list]371 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {372 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } }373 lappend res $r $c $d374 }375 set res376} {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}377do_test delete-9.5 {378 execsql {379 DELETE FROM t5;380 INSERT INTO t5 VALUES(1, 2);381 INSERT INTO t5 VALUES(3, 4);382 INSERT INTO t5 VALUES(5, 6);383 }384 set res [list]385 db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {386 if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } }387 lappend res $r $c $d388 }389 set res390} {1 a b 1 c d 2 a b 2 c d}391 392do_execsql_test delete-10.0 {393 CREATE TABLE t1(a INT UNIQUE, b INT);394 INSERT INTO t1(a,b) VALUES('1','2');395 SELECT * FROM t1 WHERE a='1' AND b='2';396} {1 2}397 398do_execsql_test delete-10.1 {399 DELETE FROM t1 WHERE a='1' AND b='2';400}401 402do_execsql_test delete-10.2 {403 SELECT * FROM t1 WHERE a='1' AND b='2';404}405 406do_execsql_test delete-11.0 {407 CREATE TABLE t11(a INTEGER PRIMARY KEY, b INT);408 WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<20)409 INSERT INTO t11(a,b) SELECT x, (x*17)%100 FROM cnt;410 SELECT * FROM t11;411} {1 17 2 34 3 51 4 68 5 85 6 2 7 19 8 36 9 53 10 70 11 87 12 4 13 21 14 38 15 55 16 72 17 89 18 6 19 23 20 40}412do_execsql_test delete-11.1 {413 DELETE FROM t11 AS xyz414 WHERE EXISTS(SELECT 1 FROM t11 WHERE t11.a>xyz.a AND t11.b<=xyz.b);415 SELECT * FROM t11;416} {6 2 12 4 18 6 19 23 20 40}417 418 419# 2023-03-15420# https://sqlite.org/forum/forumpost/e61252062c9d286d421#422# When the WHERE clause of a DELETE statement contains a subquery423# which uses the table that is being deleted from and there is a424# short-circuit operator of some kind in the WHERE clause such that425# the subquery might not run right away, then the subquery might426# run after one or more rows have been deleted, which can change427# the result of the subquery, and result in the wrong answer.428#429# Similar problem for UPDATE tested by update-21.4430# https://sqlite.org/forum/forumpost/0007d1fdb1431#432reset_db433do_execsql_test delete-12.0 {434 CREATE TABLE t0(vkey INTEGER, pkey INTEGER,c1 INTEGER);435 INSERT INTO t0 VALUES(2,1,-20),(2,2,NULL),(2,3,0),(8,4,95);436 DELETE FROM t0 WHERE NOT (437 (t0.vkey <= t0.c1) AND438 (t0.vkey <> (SELECT vkey FROM t0 ORDER BY vkey LIMIT 1 OFFSET 2))439 );440 SELECT * FROM t0;441} {8 4 95}442 443finish_test444