diff options
Diffstat (limited to 'mysql-test/suite/pbxt/t/delete.test')
| -rw-r--r-- | mysql-test/suite/pbxt/t/delete.test | 201 |
1 files changed, 0 insertions, 201 deletions
diff --git a/mysql-test/suite/pbxt/t/delete.test b/mysql-test/suite/pbxt/t/delete.test deleted file mode 100644 index 913e7df3d3a..00000000000 --- a/mysql-test/suite/pbxt/t/delete.test +++ /dev/null @@ -1,201 +0,0 @@ -# -# Check for problems with delete -# - ---disable_warnings -drop table if exists t1,t2,t3,t11,t12; ---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, - b bigint not null default 0, - c bigint not null default 0, - d bigint not null default 0, - e bigint not null default 0, - f bigint not null default 0, - g bigint not null default 0, - h bigint not null default 0, - i bigint not null default 0, - j bigint not null default 0, - primary key (a,b,c,d,e,f,g,h,i,j)); -insert into t1 (a) 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, - b bigint not null default 0, - c bigint not null default 0, - d bigint not null default 0, - e bigint not null default 0, - f bigint not null default 0, - g bigint not null default 0, - h bigint not null default 0, - i bigint not null default 0, - j bigint not null default 0, - primary key (a,b,c,d,e,f,g,h,i,j)); -insert into t1 (a) 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) -) ENGINE=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; -# -# Bug #5733: Table handler error with self-join multi-table DELETE -# - -create table t1 (a int not null auto_increment primary key, b char(32)); -insert into t1 (b) values ('apple'), ('apple'); -select * from t1; -delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a; -select * 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 1242 -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; ---error 1242 -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; ---error 1062 -insert into t11 values (2, 12); --- error 1242 -delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a); -select * from t11; ---error 1242 -delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a); -select * from t11; -drop table t11, t12, t2; - -# -# Bug #4198: deletion and KEYREAD -# - -create table t1 (a int, b int, unique key (a), key (b)); -insert into t1 values (3, 3), (7, 7); -delete t1 from t1 where a = 3; -check table t1; -select * from t1; -drop table t1; - -# -# Bug #8392: delete with ORDER BY containing a direct reference to the table -# - -CREATE TABLE t1 ( a int PRIMARY KEY ); -DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; -INSERT INTO t1 VALUES (0),(1),(2); -DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1; -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug #21392: multi-table delete with alias table name fails with -# 1003: Incorrect table name -# - -create table t1 (a int); -delete `4.t1` from t1 as `4.t1` where `4.t1`.a = 5; -delete FROM `4.t1` USING t1 as `4.t1` where `4.t1`.a = 5; -drop table t1; - -# End of 4.1 tests - -# -# Test of multi-delete where we are not scanning the first table -# - -CREATE TABLE t1 (a int not null,b int not null); -CREATE TABLE t2 (a int not null, b int not null, primary key (a,b)); -CREATE TABLE t3 (a int not null, b int not null, primary key (a,b)); -insert into t1 values (1,1),(2,1),(1,3); -insert into t2 values (1,1),(2,2),(3,3); -insert into t3 values (1,1),(2,1),(1,3); -select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; -explain select * from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; -delete t2.*,t3.* from t1,t2,t3 where t1.a=t2.a AND t2.b=t3.a and t1.b=t3.b; -# This should be empty -select * from t3; -drop table t1,t2,t3; - -# -# Bug #8143: deleting '0000-00-00' values using IS NULL -# - -create table t1(a date not null); -insert into t1 values (0); -select * from t1 where a is null; -delete from t1 where a is null; -select count(*) from t1; -drop table t1; - ---disable_query_log -drop database pbxt; ---enable_query_log |