An index on a utf8mb4_bin column changes the query result: c BETWEEN 'A' AND 'a' finds only 'A'

SET NAMES utf8mb4;

CREATE TABLE t1 (c VARCHAR(10) COLLATE utf8mb4_bin, KEY (c));

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

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE c BETWEEN 'A' AND 'a', c, 1)

if ($c != 3)

{

  --die COUNT(*) for c BETWEEN 'A' AND 'a' is $c and not 3

}

DROP TABLE t1;

Leads to:

mysqltest: At line 7: COUNT(*) for c BETWEEN 'A' AND 'a' is 1 and not 3

The index changes the query result. Without it (IGNORE INDEX (c)), the query finds 3 rows: 'a', 'B' and 'A' are between 'A' and 'a' in utf8mb4_bin. EXPLAIN shows a ref access on c with const. A prepared statement c BETWEEN ? AND 'a', an ENUM column with utf8mb4_bin, and a latin1_bin column with SET NAMES latin1 give the same. c NOT BETWEEN 'A' AND 'a', c BETWEEN 'a' AND 'a' and c BETWEEN 'A' AND 'a' COLLATE utf8mb4_bin give the right result. Optimized builds give the same result.

+--------+----------+--------------+--------+

| MySQL  | COUNT(*) | IGNORE INDEX | Engine |

+--------+----------+--------------+--------+

| 5.5.62 | 1        | 3            | MyISAM |

| 5.6.51 | 1        | 3            | MyISAM |

| 5.7.44 | 1        | 3            | InnoDB |

| 8.0.36 | 1        | 3            | InnoDB |

| 9.1.0  | 3        | 3            | InnoDB |

+--------+----------+--------------+--------+

From the code it seems that Item_func_between::add_key_fields() in sql/sql_select.cc handles c BETWEEN low AND high as c = low when low and high are equal. It compares them with Item::eq(), which for two string constants uses the collation of the constants (Type_handler_string_result::Item_const_eq() in sql/sql_type.cc), here utf8mb4_uca1400_ai_ci from SET NAMES utf8mb4, where 'A' and 'a' are equal. The BETWEEN itself compares in utf8mb4_bin, the collation of the column.