Bug #104966 different result of in expression when table has index

• Submitted: 17 Sep 2021 9:25
• Modified: 17 Sep 2021 12:22
• Reporter: jiangtao guo
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.23, 5.7.35, 8.0.26
• OS: Any
• CPU Architecture: Any

[17 Sep 2021 9:25] jiangtao guo

Description:
drop table if exists t1;
create table t1(c1 bit(4));
insert into t1 values(0x0f);
set @a = 0x3135;
select * from t1 where c1 in (@a);

alter table t1 add index i1(c1);
select * from t1 where c1 in (@a);

The results are different with and without index:

mysql> select * from t1 where c1 in (@a);
+------------+
| c1         |
+------------+
| 0x0F       |
+------------+
1 row in set (0.01 sec)

mysql>
mysql> alter table t1 add index i1(c1);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where c1 in (@a);
Empty set (0.00 sec)

How to repeat:
drop table if exists t1;
create table t1(c1 bit(4));
insert into t1 values(0x0f);
set @a = 0x3135;
select * from t1 where c1 in (@a);

alter table t1 add index i1(c1);
select * from t1 where c1 in (@a);

[17 Sep 2021 12:22] MySQL Verification Team

Hello jiangtao,

Thank you for the report and test case.

regards,
Umesh