Table collation doesn't follow schema

[dveeden]

Bug Report

When creating a table the collation should be the collation from the schema if it is not explicitly set for the table. This doesn't seem to be the case with TiDB if the character set is specified explicitly, but the collation is not.

1. Minimal reproduce step (Required)

CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE s1;
CREATE TABLE t11 (id INT PRIMARY KEY);
CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';

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

MySQL 9.1.0:

mysql-9.1.0> CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql-9.1.0> USE s1;
Database changed
mysql-9.1.0> CREATE TABLE t11 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql-9.1.0> CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql-9.1.0> CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql-9.1.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| t11        | utf8mb3_general_ci |
| t12        | utf8mb3_general_ci |
| t13        | utf8mb3_general_ci |
+------------+--------------------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

TiDB v8.5.0

mysql-8.0.11-TiDB-v8.5.0> CREATE SCHEMA s1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.05 sec)

mysql-8.0.11-TiDB-v8.5.0> USE s1;
Database changed
mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t11 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t12 (id INT PRIMARY KEY) CHARACTER SET utf8;
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> CREATE TABLE t13 (id INT PRIMARY KEY) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.04 sec)

mysql-8.0.11-TiDB-v8.5.0> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.tables WHERE TABLE_SCHEMA='s1';
+------------+-----------------+
| TABLE_NAME | TABLE_COLLATION |
+------------+-----------------+
| t11        | utf8_general_ci |
| t12        | utf8_bin        |
| t13        | utf8_general_ci |
+------------+-----------------+
3 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v8.5.0
Edition: Community
Git Commit Hash: d13e52ed6e22cc5789bed7c64c861578cd2ed55b
Git Branch: HEAD
UTC Build Time: 2024-12-18 02:26:06
GoVersion: go1.23.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv