I stumbled upon a strange behavior of MySQL (v.8) when trying to run a nested subquery in the FROM clause. The (relevant part of the) schema of the sample database I am using is as follows:

[![enter image description here](https://i.stack.imgur.com/AbwMJ.png)](https://i.stack.imgur.com/AbwMJ.png)

The following two queries run identically on SQL Server:

```
SELECT SUM(tot) as total
 FROM (
     SELECT 
        SUM(OD.quantityOrdered * OD.priceEach) as tot,
        C.customerNumber
     FROM customers C
     INNER JOIN orders O ON C.customerNumber = O.customerNumber
     INNER JOIN orderdetails OD ON O.orderNumber = OD.orderNumber
     GROUP BY O.orderNumber, C.customerNumber
) AS CO
GROUP BY CO.customerNumber;

```

and 

```
SELECT 
  (
    SELECT SUM(tot) as total
    FROM 
        (
            SELECT 
                (
                    SELECT  SUM(OD.quantityOrdered * OD.priceEach)
                    FROM orderdetails OD 
                    WHERE OD.orderNumber = O.orderNumber
                ) AS tot
            FROM orders O
            WHERE O.customerNumber = C.customerNumber
        ) AS ORD
  ) AS total
FROM customers AS C;

```

However, on MySQL, the first one runs fine, while the second one results in an error:

```
Error Code: 1054. Unknown column 'C.customerNumber' in 'where clause'

```

I will appreciate any clues about why this is happening. *Please note* that I am mostly interested not in workarounds or other ways to implement this query, but in understanding the reasons why the nested query fails.