AryaWu/sqlite
0
1# 2005 August 242#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 script is a test of the DELETE command.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix delete418 19do_execsql_test 1.1 {20 CREATE TABLE t1(x INTEGER PRIMARY KEY, y);21 INSERT INTO t1 VALUES(1, 0);22 INSERT INTO t1 VALUES(2, 1);23 INSERT INTO t1 VALUES(3, 0);24 INSERT INTO t1 VALUES(4, 1);25 INSERT INTO t1 VALUES(5, 0);26 INSERT INTO t1 VALUES(6, 1);27 INSERT INTO t1 VALUES(7, 0);28 INSERT INTO t1 VALUES(8, 1);29}30do_execsql_test 1.2 {31 DELETE FROM t1 WHERE y=1;32}33do_execsql_test 1.3 {34 SELECT x FROM t1;35} {1 3 5 7}36 37#-------------------------------------------------------------------------38#39reset_db40do_execsql_test 2.1 {41 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z);42 INSERT INTO t1 VALUES(1, 0, randomblob(200));43 INSERT INTO t1 VALUES(2, 1, randomblob(200));44 INSERT INTO t1 VALUES(3, 0, randomblob(200));45 INSERT INTO t1 VALUES(4, 1, randomblob(200));46 INSERT INTO t1 VALUES(5, 0, randomblob(200));47 INSERT INTO t1 VALUES(6, 1, randomblob(200));48 INSERT INTO t1 VALUES(7, 0, randomblob(200));49 INSERT INTO t1 VALUES(8, 1, randomblob(200));50}51do_execsql_test 2.2 {52 DELETE FROM t1 WHERE y=1;53}54do_execsql_test 2.3 {55 SELECT x FROM t1;56} {1 3 5 7}57 58 59#-------------------------------------------------------------------------60#61reset_db62do_execsql_test 3.0.1 {63 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID;64 INSERT INTO t1 VALUES(1, 2);65 INSERT INTO t1 VALUES(2, 4);66 INSERT INTO t1 VALUES(1, 5);67 DELETE FROM t1 WHERE a=1;68 SELECT printf('(%d)',changes());69 SELECT * FROM t1;70} {(2) 2 4}71do_execsql_test 3.0.2 {72 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)73 INSERT INTO t1(a,b) SELECT x, x+1 FROM c;74 SELECT printf('(%d)',changes());75 DELETE FROM t1;76 SELECT printf('(%d)',changes());77} {(100) (101)}78 79#-------------------------------------------------------------------------80# DELETE statement that uses the OR optimization81#82reset_db83do_execsql_test 3.1 {84 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b);85 CREATE INDEX i1a ON t1(a);86 CREATE INDEX i1b ON t1(b);87 INSERT INTO t1 VALUES(1, 'one', 'i');88 INSERT INTO t1 VALUES(2, 'two', 'ii');89 INSERT INTO t1 VALUES(3, 'three', 'iii');90 INSERT INTO t1 VALUES(4, 'four', 'iv');91 INSERT INTO t1 VALUES(5, 'one', 'i');92 INSERT INTO t1 VALUES(6, 'two', 'ii');93 INSERT INTO t1 VALUES(7, 'three', 'iii');94 INSERT INTO t1 VALUES(8, 'four', 'iv');95} {}96 97do_execsql_test 3.2 {98 DELETE FROM t1 WHERE a='two' OR b='iv';99}100 101do_execsql_test 3.3 {102 SELECT i FROM t1 ORDER BY i;103} {1 3 5 7}104 105do_execsql_test 3.4 { 106 PRAGMA integrity_check; 107} {ok}108 109# Between 2015-09-14 and 2015-09-28, the following test cases would result110# in corruption (wrong # of entries in index) due to a bug in the ONEPASS111# optimization.112#113do_execsql_test 4.1 {114 DROP TABLE IF EXISTS t4;115 CREATE TABLE t4(col0, col1);116 INSERT INTO "t4" VALUES(14, 'abcde');117 CREATE INDEX idx_t4_0 ON t4 (col1, col0);118 CREATE INDEX idx_t4_3 ON t4 (col0);119 DELETE FROM t4 WHERE col0=69 OR col0>7;120 PRAGMA integrity_check;121} {ok}122do_execsql_test 4.2 {123 DROP TABLE IF EXISTS t4;124 CREATE TABLE t4(col0, col1);125 INSERT INTO "t4" VALUES(14, 'abcde');126 CREATE INDEX idx_t4_3 ON t4 (col0);127 CREATE INDEX idx_t4_0 ON t4 (col1, col0);128 DELETE FROM t4 WHERE col0=69 OR col0>7;129 PRAGMA integrity_check;130} {ok}131do_execsql_test 4.11 {132 DROP TABLE IF EXISTS t4;133 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID;134 INSERT INTO t4 VALUES(14, 'abcde','xyzzy');135 CREATE INDEX idx_t4_0 ON t4 (col1, col0);136 CREATE INDEX idx_t4_3 ON t4 (col0);137 DELETE FROM t4 WHERE col0=69 OR col0>7;138 PRAGMA integrity_check;139} {ok}140do_execsql_test 4.12 {141 DROP TABLE IF EXISTS t4;142 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID;143 INSERT INTO t4 VALUES(14, 'abcde','xyzzy');144 CREATE INDEX idx_t4_3 ON t4 (col0);145 CREATE INDEX idx_t4_0 ON t4 (col1, col0);146 DELETE FROM t4 WHERE col0=69 OR col0>7;147 PRAGMA integrity_check;148} {ok}149 150# 2016-04-09151# Ticket https://sqlite.org/src/info/a306e56ff68b8fa5152# Failure to completely delete when reverse_unordered_selects is153# engaged.154#155db close156forcedelete test.db157sqlite3 db test.db158do_execsql_test 5.0 {159 PRAGMA page_size=1024;160 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);161 CREATE INDEX x1 ON t1(b, c);162 INSERT INTO t1(a,b,c) VALUES(1, 1, zeroblob(80));163 INSERT INTO t1(a,b,c) SELECT a+1, 1, c FROM t1;164 INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1;165 INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1;166 INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1;167 PRAGMA reverse_unordered_selects = ON;168 DELETE FROM t1 WHERE b=2;169 SELECT a FROM t1 WHERE b=2;170} {}171 172# 2016-05-02173# Ticket https://sqlite.org/src/tktview/dc6ebeda93960877174# A subquery in the WHERE clause of a one-pass DELETE can cause an175# incorrect answer.176#177db close178forcedelete test.db179sqlite3 db test.db180do_execsql_test 6.0 {181 CREATE TABLE t2(x INT);182 INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);183 DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x-1);184 SELECT x FROM t2;185} {1}186do_execsql_test 6.1 {187 DROP TABLE IF EXISTS t2;188 CREATE TABLE t2(x INT);189 INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);190 DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);191 SELECT x FROM t2;192} {5}193 194#-------------------------------------------------------------------------195# Test the effect of failing to find a table row based on an index key196# within a DELETE. Either because the db is corrupt, or a trigger on another197# row already deleted the entry, or because a BEFORE trigger on the current198# row has already deleted it.199#200do_execsql_test 7.1.0 {201 CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;202 CREATE INDEX t3a ON t3(a);203 CREATE INDEX t3b ON t3(b);204 205 INSERT INTO t3 VALUES(1, 1, 1);206 INSERT INTO t3 VALUES(2, 2, 2);207 INSERT INTO t3 VALUES(3, 3, 3);208 INSERT INTO t3 VALUES(4, 4, 1);209}210do_execsql_test 7.1.1 {211 DELETE FROM t3 WHERE a=4 OR b=1;212}213do_execsql_test 7.1.2 {214 SELECT * FROM t3;215} { 2 2 2 3 3 3 }216 217do_execsql_test 7.2.0 {218 CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID;219 CREATE INDEX t4i ON t4(b);220 INSERT INTO t4 VALUES(1, 'hello');221 INSERT INTO t4 VALUES(2, 'world');222 223 CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID;224 CREATE INDEX t5i ON t5(b);225 INSERT INTO t5 VALUES(1, 'hello');226 INSERT INTO t5 VALUES(3, 'world');227 228 PRAGMA writable_schema = 1;229 UPDATE sqlite_master SET rootpage = (230 SELECT rootpage FROM sqlite_master WHERE name = 't5'231 ) WHERE name = 't4';232}233 234db close235sqlite3 db test.db236do_execsql_test 7.2.1 {237 DELETE FROM t4 WHERE b='world'238}239reset_db240 241do_execsql_test 7.3.0 {242 CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;243 INSERT INTO t3 VALUES(1, 2, 3);244 INSERT INTO t3 VALUES(4, 5, 6);245 INSERT INTO t3 VALUES(7, 8, 9);246 CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN247 DELETE FROM t3 WHERE id=old.id+3;248 END;249}250 251do_execsql_test 7.3.1 {252 DELETE FROM t3 WHERE a IN(2, 5, 8);253 SELECT * FROM t3;254} {}255 256do_execsql_test 7.3.2 {257 DROP TRIGGER t3t;258 INSERT INTO t3 VALUES(1, 2, 3);259 INSERT INTO t3 VALUES(4, 5, 6);260 INSERT INTO t3 VALUES(7, 8, 9);261 CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN262 DELETE FROM t3 WHERE id=old.id;263 END;264}265 266do_execsql_test 7.3.3 {267 DELETE FROM t3 WHERE a IN(2, 5, 8);268 SELECT * FROM t3;269} {}270 271 272finish_test273