Bug #109796 STR_TO_DATE() invalid input doesn't returns NULL but invalid DATE
Submitted: 26 Jan 2023 21:17 Modified: 27 Jan 2023 6:47
Reporter: Sébastien F. Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.32, 5.7 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: date str_to_date

[26 Jan 2023 21:17] Sébastien F.
Description:
The doc about STR_TO_DATE() https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date says :

> If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning
> [...]
> Range checking on the parts of date values is as described in Section 11.2.2, “The DATE, DATETIME, and TIMESTAMP Types”.

The doc https://dev.mysql.com/doc/refman/8.0/en/datetime.html says :

> The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error.

It seems not be taken in account.

How to repeat:
set @@sql_mode = 'ANSI,TRADITIONAL';

select all @@sql_mode; -- REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION

select all date('2022-04-31'); -- NULL => OK

select all str_to_date('2022-04-31', '%Y-%m-%d'); -- 2022-04-31 => NULL expected

select all date(str_to_date('2022-04-31', '%Y-%m-%d')); -- 2022-04-31 => NULL expected
[27 Jan 2023 6:47] MySQL Verification Team
Hello Sébastien F.,

Thank you for the report and test case.
Verified as described.

regards,
Umesh