1# 2005 August 24 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# This file implements regression tests for SQLite library. The 12# focus of this script is a test of the DELETE command. 13# 14 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix delete4 18 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_db 40do_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_db 62do_execsql_test 3.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 * FROM t1; 69} {2 4} 70 71#------------------------------------------------------------------------- 72# DELETE statement that uses the OR optimization 73# 74reset_db 75do_execsql_test 3.1 { 76 CREATE TABLE t1(i INTEGER PRIMARY KEY, a, b); 77 CREATE INDEX i1a ON t1(a); 78 CREATE INDEX i1b ON t1(b); 79 INSERT INTO t1 VALUES(1, 'one', 'i'); 80 INSERT INTO t1 VALUES(2, 'two', 'ii'); 81 INSERT INTO t1 VALUES(3, 'three', 'iii'); 82 INSERT INTO t1 VALUES(4, 'four', 'iv'); 83 INSERT INTO t1 VALUES(5, 'one', 'i'); 84 INSERT INTO t1 VALUES(6, 'two', 'ii'); 85 INSERT INTO t1 VALUES(7, 'three', 'iii'); 86 INSERT INTO t1 VALUES(8, 'four', 'iv'); 87} {} 88 89do_execsql_test 3.2 { 90 DELETE FROM t1 WHERE a='two' OR b='iv'; 91} 92 93do_execsql_test 3.3 { 94 SELECT i FROM t1 ORDER BY i; 95} {1 3 5 7} 96 97do_execsql_test 3.4 { 98 PRAGMA integrity_check; 99} {ok} 100 101# Between 2015-09-14 and 2015-09-28, the following test cases would result 102# in corruption (wrong # of entries in index) due to a bug in the ONEPASS 103# optimization. 104# 105do_execsql_test 4.1 { 106 DROP TABLE IF EXISTS t4; 107 CREATE TABLE t4(col0, col1); 108 INSERT INTO "t4" VALUES(14, 'abcde'); 109 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 110 CREATE INDEX idx_t4_3 ON t4 (col0); 111 DELETE FROM t4 WHERE col0=69 OR col0>7; 112 PRAGMA integrity_check; 113} {ok} 114do_execsql_test 4.2 { 115 DROP TABLE IF EXISTS t4; 116 CREATE TABLE t4(col0, col1); 117 INSERT INTO "t4" VALUES(14, 'abcde'); 118 CREATE INDEX idx_t4_3 ON t4 (col0); 119 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 120 DELETE FROM t4 WHERE col0=69 OR col0>7; 121 PRAGMA integrity_check; 122} {ok} 123do_execsql_test 4.11 { 124 DROP TABLE IF EXISTS t4; 125 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID; 126 INSERT INTO t4 VALUES(14, 'abcde','xyzzy'); 127 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 128 CREATE INDEX idx_t4_3 ON t4 (col0); 129 DELETE FROM t4 WHERE col0=69 OR col0>7; 130 PRAGMA integrity_check; 131} {ok} 132do_execsql_test 4.12 { 133 DROP TABLE IF EXISTS t4; 134 CREATE TABLE t4(col0, col1, pk PRIMARY KEY) WITHOUT ROWID; 135 INSERT INTO t4 VALUES(14, 'abcde','xyzzy'); 136 CREATE INDEX idx_t4_3 ON t4 (col0); 137 CREATE INDEX idx_t4_0 ON t4 (col1, col0); 138 DELETE FROM t4 WHERE col0=69 OR col0>7; 139 PRAGMA integrity_check; 140} {ok} 141 142 143 144 145finish_test 146