MySQL Bugs: #41427: Subquery cannot access field from two levels up

Bug #41427	Subquery cannot access field from two levels up
Submitted:	12 Dec 2008 11:34	Modified:	12 Dec 2008 13:02
Reporter:	Arjen lastname	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.67	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This select yields a error:

SELECT ID,
 (SELECT MAX(data) FROM (SELECT data FROM subtable ps
     WHERE ps.maintableID = p.ID) as foo) as datamax
FROM maintable p

ERROR 1054 (42S22): Unknown column 'p.ID' in 'where clause'

The 'maintable' contains a field ID, so the message is incorrect. This is a minimal testcase for a more complicated query, where I couldn't rewrite it to a normal join and/or to only a single level of subqueries in the select-list.

How to repeat:
CREATE TEMPORARY TABLE maintable 
(
  ID INT PRIMARY KEY
);
INSERT INTO maintable VALUES (1), (2), (3);

CREATE TEMPORARY TABLE subtable
(
  maintableID INT NOT NULL,
  data INT NOT NULL
);
INSERT INTO subtable VALUES (1,4),(1,5),(2,6),(3,7),(3,8);

SELECT ID,
 (SELECT MAX(data) FROM (SELECT data FROM subtable ps
     WHERE ps.maintableID = maintable.ID) as foo) as datamax
FROM maintable;

I forgot the expected result, this is what PostgreSQL sais:

 id | datamax
----+---------
  1 |       5
  2 |       6
  3 |       8

I've got the same error message on 5.0.72, 5.1.30 and 6.0.8, but it looks like a documented limitation (http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html):

"Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation."

Well, its a query-type I've used more often, and know of others have tried using. So I'd like it as a feature (since its afaik allowed in sql:2003).

Surely, it would be nice to have this implemented.

Actually, it looks like a duplicale of bug #8019.