Bug #2148 Subqueries in the FROM clause
Submitted: 18 Dec 2003 1:26 Modified: 18 Dec 2003 4:08
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:MySQL 4.0.15 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[18 Dec 2003 1:26] [ name withheld ] 
Description:
I've tried to execute the exemple found in the chapter 6.4.2.8 of the online manual but I always get an error #1064.

SELECT AVG(sum_column1)
       FROM (SELECT SUM(column1) AS sum_column1
             FROM t1 GROUP BY column1) AS t1;

Here is the example with my database:

SELECT SUM( npgFax ) AS nnpgFax
FROM (SELECT pgFax AS npgFax
FROM `suivis` 
WHERE NSOC = 1 AND NCLI = 61 AND NCOL = 100 AND TYPET = 'FAX' AND Fact <> 0
GROUP BY RefEnCours
) AS `suivis` 
LIMIT 0 , 30 

MySQL a répondu:
#1064 - Erreur de syntaxe près de 'SELECT pgFax AS npgFax
FROM  `suivis` 
WHERE NSOC = 1 AND NCLI =' à la ligne 3

Did someone know something about this problem ???

How to repeat:
try to execute the following exemple:
SELECT AVG(sum_column1)
       FROM (SELECT SUM(column1) AS sum_column1
             FROM t1 GROUP BY column1) AS t1;
[18 Dec 2003 4:08] MySQL Verification Team 
Hi!

These are not sub-queries in the FROM clause.

These are derived tables, or how they call them in some other RDBMS's 
inline VIEW's.

Derived tables in all formats (but with obligatory alias as per SQL-99 standard)
are fully functional in 4.1.