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)
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)