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

[30 May 2021 2:30] Sean Stapleton
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`.
[30 May 2021 10:57] MySQL Verification Team
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.
[30 May 2021 16:47] Sean Stapleton
Upgrading to 5.7.34 seems to have resolved the issue. Thank you!
[30 May 2021 18:05] MySQL Verification Team
Thank you for the feedback.