| Bug #110955 | Query the union all derived table error: the character set cannot be converted | ||
|---|---|---|---|
| Submitted: | 9 May 2023 5:17 | Modified: | 9 May 2023 6:38 |
| Reporter: | Shujun Chen | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.32 | OS: | CentOS (7.5) |
| Assigned to: | CPU Architecture: | Any | |
[9 May 2023 6:38]
MySQL Verification Team
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
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.