Bug #104350 | year() results differ when delimiter differ | ||
---|---|---|---|
Submitted: | 19 Jul 2021 3:35 | Modified: | 1 Nov 2022 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 3:35]
Zhengru Wang
[19 Jul 2021 5:44]
MySQL Verification Team
Hello Zhengru, Thank you for the report and test case. regards, Umesh
[6 Oct 2022 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 2022 21:16]
Dag Wanvik
Posted by developer: Single digit year is not supported or documented and is deprecated.
[1 Nov 2022 21:20]
Dag Wanvik
Single digit YEAR is not documented and is deprecated.