Bug #104350 year() results differ when delimiter differ

• Submitted: 19 Jul 2021 3:35
• Modified: 19 Jul 2021 5:44
• Reporter: Zhengru Wang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.25, 5.7.34
• OS: Any
• 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