Referencing a query result alias in a subquery

This is mostly a SQL syntax / SQL capability question. Why does the following query NOT work:

SELECT * from 
(
    select m.*, p.type,
    from multipliers m
    inner join pushes p
    on m.push_id = p.id
    where p.type = 'CONSTANT'
) AS res1 where res1.push_id = ( 
    select max(push_id) from res1
);

when the following completes without issue:

SELECT * from 
(
    select m.*, p.type,
    from multipliers m
    inner join pushes p
    on m.push_id = p.id
    where p.type = 'CONSTANT'
) AS res1 where res1.push_id = ( 
    select max(push_id) from    
        (
            select m.push_id
            from multipliers m
            inner join pushes p
            on m.push_id = p.id
            where p.type = 'CONSTANT'
        ) AS res2
);

Answer

Per the MySQL 5.7 documentation:

Derived tables cannot be correlated subqueries, or contain outer references or references to other tables of the same SELECT.

In other words, you can’t reference a derived table in a subquery. The documentation doesn’t state this, but it likely acts this way because of an OOO issue since the derived table isn’t necessarily processed before the subquery. In MySQL 8.0 you will be able to use a Common Table Expression or CTE, which basically lets you define a reusable derived table before your query, but until then use your second approach.