Bug #104246 query result error with unique index

• Submitted: 8 Jul 2021 3:44
• Modified: 8 Jul 2021 4:39
• Reporter: peng chen
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.25
• OS: Any
• 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