Bug #109796 STR_TO_DATE() invalid input doesn't returns NULL but invalid DATE

• Submitted: 26 Jan 2023 21:17
• Modified: 1 May 10:12
• Reporter: Sébastien F.
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.32, 5.7
• OS: Ubuntu
• 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

[1 May 10:12] Sébastien F.

When I execute the SQL snippet on MySQL 8.0.37 I now get the awaited values.

Can we consider this bug corrected ?

[21 May 12:29] Roy Lyseng

It looks like a duplicate of 108782, which was fixed in 8.0.35.