Bug #110521 8.0.32 subquery with utf8mb4 equal case empty result
Submitted: 28 Mar 2023 2:03 Modified: 28 Mar 2023 7:01
Reporter: jifa Xu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.32 OS:Linux
Assigned to: CPU Architecture:Any

[28 Mar 2023 2:03] jifa Xu 
Description:
in 8.0.32 subquery with utf8mb4 eqal sql print empty result and one warning;

How to repeat:
select * from (select "の" as v) tbl where tbl.v = "の";
show warnings:
Warning 1300 Cannot convert string '\xE3\x81\xAE' from utf8mb4 to binary;

But if change the sql to 
select * from (select "测试" as v limit 1) tbl where tbl.v = "测试";
It worked as expected;
[28 Mar 2023 5:42] jifa Xu 
Another example:

create table tbl(id int primary key, v varchar(30)) default charset utf8mb4;

insert into tbl values (1, 'の');

select * from (select * from tbl union select * from tbl) t where t.v = 'の';
[28 Mar 2023 6:53] jifa Xu 
disable derived_condition_pushdown solved this problem
[28 Mar 2023 7:01] MySQL Verification Team 
Hello jeff Jiang,

Thank you for the report and test case.
IMHO this is duplicate of Bug #109699, please see Bug #109699.

regards,
Umesh