From 9a1b9f5b8480466f5d2f1f28e838c97451a0a4dc Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 26 Oct 2022 14:26:21 +0400 Subject: MDEV-9777 MyISAM and InnoDB work differently when comparing a TIME column to an empty string This problem was fixed earlier (presumably by MDEV-9604 in 10.1). Adding MTR tests only. --- mysql-test/suite/innodb/r/type_time.result | 68 ++++++++++++++++++++++++++++++ mysql-test/suite/innodb/t/type_time.test | 35 +++++++++++++++ 2 files changed, 103 insertions(+) create mode 100644 mysql-test/suite/innodb/r/type_time.result create mode 100644 mysql-test/suite/innodb/t/type_time.test 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; -- cgit v1.2.1