Bug #114717 Inconsistent behavior of explicit CHARSET/COLLATE column definition
Submitted: 20 Apr 18:57 Modified: 22 Apr 9:29
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: charset, collation

[20 Apr 18:57] Przemyslaw Malkowski
Description:
For some table collations, when columns inherit the default one from table, show create table prints explicit column COLLATE definition wrongly.
Then when the same shown definition is repeated, the column is really set to use explicit charset and collation.

How to repeat:
mysql> CREATE TABLE test (
    -> id int NOT NULL AUTO_INCREMENT,
    -> a int,
    -> b varchar(10), 
    -> PRIMARY KEY (`id`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation
                "collation_id": 224,
                "is_explicit_collation": false

mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `test` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `a` int DEFAULT NULL,
    ->   `b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation
                "collation_id": 224,
                "is_explicit_collation": true

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Suggested fix:
When collation is not set explicitly for a column, it should stay that way ;)
[22 Apr 9:29] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report and feedback. 
Verified as described.

Thanks,
Umesh