Description:
version: Server version: 8.0.23 MySQL Community Server - GPL
drop table t1_no_idx;
create table t1_no_idx(id int, col_bit bit(16));
insert into t1_no_idx values(1, 0x3135);
insert into t1_no_idx values(2, 0x0f);
prepare stmt from 'select * from t1_no_idx where col_bit = ?'; set @a = 0x3135;
execute stmt using @a;
prepare stmt1 from 'select * from t2 where col_bit in (?)';
execute stmt1 using @a;
mysql> execute stmt1 using @a;
+------+------------------+
| id | col_bit |
+------+------------------+
| 2 | 0x000F |
+------+------------------+
1 row in set (0.00 sec)
drop table t2_idx;
create table t2_idx(id int, col_bit bit(16), key(col_bit));
insert into t2_idx values(1, 0x3135);
insert into t2_idx values(2, 0x0f);
prepare stmt from 'select * from t2_idx where col_bit = ?';
set @a = 0x3135;
execute stmt using @a;
+------+------------------+
| id | col_bit |
+------+------------------+
| 1 | 0x3135 |
+------+------------------+
1 row in set (0.00 sec)
t1_no_idx is same with t2_idx expect t2_idx got idx on col_bit. But same execute got different result.
How to repeat:
drop table t1_no_idx;
create table t1_no_idx(id int, col_bit bit(16));
insert into t1_no_idx values(1, 0x3135);
insert into t1_no_idx values(2, 0x0f);
prepare stmt from 'select * from t1_no_idx where col_bit = ?'; set @a = 0x3135;
execute stmt using @a;
prepare stmt1 from 'select * from t2 where col_bit in (?)';
execute stmt1 using @a;
drop table t2_idx;
create table t2_idx(id int, col_bit bit(16), key(col_bit));
insert into t2_idx values(1, 0x3135);
insert into t2_idx values(2, 0x0f);
prepare stmt from 'select * from t2_idx where col_bit = ?';
set @a = 0x3135;
execute stmt using @a;
