MySQL Subqueries in the FROM clause

[cah]

http://dev.mysql.com/doc/refman/5.0/en/ ... views.html

Code: Select all

 SELECT name,nop,(nop+1) AS subtotal FROM Wedding.Wedding_Registration
 UNION
 SELECT NULL,NULL,SUM(subtotal)
 FROM (SELECT name AS n1,nop AS n2,(nop+1) AS subtotal FROM Wedding.Wedding_Registration) AS T1 GROUP by n1;

Output:

Code: Select all

'Chang-An Hsiao', 0, 1
'', , 1

However, it fails when creating a view:

Code: Select all

CREATE VIEW `Wedding`.`Guest_Registration_View` AS
 SELECT name,nop,(nop+1) AS subtotal FROM Wedding_Registration
 UNION
 SELECT NULL,NULL,SUM(subtotal)
 FROM (SELECT name AS n1,nop AS n2,(nop+1) AS subtotal FROM Wedding_Registration) AS T1 GROUP by n1;

It will report an error:

Script line: 1 View's SELECT contains a subquery in the FROM clause

From MySQL's bug report site, people have been asking this bug to be fixed for years (since 2005)!
Don't know why this problem still exists without being fixed after so many releases.
I have upgraded MySQL a few times already.
The latest one is MySQL 5.1.40 and the previous one is 5.0.51b.
"View + subquery" in the FROM clause fails on both.

http://bugs.mysql.com/bug.php?id=12755
http://bugs.mysql.com/bug.php?id=16757