MySQL Bugs: #31811: outer aliases are not supported in subqueries

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:	MySQL Verification Team	CPU Architecture:	Any

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.

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>

Please add this before the last query:
set @@sql_mode='ONLY_FULL_GROUP_BY';

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

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.