Bug #8019 correlated subqueries in from (nested)
Submitted: 19 Jan 2005 20:35 Modified: 10 Oct 2019 21:17
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.x, 4.1.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[19 Jan 2005 20:35] Martin Friebe
Description:
I am reporting this as a feature request, because the documentation (AFAIK) doesnt mention it works. On the other hand the documentation says that mysql does correlated subqueries, and it does not say that they do not work in from clauses. So this could be considered a bug too.

looking on the "repeat" section, the first query works, and it demonstrates that mysql can find values from outer queries, even if the inner query is nested.

The 2nd case doesnt work. it seem obvious that correlated sub queries in "from" can not always work. as in

select 1 as aa from (select 1 from t where aa=1) x;

aa will only be defined, after the subquery was executed.

In the below (2nd how to repeat) example aa should be defined:
The middle subquery, should be executed for each row in t, defining aa for each such row. 
(And looking at the 1st example the column alias aa, is available to the subqueries (both)) 

this means that aa is defined at the time the inner subquery gets executed.

It seems that it only stops working because the inner subquery moved from the select part to the from part of the middle subquery. Mysql seems to thing that correlation for subqueries in from is impossible.

just a note: the queries are simplified, they need limit clauses, if there was data in the tables.

How to repeat:
create table t (a integer);
 
select 1 as aa , row(1,1)=(select 2, (select 3 from t where aa=1) from t ) from t ;
Empty set (0.00 sec)

select 1 as aa ,         (select 2 from  (select 3 from t  where aa=1 ) x ) from t ;
ERROR 1054 (42S22): Unknown column 'aa' in 'where clause'

Suggested fix:
it depends how mysql optimzes the above query, and in which order the queries get executed, mightby a simple problem of the parser, might be complex, due to current order of  execution.
[9 Oct 2006 16:23] Shay Pierce
I'm very surprised that this feature still isn't in place. Just wanted to 'second' its request, it's something I'm very interested in as well.
[12 Oct 2006 12:21] Valeriy Kravchuk
Bug #23172 was marked as a duplicate of this one. This is a reasonable feature request.
[12 Dec 2008 13:03] Valeriy Kravchuk
Bug #41427 was marked as a duplicate of this one.
[12 Dec 2008 13:35] Arjen lastname
I actually don't understand why the first query of Martin works...

Its - afaik - not valid SQL, since columns in the select list can't reference eachother. His first example can be simplified to:
select 1 as aa, (select aa);

And that is semantically equivalent to:
select 1 as aa, aa;

But the second query is (imho) correctly seen as invalid. Both duplicated bugreports contain valid sql-examples.
[10 Oct 2019 21:17] Roy Lyseng
Posted by developer:
 
Implemented in 8.0.
But please note this is a non-standard feature.