Bug #26731 ALTER TABLE `Table` CONVERT TO CHARSET .... Fails on Enums

Description:
When using the ALTER TABLE `table` CONVERT TO CHARACTER SET. Enums fails to convert.

If I create a table with an enum with chars that doesn't exists in the new charset then this char and rest of the enum definition will be removed.

If I set a default value the alter command fails with #1067: Invalid default value for.... If I change the default value to a char that exists in the new charset, the commnad is accepted. But then the problem above occurs.

How to repeat:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.26-log | 
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test` (`letter` ENUM('a','å','ä','ö') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'å') ENGINE = myisam CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> show full fields from `test`;
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type                     | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| letter | enum('a','å','ä','ö')    | latin1_swedish_ci | NO   |     | å       |       | select,insert,update,references |         | 
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> insert into `test` values ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into `test` values ('a');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+
| letter |
+--------+
| å      | 
| a      | 
+--------+
2 rows in set (0.00 sec)

mysql> alter table `test` convert to character set utf8 collate utf8_swedish_ci;
ERROR 1067 (42000): Invalid default value for 'letter'

mysql> alter table `test` alter `letter` set default 'a';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show full fields from `test`;
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type                     | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| letter | enum('a','å','ä','ö')    | latin1_swedish_ci | NO   |     | a       |       | select,insert,update,references |         | 
+--------+--------------------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

mysql> alter table `test` convert to character set utf8 collate utf8_swedish_ci;
Query OK, 2 rows affected, 3 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from `test`;
+--------+
| letter |
+--------+
|        | 
| a      | 
+--------+
2 rows in set (0.00 sec)

Sorry..

Forgot one thing in the test case.

mysql> show full fields from `test`;
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type       | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| letter | enum('a',' | utf8_swedish_ci | NO   |     | a       |       | select,insert,update,references |         | 
+--------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

Thank you for the report.

Verified as described.

Reproducable on 5.6.21. This does a lot of damage even if STRICT_ALL_TABLES and InnoDB Strict Mode are enabled. I think this should have severity S2 instead of S3.

mysql [test3] > CREATE TABLE t1 (id int auto_increment primary key, val varchar(100), val2 enum('Café','Wrocław')) default character set utf8;
Query OK, 0 rows affected (0.01 sec)

mysql [test3] > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(100) DEFAULT NULL,
  `val2` enum('Café','Wrocław') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql [test3] > ALTER TABLE t1 CONVERT TO CHARACTER SET utf32;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [test3] > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(100) DEFAULT NULL,
  `val2` enum('?','??') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf32
1 row in set (0.00 sec)

mysql [test3] > ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [test3] > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(100) DEFAULT NULL,
  `val2` enum('Café6C3A9','WrocławC5826177') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql [test3] > select @@innodb_strict_mode, @@sql_mode;
+----------------------+-------------------+
| @@innodb_strict_mode | @@sql_mode        |
+----------------------+-------------------+
|                    1 | STRICT_ALL_TABLES |
+----------------------+-------------------+
1 row in set (0.00 sec)

Also STRICT_ALL_TABLES and InnoDB Strict Mode do not prevent me creating ENUMs with chars which are not valid for the character set of the table.

mysql [test3] > CREATE TABLE t2 (id int auto_increment primary key, val varchar(100), val2 enum('Café','Wrocław'));
Query OK, 0 rows affected (0.01 sec)

mysql [test3] > show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(100) DEFAULT NULL,
  `val2` enum('Café','Wroc?aw') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql [test3] > select @@innodb_strict_mode, @@sql_mode;                                                        +----------------------+-------------------+
| @@innodb_strict_mode | @@sql_mode        |
+----------------------+-------------------+
|                    1 | STRICT_ALL_TABLES |
+----------------------+-------------------+
1 row in set (0.00 sec)

Addition to my previous message: I can create the table and are also allowed to insert this value. This differs from how a varchar is handled.

mysql [test3] > insert into t2(val, val2) values('Wrocław','Wrocław');
ERROR 1366 (HY000): Incorrect string value: '\xC5\x82aw' for column 'val' at row 1
mysql [test3] > insert into t2(val, val2) values('Café','Wrocław');
Query OK, 1 row affected (0.00 sec)

mysql [test3] > select * from t2;
+----+-------+---------+
| id | val   | val2    |
+----+-------+---------+
|  1 | Café  | Wroc?aw |
+----+-------+---------+
1 row in set (0.01 sec)

This can also cause information_schema queries to generate warnings.

mysql [footest] > create table t3 (id int, val enum('Café','foo'));
Query OK, 0 rows affected (0.01 sec)

mysql [footest] > select table_schema, table_name, column_name from information_schema.columns where table_name='t3';
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| footest      | t3         | id          |
| footest      | t3         | val         |
+--------------+------------+-------------+
2 rows in set (0.00 sec)

mysql [footest] > alter table t3 convert to character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [footest] > select table_schema, table_name, column_name from information_schema.columns where table_name='t3';
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| footest      | t3         | id          |
| footest      | t3         | val         |
+--------------+------------+-------------+
2 rows in set, 2 warnings (0.00 sec)

Warning (Code 1366): Incorrect string value: '\xE9','fo...' for column 'COLUMN_TYPE' at row 1
Warning (Code 1366): Incorrect string value: '\xE9','fo...' for column 'COLUMN_TYPE' at row 1
mysql [footest] > show create table t3\G                                                                        *************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `val` enum('Caf�','foo') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

This is how PostgreSQL 9.4 behaves with enums:
I had to start psql with --no-readline to be able to enter multibyte characters.

---------------------------------------------
postgres=# CREATE DATABASE test1 WITH TEMPLATE template0 ENCODING 'LATIN1' LC_CTYPE 'C' LC_COLLATE='C'; 
CREATE DATABASE
postgres=# CREATE DATABASE test2;
CREATE DATABASE
postgres=# \c test2
You are now connected to database "test2" as user "postgres".
test2=# create type test1 as enum ('Cafe','Wrocław');
CREATE TYPE
test2=# create type test2 as enum ('Café','Wrocław');
CREATE TYPE
test2=# \dT+
                                 List of data types
 Schema | Name  | Internal name | Size | Elements | Access privileges | Description 
--------+-------+---------------+------+----------+-------------------+-------------
 public | test1 | test1         | 4    | Cafe    +|                   | 
        |       |               |      | Wrocław  |                   | 
 public | test2 | test2         | 4    | Café    +|                   | 
        |       |               |      | Wrocław  |                   | 
(2 rows)
test2=# \c test1 
You are now connected to database "test1" as user "postgres".
test1=# create type test1 as enum ('Cafe','Wrocław');
ERROR:  character with byte sequence 0xc5 0x82 in encoding "UTF8" has no equivalent in encoding "LATIN1"
test1=# create type test2 as enum ('Café','Wrocław');
ERROR:  character with byte sequence 0xc5 0x82 in encoding "UTF8" has no equivalent in encoding "LATIN1"
---------------------------------------------

PostgreSQL handles character set encodings on a cluster and database level, not on a table/column level.
PostgreSQL doesn't have a CONVER TO CHARACTER SET option. Conversion is done with a dump/restore cycle.

---------------------------------------------
test1=# create type test3 as enum ('Café','Wroclaw');
CREATE TYPE
test1=# \dT+ test3
                                 List of data types
 Schema | Name  | Internal name | Size | Elements | Access privileges | Description 
--------+-------+---------------+------+----------+-------------------+-------------
 public | test3 | test3         | 4    | Café    +|                   | 
        |       |               |      | Wroclaw  |                   | 
(1 row)
$ pg_dump -U postgres -E utf8 test1 > test1_utf8.sql
$ psql --no-readline -U postgres test2 < test1_utf8.sql
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
CREATE TYPE
ALTER TYPE
REVOKE
REVOKE
GRANT
GRANT
test2=# \dT+ test3
                                 List of data types
 Schema | Name  | Internal name | Size | Elements | Access privileges | Description 
--------+-------+---------------+------+----------+-------------------+-------------
 public | test3 | test3         | 4    | Café    +|                   | 
        |       |               |      | Wroclaw  |                   | 
(1 row)
---------------------------------------------