Bug #104246 query result error with unique index
Submitted: 8 Jul 2021 3:44 Modified: 8 Jul 2021 4:39
Reporter: peng chen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Jul 2021 3:44] peng chen
Description:
mysql> select * from PK_MULTI_COL_8664 where col3 >= -52 and col1 is not null and col2 between "V" and "v";
Empty set (0.00 sec)

mysql> alter table PK_MULTI_COL_8664 drop index uidx;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from PK_MULTI_COL_8664 where col3 >= -52 and col1 is not null and col2 between "V" and "v";
+------------+------+------+
| COL1       | COL2 | COL3 |
+------------+------+------+
| 0xA6       | ]    |   33 |
+------------+------+------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists PK_MULTI_COL_8664;
CREATE TABLE `PK_MULTI_COL_8664` (
  `COL1` blob NOT NULL,
  `COL2` char(1) COLLATE utf8mb4_bin NOT NULL,
  `COL3` tinyint NOT NULL,
  UNIQUE KEY `UIDX` (`COL2`));
insert into PK_MULTI_COL_8664 values(0xA6, "]", 33);
select * from PK_MULTI_COL_8664 where col3 >= -52 and col1 is not null and col2 between "V" and "v";
alter table PK_MULTI_COL_8664 drop index uidx;
select * from PK_MULTI_COL_8664 where col3 >= -52 and col1 is not null and col2 between "V" and "v";
[8 Jul 2021 4:39] MySQL Verification Team
Hello peng chen,

Thank you for the report and test case.
Observed that 8.0.25 build is affected.

regards,
Umesh