CAST AS..YEAR TiDB results not matching with MySQL 8.0

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SELECT CAST(69  AS YEAR), CAST(70  AS YEAR),CAST(00  AS YEAR);
select (CAST(20201 AS YEAR));
SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);

2. What did you expect to see? (Required)

mysql> SELECT CAST(69  AS YEAR), CAST(70  AS YEAR),CAST(00  AS YEAR);
+-------------------+-------------------+-------------------+
| CAST(69  AS YEAR) | CAST(70  AS YEAR) | CAST(00  AS YEAR) |
+-------------------+-------------------+-------------------+
|              2069 |              1970 |                 0 |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql> select (CAST(20201 AS YEAR));
+-----------------------+
| (CAST(20201 AS YEAR)) |
+-----------------------+
|                  NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
--Warning | 1292 | Truncated incorrect YEAR value: '20201'
mysql> SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);
+---------------------------------------------+
| CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR) |
+---------------------------------------------+
|                                        NULL |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
--| Warning | 1292 | Truncated incorrect YEAR value: '579'

3. What did you see instead (Required)

mysql> SELECT CAST(69  AS YEAR), CAST(70  AS YEAR),CAST(00  AS YEAR);
+-------------------+-------------------+-------------------+
| CAST(69  AS YEAR) | CAST(70  AS YEAR) | CAST(00  AS YEAR) |
+-------------------+-------------------+-------------------+
|                69 |                70 |              0000 |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql> select (CAST(20201 AS YEAR));
+-----------------------+
| (CAST(20201 AS YEAR)) |
+-----------------------+
|                 20201 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR);
+---------------------------------------------+
| CAST(TIMESTAMP'579-10-10 10:10:10' AS YEAR) |
+---------------------------------------------+
|                               5791010101010 |
+---------------------------------------------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_analyze_version    | 2                                                                        |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v5.2.2                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+

[morgo]

CAST .. AS YEAR is new to MySQL 8.0. In MySQL 5.7, this returns a syntax error:

mysql [localhost:5735] {msandbox} (test) > SELECT CAST(69  AS YEAR), CAST(70  AS YEAR),CAST(00  AS YEAR);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'YEAR), CAST(70  AS YEAR),CAST(00  AS YEAR)' at line 1

So TiDB could chose to either return an error as well (5.7) or correct results per MySQL 8.0. But currently the results are incorrect, which could cause problems.

[fuzhe1989]

/assign @ichn-hu

[solotzg]

According to https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html. MySQL supports CAST( AS YEAR) since 8.0.22. TiDB should follow those behaviors if need to be compatible with mysql 8.0.
But for mysql 5.7, tidb should raise error like ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...

[zanmato1984]

We'll aim to MySQL 8.0. But this usage, i.e., two digits number, is not very common. We consider this to be a moderate issue.