Bug #46239 mysql doesn't show what collation was used if that collation is the default

Description:
if you create a table with a character column and specify it's charset and collation, the output of 'show create table' doesn't show the collation if the collation used was the default for the charset.

example: both tables are created with explicit COLLATE options for columns `a`, but 'show create table' only shows one of them:

mysql> show create table t1;
+-------+-------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------
| t1    | CREATE TABLE `t1` (
  `a` char(1) character set utf8 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+-------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------
| t2    | CREATE TABLE `t2` (
  `a` char(1) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------
1 row in set (0.00 sec)

If i specified the collation i want it always to be shown in show create table!
What if the server is recompiled with another default collation? what happens?

How to repeat:
drop table if exists t1,t2;
create table t1(a char(1) charset utf8 collate utf8_general_ci) charset=latin1;
create table t2(a char(1) charset utf8 collate utf8_bin) charset=latin1;
show create table t1;
show create table t2;

#notice collation is not shown for t1.

Suggested fix:
always put the collation in the show create table output, even if it's the default one.

Thank you for the bug report. Verified just as described with recent bzr threes:

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

mysql> drop table if exists t1,t2;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table t1(a char(1) charset utf8 collate utf8_general_ci)
    -> charset=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2(a char(1) charset utf8 collate utf8_bin)
    -> charset=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` char(1) character set utf8 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` char(1) character set utf8 collate utf8_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

huh .. I thought it was a *feature*! no collation displayed => default collation was defined. 9 months since I first noticed this!

This is an intentional behaviour, to reduce "SHOW CREATE TABLE"
output size. 

However, having a full "CREATE TABLE" could be useful in some cases.

Reasonable feature request.

One more comment: it works this way since version 4.1.0 when
we introduced collations in MySQL. There's no regression here.

Bar, reducing the output size of SHOW CREATE TABLE is not a useful argument here. What we need is accurate information!

Shane, I agree full information is better for reading dumps
or seeing at "SHOW CREATE TABLE" output. So this is a reasonable
feature request.

However, from dumping and restoring point of view it's safe anyway.

Even if mysql was compiled for example
"--default-character-set=utf8 --default-collation=utf8_unicode_ci"

it only means that when you do "CREATE DATABASE", you get
"CHARACTER SET utf8 COLLATE utf8_unicode_ci" by default.

The default collation for utf8 is STILL utf8_general_ci.
So there's no any ambiguity depending on compilation flags here.

If the future we'll probably allow to change the default collation
for a character set. So full information will be important someday!

this bug comes to light now that 8.0 has different default collation for utf8mb4
See:
  https://bugs.mysql.com/bug.php?id=88373

Workaround:
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = name;
SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = name;

Posted by developer:
 
Fixed in 8.0.11.

Previously, SHOW CREATE TABLE did not show collation for a column if
the collation was the same as the table default, even if the
collation was explicitly specified at table-creation time. Now, SHOW
CREATE TABLE always shows the column collation if the collation was
explicitly specified, even if the collation is the same as the table
default.

Bug#89606 PLEASE PRINT DEFAULT COLLATION IN DUMP/PUMP/SHOW CREATE TABLE

Bug#89241 Modify SHOW CREATE TABLE to provide full charset and collation

were marked as duplicates.

see https://bugs.mysql.com/bug.php?id=114195