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:
None 
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
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);

   /*
[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.