ENUM column IN (SELECT int_col) gives 0 where a = 1 gives 1; the IN compares the string and not the number of the value

CREATE TABLE t1 (a ENUM('a','b','c'));

INSERT INTO t1 VALUES ('a');

CREATE TABLE t2 (i INT);

INSERT INTO t2 VALUES (1);

--let $c= query_get_value(SELECT a IN (SELECT i FROM t2) AS c FROM t1, c, 1)

if ($c != 1)

{

  --die a IN (SELECT i FROM t2) gives $c and not 1, as a = 1 does

}

DROP TABLE t1, t2;

Leads to:

mysqltest: At line 8: a IN (SELECT i FROM t2) gives 0 and not 1, as a = 1 does

In a WHERE clause the IN becomes a semi-join and gives the right result. With semijoin=off, COUNT for WHERE a IN (SELECT i FROM t2) is 0. a = ANY, (a, 1) IN, NOT IN, NOT EXISTS (through exists_to_in), a key on the subquery column, a SET column, a DECIMAL or DOUBLE subquery column and a prepared statement give the same wrong result. Most of these queries give a warning such as Truncated incorrect DECIMAL value: 'a'. For ENUM('2','1') with the value '2', b IN (SELECT i FROM t6) with i = 2 gives 1, where b = 2 gives 0. Optimized builds give the same result.

From the code it seems that Item_in_optimizer::fix_left() in sql/item_cmpfunc.cc caches the left value with get_cache(). For an ENUM or SET column the type of the item is VARCHAR, so the cache is an Item_cache_str. Its val_int(), val_real() and val_decimal() convert the string, and 'a' gives 0. The column itself gives its number, 1. A semi-join compares the column itself. MIN() and MAX() of an ENUM or SET column keep the value in the same kind of string cache: MIN(a) + 0 gives 0, and the number when a GROUP BY uses a temporary table (MDEV-39580).