| Bug #104350 | year() results differ when delimiter differ | ||
|---|---|---|---|
| Submitted: | 19 Jul 2021 3:35 | Modified: | 1 Nov 21:20 |
| Reporter: | Zhengru Wang | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.25, 5.7.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Jul 2021 5:44]
MySQL Verification Team
Hello Zhengru, Thank you for the report and test case. regards, Umesh
[6 Oct 16:37]
Dag Wanvik
Posted by developer:
I don't think this issue should be fixed: the problem is that in the example case,
year("1.1.1") is wrongly interpreted as "internal format" (non-delimited datetime specification),
based on the presence of a (presumed decimal) period: e.g. as in
mysql> SELECT CAST('011231120000.4' AS DATETIME(4));
+---------------------------------------+
| CAST('011231120000.4' AS DATETIME(4)) |
+---------------------------------------+
| 2001-12-31 12:00:00.4000 |
+---------------------------------------+
In our docs, we say say that years can be given with *two* digits and MySQL will add in 1900 or 2000,
e.g 'YY-MM-DD'. We also say that any other delimiter can be used (although that is now deprecated), but
nowhere do we say that a single digit can serve as anything other that itself, i.e. with no automatic addition.
So the error here is that we add 2000 in the case of YEAR('1.1.1');
We are also planning to deprecate allowing two digit years in datetime literals, cf. WL#13603, so
extending our support to single digit years now seems the wrong strategic choice. I suggest we close this as won't fix.
[1 Nov 21:16]
Dag Wanvik
Posted by developer: Single digit year is not supported or documented and is deprecated.
[1 Nov 21:20]
Dag Wanvik
Single digit YEAR is not documented and is deprecated.
Description: When delimiter is '.', year('1.1.1') returns 2001. But when delimiter is '-', year('1-1-1') returns 1 This difference can also occur in functions such as CAST AS DATE. How to repeat: mysql> SELECT year("1.1.1"); +---------------+ | year("1.1.1") | +---------------+ | 2001 | +---------------+ 1 row in set (0.00 sec) mysql> SELECT year("1-1-1"); +---------------+ | year("1-1-1") | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) Suggested fix: diff --git a/mysys/my_time.cc b/mysys/my_time.cc index e33ba569..41092cdb 100644 --- a/mysys/my_time.cc +++ b/mysys/my_time.cc @@ -648,7 +648,7 @@ bool str_to_datetime(const char *str, std::size_t length, MYSQL_TIME *l_time, } l_time->neg = false; - if (year_length == 2 && not_zero_date) + if (year_length <= 2 && not_zero_date) l_time->year += (l_time->year < YY_PART_YEAR ? 2000 : 1900); /*