Bug #68577 Incorrect enum values returned when using UNION after CREATE AS SELECT

• Submitted: 5 Mar 2013 19:29
• Modified: 5 Mar 2013 19:38
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5, 5.6
• OS: Any
• CPU Architecture: Any

[5 Mar 2013 19:29] Elena Stepanova

Description:
Filing on behalf of our user, who initially reported it as https://mariadb.atlassian.net/browse/MDEV-4215

[d1]> create table t1 (e enum('one', 'two'));
Query OK, 0 rows affected (0.75 sec)

[d1]> insert into t1 values ('one');
Query OK, 1 row affected (0.40 sec)

[d1]> create table t2 (e enum('one', 'two')) as select * from t1;
Query OK, 1 row affected (0.78 sec)
Records: 1  Duplicates: 0  Warnings: 0

# Correct result:

[d1]> select * from t2;
+------+
| e    |
+------+
| one  |
+------+
1 row in set (0.01 sec)

# Wrong result:

[d1]> select * from t2 union all select * from t2;
+------+
| e    |
+------+
| o    |
| o    |
+------+
2 rows in set (0.00 sec)

[d1]> alter table t2 engine MyISAM;
Query OK, 1 row affected (0.91 sec)                
Records: 1  Duplicates: 0  Warnings: 0

# Correct result:

[d1]> select * from t2 union all select * from t2;
+------+
| e    |
+------+
| one  |
| one  |
+------+
2 rows in set (0.00 sec)

How to repeat:
create table t1 (e enum('one', 'two'));
insert into t1 values ('one');
create table t2 (e enum('one', 'two')) as select * from t1;

select * from t2;
select * from t2 union all select * from t2;
alter table t2 engine MyISAM;
select * from t2 union all select * from t2;

[5 Mar 2013 19:38] MySQL Verification Team

Thank you for the report, verified

[11 Dec 2019 22:01] Bruce Hunsaker

I just ran into this bug on 5.7.21  and verified it is also a bug on version 8.0.13

Please fix this!

[12 Dec 2019 7:57] Roy Lyseng

Please try a later version of 8.0. I am not getting this problem on MySQL 8.0.17.

[12 Dec 2019 16:14] Bruce Hunsaker

I tested on 8.0.15 and 8.0.16 and could not reproduce the bug.  But the bug exists in 8.0.13 and earlier.  I couldn't test on 8.0.14 because that version isn't available in Amazon RDS.