Bug #118913 queries have different results for non primary key table and table with primary key

• Submitted: 29 Aug 3:40
• Reporter: leo song
• Status: Open
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: MySQL8.0.41
• OS: Any
• CPU Architecture: Any

[29 Aug 3:40] leo song

Description:
queries have different results for non primary key table and table with primary key，the following is the testcase
create table t1( s1 int, s2 int) engine =innodb;
insert into t1 values(20,15);

create table t2( s1 int primary key, s2 int) engine =innodb;
insert into t2 values(20,15);
select * from t1 where (('a',null) <=> (select 'a', s2 from t1 where s1=1));
Empty set (0.00 sec)

select * from t2 where (('a',null) <=> (select 'a', s2 from t2 where s1=1));
+----+------+
| s1 | s2   |
+----+------+
| 20 |   15 |
+----+------+
1 row in set (0.00 sec)

How to repeat:
create table t1( s1 int, s2 int) engine =innodb;
insert into t1 values(20,15);

create table t2( s1 int primary key, s2 int) engine =innodb;
insert into t2 values(20,15);
select * from t1 where (('a',null) <=> (select 'a', s2 from t1 where s1=1));
Empty set (0.00 sec)

select * from t2 where (('a',null) <=> (select 'a', s2 from t2 where s1=1));
+----+------+
| s1 | s2   |
+----+------+
| 20 |   15 |
+----+------+
1 row in set (0.00 sec)