MySQL Bugs: #110005: When using union, when the value of the field in the where condition is Chinese

Bug #110005	When using union, when the value of the field in the where condition is Chinese
Submitted:	9 Feb 2023 11:35	Modified:	10 Feb 2023 9:30
Reporter:	jeff Jiang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.32	OS:	Ubuntu (Ubuntu 20.04.5 LTS)
Assigned to:		CPU Architecture:	x86
Tags:	UNION ALL

Description:

When using the union all statement, when the value of the field in the where condition is Chinese characters, the original data that should be queried cannot be queried,and show to warnings like this:Cannot convert string '\xE5\x44\xA7...' from utf8mb4 to binary.

How to repeat:
1、Create a table `test` with two fields, such as id and name
mysql>create table test ( id int unsigned auto_increment key, name varchar(50))ENGINE=INNODB;
2、Insert data into the table. The name field is Chinese characters, such as "姜志福"
mysql>insert into  test(id,name) values(1,'姜志福');
3、Use the union statement to query, such as：
select * from (
select * from test 
union all 
select * from test 
)a  where id = 1 and name = '姜志福'

Suggested fix:
At present, I have reduced the version from 8.0.32 to 8.0.30 to solve this problem

Hello jeff Jiang,

Thank you for the report and test case.

regards,
Umesh

Likely a duplicate of 109699.
Possible workaround: set optimizer_switch='derived_condition_pushdown=off';

Hello jeff Jiang,

As Roy confirmed this is duplicate of Bug #109699, please see Bug #109699.
Since 8.0.33 is not yet released, as he suggested please use the workaround for now.

regards,
Umesh