Bug #26801 Use of non existant columns in subqueries does not throw an error/

• Submitted: 2 Mar 2007 19:08
• Modified: 2 Mar 2007 21:34
• Reporter: Rolf Martin-Hoster
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S2 (Serious)
• Version: 5.0.33
• OS: Linux (Debian)
• CPU Architecture: Any

[2 Mar 2007 19:08] Rolf Martin-Hoster

Description:
I referenced an illegal column in a subquery and instead of firing an exception it was treated as a null set:

mysql> select * from assets where parent_id=4490 and id not in (select id from network_ports);
Empty set (0.00 sec)
mysql> select id from network_ports;
ERROR 1054 (42S22): Unknown column 'id' in 'field list'

I had a fellow #mysql'er verify on his server.

How to repeat:
#
mysql> show create table network_ports\G
#
*************************** 1. row ***************************
#
       Table: network_ports
#
Create Table: CREATE TABLE `network_ports` (
#
  `asset_id` int(11) NOT NULL default '0',
#
  `mac_addr` varchar(17) default NULL,
#
  `if_name` varchar(255) default NULL,
#
  `dev_name` varchar(255) default NULL,
#
  `switch_id` int(11) default NULL,
#
  `port_phys` varchar(255) default NULL,
#
  `port_virt` int(11) default NULL,
#
  `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#
  PRIMARY KEY  (`asset_id`),
#
  UNIQUE KEY `mac_addr` (`mac_addr`),
#
  KEY `switch_id` (`switch_id`),
#
  CONSTRAINT `network_ports_ibfk_1` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
#
  CONSTRAINT `network_ports_ibfk_2` FOREIGN KEY (`switch_id`) REFERENCES `switches` (`asset_id`) ON DELETE SET NULL
#
) ENGINE=InnoDB DEFAULT CHARSET=latin1
#
1 row in set (0.00 sec)
#
 
#
 
#
ysql> show create table assets\G
#
*************************** 1. row ***************************
#
       Table: assets
#
Create Table: CREATE TABLE `assets` (
#
  `id` int(11) NOT NULL auto_increment,
#
  `asset_def_id` int(11) NOT NULL default '0',
#
  `natnet_serial` varchar(255) default NULL,
#
  `vendor_serial` varchar(255) default NULL,
#
  `manu_serial` varchar(255) default NULL,
#
  `po_id` int(11) default NULL,
#
  `owner_account_id` int(11) NOT NULL default '3781',
#
  `location_id` int(11) default NULL,
#
  `date_start` datetime default NULL,
#
  `date_end` datetime default NULL,
#
  `date_acquired` datetime default NULL,
#
  `status_id` int(11) NOT NULL default '0',
#
  `account_id` int(11) NOT NULL default '0',
#
  `parent_id` int(11) default NULL,
#
  `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
#
  PRIMARY KEY  (`id`),
#
  UNIQUE KEY `natnet_serial` (`natnet_serial`),
#
  UNIQUE KEY `vendor_serial` (`vendor_serial`),
#
  UNIQUE KEY `manu_serial` (`manu_serial`),
#
  KEY `asset_def_id` (`asset_def_id`),
#
  KEY `po_id` (`po_id`),
#
  KEY `location_id` (`location_id`),
#
  KEY `status_id` (`status_id`),
#
  KEY `account_id` (`account_id`),
#
  KEY `parent_id` (`parent_id`),
#
  KEY `owner_account_id` (`owner_account_id`),
#
  CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`asset_def_id`) REFERENCES `asset_defs` (`id`),
#
  CONSTRAINT `assets_ibfk_3` FOREIGN KEY (`owner_account_id`) REFERENCES `accounts` (`id`),
#
  CONSTRAINT `assets_ibfk_4` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`),
#
  CONSTRAINT `assets_ibfk_5` FOREIGN KEY (`status_id`) REFERENCES `asset_statuses` (`id`),
#
  CONSTRAINT `assets_ibfk_6` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
#
  CONSTRAINT `assets_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE
#
) ENGINE=InnoDB DEFAULT CHARSET=latin1
#
1 row in set (0.00 sec)
#
 
#
mysql> select * from assets where parent_id=4490 and id not in (select id from network_ports);
#
Empty set (0.00 sec)
#
 
#
mysql> select id from network_ports;
ERROR 1054 (42S22): Unknown column 'id' in 'field list'

Suggested fix:
Should throw ERROR 1054 (42S22): Unknown column 'id' in 'field list'

[2 Mar 2007 21:34] Valeriy Kravchuk

Sorry, but this is not a bug. Your subquery refers to id column from main table. Look:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t01(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t02(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t01 where c1 not in (select c2 from t02);
ERROR 1054 (42S22): Unknown column 'c2' in 'field list'

You have error above, but:

mysql> create table t03(c3 int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t01 where c1 not in (select c1 from t03);
Empty set (0.00 sec)