[#MDEV-9777] MyISAM and InnoDB work differently when comparing a TIME column to an empty string

This bug is related to ~~MDEV-9604~~.

All problems described here were fixed in 10.1 under terms of ~~MDEV-9604~~. We now need to back-port these fixes to 10.0.

Note, the fix for 10.1 may not work for 10.0 as is, because equal field propagation in 10.1 looks very differently. So some adjusting will most likely be needed.

SET sql_mode='';

DROP TABLE IF EXISTS t1;

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;

returns 0 rows for InnoDB (this is wrong)
returns 1 row for MyISAM

SET sql_mode='';

DROP TABLE IF EXISTS t1;

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;

returns 0 rows for InnoDB (this is wrong)
returns 1 row for MyISAM

SET sql_mode='';

DROP TABLE IF EXISTS 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='';

returns 0 rows for InnoDB (this is wrong)
returns 1 row for MyISAM

SET sql_mode='';

DROP TABLE IF EXISTS 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;

returns 1 row for InnoDB (correctly)
returns 1 row for MyISAM (correctly)

Similar sort of inconsistency is observed with the DATE and DATETIME data types.

[MDEV-9777] MyISAM and InnoDB work differently when comparing a TIME column to an empty string Created: 2016-03-23 Updated: 2022-10-26 Resolved: 2022-10-26
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0
Fix Version/s:	N/A

[MDEV-9777] MyISAM and InnoDB work differently when comparing a TIME column to an empty string Created: 2016-03-23 Updated: 2022-10-26 Resolved: 2022-10-26