Bug #103161 The return value is different after adding a normal index

• Submitted: 31 Mar 2021 6:57
• Modified: 31 Mar 2021 7:40
• Reporter: Guo Wenxuan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.23
• OS: Any
• CPU Architecture: Any

[31 Mar 2021 6:57] Guo Wenxuan

Description:
After adding a normal index, between returns an error result

correct：
mysql> select * from t1 where col1 between "U" and "u";
+------+
| COL1 |
+------+
| ]    |
+------+
1 row in set (0.27 sec)
 
incorrect：
mysql> select * from t1 where col1 between "U" and "u";
Empty set
 

How to repeat:
correct：
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `COL1` char(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t1` VALUES (']');

select * from t1 where col1 between "U" and "u";

incorrect：
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `COL1` char(1),
  KEY `UK_COL1` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t1` VALUES (']');

select * from t1 where col1 between "U" and "u";

[31 Mar 2021 7:40] MySQL Verification Team

Hello xuan wen,

Thank you for the report and test case.
Verified as described with 8.0.23 build.

regards,
Umesh

[8 Apr 2021 8:12] Erlend Dahl

It looks like the where clause in the second statement gets optimized away because collation_connection (default utf8mb4_0900_ai_ci) is case insensitive (hence "U" = "u").

Forcing the collation makes the 2nd select work:

select * from t1 where col1 between "U" collate utf8mb4_bin and "u";