delete.test 2.78 KB
Newer Older
1 2 3 4
#
# Check for problems with delete
#

5
--disable_warnings
6
drop table if exists t1,t11,t12,t2;
7
--enable_warnings
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
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;
unknown's avatar
unknown committed
26

27 28 29 30
#
# Test of delete when the delete will cause a node to disappear and reappear
# (This assumes a block size of 1024)
#
unknown's avatar
unknown committed
31

32 33 34 35 36 37 38
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;
unknown's avatar
unknown committed
39
drop table t1;
unknown's avatar
unknown committed
40

unknown's avatar
unknown committed
41
CREATE TABLE `t1` (
unknown's avatar
unknown committed
42 43 44
  `i` int(10) NOT NULL default '0',
  `i2` int(10) NOT NULL default '0',
  PRIMARY KEY  (`i`)
unknown's avatar
unknown committed
45
);
unknown's avatar
unknown committed
46
-- error 1054
unknown's avatar
unknown committed
47 48
DELETE FROM t1 USING t1 WHERE post='1';
drop table t1;
unknown's avatar
unknown committed
49

50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
#
# 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;

67 68 69 70 71 72
select count(*) from t1;
delete from t1 where 1 > 2;
select count(*) from t1;
delete from t1 where 3 > 2;
select count(*) from t1;

73
drop table t1;
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

#
# 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;