MySQL Bugs: #96361: Request with empty date

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

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;

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

Also, Bug #95466

Also - Bug #95780

https://lefred.be/content/mysql-8-0-and-wrong-dates/

This is a blocker for us - preventing us from upgrading beyond 8.0.15. This should be addressed. Thanks.