| Bug #110228 | pushdown condition Including Chinese convert string error | ||
|---|---|---|---|
| Submitted: | 28 Feb 2023 0:38 | Modified: | 6 Mar 2023 4:40 |
| Reporter: | peng gao | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Feb 2023 1:00]
peng gao
8032 release note： A condition pushdown into a UNION of queries having LIKE clauses did not preserve the correct character set, leading to an (erroneous) empty result. We solve this problem in two parts: By refactoring resolution of LIKE expressions, in which character set determination and propagation were previously performed in two separate blocks of the code that were not always consistent with one another. By adding, in the internal parse_expression() function, a character set prefix to any literal character string that is cloned. (Bug #107787, Bug #34359297, Bug #34589153)
[28 Feb 2023 8:32]
MySQL Verification Team
Hello peng gao, Thank you for the report and feedback. regards, Umesh
[6 Mar 2023 4:40]
MySQL Verification Team
Hello peng gao, Confirmed internally that this is duplicate of Bug #109699, which is fixed in MySQL Server 8.0.33. Please see Bug #109699 for more details. regards, Umesh
Description: Hi: Recently we encountered a problem , when set derived_condition_pushdown=on and this option is default , if pushdown condition including Chinese ,statement Unable to get results. When loop derived_query_expression's every query block copy outer_query_block's Item_func_like Condition_pushdown::make_cond_for_derived ->Query_block::clone_expression ->parse_expression In parse_expression function , StringBuffer<1024> str default charset is binary ,but Item_func_like args[1] charset is utf8mb4. How to repeat: mysql> CREATE TABLE `testc` ( -> `ID` varchar(64) NOT NULL , -> `NAME` varchar(200) DEFAULT NULL , -> PRIMARY KEY (`ID`) USING BTREE -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.08 sec) mysql> mysql> CREATE TABLE `testd` ( -> `ID` varchar(64) NOT NULL, -> `addr` varchar(200) DEFAULT NULL, -> PRIMARY KEY (`ID`) USING BTREE -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; Query OK, 0 rows affected (0.08 sec) mysql> mysql> insert into testc values('1','去去去'); Query OK, 1 row affected (0.01 sec) mysql> insert into testd values('1','去去去'); Query OK, 1 row affected (0.01 sec) mysql> select * from (select name,addr from testc,testd where testc.id=testd.id union all select name,addr from testc,testd where testc.id=testd.id) a where name like '%去%'; Empty set, 2 warnings (4.88 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1300 | Cannot convert string '%\xE5\x8E\xBB%' from utf8mb4 to binary | | Warning | 1300 | Cannot convert string '%\xE5\x8E\xBB%' from utf8mb4 to binary | +---------+------+---------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> set optimizer_switch='derived_condition_pushdown=off'; Query OK, 0 rows affected (0.00 sec) mysql> select * from (select name,addr from testc,testd where testc.id=testd.id union all select name,addr from testc,testd where testc.id=testd.id) a where name like '%去%'; +-----------+-----------+ | name | addr | +-----------+-----------+ | 去去去 | 去去去 | | 去去去 | 去去去 | +-----------+-----------+ 2 rows in set (0.00 sec)