| 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: | |
| 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: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
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.