1--source include/have_innodb.inc 2 3--echo # 4--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a 5--echo # table is updated twice 6--echo # 7 8# Results differ between storage engines. 9# See multi_update.test for the MyISAM variant of this test 10CREATE TABLE t1( 11 pk INT, 12 a INT, 13 b INT, 14 PRIMARY KEY (pk) 15) ENGINE=InnoDB; 16 17INSERT INTO t1 VALUES (0,0,0); 18--error ER_MULTI_UPDATE_KEY_CONFLICT 19UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2; 20SELECT * FROM t1; 21 22CREATE VIEW v1 AS SELECT * FROM t1; 23--error ER_MULTI_UPDATE_KEY_CONFLICT 24UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2; 25SELECT * FROM t1; 26 27UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2; 28--echo # Should be (0,1,2) 29SELECT * FROM t1; 30 31DROP VIEW v1; 32DROP TABLE t1; 33 34--echo # 35--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS 36--echo # UPDATED TWICE 37--echo # 38 39# Results differ between storage engines. 40# See multi_update.test for the MyISAM variant of this test 41CREATE TABLE t1 ( 42 col_int_key int, 43 pk int, 44 col_int int, 45 key(col_int_key), 46 primary key (pk) 47) ENGINE=InnoDB; 48INSERT INTO t1 VALUES (1,2,3); 49 50--echo 51CREATE TABLE t2 ( 52 col_int_key int, 53 pk_1 int, 54 pk_2 int, 55 col_int int, 56 key(col_int_key), 57 primary key (pk_1,pk_2) 58) ENGINE=InnoDB; 59INSERT INTO t2 VALUES (1,2,3,4); 60 61--echo 62--error ER_MULTI_UPDATE_KEY_CONFLICT 63UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; 64--echo 65SELECT * FROM t1; 66 67--echo 68--error ER_MULTI_UPDATE_KEY_CONFLICT 69UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; 70--echo 71--error ER_MULTI_UPDATE_KEY_CONFLICT 72UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; 73 74--echo 75SELECT * FROM t2; 76 77DROP TABLE t1,t2; 78 79# 80# MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 81# 82create table t1 (id serial, size int(11)) engine=innodb; 83create table t2 (id serial, size int, account_id int) engine=innodb; 84create table t3 (id serial, size int, article_id int) engine=innodb; 85create table t4 (id serial, file_id int, article_id int) engine=innodb; 86insert t1 values(null, 400); 87insert t2 values(null, 0, 1), (null, 1, 1); 88insert t3 values(null, 100, 1); 89insert t4 values(null, 1, 2); 90create trigger file_update_article before update on t3 for each row 91 update t2 set t2.size = new.size where t2.id = new.article_id; 92create trigger article_update_account before update on t2 for each row 93 update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; 94update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; 95drop table t1, t2, t3, t4; 96 97--echo # 98--echo # end of 5.5 tests 99--echo # 100 101--echo 102--echo # Bug mdev-5970 103--echo # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD 104--echo 105 106CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; 107CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT) ENGINE=InnoDB; 108INSERT INTO t1 VALUES (5, 7); 109INSERT INTO t2 VALUES (6, 97); 110 111CREATE ALGORITHM = MERGE VIEW v1 AS 112SELECT a2.f1 AS f1, a2.f2 AS f2 113FROM t1 AS a1 JOIN t2 AS a2 ON a1.f2 > a2.f1 114WITH LOCAL CHECK OPTION; 115 116SELECT * FROM v1; 117UPDATE v1 SET f1 = 1; 118SELECT * FROM v1; 119 120DROP TABLE t1, t2; 121DROP VIEW v1; 122 123--echo # 124--echo # MDEV-5973: MySQL Bug#11757486:49539: NON-DESCRIPTIVE ERR (ERROR 0 125--echo # FROM STORAGE ENGINE) WITH MULTI-TABLE UPDATE 126--echo # 127 128CREATE TABLE table_11757486 (field1 tinyint) ENGINE=INNODB; 129INSERT INTO table_11757486 VALUES (0),(0); 130SET SESSION SQL_MODE='STRICT_ALL_TABLES'; 131UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 132UPDATE IGNORE table_11757486 SET field1=128; 133 134--error ER_WARN_DATA_OUT_OF_RANGE 135UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 136--error ER_WARN_DATA_OUT_OF_RANGE 137UPDATE table_11757486 SET field1=128; 138 139SET SESSION SQL_MODE=''; 140UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1; 141UPDATE IGNORE table_11757486 SET field1=128; 142 143DROP TABLE table_11757486; 144 145SET SESSION SQL_MODE=default; 146 147# Test for Bug#5837 delete with outer join and const tables 148create table t1 ( 149 aclid bigint not null primary key, 150 status tinyint(1) not null 151) engine = innodb; 152 153create table t2 ( 154 refid bigint not null primary key, 155 aclid bigint, index idx_acl(aclid) 156) engine = innodb; 157insert into t2 values(1,null); 158delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1'; 159drop table t1, t2; 160 161# 162# Test for Bug#1980. 163# 164create table t1 ( c char(8) not null ) engine=innodb; 165 166insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 167insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 168 169alter table t1 add b char(8) not null; 170alter table t1 add a char(8) not null; 171alter table t1 add primary key (a,b,c); 172update t1 set a=c, b=c; 173 174create table t2 like t1; 175insert into t2 select * from t1; 176 177delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 178 179drop table t1,t2; 180 181create table t1 ( c char(8) not null ) engine=innodb; 182 183insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'); 184insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F'); 185 186alter table t1 add b char(8) not null; 187alter table t1 add a char(8) not null; 188alter table t1 add primary key (a,b,c); 189update t1 set a=c, b=c; 190 191create table t2 like t1; 192insert into t2 select * from t1; 193 194delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; 195 196drop table t1,t2; 197 198--echo # 199--echo # MDEV-16240: Assertion `0' failed in 200--echo # row_sel_convert_mysql_key_to_innobase 201--echo # 202 203SET @save_sql_mode=@@sql_mode; 204set sql_mode=''; 205 206CREATE TABLE `t3` ( 207 `f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), 208 `f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), 209 `f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', 210 `pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 211 `f4` datetime DEFAULT current_timestamp(), 212 PRIMARY KEY (`pk`), 213 UNIQUE KEY `f2k` (`f2`), 214 KEY `f4k` (`f4`) 215 ) ENGINE=InnoDB; 216 217INSERT 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'); 218 219CREATE VIEW `v1` AS 220SELECT `t3`.`pk` AS `pk`, 221 `t3`.`f3` AS `f3`, 222 `t3`.`f4` AS `f4`, 223 `t3`.`f2` AS `f2`, 224 `t3`.`f1` AS `f1` 225FROM `t3`; 226 227CREATE TABLE `t4` ( 228 `f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), 229 `f3` timestamp NULL DEFAULT NULL, 230 `f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), 231 `pk` int(11) NOT NULL, 232 `f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 233 PRIMARY KEY (`pk`) 234) ENGINE=InnoDB; 235 236INSERT 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'); 237 238--error ER_DUP_ENTRY 239UPDATE `v1` t1, `t4` t2 240SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; 241 242DROP VIEW v1; 243DROP TABLE t3,t4; 244SET @@sql_mode=@save_sql_mode; 245 246--echo # End of 10.2 tests 247