Bug #105875 Unexpected output for CAST('2000' AS YEAR) when using MySQL Shell
Submitted: 13 Dec 2021 9:40 Modified: 13 Dec 2021 10:51
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Shell General / Core Client Severity:S1 (Critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: x protocol

[13 Dec 2021 9:40] Daniël van Eeden
Description:
Via MySQL Shell the output of `CAST('2000' AS YEAR)` is incorrect.

How to repeat:
# Via MySQL Shell

 MySQL  127.0.0.1:18027+ ssl  SQL > SELECT CAST('2000' AS YEAR), VERSION();
+----------------------+-----------+
| CAST('2000' AS YEAR) | VERSION() |
+----------------------+-----------+
|                 4000 | 8.0.27    |
+----------------------+-----------+
1 row in set (0.0009 sec)

# Via MySQL Client

mysql> SELECT CAST('2000' AS YEAR), VERSION();
+----------------------+-----------+
| CAST('2000' AS YEAR) | VERSION() |
+----------------------+-----------+
|                 2000 | 8.0.27    |
+----------------------+-----------+
1 row in set (0.00 sec)

Suggested fix:
Make sure the result is the same as with MySQL Client.
[13 Dec 2021 9:42] Daniël van Eeden
With `--column-type-info`:

 MySQL  127.0.0.1:18027+ ssl  SQL > SELECT CAST('2000' AS YEAR), VERSION();
Field 1
Name:      `CAST('2000' AS YEAR)`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      UInteger
DbType:    SHORT
Collation: binary (63)
Length:    5
Decimals:  0
Flags:     UNSIGNED NUM 

Field 2
Name:      `VERSION()`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      String
DbType:    VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length:    18
Decimals:  0
Flags:     NOT_NULL 

+----------------------+-----------+
| CAST('2000' AS YEAR) | VERSION() |
+----------------------+-----------+
|                 4000 | 8.0.27    |
+----------------------+-----------+
1 row in set (0.0008 sec)
[13 Dec 2021 9:44] Daniël van Eeden
With `mysql --column-type-info`:

mysql> SELECT CAST('2000' AS YEAR), VERSION();
Field   1:  `CAST('2000' AS YEAR)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       YEAR
Collation:  binary (63)
Length:     5
Max_length: 4
Decimals:   0
Flags:      BINARY NUM 

Field   2:  `VERSION()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_0900_ai_ci (255)
Length:     24
Max_length: 6
Decimals:   31
Flags:      NOT_NULL 

+----------------------+-----------+
| CAST('2000' AS YEAR) | VERSION() |
+----------------------+-----------+
|                 2000 | 8.0.27    |
+----------------------+-----------+
1 row in set (0.00 sec)
[13 Dec 2021 9:54] Daniël van Eeden
MySQL  127.0.0.1:18027+ ssl  SQL > WITH RECURSIVE n(n) AS (SELECT 0 UNION ALL SELECT n + 20 FROM n WHERE n < 4000) SELECT n, CAST(n AS YEAR) FROM n WHERE n < 100 OR (n BETWEEN 1901 AND 2100);
+------+-----------------+
| n    | CAST(n AS YEAR) |
+------+-----------------+
|    0 |               0 |
|   20 |            4040 |
|   40 |            4080 |
|   60 |            4120 |
|   80 |            3960 |
| 1920 |            3840 |
| 1940 |            3880 |
| 1960 |            3920 |
| 1980 |            3960 |
| 2000 |            4000 |
| 2020 |            4040 |
| 2040 |            4080 |
| 2060 |            4120 |
| 2080 |            4160 |
| 2100 |            4200 |
+------+-----------------+
15 rows in set (0.0015 sec)
[13 Dec 2021 9:55] Daniël van Eeden
Via the 'classic' protocol things seem fine:

 MySQL  127.0.0.1:8027 ssl  SQL > WITH RECURSIVE n(n) AS (SELECT 0 UNION ALL SELECT n + 20 FROM n WHERE n < 4000) SELECT n, CAST(n AS YEAR) FROM n WHERE n < 100 OR (n BETWEEN 1901 AND 2100);
+------+-----------------+
| n    | CAST(n AS YEAR) |
+------+-----------------+
|    0 |               0 |
|   20 |            2020 |
|   40 |            2040 |
|   60 |            2060 |
|   80 |            1980 |
| 1920 |            1920 |
| 1940 |            1940 |
| 1960 |            1960 |
| 1980 |            1980 |
| 2000 |            2000 |
| 2020 |            2020 |
| 2040 |            2040 |
| 2060 |            2060 |
| 2080 |            2080 |
| 2100 |            2100 |
+------+-----------------+
15 rows in set (0.0013 sec)
[13 Dec 2021 10:01] Daniël van Eeden
MySQL  127.0.0.1:18027+ ssl  SQL > SELECT CAST(2000 AS YEAR) INTO @a;
Query OK, 1 row affected (0.0008 sec)
 MySQL  127.0.0.1:18027+ ssl  SQL > SELECT @a, CAST(2000 AS YEAR), CAST(2000 AS YEAR) = @a;
+------+--------------------+-------------------------+
| @a   | CAST(2000 AS YEAR) | CAST(2000 AS YEAR) = @a |
+------+--------------------+-------------------------+
| 2000 |               4000 |                       1 |
+------+--------------------+-------------------------+
1 row in set (0.0009 sec)
[13 Dec 2021 10:51] MySQL Verification Team
Hello Daniël,

Thank you for the report and feedback.

regards,
Umesh