Bug #66667 Subquery in where clause deriving columns from parent query (correlated query)
Submitted: 3 Sep 2012 14:34 Modified: 18 Feb 2013 9:09
Reporter: Andrew Rose Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.5.24 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any

[3 Sep 2012 14:34] Andrew Rose
Description:
When performing a sub query, if a column does not exist in the sub query but exists in the parent it will be picked up due to subquery correlation.

This can lead to unexpected behaviour as it is not explicitly required to define the parent table from the sub query.

How to repeat:
create table tbl0(id int, userid int);
create table tbl1(id int);

select * from tbl0 where userid in (select userid from tbl1);
Empty set (0.00 sec)

Suggested fix:
Require explicit declaration of the parent table from sub query to qualify as correlated.
[18 Feb 2013 9:09] Jørgen Løland
Hi Andrew,

Thank you for the feature request.

I'm afraid we won't fix this because it contradicts the SQL standard. 

A suggestion to avoid confusion is to use unique aliases for all tables in a query:

select * from tbl0 as outer
where outer.userid in (select inner.userid from tbl1 as inner);