MySQL Bugs: #5618: Union with enum is broken

Bug #5618	Union with enum is broken
Submitted:	16 Sep 2004 21:04	Modified:	26 Sep 2004 15:52
Reporter:	Mike Meyers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.5	OS:	Linux (Linux)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
If you union two or more queries, and one of the queries includes an enum column the results are confussing. 

It would appear that the top node of the query executer evaluates the results of each unioned queries and in some cases takes those results and assumes they are part of the enumeration.

How to repeat:
example % cat union.sql
DROP TABLE IF EXISTS withEnum;
create table withEnum (
    shift ENUM('BUG', 'No') NOT NULL
);

insert into withEnum values ('No');

-- This one takes the "1" in the first query and evalautes as the enumeration
select "1" as "Only Column"
union
select shift from withEnum;

-- This one takes the 12 and presumably evaluates as an enumeration and 
-- comes back empty.
select "12" as "Only Column"
union
select shift from withEnum;

-- This one takes the "123" and does not evaluate against the enumeration.
select "123" as "Only Column"
union
select shift from withEnum;
example%

Here is the results of running the above test case.

mysql> source union.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+-------------+
| Only Column |
+-------------+
| BUG         |
| No          |
+-------------+
2 rows in set (0.00 sec)

+-------------+
| Only Column |
+-------------+
|             |
| No          |
+-------------+
2 rows in set (0.00 sec)

+-------------+
| Only Column |
+-------------+
| 123         |
| No          |
+-------------+
2 rows in set (0.00 sec)

mysql>

Verified against 4.1.5-beta: 
 
4.1.5-gamma-debug-log 
[00:14] root@test> create table t1 (a enum('1','2')); 
Query OK, 0 rows affected (0.11 sec) 
 
4.1.5-gamma-debug-log 
[00:14] root@test> create table t2 (a enum('2','3')); 
Query OK, 0 rows affected (0.09 sec) 
 
4.1.5-gamma-debug-log 
[00:14] root@test> insert into t1 values ('1'); 
Query OK, 1 row affected (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:14] root@test> insert into t2 values ('3'); 
Query OK, 1 row affected (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:14] root@test> select a from t1 union all select a from t2; 
+------+ 
| a    | 
+------+ 
| 1    | 
|      | 
+------+ 
2 rows in set (0.00 sec)

It's more obvious what is going on when using UNION ALL

 select "1" as "Only Column" union ALL select shift from withEnum;
+-------------+
| Only Column |
+-------------+
| BUG         |
| No          |
+-------------+

select "12" as "Only Column" union ALL select shift from withEnum;
+-------------+
| Only Column |
+-------------+
|             |
| No          |
+-------------+

Not sure if it's related (if not file another report), but UNION with SET also fails: 
 
[00:30] root@test> create table t1 (a set('1','2')); 
Query OK, 0 rows affected (0.07 sec) 
 
4.1.5-gamma-debug-log 
[00:30] root@test> create table t2 (a set('2','3')); 
Query OK, 0 rows affected (0.09 sec) 
 
4.1.5-gamma-debug-log 
[00:30] root@test> insert into t1 values ('1'); 
Query OK, 1 row affected (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:30] root@test> insert into t2 values ('3'); 
Query OK, 1 row affected (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:30] root@test> select a from t1; 
+------+ 
| a    | 
+------+ 
| 1    | 
+------+ 
1 row in set (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:30] root@test> select a from t2; 
+------+ 
| a    | 
+------+ 
| 3    | 
+------+ 
1 row in set (0.00 sec) 
 
4.1.5-gamma-debug-log 
[00:31] root@test> select a from t1 union select a from t2; 
+------+ 
| a    | 
+------+ 
| 1    | 
| 1,2  | 
+------+ 
2 rows in set (0.00 sec)

ChangeSet 
  1.2018 04/09/17 13:23:57 bell@sanja.is.com.ua +3 -0 
  Do not try use fields examples is expression and fiend used or SET/ENUM field used in 
types merging procedure (BUG#5618)

Is this related? The test does not use enumerations, but a similar symptom of unexpected results.

DROP TABLE IF EXISTS unionTab;
create table unionTab (
    col1 VARCHAR(32) NOT NULL
);

insert into unionTab values ('abcdefghijklmnopqrstuvwxyz');

select "1"  as "Col1"
union
select col1 from unionTab;

select col1 from unionTab
union
select "1";

mysql> source u2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+------+
| Col1 |
+------+
| 1    |
| a    |
+------+
2 rows in set (0.00 sec)

+----------------------------+
| col1                       |
+----------------------------+
| abcdefghijklmnopqrstuvwxyz |
| 1                          |
+----------------------------+
2 rows in set (0.00 sec)

mysql>

ChangeSet 
  1.2020 04/09/25 15:07:50 bell@sanja.is.com.ua +3 -0 
  postreview fixes (BUG#5618 & BUG#5590)

Reviewed by Dmitri and Konstantin during the Malta conference.

Thank You for bugreport! bug is fixed and patch is pushed into source repository.