Description:
mysql> create table test_enum(c1 int, c2 enum ('1','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> create table t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 value(1,1),(2,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1 where (select c2 from test_enum) in (2);
Empty set (0.00 sec)
mysql> select * from t1 where 2 in (select c2 from test_enum);
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
That is to say, integer in (enum) and enum in (integer) produce different and inconsistent results.
IMHO, both statements should have returned empty set.
How to repeat:
create table test_enum(c1 int, c2 enum ('1','a','b'));
insert into test_enum value(2,'a');
create table t1(c1 int, c2 int);
insert into t1 value(1,1),(2,2);
select * from t1 where (select c2 from test_enum) in (2);
select * from t1 where 2 in (select c2 from test_enum);
Suggested fix:
select * from t1 where (select c2 from test_enum) in (2);
select * from t1 where 2 in (select c2 from test_enum);
both return 2 records
or
both return empty set.
IMHO, both return empty set will be more preferable and reasonable.
Description: mysql> create table test_enum(c1 int, c2 enum ('1','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> create table t1(c1 int, c2 int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 value(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1 where (select c2 from test_enum) in (2); Empty set (0.00 sec) mysql> select * from t1 where 2 in (select c2 from test_enum); +------+------+ | c1 | c2 | +------+------+ | 1 | 1 | | 2 | 2 | +------+------+ 2 rows in set (0.00 sec) That is to say, integer in (enum) and enum in (integer) produce different and inconsistent results. IMHO, both statements should have returned empty set. How to repeat: create table test_enum(c1 int, c2 enum ('1','a','b')); insert into test_enum value(2,'a'); create table t1(c1 int, c2 int); insert into t1 value(1,1),(2,2); select * from t1 where (select c2 from test_enum) in (2); select * from t1 where 2 in (select c2 from test_enum); Suggested fix: select * from t1 where (select c2 from test_enum) in (2); select * from t1 where 2 in (select c2 from test_enum); both return 2 records or both return empty set. IMHO, both return empty set will be more preferable and reasonable.