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:40]
Daniël van Eeden
[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