2 files changed, 89 insertions, 0 deletions
diff --git a/mysql-test/main/multi_update_innodb.result b/mysql-test/main/multi_update_innodb.result
index 5890fd24f5f..294ebfcebdf 100644
--- a/mysql-test/main/multi_update_innodb.result
+++ b/mysql-test/main/multi_update_innodb.result
@@ -151,3 +151,43 @@ 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;
+#
+# MDEV-16240: Assertion `0' failed in
+# row_sel_convert_mysql_key_to_innobase
+#
+SET @save_sql_mode=@@sql_mode;
+set sql_mode='';
+CREATE TABLE `t3` (
+`f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
+`f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(),
+`f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00',
+`pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+`f4` datetime DEFAULT current_timestamp(),
+PRIMARY KEY (`pk`),
+UNIQUE KEY `f2k` (`f2`),
+KEY `f4k` (`f4`)
+) ENGINE=InnoDB;
+INSERT 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');
+CREATE VIEW `v1` AS
+SELECT `t3`.`pk` AS `pk`,
+`t3`.`f3` AS `f3`,
+`t3`.`f4` AS `f4`,
+`t3`.`f2` AS `f2`,
+`t3`.`f1` AS `f1`
+FROM `t3`;
+CREATE TABLE `t4` (
+`f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+`f3` timestamp NULL DEFAULT NULL,
+`f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(),
+`pk` int(11) NOT NULL,
+`f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+PRIMARY KEY (`pk`)
+) ENGINE=InnoDB;
+INSERT 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');
+UPDATE `v1` t1, `t4` t2
+SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000;
+ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k'
+DROP VIEW v1;
+DROP TABLE t3,t4;
+SET @@sql_mode=@save_sql_mode;
+# End of 10.2 tests
diff --git a/mysql-test/main/multi_update_innodb.test b/mysql-test/main/multi_update_innodb.test
index f5f8f91edb8..2e46ee06d4d 100644
--- a/mysql-test/main/multi_update_innodb.test
+++ b/mysql-test/main/multi_update_innodb.test
@@ -173,3 +173,52 @@ delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
 
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-16240: Assertion `0' failed in
+--echo # row_sel_convert_mysql_key_to_innobase
+--echo #
+
+SET @save_sql_mode=@@sql_mode;
+set sql_mode='';
+
+CREATE TABLE `t3` (
+    `f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
+    `f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(),
+    `f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00',
+    `pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+    `f4` datetime DEFAULT current_timestamp(),
+                   PRIMARY KEY (`pk`),
+                   UNIQUE KEY `f2k` (`f2`),
+                   KEY `f4k` (`f4`)
+    ) ENGINE=InnoDB;
+
+INSERT 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');
+
+CREATE VIEW `v1` AS
+SELECT `t3`.`pk` AS `pk`,
+       `t3`.`f3` AS `f3`,
+       `t3`.`f4` AS `f4`,
+       `t3`.`f2` AS `f2`,
+       `t3`.`f1` AS `f1`
+FROM `t3`;
+
+CREATE TABLE `t4` (
+  `f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `f3` timestamp NULL DEFAULT NULL,
+  `f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(),
+  `pk` int(11) NOT NULL,
+  `f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  PRIMARY KEY (`pk`)
+) ENGINE=InnoDB;
+
+INSERT 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');
+
+--error ER_DUP_ENTRY
+UPDATE `v1` t1, `t4` t2
+SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000;
+
+DROP VIEW v1;
+DROP TABLE t3,t4;
+SET @@sql_mode=@save_sql_mode;
+
+--echo # End of 10.2 tests