Bug #31811 outer aliases are not supported in subqueries
Submitted: 24 Oct 2007 10:14 Modified: 24 Oct 2007 18:09
Reporter: Maciej Pilichowski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.45 OS:Any
Assigned to: Miguel Solorzano CPU Architecture:Any

[24 Oct 2007 10:14] Maciej Pilichowski
Description:
Consider:
select A*A A2,(select B from I where T.A*T.A=I.B) from T;

and
select A*A A2,(select B from I where A2=I.B) from T;

however MySQL does not support this. Excerpt from documentation -- 12.2.8.7.
"For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list."

It should be also true for WHERE clause -- when the subquery runs all values are already known. Of course it is contrary to plain select, for example:
select A*A A2 from T where B=A2;

but nevertheless it should work when you create alias in outer query and use it later subquery.

The other story is error while displaying error message, I already reported this.

How to repeat:
Just run such query.

Suggested fix:
Support aliases in subqueries. Btw. I don't know if this is described by SQL standard. If not -- change the status to feature request.
[24 Oct 2007 10:58] Miguel Solorzano
Thank you for the bug report.

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table T (A int);
Query OK, 0 rows affected (0.09 sec)

mysql> create table I (B int);
Query OK, 0 rows affected (0.14 sec)

mysql> select A*A A2,(select B from I where T.A*T.A=I.B) from T;
Empty set (0.00 sec)

mysql> select A*A A2,(select B from I where A2=I.B) from T;
Empty set (0.00 sec)

mysql>
[24 Oct 2007 12:57] Maciej Pilichowski
Please add this before the last query:
set @@sql_mode='ONLY_FULL_GROUP_BY';
[24 Oct 2007 16:36] Miguel Solorzano
Thank you for the feedback. Performing the slq_mode mentioned, MySQL behaves
the same as other RDBMS like Oracle, just the error message is different:

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.52-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select A*A A2,(select B from I where T.A*T.A=I.B) from T;
Empty set (0.00 sec)

mysql> select A*A A2,(select B from I where A2=I.B) from T;
ERROR 1463 (42000): non-grouping field 'A2' is used in HAVING clause
mysql>
--------------------------------------------------------------------
SQL> select A*A A2,(select B from I where T.A*T.A=I.B) from T;

nÒo hß linhas selecionadas

SQL> select A*A A2,(select B from I where A2=I.B) from T;
select A*A A2,(select B from I where A2=I.B) from T
                                     *
ERRO na linha 1:
ORA-00904: "A2": identificador invßlido
[24 Oct 2007 18:09] Maciej Pilichowski
Miguel, thank for your reply -- so please make it a wish.

It is pretty valid (in logical term) query and it would be very useful to have it.