Bug #54112 mysql improperly assumes column name in subselect

• Submitted: 31 May 2010 17:01
• Modified: 31 May 2010 18:30
• Reporter: Bogdan Kecman
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.1.41, 5.5.4
• OS: Any
• CPU Architecture: Any

[31 May 2010 17:01] Bogdan Kecman

Description:
MySQL improperly assume column name in subselect.

How to repeat:
CREATE TABLE `t1` (
  xyz int not null primary key
) engine = myisam;
CREATE TABLE `t2` (
  id int not null primary key,
  a int
) engine = myisam;
INSERT INTO `t1` VALUES (1), (2), (3), (4), (5);
INSERT INTO `t2` VALUES (1,1), (2,1), (3,1), (4,1), (5,1);
UPDATE `t2` SET `a` = 2 WHERE `id` IN (SELECT `id` FROM `t1`);

MySQL will assume id in subselect to be from t2 (in t1 there is no id field)

Suggested fix:
return error "`id` column not exist"