From a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Fri, 9 Mar 2018 14:05:35 +0200 Subject: Create 'main' test directory and move 't' and 'r' there --- mysql-test/main/multi_update_innodb.test | 175 +++++++++++++++++++++++++++++++ 1 file changed, 175 insertions(+) create mode 100644 mysql-test/main/multi_update_innodb.test (limited to 'mysql-test/main/multi_update_innodb.test') diff --git a/mysql-test/main/multi_update_innodb.test b/mysql-test/main/multi_update_innodb.test new file mode 100644 index 00000000000..f5f8f91edb8 --- /dev/null +++ b/mysql-test/main/multi_update_innodb.test @@ -0,0 +1,175 @@ +--source include/have_innodb.inc + +--echo # +--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a +--echo # table is updated twice +--echo # + +# Results differ between storage engines. +# See multi_update.test for the MyISAM variant of this test +CREATE TABLE t1( + pk INT, + a INT, + b INT, + PRIMARY KEY (pk) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (0,0,0); +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2; +SELECT * FROM t1; + +CREATE VIEW v1 AS SELECT * FROM t1; +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2; +SELECT * FROM t1; + +UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2; +--echo # Should be (0,1,2) +SELECT * FROM t1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo # +--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS +--echo # UPDATED TWICE +--echo # + +# Results differ between storage engines. +# See multi_update.test for the MyISAM variant of this test +CREATE TABLE t1 ( + col_int_key int, + pk int, + col_int int, + key(col_int_key), + primary key (pk) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,2,3); + +--echo +CREATE TABLE t2 ( + col_int_key int, + pk_1 int, + pk_2 int, + col_int int, + key(col_int_key), + primary key (pk_1,pk_2) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,2,3,4); + +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; +--echo +SELECT * FROM t1; + +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; + +--echo +SELECT * FROM t2; + +DROP TABLE t1,t2; + +--echo +--echo # Bug mdev-5970 +--echo # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD +--echo + +CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; +CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (5, 7); +INSERT INTO t2 VALUES (6, 97); + +CREATE ALGORITHM = MERGE VIEW v1 AS +SELECT a2.f1 AS f1, a2.f2 AS f2 +FROM t1 AS a1 JOIN t2 AS a2 ON a1.f2 > a2.f1 +WITH LOCAL CHECK OPTION; + +SELECT * FROM v1; +UPDATE v1 SET f1 = 1; +SELECT * FROM v1; + +DROP TABLE t1, t2; +DROP VIEW v1; + +--echo # +--echo # MDEV-5973: MySQL Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 +--echo # FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE +--echo # + +CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB; +INSERT INTO table_11757486 VALUES (0),(0); +SET SESSION SQL_MODE='STRICT_ALL_TABLES'; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +UPDATE IGNORE table_11757486 SET field1=128; + +--error ER_WARN_DATA_OUT_OF_RANGE +UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +--error ER_WARN_DATA_OUT_OF_RANGE +UPDATE table_11757486 SET field1=128; + +SET SESSION SQL_MODE=''; +UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; +UPDATE IGNORE table_11757486 SET field1=128; + +DROP TABLE table_11757486; + +SET SESSION SQL_MODE=default; + +# Test for Bug#5837 delete with outer join and const tables +create table t1 ( + aclid bigint not null primary key, + status tinyint(1) not null +) engine = innodb; + +create table t2 ( + refid bigint not null primary key, + aclid bigint, index idx_acl(aclid) +) engine = innodb; +insert into t2 values(1,null); +delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; +drop table t1, t2; + +# +# Test for Bug#1980. +# +create table t1 ( c char(8) not null ) engine=innodb; + +insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); +insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); + +alter table t1 add b char(8) not null; +alter table t1 add a char(8) not null; +alter table t1 add primary key (a,b,c); +update t1 set a=c, b=c; + +create table t2 like t1; +insert into t2 select * from t1; + +delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; + +drop table t1,t2; + +create table t1 ( c char(8) not null ) engine=innodb; + +insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); +insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); + +alter table t1 add b char(8) not null; +alter table t1 add a char(8) not null; +alter table t1 add primary key (a,b,c); +update t1 set a=c, b=c; + +create table t2 like t1; +insert into t2 select * from t1; + +delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; + +drop table t1,t2; + -- cgit v1.2.1