Bug #110104 Query exception caused by union in 8.0.32

• Submitted: 17 Feb 2023 2:51
• Modified: 20 Feb 2023 6:36
• Reporter: tuantuan Xue
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: CentOS
• CPU Architecture: Any

[17 Feb 2023 2:51] tuantuan Xue

Description:
In 8.0.32, the matching query of Chinese characters is included, and the result set is incorrect

How to repeat:
CREATE TABLE test_1(id INT,NAME VARCHAR(100));
CREATE TABLE test_2(id INT,NAME VARCHAR(100));

INSERT INTO test_1 VALUES(1,'aaa');
INSERT INTO test_1 VALUES(2,'bbb');
INSERT INTO test_1 VALUES(3,'ccc');
INSERT INTO test_1 VALUES(4,'ddd');
INSERT INTO test_1 VALUES(5,'测试');

INSERT INTO test_2 VALUES(11,'eee');

##Query at this time

SELECT * FROM (
SELECT * FROM test_1 WHERE NAME = '测试'
UNION 
SELECT * FROM test_2 WHERE NAME = 'bbb'
) tmp

##Results obtained
    id  name    
------  --------
     5  测试  

## after adding a where condition

SELECT * FROM (
SELECT * FROM test_1 WHERE NAME = '测试'
UNION 
SELECT * FROM test_2 WHERE NAME = 'bbb'
) tmp
WHERE NAME = '测试'

##Results obtained
    id  name    
------  --------

Suggested fix:
The new version of BUG has not been repaired

[17 Feb 2023 3:56] MySQL Verification Team

Hello tuantuan Xue,

Thank you for the report and feedback.
Confirmed internally that this is duplicate of Bug #109699, please see Bug #109699 which is fixed in the MySQL 8.0.33. Since 8.0.33 is not yet released, we suggest you to please use the workaround for now.

Quoting Roy's note from another bug - Possible workaround: set optimizer_switch='derived_condition_pushdown=off';

regards,
Umesh

[20 Feb 2023 6:36] tuantuan Xue

ok，Thank you very much！