Bug #106486 The union query returns an incorrect result
Submitted: 17 Feb 9:27 Modified: 17 Feb 9:44
Reporter: wugong wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.26, 8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[17 Feb 9:27] wugong wang
Description:
root_test2_3306> show create table tt1\G
*************************** 1. row ***************************
       Table: tt1
Create Table: CREATE TABLE `tt1` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

root_test2_3306> show create table tt2\G
*************************** 1. row ***************************
       Table: tt2
Create Table: CREATE TABLE `tt2` (
  `c1` int DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

root_test2_3306> select * from tt1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    1 |
|    5 |    1 |
+------+------+
5 rows in set (0.00 sec)

root_test2_3306> select * from tt2;
Empty set (0.01 sec)

root_test2_3306>

------------------------------
-- The wrong result:

root_test2_3306> select 1 in (select  a.c1 from (select c1,c2 from tt1 union all select c1,c2 from tt2) a where a.c2 > 1) r;
+------+
| r    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
------------------------------

-- The right result:
root_test2_3306> select 1 in (select a.c1 from (select c1,c2 from tt1 where c2>1 union all select c1,c2 from tt2 where c2>1) a ) r;
+------+
| r    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
------------------------------

How to repeat:
Repeat my steps above。
[17 Feb 9:44] MySQL Verification Team
Hello wugong wang,

Thank you for the report and test case.
Observed that 8.0.28 build is affected.

regards,
Umesh