The problem here must be with the
id reference in the innermost
SELECT. For a long time MySQL has had trouble with resolving outer references in correlated subqueries if those references were placed inside derived tables in the correlated subqueries.
Apparently the problem has been resolved in 8.0.14:
- Previously, derived tables and common table expressions could not contain outer references. Outer references are now permitted.
If you want the query to compile both in 8.0.14 and in 8.0.11, apparently you need to rewrite it to somehow avoid referencing the
id column in a derived table. Before making any suggestions, I must note that your example has other issues than the unresolved
id reference. The very first line in this correlated subquery:
from ( select sum(SETNO)
already has a problem, because it references an unknown column
SETNO. The derived table
a does not have such a column.
So, for the sake of showing how to resolve the
id reference issue, I am going to assume that
sum(SETNO) was meant to be written as
sum(SETNO) AS SETNO. That way at least* the outer level
SETNO reference becomes valid.
Basically, the solution is to get the
MAX(SETNO) in an alternative way, such that a derived table is actually not needed at all and thus the
id column can be referenced directly in the correlated subquery. For that you will use
ORDER BY and
LIMIT 1. Specifically, you rewrite your correlated subquery as follows:
WHERE SETNO = id
ORDER BY sum(SETNO) DESC
This way the correlated subquery is still getting the same value but no longer references outer columns from a derived table.
*The query still does not make much sense as it is, though. But at least it should compile now and allow us to demonstrate the solution.