2
# Check for problems with delete
6
drop table if exists t1,t2,t3,t11,t12;
8
CREATE TABLE t1 (a tinyint(3), b tinyint(5));
9
INSERT INTO t1 VALUES (1,1);
10
INSERT LOW_PRIORITY INTO t1 VALUES (1,2);
11
INSERT INTO t1 VALUES (1,3);
12
DELETE from t1 where a=1 limit 1;
13
DELETE LOW_PRIORITY from t1 where a=1;
15
INSERT INTO t1 VALUES (1,1);
18
INSERT INTO t1 VALUES (1,2);
21
INSERT INTO t1 VALUES (1,2);
28
# Test of delete when the delete will cause a node to disappear and reappear
29
# (This assumes a block size of 1024)
34
b bigint not null default 0,
35
c bigint not null default 0,
36
d bigint not null default 0,
37
e bigint not null default 0,
38
f bigint not null default 0,
39
g bigint not null default 0,
40
h bigint not null default 0,
41
i bigint not null default 0,
42
j bigint not null default 0,
43
primary key (a,b,c,d,e,f,g,h,i,j));
44
insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23);
45
delete from t1 where a=26;
49
b bigint not null default 0,
50
c bigint not null default 0,
51
d bigint not null default 0,
52
e bigint not null default 0,
53
f bigint not null default 0,
54
g bigint not null default 0,
55
h bigint not null default 0,
56
i bigint not null default 0,
57
j bigint not null default 0,
58
primary key (a,b,c,d,e,f,g,h,i,j));
59
insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27);
60
delete from t1 where a=27;
64
`i` int(10) NOT NULL default '0',
65
`i2` int(10) NOT NULL default '0',
69
DELETE FROM t1 USING t1 WHERE post='1';
73
# CHAR(0) bug - not actually DELETE bug, but anyway...
77
bool char(0) default NULL,
78
not_null varchar(20) binary NOT NULL default '',
79
misc integer not null,
80
PRIMARY KEY (not_null)
83
INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7);
85
select * from t1 where misc > 5 and bool is null;
86
delete from t1 where misc > 5 and bool is null;
87
select * from t1 where misc > 5 and bool is null;
89
select count(*) from t1;
90
delete from t1 where 1 > 2;
91
select count(*) from t1;
92
delete from t1 where 3 > 2;
93
select count(*) from t1;
97
# Bug #5733: Table handler error with self-join multi-table DELETE
100
create table t1 (a int not null auto_increment primary key, b char(32));
101
insert into t1 (b) values ('apple'), ('apple');
103
delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a;
110
create table t11 (a int NOT NULL, b int, primary key (a));
111
create table t12 (a int NOT NULL, b int, primary key (a));
112
create table t2 (a int NOT NULL, b int, primary key (a));
113
insert into t11 values (0, 10),(1, 11),(2, 12);
114
insert into t12 values (33, 10),(0, 11),(2, 12);
115
insert into t2 values (1, 21),(2, 12),(3, 23);
120
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
123
delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
126
insert into t11 values (2, 12);
128
delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
130
delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
132
drop table t11, t12, t2;
135
# Bug #4198: deletion and KEYREAD
138
create table t1 (a int, b int, unique key (a), key (b));
139
insert into t1 values (3, 3), (7, 7);
140
delete t1 from t1 where a = 3;
146
# Bug #8392: delete with ORDER BY containing a direct reference to the table
149
CREATE TABLE t1 ( a int PRIMARY KEY );
150
DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
151
INSERT INTO t1 VALUES (0),(1),(2);
152
DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1;
159
# Test of multi-delete where we are not scanning the first table
162
CREATE TABLE t1 (a int not null,b int not null);
163
CREATE TABLE t2 (a int not null, b int not null, primary key (a,b));
164
CREATE TABLE t3 (a int not null, b int not null, primary key (a,b));
165
insert into t1 values (1,1),(2,1),(1,3);
166
insert into t2 values (1,1),(2,2),(3,3);
167
insert into t3 values (1,1),(2,1),(1,3);
168
select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
169
explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
170
delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b;
171
# This should be empty
176
# Bug #8143: deleting '0000-00-00' values using IS NULL
179
create table t1(a date not null);
180
insert into t1 values (0);
181
select * from t1 where a is null;
182
delete from t1 where a is null;
183
select count(*) from t1;