incompatible with mysql in column-type-info when use cast() #61350
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
create table testfield (f0 int,f1 decimal(10,2),f2 bigint,f3 datetime,f4 float,f5 double,primary key (f0));
insert into testfield values (1,1.1,1,'20250527',1.1,1.1);
insert into testfield values (2,2.2,2,'20250527',2.2,2.2);
TiDB nightly:
mysql> select cast(f0 as char),cast(f1 as char),cast(f2 as char),cast(f3 as char),cast(f4 as char),cast(f5 as char) from testfield;
Field 1: cast(f0 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 44
Max_length: 1
Decimals: 31
Flags:
Field 2: cast(f1 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 5
Max_length: 4
Decimals: 31
Flags:
Field 3: cast(f2 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 80
Max_length: 1
Decimals: 31
Flags:
Field 4: cast(f3 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 5
Max_length: 19
Decimals: 31
Flags:
Field 5: cast(f4 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 5
Max_length: 3
Decimals: 31
Flags:
Field 6: cast(f5 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_bin (46)
Length: 5
Max_length: 3
Decimals: 31
Flags:
+------------------+------------------+------------------+---------------------+------------------+------------------+
| cast(f0 as char) | cast(f1 as char) | cast(f2 as char) | cast(f3 as char) | cast(f4 as char) | cast(f5 as char) |
+------------------+------------------+------------------+---------------------+------------------+------------------+
| 1 | 1.10 | 1 | 2025-05-27 00:00:00 | 1.1 | 1.1 |
| 2 | 2.20 | 2 | 2025-05-27 00:00:00 | 2.2 | 2.2 |
+------------------+------------------+------------------+---------------------+------------------+------------------+
2 rows in set (0.00 sec)
MySQL 8.4.4:
[tidb@k8s-master ~]$ mysql --column-type-info -u root -p123123 -D test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.4.4 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select cast(f0 as char),cast(f1 as char),cast(f2 as char),cast(f3 as char),cast(f4 as char),cast(f5 as char) from testfield;
Field 1: cast(f0 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 44
Max_length: 1
Decimals: 31
Flags:
Field 2: cast(f1 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 48
Max_length: 4
Decimals: 31
Flags:
Field 3: cast(f2 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 80
Max_length: 1
Decimals: 31
Flags:
Field 4: cast(f3 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 76
Max_length: 19
Decimals: 31
Flags:
Field 5: cast(f4 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 48
Max_length: 3
Decimals: 31
Flags:
Field 6: cast(f5 as char)
Catalog: def
Database: Table:
Org_table: ``
Type: VAR_STRING
Collation: utf8mb4_0900_ai_ci (255)
Length: 88
Max_length: 3
Decimals: 31
Flags:
+------------------+------------------+------------------+---------------------+------------------+------------------+
| cast(f0 as char) | cast(f1 as char) | cast(f2 as char) | cast(f3 as char) | cast(f4 as char) | cast(f5 as char) |
+------------------+------------------+------------------+---------------------+------------------+------------------+
| 1 | 1.10 | 1 | 2025-05-27 00:00:00 | 1.1 | 1.1 |
| 2 | 2.20 | 2 | 2025-05-27 00:00:00 | 2.2 | 2.2 |
+------------------+------------------+------------------+---------------------+------------------+------------------+
2 rows in set (0.00 sec)
mysql>
2. What did you expect to see? (Required)
Compatible with MySQL when use cast(XXX as varchar), include datetime\float\double\decimal
3. What did you see instead (Required)
the values of length in column-type-info incompatible with MySQL when use cast(xxx as varchar)
4. What is your TiDB version? (Required)
+--------------------------------------------+
| version() |
+--------------------------------------------+
| 8.0.11-TiDB-v9.0.0-beta.1.pre-803-g7702f73 |
+--------------------------------------------+
1 row in set (0.01 sec)