Bug #96361 Request with empty date
Submitted: 29 Jul 2019 8:39 Modified: 29 Jul 2019 9:08
Reporter: Emmanuel CARVIN Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.17 OS:Windows
Assigned to: CPU Architecture:Any

[29 Jul 2019 8:39] Emmanuel CARVIN
Description:
Hi,

This is non critical bug but this is a syntax that is allowed in 5.7 which is no longer in 8.

In our very old software, we have some request that test if date field are empty or null.

Example :

SELECT * FROM sales WHERE sales_date = '' OR sales_date IS NULL;

This syntax works perfectly in 5.7 and older version. But on 8.0.17 MySQL's version request like the example return error :

Error Code: 1525. Incorrect DATE value: ''

I try to enable allow_invalid_dates in sql_mode but no change.

Change all our software's code would be relatively long... Maybe a variable to set?

Best regards,

How to repeat:
CREATE TABLE `sch_test`.`new_table` (
  `id_table` INT NOT NULL AUTO_INCREMENT,
  `text_table` VARCHAR(45) NULL DEFAULT NULL,
  `date_table` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id_table`));
  
SELECT id_table, text_table FROM new_table WHERE date_table = '' OR date_table IS NULL;
[29 Jul 2019 9:08] MySQL Verification Team
Hello Emmanuel CARVIN,

Thank you for the report.
This behavior is introduced after Bug #93513. Marking this is as a duplicate of Bug #93513, please see Bug #93513.

regards,
Umesh
[29 Jul 2019 9:08] MySQL Verification Team
Also, Bug #95466
[29 Jul 2019 9:10] MySQL Verification Team
Also - Bug #95780
[31 Jul 2019 19:59] Frederic Descamps
https://lefred.be/content/mysql-8-0-and-wrong-dates/
[31 Mar 2020 13:39] Jojo Thomas
This is a blocker for us - preventing us from upgrading beyond 8.0.15. This should be addressed. Thanks.