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
• Status: Won't fix
• 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);