MySQL Bugs: #8019: correlated subqueries in from (nested)

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

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.

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.

Bug #23172 was marked as a duplicate of this one. This is a reasonable feature request.

Bug #41427 was marked as a duplicate of this one.

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.

Posted by developer:
 
Implemented in 8.0.
But please note this is a non-standard feature.