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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.24a OS:pc-linux-gnu
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.