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.
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.