Bug #99524 different subquery results with primary key or not

• Submitted: 12 May 2020 9:09
• Modified: 12 May 2020 10:20
• Reporter: Feng Liyuan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6/5.7/8.0
• OS: Any
• CPU Architecture: Any

[12 May 2020 9:09] Feng Liyuan

Description:
drop table if exists tb, ta1, ta2 ;
CREATE TABLE tb (value varchar(50));
insert into tb values ("1801425248110076222");

CREATE TABLE ta1 (`id` bigint );
CREATE TABLE ta2 (`id` bigint, PRIMARY KEY (`id`));
insert into ta1 values (1801425248110076165);
insert into ta2 values (1801425248110076165);

select value from tb where value in (select id from ta1); -- Expected, one row result
select value from tb where value in (select id from ta2); -- Unexpected, empty set

How to repeat:
~

[12 May 2020 10:18] MySQL Verification Team

Thank you for the bug report.

[12 May 2020 10:20] Feng Liyuan

For reference, run IN function with constraint arguments

mysql> select value from tb where value in (1801425248110076165);
+---------------------+
| value               |
+---------------------+
| 1801425248110076222 |
+---------------------+
1 row in set (0.00 sec)