| 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: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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;