The IN to equality rewrite changes the query result: c IN ('a', 'A' COLLATE utf8mb4_general_ci) misses 'A' on a utf8mb4_bin column

Likely cause: commit 5027cb2b74a0b37cbdd3ad190cb8b2bf738c0cde (MDEV-29662), in Item_func_in::in_predicate_to_equality_transformer(). CS 10.6.9, from before it, does not have the bug.

SET NAMES utf8mb4;

CREATE TABLE t1 (c VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

INSERT INTO t1 VALUES ('a'),('A'),('b');

SELECT COUNT(*) INTO @c FROM t1 WHERE c IN ('a', 'A' COLLATE utf8mb4_general_ci);

--let $c= query_get_value(SELECT @c AS c, c, 1)

if ($c != 2)

{

  --die Only 1 of the 2 rows 'a' and 'A' is found by c IN ('a', 'A' COLLATE utf8mb4_general_ci), which compares with utf8mb4_general_ci

}

DROP TABLE t1;

Leads to:

mysqltest: At line 8: Only 1 of the 2 rows 'a' and 'A' is found by c IN ('a', 'A' COLLATE utf8mb4_general_ci), which compares with utf8mb4_general_ci

The IN to equality rewrite changes the query result. With a third value that is not equal, c IN ('a', 'A' COLLATE utf8mb4_general_ci, 'zzz'), the IN is not rewritten and the query finds 2 rows. c = 'a' COLLATE utf8mb4_general_ci OR c = 'A' COLLATE utf8mb4_general_ci also finds 2 rows. In the SELECT list, the IN is 1 for 'a' and for 'A'. NOT IN returns 'A' and 'b', where only 'b' is expected. With a utf8mb4_general_ci column, c IN ('a', 'a' COLLATE utf8mb4_bin) finds 'a' and 'A', where only 'a' is expected. A key on c and a prepared statement give the same. Optimized builds give the same results.

MySQL is not affected: MySQL 5.5.62 to 9.1.0 find the 2 rows, and give the right result for the other queries too.

From the code it seems that Item_func_in::in_predicate_to_equality_transformer() in sql/item_cmpfunc.cc finds that all values of the list are equal in the collation of the IN, here utf8mb4_general_ci. It then replaces the IN with Item_func_eq(args[0], args[1]), which is c = 'a'. That equality has no COLLATE, so it compares with the column collation, utf8mb4_bin. EXPLAIN EXTENDED shows the condition as t1.c = 'a'.