DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a CHAR);

ALTER TABLE t1

  MODIFY a CHAR COLLATE DEFAULT,

  CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;

SHOW CREATE TABLE t1;

+-------+--------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                   |

+-------+--------------------------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `a` char(1) COLLATE utf8mb3_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |

+-------+--------------------------------------------------------------------------------------------------------------------------------+

utf8mb3_bin for the column 'a' looks wrong. The ALTER statement asked to use COLLATE DEFAULT for the column "a", which is utf8mb3_general_ci. But COLLATE DEFAULT was erroneously ignored.

Note, if I change the script slightly to ask for:

• binary collation on the column level
• default collation on the table level
  it returns the expected result:

  DROP TABLE IF EXISTS t1;

  CREATE TABLE t1 (a CHAR);

  ALTER TABLE t1

  MODIFY a CHAR BINARY,

  CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;

  SHOW CREATE TABLE t1;

  +-------+----------------------------------------------------------------------------------------------------------------------------------+

  | Table | Create Table |

  +-------+----------------------------------------------------------------------------------------------------------------------------------+

  | t1 | CREATE TABLE `t1` (

  `a` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

  +-------+----------------------------------------------------------------------------------------------------------------------------------+

Notice, the BINARY on the column level was not ignored, which is correct.

The first script should be fixed not to ingore COLLATE DEFAULT.