MySQL Bugs: #115519: INTERSECT within correlated subquery results in incorrect result

Bug #115519	INTERSECT within correlated subquery results in incorrect result
Submitted:	5 Jul 2024 4:24	Modified:	16 Jul 2024 13:34
Reporter:	Owen Conti	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.2, 8.4.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
It appears that there may be an issue with versions 8.2+ when using an `INTERSECT` within a correlated sub query. When performing a `COUNT(*)` on the result of the `INTERSECT`, the value of the count is added to each following row in the result set.

For example:

- Row 1 has a count of 0
- Row 2 has a count of 1
- Row 3 has a count of 0
- Row 4 has a count of 1

The result set would output:

- 0
- 1
- 1
- 2

This functionality does not happen in versions less than 8.2 (worked correctly in 8.1) and I cannot find any documentation that says the new behaviour is intended (also just seems wrong).

Running the example file provided in the next section between versions 8.1 and 8.2 is the easiest way to see the issue.

How to repeat:
Example file you can run to see the result. Running on <= 8.1 gets the expected result at the bottom, running on >= 8.2 gets the incorrect result.

```
create table users (id int);
create table posts (id int, user_id int);
create table articles (id int, user_id int);

insert into users values (1), (2), (3), (4); -- Creating 4 users
insert into posts values (1, 2); -- Creating posts for user_id=2 ONLY
insert into articles values (1, 2); -- Creating articles for user_id=2 ONLY

select
	id as user_id,
	(
		select count(*) from (
			select user_id from posts where posts.user_id = users.id
			INTERSECT
			select user_id from articles where articles.user_id = users.id
		) as tmp
	) as "intersect"
from users;

/*
Expected result:

user_id intersect
1		0
2		1
3		0
4		0
*/
```

Hello Owen Conti,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Rejected as duplicate of Bug#36307622 (external Bug# 114017), which has been fixed.

Hi Dag Wanvik,

I can't seem to access the original bugs you linked. Have those been resolved and released? Should the issue I reported be fixed in the latest version of 8.2? Specifically this image: https://hub.docker.com/layers/library/mysql/8.2/images/sha256-5ba9d31938cfbfbcd6b29977181c...

Thanks,

Owen