Bug #22706 WHERE IN select clause permits invalid column names
Submitted: 26 Sep 2006 17:27 Modified: 26 Sep 2006 17:50
Reporter: Simon Grantham Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.19 OS:Linux (debian)
Assigned to: CPU Architecture:Any

[26 Sep 2006 17:27] Simon Grantham 
Description:
Within the sub select portion of a WHERE IN clause, you can erroneously specify a column from the main query as a valid column in the sub select.  E.G.

create table tmp1
(
    tmp1_id int,
    status int
)

create table tmp2
(
    tmp2_id int
)

-- The following query should fail as "status" is not a column in table "tmp2"
select * from tmp1 where tmp1_id in (select status from tmp2)

This is particularly painful if you happen to be doing an update or delete.  E.g.

-- The following query should fail but instead updates every row in tmp1
update tmp1 set status=1 where tmp1_id in (select tmp1_id from tmp2)

How to repeat:
use where in clause that specifies a main query result column that doesn't exist in the where in subselect.

Suggested fix:
correctly filter column names
[26 Sep 2006 17:50] MySQL Verification Team 
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=22514.