MySQL Bugs: #103854: indexes on outer join with subquery causing incorrect response

Bug #103854	indexes on outer join with subquery causing incorrect response
Submitted:	30 May 2021 2:30	Modified:	30 May 2021 16:47
Reporter:	Sean Stapleton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.12	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
When performing an outer join on a subquery, I'm seeing inaccurate results returned, based on the index used to complete the query. When I force a full scan, the correct results are returned.

In particular, there are some non-null results returned from an INNER JOIN, that are NULL in the outer join. 

How to repeat:
Here's a similar query to what is being run:

SELECT A.id AS aid, A.entity_id AS entity_id, subquery.id AS bid
FROM A -- force a full scan: USE INDEX ()
LEFT OUTER JOIN (
	SELECT B.id AS bid, B.organization_id AS organization_id, B.entity_id AS entity_id
	FROM B
        INNER JOIN C ON C.id = B.entity_id
	AND B.status = "ACTIVE"
        AND C.status = "ACTIVE"
) AS subquery ON A.entity_id = subquery.entity_id AND A.organization_id = subquery.organization_id;

If the outer join is made an inner join, there are results returned with a non-null value for `bid`. When an outer join is run instead, these rows have null values for `bid`.

Thank you for the bug report. You are using a quite older version 5.7.12, current release version is 5.7.34, a lot of bug fixes were done since. Please check with 5.7.34, if the issue continues please provide a complete repeatable test case with table and data dump.

Upgrading to 5.7.34 seems to have resolved the issue. Thank you!

Thank you for the feedback.