MySQL Bugs: #58737: nested query fails to execute

Bug #58737	nested query fails to execute
Submitted:	5 Dec 2010 7:59	Modified:	6 Dec 2010 1:41
Reporter:	C S	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.53, 5.1.54	OS:	Windows (7)
Assigned to:		CPU Architecture:	Any
Tags:	nested query fails unknown column in field list

Description:
The following query fails to execute:
select (select * from (select my_field) as t) from (select 1 as my_field) as t2;

It produces the following error:
ERROR 1054 (42S22): Unknown column 'my_field' in 'field list'

The query is syntactically correct, but the inner query is unable to see the my_field field. This query executes fine in SQLite, so it seems to be an issue with only MySQL.

How to repeat:
Execute the query given.

Suggested fix:
Make the fields in the outer query visible to the inner query in situations like the one given.

Verified on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select (select * from (select my_field) as t) from (select 1 as my_field) as t2;
ERROR 1054 (42S22): Unknown column 'my_field' in 'field list'

Is there some kind of workaround for this issue? I am having a lot of trouble with a query of a similar format that I cannot get to execute.

Thanks,

C S