Bug #107969 wrong result when search binary columns
Submitted: 26 Jul 2022 3:48 Modified: 26 Jul 2022 5:28
Reporter: lou shuai (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[26 Jul 2022 3:48] lou shuai
Description:
When query for binary columns with the same condition but different order , result set will be different.

How to repeat:
create table t3(a3 int, b3 binary(16), c3 int not null, d3 int not null,
                primary key(a3, b3)) engine = innodb;
insert into t3 values (0x1f, 'Ole', 1, 0x1f);
insert into t3 values (0x2f, 'Dole', 2, 0x2f);
insert into t3 values (0x3f, 'Doffen', 2, 0x3f);

select * from t3 where  ( `a3` = 47 )  AND  ( `b3` = 'Dole\0\0\0\0\0\0\0\0\0\0\0\0' )  AND  ( (`b3` = 'Dole') );

+----+------------------------------------+----+----+
| a3 | b3                                 | c3 | d3 |
+----+------------------------------------+----+----+
| 47 | 0x446F6C65000000000000000000000000 |  2 | 47 |
+----+------------------------------------+----+----+
1 row in set (0.00 sec)

select * from t3 where  ( `a3` = 47 )  AND ( (`b3` = 'Dole') ) AND  ( `b3` = 'Dole\0\0\0\0\0\0\0\0\0\0\0\0' )   ;
Empty set (0.00 sec)

Suggested fix:
when build_equal_items_for_cond, check_simple_equality should convert const item's charset to the field's charset before compare_const.
[26 Jul 2022 5:28] MySQL Verification Team
Hello lou shuai,

Thank you for the report and feedback.

regards,
Umesh