Bug #117549 Wrong result when use "in (select * from (set operation) d)"

• Submitted: 24 Feb 2:24
• Modified: 24 Feb 4:32
• Reporter: yucong Yuan
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any
• Tags: wrong result;set operation

[24 Feb 2:24] yucong Yuan

Description:
When we use set operation in a subquery, we might get the wrong answer if we put an additional "select *".

How to repeat:
create database yyc;
use yyc;
create table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),NULL;

--correct result
select * from t1 where a in (select 1 except select 2);
+------+
| a    |
+------+
|     1|
+------+

--wrong result
select * from t1 where a in (select * from (select 1 except select 2) d);
Empty set

Suggested fix:
In Set operation, we might build a temp table_list in create_tmp_table_for_set_op.
But subquery will also create a temptable to store result rows.
One of the two table_list has the "set_counter" column, the other doesn't.
Query_result of the set operation sets the wrong table_list when a subquey wrapped in "select *" exists, so we will read a table_list with "set_counter" column.
Of course, 
That't why we will get "Empty result".
I tried to remove the addtional table_list, but some new problems appear.

[24 Feb 4:32] MySQL Verification Team

Hello yucong Yuan,

Thank you for the report and test case.
Verified as described.

regards,
Umesh