Alexander Barkov added a comment - 2018-04-21 19:40 - edited The problem happens in adjust_max_effective_column_length(). It's supposed to adjust the maximum length for the integer data types according to their capacity (instead of the user-specified length), e.g. convert int(3) to int(11) . However, the code also erroneously adjusts the length for the blob data types, because their max_display_length() returns something bigger than max_length, which was previously set to char_length() by Type_std_attributes::set(). So later Type_handler::blob_type_handler() converts a wrong (too large) length to a wrong (longer) BLOB variant. Another option would be to fix Item_splocal::create_field_for_create_select() from: Field *create_field_for_create_select(TABLE *table) { return tmp_table_field_from_field_type(table); } to: Field *create_field_for_create_select(TABLE *table) { return create_table_field_from_handler(table); } This will make sure preserve the data type of the variable.

Alexander Barkov added a comment - 2018-04-22 10:45 - edited The same problem: I start mysql --column-type-info test an run this script: DELIMITER $$ BEGIN NOT ATOMIC DECLARE a ENUM( 'a' ) DEFAULT 'a' ; SELECT a; END ; $$ Field 1: `a` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: utf8_general_ci (33) Length: 3 Max_length: 1 Decimals: 0 Flags: ENUM Notice, the variable reports itself STRING (that is CHAR ). Ok. Now I use a ENUM column in a UNION with a CHAR column: CREATE OR REPLACE TABLE t1 (a CHAR (1), b ENUM( 'a' )); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1; DESC t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ Notice, the ENUM column worked as CHAR and the result column t2.a is also of the type CHAR . This is also OK. Now I use a ENUM variable in a UNION with a CHAR column DELIMITER $$ BEGIN NOT ATOMIC DECLARE b ENUM( 'b' ) DEFAULT 'b' ; CREATE OR REPLACE TABLE t1 (a CHAR (1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b; DESC t2; END ; $$ +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ Notice, the ENUM variable worked as CHAR and the result column t2.a is also of the type CHAR . This is also OK. But if I now create a table from an ENUM variable alone, without a UNION , it works as VARCHAR : DELIMITER $$ BEGIN NOT ATOMIC DECLARE a ENUM( 'a' ) DEFAULT 'a' ; CREATE OR REPLACE TABLE t1 AS SELECT a; DESC t1; END ; $$ +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | varchar(1) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ This is wrong. It should create at least a column of the char(1) data type. (eventually it could be fixed to even create an ENUM column)

Alexander Barkov added a comment - 2018-04-22 10:53 The same problem is repeatable with a CHAR variable: DELIMITER $$ BEGIN NOT ATOMIC DECLARE a CHAR (1) DEFAULT 'a' ; CREATE OR REPLACE TABLE t1 AS SELECT a; DESC t1; END ; $$ +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | varchar(1) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ The expected column data type should be char(1) .