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

[18 Jun 2004 13:29] Peter Ritzinger
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.
[19 Jun 2004 12:22] Oleksandr Byelkin
Thank you for bug report, but this bug was already reported as Bug #4090 
(http://bugs.mysql.com/bug.php?id=4090)
[2 Sep 2004 10:38] Peter Ritzinger
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.
[2 Sep 2004 11:28] Oleksandr Byelkin
oops, sorry. It is not duplicate bug, it is implementation limitation. 
 
MySQL can't have correlated subquery in the FROM clause.