Description:
mysql> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table test_enum(c1 int, c2 enum('2', 'a', 'b'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_enum value(2, 'a');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 select * from test_enum;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.01 sec)
Yeah ,everything seems good. However :
mysql> select * from t1 where (select c2 from test_enum ) > 1;
Empty set (0.00 sec)
why empty set ? we can deduce that mysql treats the enum column as string when compare it with integer.
mysql> select c2 from test_enum where c2 > 1;
+------+
| c2 |
+------+
| a |
+------+
1 row in set (0.01 sec)
why one record ? we can deduce that mysql treats the enum column as index of type integer when compare it with integer.
It contradicts.
How to repeat:
create table t1(c1 int, c2 int);
create table test_enum(c1 int, c2 enum('2', 'a', 'b'));
insert into test_enum value(2, 'a');
Query OK, 1 row affected (0.01 sec)
insert into t1 select * from test_enum;
select * from t1 where (select c2 from test_enum ) > 1;
select c2 from test_enum where c2 > 1;
Suggested fix:
select * from t1 where (select c2 from test_enum ) > 1;
select c2 from test_enum where c2 > 1;
both return empty set
or
both return one record.
Description: mysql> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> create table test_enum(c1 int, c2 enum('2', 'a', 'b')); Query OK, 0 rows affected (0.01 sec) mysql> insert into test_enum value(2, 'a'); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 select * from test_enum; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+------+ | c1 | c2 | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.01 sec) Yeah ,everything seems good. However : mysql> select * from t1 where (select c2 from test_enum ) > 1; Empty set (0.00 sec) why empty set ? we can deduce that mysql treats the enum column as string when compare it with integer. mysql> select c2 from test_enum where c2 > 1; +------+ | c2 | +------+ | a | +------+ 1 row in set (0.01 sec) why one record ? we can deduce that mysql treats the enum column as index of type integer when compare it with integer. It contradicts. How to repeat: create table t1(c1 int, c2 int); create table test_enum(c1 int, c2 enum('2', 'a', 'b')); insert into test_enum value(2, 'a'); Query OK, 1 row affected (0.01 sec) insert into t1 select * from test_enum; select * from t1 where (select c2 from test_enum ) > 1; select c2 from test_enum where c2 > 1; Suggested fix: select * from t1 where (select c2 from test_enum ) > 1; select c2 from test_enum where c2 > 1; both return empty set or both return one record.