Bug #108782 Mysql str_to_date can correct convert for '2022-11-30'
Submitted: 14 Oct 2022 11:01 Modified: 17 Aug 2023 13:24
Reporter: sundy sun Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[14 Oct 2022 11:01] sundy sun
Description:
select  str_to_date('31/11/22', '%d/%m/%Y') d1,str_to_date('2021-11-31', '%Y-%m-%d') d2, EXTRACT(DAY FROM '2021-11-31') d3 from dual;

the statement result
d1              d2              d3
2022-11-31	2021-11-31      null

Expected result:
d1              d2              d3
null     	null            null

Boz d1 and d2 are invalid date.

How to repeat:
run this sql:
select  str_to_date('31/11/22', '%d/%m/%Y') d1,str_to_date('2021-11-31', '%Y-%m-%d') d2, EXTRACT(DAY FROM '2021-11-31') d3 from dual;
[14 Oct 2022 14:21] MySQL Verification Team
HI Mr. sun,

Thank you for your bug report.

We have repeated the behaviour and we think that you are correct in your analysis.

We can not yet know whether this will remain a code bug or a documentation bug.

Verified as reported.
[14 Oct 2022 14:21] MySQL Verification Team
Correct status.
[28 Apr 2023 5:31] MySQL Verification Team
Current trunk says this:

mysql> select  str_to_date('31/11/22', '%d/%m/%Y') d1,str_to_date('2021-11-31', '%Y-%m-%d') d2, EXTRACT(DAY FROM '2021-11-31') d3,version() from dual;
+------------+------------+------+-----------+
| d1         | d2         | d3   | version() |
+------------+------------+------+-----------+
| 2022-11-31 | 2021-11-31 | NULL | 8.1.0     |
+------------+------------+------+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2021-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
[17 Aug 2023 13:24] Jon Stephens
Documented fix as follows in the MySQL 5.7.44, 8.0.35, and 8.2.0 changelogs:

    The STR_TO_DATE() function did not perform complete range
    checking on the string to be converted, so that it was possible
    to pass it a string which yielded an invalid date such as
    '2021-11-31'.

Closed.
[17 Aug 2023 13:25] MySQL Verification Team
Thank you, Jon.
[17 Aug 2023 13:26] Jon Stephens
Also noted the behaviour change in the function's description in the relevant versions of the MySQL Manual.