1# 2# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a 3# table is updated twice 4# 5CREATE TABLE t1( 6pk INT, 7a INT, 8b INT, 9PRIMARY KEY (pk) 10) ENGINE=InnoDB; 11INSERT INTO t1 VALUES (0,0,0); 12UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2; 13ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 14SELECT * FROM t1; 15pk a b 160 0 0 17CREATE VIEW v1 AS SELECT * FROM t1; 18UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2; 19ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 20SELECT * FROM t1; 21pk a b 220 0 0 23UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2; 24# Should be (0,1,2) 25SELECT * FROM t1; 26pk a b 270 1 2 28DROP VIEW v1; 29DROP TABLE t1; 30# 31# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 32# UPDATED TWICE 33# 34CREATE TABLE t1 ( 35col_int_key int, 36pk int, 37col_int int, 38key(col_int_key), 39primary key (pk) 40) ENGINE=InnoDB; 41INSERT INTO t1 VALUES (1,2,3); 42 43CREATE TABLE t2 ( 44col_int_key int, 45pk_1 int, 46pk_2 int, 47col_int int, 48key(col_int_key), 49primary key (pk_1,pk_2) 50) ENGINE=InnoDB; 51INSERT INTO t2 VALUES (1,2,3,4); 52 53UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; 54ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 55 56SELECT * FROM t1; 57col_int_key pk col_int 581 2 3 59 60UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; 61ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 62 63UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; 64ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B' 65 66SELECT * FROM t2; 67col_int_key pk_1 pk_2 col_int 681 2 3 4 69DROP TABLE t1,t2; 70create table t1 (id serial, size int(11)) engine=innodb; 71create table t2 (id serial, size int, account_id int) engine=innodb; 72create table t3 (id serial, size int, article_id int) engine=innodb; 73create table t4 (id serial, file_id int, article_id int) engine=innodb; 74insert t1 values(null, 400); 75insert t2 values(null, 0, 1), (null, 1, 1); 76insert t3 values(null, 100, 1); 77insert t4 values(null, 1, 2); 78create trigger file_update_article before update on t3 for each row 79update t2 set t2.size = new.size where t2.id = new.article_id; 80create trigger article_update_account before update on t2 for each row 81update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; 82update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; 83drop table t1, t2, t3, t4; 84# 85# end of 5.5 tests 86# 87 88# Bug mdev-5970 89# Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD 90 91CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; 92CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; 93INSERT INTO t1 VALUES (5, 7); 94INSERT INTO t2 VALUES (6, 97); 95CREATE ALGORITHM = MERGE VIEW v1 AS 96SELECT a2.f1 AS f1, a2.f2 AS f2 97FROM t1 AS a1 JOIN t2 AS a2 ON a1.f2 > a2.f1 98WITH LOCAL CHECK OPTION; 99SELECT * FROM v1; 100f1 f2 1016 97 102UPDATE v1 SET f1 = 1; 103SELECT * FROM v1; 104f1 f2 1051 97 106DROP TABLE t1, t2; 107DROP VIEW v1; 108# 109# MDEV-5973: MySQL Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 110# FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE 111# 112CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB; 113INSERT INTO table_11757486 VALUES (0),(0); 114SET SESSION SQL_MODE='STRICT_ALL_TABLES'; 115UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 116Warnings: 117Warning 1264 Out of range value for column 'field1' at row 1 118Warning 1264 Out of range value for column 'field1' at row 2 119UPDATE IGNORE table_11757486 SET field1=128; 120Warnings: 121Warning 1264 Out of range value for column 'field1' at row 1 122Warning 1264 Out of range value for column 'field1' at row 2 123UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 124ERROR 22003: Out of range value for column 'field1' at row 1 125UPDATE table_11757486 SET field1=128; 126ERROR 22003: Out of range value for column 'field1' at row 1 127SET SESSION SQL_MODE=''; 128UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 129Warnings: 130Warning 1264 Out of range value for column 'field1' at row 1 131Warning 1264 Out of range value for column 'field1' at row 2 132UPDATE IGNORE table_11757486 SET field1=128; 133Warnings: 134Warning 1264 Out of range value for column 'field1' at row 1 135Warning 1264 Out of range value for column 'field1' at row 2 136DROP TABLE table_11757486; 137SET SESSION SQL_MODE=default; 138create table t1 ( 139aclid bigint not null primary key, 140status tinyint(1) not null 141) engine = innodb; 142create table t2 ( 143refid bigint not null primary key, 144aclid bigint, index idx_acl(aclid) 145) engine = innodb; 146insert into t2 values(1,null); 147delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; 148drop table t1, t2; 149create table t1 ( c char(8) not null ) engine=innodb; 150insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 151insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 152alter table t1 add b char(8) not null; 153alter table t1 add a char(8) not null; 154alter table t1 add primary key (a,b,c); 155update t1 set a=c, b=c; 156create table t2 like t1; 157insert into t2 select * from t1; 158delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 159drop table t1,t2; 160create table t1 ( c char(8) not null ) engine=innodb; 161insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 162insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 163alter table t1 add b char(8) not null; 164alter table t1 add a char(8) not null; 165alter table t1 add primary key (a,b,c); 166update t1 set a=c, b=c; 167create table t2 like t1; 168insert into t2 select * from t1; 169delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 170drop table t1,t2; 171# 172# MDEV-16240: Assertion `0' failed in 173# row_sel_convert_mysql_key_to_innobase 174# 175SET @save_sql_mode=@@sql_mode; 176set sql_mode=''; 177CREATE TABLE `t3` ( 178`f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), 179`f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), 180`f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', 181`pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 182`f4` datetime DEFAULT current_timestamp(), 183PRIMARY KEY (`pk`), 184UNIQUE KEY `f2k` (`f2`), 185KEY `f4k` (`f4`) 186) ENGINE=InnoDB; 187INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); 188CREATE VIEW `v1` AS 189SELECT `t3`.`pk` AS `pk`, 190`t3`.`f3` AS `f3`, 191`t3`.`f4` AS `f4`, 192`t3`.`f2` AS `f2`, 193`t3`.`f1` AS `f1` 194FROM `t3`; 195CREATE TABLE `t4` ( 196`f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), 197`f3` timestamp NULL DEFAULT NULL, 198`f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), 199`pk` int(11) NOT NULL, 200`f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 201PRIMARY KEY (`pk`) 202) ENGINE=InnoDB; 203INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); 204UPDATE `v1` t1, `t4` t2 205SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; 206ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k' 207DROP VIEW v1; 208DROP TABLE t3,t4; 209SET @@sql_mode=@save_sql_mode; 210# End of 10.2 tests 211