Bug #96361 Request with empty date
Submitted: 29 Jul 8:39 Modified: 29 Jul 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:Microsoft Windows
Assigned to: CPU Architecture:Any

[29 Jul 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 9:08] Umesh Shastry
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 9:08] Umesh Shastry
Also, Bug #95466
[29 Jul 9:10] Umesh Shastry
Also - Bug #95780
[31 Jul 19:59] Frederic Descamps
https://lefred.be/content/mysql-8-0-and-wrong-dates/