Bug #23420 Subquery does not fail when unknown field specified

• Submitted: 18 Oct 2006 13:34
• Modified: 18 Oct 2006 15:35
• Reporter: Balint Toth
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.24a
• OS: pc-linux-gnu
• CPU Architecture: Any
• Tags: subquery delete

[18 Oct 2006 13:34] Balint Toth

Description:
When using "delete t1 where t1id in (select t1id from t2)" mysql returns "0 rows affected" however there's no t1id field in table t2.

How to repeat:
mysql> create table t1 (t1id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2 (t2id int);
Query OK, 0 rows affected (0.01 sec)
mysql> delete from t1 where t1id in (select t1id from t2);
Query OK, 0 rows affected (0.00 sec)

When you specify the table name in the subquery, it fails as expected:

mysql> delete from t1 where t1id in (select t2.t1id from t2);
ERROR 1054 (42S22): Unknown column 't2.t1id' in 'field list'

[18 Oct 2006 15:17] Valeriy Kravchuk

Sorry, but it is not a bug. When you use "t1id"

delete from t1 where t1id in (select t1id from t2);

you refer to the t1id column from outer table, t1, in your subquery. Try:

delete from t1 where t1id in (select no_such_col from t2);

and you'll see the difference...

[18 Oct 2006 15:35] Balint Toth

I see. But why can you refer to an outer table? :) thanks anyway.