Bug #110376 upgrade mysql from 8.0.29 to 8.0.32, union error

• Submitted: 15 Mar 2023 10:24
• Modified: 16 Mar 2023 14:29
• Reporter: caiqinghua cai
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.32 linix-generic
• OS: CentOS
• CPU Architecture: Any
• Tags: union error

[15 Mar 2023 10:24] caiqinghua cai

Description:
upgrade mysql from 8.0.29 to 8.0.32 with package mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz,
in SQL with union, if there are Chinese characters in the query criteria, the returned query result is incorrect

How to repeat:
select * from 
(select '中国' as corporation 
union all 
select '法国' as corporation ) t  
where t.corporation  like '%中国%'

the result includes both '中国' and '法国' ,but the correct is '中国'

[15 Mar 2023 10:31] caiqinghua cai

upgrade mysql from 8.0.29 to 8.0.32,union error

Attachment: error.png (image/png, text), 32.03 KiB.

[15 Mar 2023 10:31] caiqinghua cai

upgrade mysql from 8.0.29 to 8.0.32,union error

Attachment: error.png (image/png, text), 32.03 KiB.

[15 Mar 2023 11:29] MySQL Verification Team

Hello caiqinghua cai,

Thank you for the report and feedback.
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

[16 Mar 2023 9:45] caiqinghua cai

Because this problem has a great impact on us, is it convenient to give a software patch or 8.0.33 temporary version before the official version is released? 
thank a lot.

[16 Mar 2023 9:47] caiqinghua cai

Because this problem has some impact on us, is it convenient to give a software patch or 8.0.33 temporary version before the official version is released? 
thank a lot.

[16 Mar 2023 9:49] caiqinghua cai

Because this problem has some impact on us, is it convenient to give a software patch or 8.0.33 temporary version before the official version is released? 
thank a lot.

[16 Mar 2023 9:52] caiqinghua cai

Because this problem has some impact on us, is it convenient to give a software patch or 8.0.33 temporary version before the official version is released? 
thank a lot.

[16 Mar 2023 10:42] caiqinghua cai

Because this bug has some impact on us, is it convenient to provide a fixed patch or a temporary version of 8.0.33 before the official release of version 8.0.33？
Thank a lot.

[16 Mar 2023 12:46] caiqinghua cai

Because this bug has some impact on us, is it convenient to provide a fixed patch or a temporary version of 8.0.33 before the official release of version 8.0.33？
tanks a lot.

[16 Mar 2023 14:10] MySQL Verification Team

MySQL 8.0.33 is not yet released, till then you can use the proposed workaround by develop in Bug #109699

== Workaround

 [15 Mar 11:41] Chaithra Gopala Reddy

Posted by developer:
 
As a workaround, set derived condition pushdown to off like this:

set optimizer_switch="derived_condition_pushdown=off";

[16 Mar 2023 14:29] caiqinghua cai

Because this bug has some impact on us, is it convenient to provide a fixed patch or a temporary version of 8.0.33 before the official release of version 8.0.33？
thanks a lot.