Bug #110955 Query the union all derived table error: the character set cannot be converted

Description:
Union all derived table query error: Warning 1300 Cannot convert string '\xE4\xB8\x8A\xE6\xB5\xB7...' from utf8mb4 to binary

How to repeat:
1. my.cnf:
character_set_server=utf8mb4

2. prepare table
set names utf8mb4
CREATE TABLE `t1` (
  `sttstc_dt` varchar(500)  DEFAULT NULL COMMENT '统计日期',
  `cmpny_nm` varchar(500)  DEFAULT NULL COMMENT '分公司名称',
  `cmbn_org_nm` varchar(500) DEFAULT NULL COMMENT '合并机构名称'
  )ENGINE=InnoDB;
  
insert into test_t2 values('2022-10-31','上海分公司','闵行');

3. execute sql
mysql> select * from (select distinct cmpny_nm from t1 where sttstc_dt is not null and cmpny_nm='上海分公司' union all select distinct cmbn_org_nm from t1 where sttstc_dt is not null and cmpny_nm='上海分公司') t where cmpny_nm='上海分公司' ;
Empty set, 2 warnings (0.00 sec)
 
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1300 | Cannot convert string '\xE4\xB8\x8A\xE6\xB5\xB7...' from utf8mb4 to binary |
| Warning | 1300 | Cannot convert string '\xE4\xB8\x8A\xE6\xB5\xB7...' from utf8mb4 to binary |
+---------+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4. others
This problem does not exist in 8.0.31.
If you remove the union all in the query, there is no such problem.

Hello Shujun Chen,

Thank you for the report and feedback.
Confirmed that this is duplicate of Bug #109699, which is fixed in  MySQL Server 8.0.33. Please see Bug #109699 for more details.

8.0.32 - as a workaround, please set derived condition pushdown to off like this:
set optimizer_switch="derived_condition_pushdown=off";

regards,
Umesh