diff options
| -rw-r--r-- | mysql-test/suite/innodb/r/type_time.result | 68 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/type_time.test | 35 |
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; |