MySQL Bugs: #4202: Error 'Unknown table' in second level correlated subquery

Bug #4202	Error 'Unknown table' in second level correlated subquery
Submitted:	18 Jun 2004 13:29	Modified:	2 Sep 2004 11:28
Reporter:	Peter Ritzinger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.2a	OS:	Any (all)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
MySQL doesn't seems to find the main query FROM's table in a second level subquery.

If I reference to the main query FROM's table from a first-level nested subquery, MySQL finds it. Only the second level (and probably all more) subqueries doesn't find the table.

The example below with the tables and query works fine in MS SQL Server and PostgreSQL.

How to repeat:
My database is:

CREATE TABLE `team` (
  `teamid` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`teamid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `team` VALUES (1,'T1'),(2,'T2'),(3,'T3');

CREATE TABLE `player` (
  `playerid` int(11) NOT NULL auto_increment,
  `points` int(11) NOT NULL default '0',
  `teamid` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`playerid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `player` VALUES (1,10,1,'A'),(2,11,1,'B'),(3,12,1,'C'),(4,11,2,'D'),(6,15,2,'E'),(7,33,3,'F'),(8,10,1,'G');

I want to calculate the points for each team. I define the team points as the sum of the top 3 players' points in the team. So I use a query:

SELECT teamid, name,
 (SELECT SUM(playerpoints)
  FROM (SELECT player.points AS playerpoints 
        FROM     player
        WHERE    player.teamid = team.teamid
        ORDER BY player.points DESC
        LIMIT 3) AS subquery) AS teampoints
FROM team;

But I always get an error: Unknown table 'team' in where clause.

Thank you for bug report, but this bug was already reported as Bug #4090 
(http://bugs.mysql.com/bug.php?id=4090)

In the duplicate bug (#4090) comments was written, that this bug will be fixed in the next server release.
However, this bug is still a bug in version 4.1.4-gamma-win32.

oops, sorry. It is not duplicate bug, it is implementation limitation. 
 
MySQL can't have correlated subquery in the FROM clause.