An index changes the query result: UPPER(c) = 'X' COLLATE utf8mb4_general_ci on a utf8mb4_uca1400_ai_ci column

Likely cause: commit e987b9350cb83038c73a165922986441f345a3a9 (MDEV-31496), in is_upper_key_col(). CS 10.11.19, from before it, does not have the bug.

CREATE TABLE t1 (c VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci, KEY (c));

INSERT INTO t1 VALUES (_utf8mb4 0xC680),('a'),('b');

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE UPPER(c) = _utf8mb4 0xC983 COLLATE utf8mb4_general_ci, c, 1)

if ($c != 1)

{

  --die The row U+0180 is not found by UPPER(c) = U+0243 COLLATE utf8mb4_general_ci, though UPPER(c) gives U+0243

}

DROP TABLE t1;

Leads to:

mysqltest: At line 6: The row U+0180 is not found by UPPER(c) = U+0243 COLLATE utf8mb4_general_ci, though UPPER(c) gives U+0243

The index changes the query result. Without KEY (c) on the same data, or with optimizer_switch='sargable_casefold=off', the query finds 1 row. In the SELECT list, UPPER(c) is U+0243 for the row U+0180, and the comparison is 1. With IGNORE INDEX (c), the query still finds 0 rows. UPPER(c) = U+0180 COLLATE utf8mb4_general_ci finds the row U+0180, though no row has UPPER(c) U+0180. UCASE(), UPPER(c) IN (...) and a prepared statement give the same. A utf8mb4_unicode_520_ci column gives the same, and so does a utf8mb3_uca1400_ai_ci column against utf8mb3_general_ci. Optimized builds give the same results.

ES 10.6 has the rewrite from MENT-1892, off by default. It has no utf8mb4_uca1400_ai_ci. With optimizer_switch='sargable_casefold=on' and a utf8mb4_unicode_520_ci column, it gives the same.

MySQL is not affected: it has no utf8mb4_uca1400_ai_ci, and with utf8mb4_unicode_520_ci (5.6.51 to 9.1.0) or utf8mb4_0900_ai_ci (8.0.36, 9.1.0), MySQL finds the row.

From the code it seems that Item_func_eq::varchar_upper_cmp_transformer() and Item_func_in::varchar_upper_cmp_transformer() in sql/opt_rewrite_remove_casefold.cc remove UPPER() when the comparison collation, here utf8mb4_general_ci, has MY_CS_UPPER_EQUAL_AS_EQUAL. is_upper_key_col() does not check the collation of UPPER(c). UPPER(c) uses the case rules of the column collation, which map U+0180 to U+0243. utf8mb4_general_ci does not treat U+0180 and U+0243 as equal. EXPLAIN EXTENDED shows the condition as t1.c = ... collate utf8mb4_general_ci, and Note 1105 says that the key on c cannot be used for it.