# # Check for problems with delete # --disable_warnings drop table if exists t1,t11,t12,t2; --enable_warnings CREATE TABLE t1 (a tinyint(3), b tinyint(5)); INSERT INTO t1 VALUES (1,1); INSERT LOW_PRIORITY INTO t1 VALUES (1,2); INSERT INTO t1 VALUES (1,3); DELETE from t1 where a=1 limit 1; DELETE LOW_PRIORITY from t1 where a=1; INSERT INTO t1 VALUES (1,1); DELETE from t1; LOCK TABLE t1 write; INSERT INTO t1 VALUES (1,2); DELETE from t1; UNLOCK TABLES; INSERT INTO t1 VALUES (1,2); SET AUTOCOMMIT=0; DELETE from t1; SET AUTOCOMMIT=1; drop table t1; # # Test of delete when the delete will cause a node to disappear and reappear # (This assumes a block size of 1024) # create table t1 (a bigint not null, primary key (a,a,a,a,a,a,a,a,a,a)); insert into t1 values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23); delete from t1 where a=26; drop table t1; create table t1 (a bigint not null, primary key (a,a,a,a,a,a,a,a,a,a)); insert into t1 values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27); delete from t1 where a=27; drop table t1; CREATE TABLE `t1` ( `i` int(10) NOT NULL default '0', `i2` int(10) NOT NULL default '0', PRIMARY KEY (`i`) ); -- error 1054 DELETE FROM t1 USING t1 WHERE post='1'; drop table t1; # # CHAR(0) bug - not actually DELETE bug, but anyway... # CREATE TABLE t1 ( bool char(0) default NULL, not_null varchar(20) binary NOT NULL default '', misc integer not null, PRIMARY KEY (not_null) ) TYPE=MyISAM; INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7); select * from t1 where misc > 5 and bool is null; delete from t1 where misc > 5 and bool is null; select * from t1 where misc > 5 and bool is null; select count(*) from t1; delete from t1 where 1 > 2; select count(*) from t1; delete from t1 where 3 > 2; select count(*) from t1; drop table t1; # # IGNORE option # create table t11 (a int NOT NULL, b int, primary key (a)); create table t12 (a int NOT NULL, b int, primary key (a)); create table t2 (a int NOT NULL, b int, primary key (a)); insert into t11 values (0, 10),(1, 11),(2, 12); insert into t12 values (33, 10),(0, 11),(2, 12); insert into t2 values (1, 21),(2, 12),(3, 23); select * from t11; select * from t12; select * from t2; -- error 1241 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; select * from t12; delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; select * from t12; insert into t11 values (2, 12); -- error 1241 delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); select * from t11; drop table t11, t12, t2;