show create table behavior differences between TiDB and MySQL when the column has the explicit COLLATE configured

[yahonda]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table foo1 (bar varchar(250) COLLATE utf8mb4_bin) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
show create table foo1\G

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

show create table should show the column level COLLATE as MySQL

mysql> create table foo1 (bar varchar(250) COLLATE utf8mb4_bin) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table foo1\G
*************************** 1. row ***************************
       Table: foo1
Create Table: CREATE TABLE `foo1` (
  `bar` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.3     |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

The column level collation does not appear at TiDB.

mysql> create table foo1 (bar varchar(250) COLLATE utf8mb4_bin)  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table foo1\G
*************************** 1. row ***************************
       Table: foo1
Create Table: CREATE TABLE `foo1` (
  `bar` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.1.0 |
+--------------------+
1 row in set (0.00 sec)

[yahonda]

When the table default collation and the column level collation does not match, TiDB shows the column level collation as follows.

mysql> create table foo2 (bar varchar(250) COLLATE utf8mb4_0900_bin) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table foo2\G
*************************** 1. row ***************************
       Table: foo2
Create Table: CREATE TABLE `foo2` (
  `bar` varchar(250) COLLATE utf8mb4_0900_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.1.0 |
+--------------------+
1 row in set (0.00 sec)

[bb7133]

Hi @yahonda , thanks for reporting this.

Though we have different CREATE TABLE statement, however, it doesn't seems to lead a trouble to usage of TiDB. So I don't think we need to fix it.