MDEV-9777 MyISAM and InnoDB work differently when comparing a TIME column to an empty stringbb-10.3-bar-MDEV-9777

This problem was fixed earlier (presumably by MDEV-9604 in 10.1).
Adding MTR tests only.

2 files changed, 103 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/type_time.result b/mysql-test/suite/innodb/r/type_time.result
new file mode 100644
index 00000000000..f8dd80bf96a
--- /dev/null
+++ b/mysql-test/suite/innodb/r/type_time.result
@@ -0,0 +1,68 @@
+#
+#  MDEV-9777 MyISAM and InnoDB work differently when comparing a TIME column to an empty string
+#
+SET sql_mode='';
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+1
+1
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+1
+1
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+1
+1
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+1
+1
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1 WHERE b='';
+a	b	c
+	00:00:00	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT * FROM t1 WHERE b='';
+a	b	c
+	00:00:00	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+DROP TABLE t1;
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1 WHERE a=b;
+a	b	c
+	00:00:00	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT * FROM t1 WHERE a=b;
+a	b	c
+	00:00:00	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/type_time.test b/mysql-test/suite/innodb/t/type_time.test
new file mode 100644
index 00000000000..0bf090f2e79
--- /dev/null
+++ b/mysql-test/suite/innodb/t/type_time.test
@@ -0,0 +1,35 @@
+-- source include/have_innodb.inc
+
+--echo #
+--echo #  MDEV-9777 MyISAM and InnoDB work differently when comparing a TIME column to an empty string
+--echo #
+
+SET sql_mode='';
+
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+SELECT * FROM t1 WHERE b='';
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT * FROM t1 WHERE b='';
+DROP TABLE t1;
+
+CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('','00:00:00',0);
+SELECT * FROM t1 WHERE a=b;
+ALTER TABLE t1 ENGINE=MyISAM;
+SELECT * FROM t1 WHERE a=b;
+DROP TABLE t1;