Bug #92173 Enum type casting works wrong with subqueries
Submitted: 24 Aug 2018 7:45 Modified: 17 Dec 2018 19:32
Reporter: Alexey Eremikhin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7/8.0 OS:Linux
Assigned to: CPU Architecture:Any

[24 Aug 2018 7:45] Alexey Eremikhin
Description:
In MySQL 5.7 there was introduced issue with enums type casting. There are 2 ways to get the enum integer value:

1) use 0 + enum_column
2) use CAST(enum_column as unsigned)

Both ways are working fine with plain SELECT query. The difference comes when you are using the nested query syntax like 

SELECT 0 + enum_column, CAST(enum_column AS UNSIGNED) 
FROM (
    SELECT enum_column FROM table
) t

Starting from MySQL 5.7 syntax with CAST(enum_column AS UNSIGNED) does not respect enum type and treats value as string. 

In MySQL 5.6 both approaches working fine.

How to repeat:
myhost/test mysql> create table az_t (c1 enum('a','b','c'));
Query OK, 0 rows affected (0.04 sec)

myhost/test mysql> insert into az_t values('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

myhost/test mysql> select * from az_t;
+------+
| c1   |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

myhost/test mysql> SELECT *, c1 + 0 as c4, cast(c1 as unsigned) as c5 from (select c1, 0+c1 as c2, cast(c1 as unsigned) as c3 from az_t) t;
+------+------+------+------+------+
| c1   | c2   | c3   | c4   | c5   |
+------+------+------+------+------+
| a    |    1 |    1 |    1 |    0 |
| b    |    2 |    2 |    2 |    0 |
| c    |    3 |    3 |    3 |    0 |
+------+------+------+------+------+
3 rows in set, 3 warnings (0.00 sec)

dbm1.d3/test mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'b' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'c' |
+---------+------+----------------------------------------+
3 rows in set (0.00 sec)
[24 Aug 2018 8:14] MySQL Verification Team
Thank you for the bug report, verified as describe. Version 8.0 affected too.
[17 Dec 2018 19:32] Paul Dubois
Posted by developer:
 
Fixed in 8.0.15.

Type casting of ENUM behaved differently in subqueries than not in
subqueries.