MySQL Bugs: #95462: Data comparison broke in MySQL 8.0.16

Bug #95462	Data comparison broke in MySQL 8.0.16
Submitted:	21 May 2019 18:42	Modified:	25 Jul 2019 11:07
Reporter:	Raman Haran	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
We recently upgraded our system to 8.0.16 community server edition from 8.0.11. After we upgraded the following query broke

select @@version , from_unixtime(1360863880706/1000)>= '';

Output:
8.0.11  	1

In 8.016 we are getting the following error

SQL Error [1525][HY000] : Incorrect DATETIME value ''

Please help

How to repeat:
We recently upgraded our system to 8.0.16 community server edition from 8.0.11. After we upgraded the following query broke

select @@version , from_unixtime(1360863880706/1000)>= '';

Output:
8.0.11  	1

In 8.016 we are getting the following error

SQL Error [1525][HY000] : Incorrect DATETIME value ''

Please help

Hello Raman,

Thank you for the report and test case.

regards,
Umesh

This is intended behaviour, see

Bug#93513 Unexpected behaviour for date comparison with constant strings

Upgraded our DB to 8 from 5.7

WHERE/IF sentences as the following should still be allowed to run:

SELECT * FROM foo
WHERE IF(''='', true, datecolumn <= '')

The new version should at least have a setting to allow older queries to run.
Is there such a setting? ALLOW_INVALID_DATES does not seem to help.

I don't believe the specific case of comparing date/time to '' empty string is the same as the broader scenario described in these report: (i.e. comparing date time to quoted string literal or out of range dates)
https://bugs.mysql.com/bug.php?id=93513
https://bugs.mysql.com/bug.php?id=95466

On the server ZERO_DATE is ALLOWED, MySql server has no trouble converting '' string to a date (non-NULL value returned by STR_TO_DATE)
Query
SELECT STR_TO_DATE('','%Y-%m-%d')
Output
0000-00-00

If the STR_TO_DATE function does NOT return NULL, then the system should not throw an error if we 'ALLOW_INVALID_DATES' and DON'T have [NO_ZERO_IN_DATE,NO_ZERO_DATE]. 

Clearly the conversion of '' (empty string) resulted in a valid '0000-00-00' date in system where the correct sql mode is set. There is no reason to break this backward compatibility.