MySQL Bugs: #52424: ERROR 1054 (42S22): Unknown column 'oid' in 'where clause'

Bug #52424	ERROR 1054 (42S22): Unknown column 'oid' in 'where clause'
Submitted:	29 Mar 2010 4:52	Modified:	29 Mar 2010 15:45
Reporter:	Tyler Hall	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.77	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following query works on 5.0.32;

mysql> SELECT order_id AS oid FROM ( SELECT order_id, activity FROM orders WHERE order_status IN ( 2,7,13,15,19,21,25,27,35,39,43,45,47,53,67,72,95,118,119,120,125,130,133,138,141,146,152,158,164,171,179,187,192,195,198,201,202,206,214,216,225,227,230,238,246,256,258,259,262,265,269) ) AS orders WHERE activity > '1268026657' AND 0 = ( SELECT COUNT(*) FROM order_tickets WHERE timestamp > '1268026657' AND source = 'staff' AND order_id = oid );
+---------+
| oid     |
+---------+
| 1012359 | 
| 1012335 | 
| 1012357 | 
| 1012365 | 
+---------+
4 rows in set (0.02 sec)

The same query doesn't work on 5.0.77;

mysql> SELECT order_id AS oid FROM ( SELECT order_id, activity FROM orders WHERE order_status IN ( 2,7,13,15,19,21,25,27,35,39,43,45,47,53,67,72,95,118,119,120,125,130,133,138,141,146,152,158,164,171,179,187,192,195,198,201,202,206,214,216,225,227,230,238,246,256,258,259,262,265,269) ) AS orders WHERE activity > '1268026657' AND 0 = ( SELECT COUNT(*) FROM order_tickets WHERE timestamp > '1268026657' AND source = 'staff' AND order_id = oid );

ERROR 1054 (42S22): Unknown column 'oid' in 'where clause'
mysql> 

How to repeat:
By re-running the query.

Thank you for the report.

Please provide output of SHOW CREATE TABLE orders and SHOW CREATE TABLE order_tickets

| orders | CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL auto_increment,
  `ts` int(11) NOT NULL default '0',
  `order_form_id` int(11) NOT NULL default '0',
  `order_queue_id` int(11) NOT NULL default '0',
  `order_status` int(11) NOT NULL,
  `priority` tinyint(1) NOT NULL default '0',
  `owner` varchar(255) default '',
  `client_id` int(11) NOT NULL default '0',
  `listed_company` varchar(255) default '',
  `total` float(20,2) NOT NULL default '0.00',
  `hash` varchar(50) default '',
  `activity` int(11) NOT NULL default '0',
  `progress` mediumtext,
  `info` mediumtext,
  `signature` mediumtext,
  PRIMARY KEY  (`order_id`),
  KEY `order_status` (`order_status`),
  KEY `order_queue_id` (`order_queue_id`),
  KEY `activity` (`activity`),
  KEY `client_id` (`client_id`),
  KEY `hash` (`hash`),
  KEY `ts` (`ts`,`order_form_id`,`order_queue_id`,`order_status`),
  FULLTEXT KEY `info` (`info`)
) ENGINE=MyISAM AUTO_INCREMENT=1012400 DEFAULT CHARSET=utf8 |

| order_tickets | CREATE TABLE `order_tickets` (
  `ticket` int(10) unsigned NOT NULL auto_increment,
  `order_id` int(10) unsigned NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  `author` varchar(100) default '',
  `recipient` varchar(100) default '',
  `subject` varchar(100) default '',
  `attachment` tinyint(1) unsigned NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  `type` varchar(15) default '',
  `source` varchar(255) default 'client',
  `assignment` int(10) unsigned NOT NULL default '0',
  `activity` int(10) unsigned NOT NULL default '0',
  `cc` varchar(50) default '',
  `body` text,
  `priority` tinyint(1) unsigned NOT NULL default '1',
  `q_id` int(5) unsigned NOT NULL default '0',
  `weight` tinyint(1) unsigned default NULL,
  `rem_assign` int(1) unsigned NOT NULL default '0',
  `public_comment` int(1) unsigned NOT NULL default '0',
  `company_assignment` int(11) NOT NULL default '0',
  `activity_type` varchar(255) default 'none',
  `headers` text,
  PRIMARY KEY  (`ticket`),
  KEY `type` (`type`),
  KEY `timestamp_activity` (`timestamp`,`activity`),
  KEY `priority` (`priority`),
  KEY `company_assignment` (`company_assignment`),
  KEY `q_id` (`q_id`),
  KEY `parent` (`order_id`,`q_id`,`type`),
  KEY `c_p_t` (`contact_id`,`order_id`,`timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=411366 DEFAULT CHARSET=utf8 |

Thank you for the feedback.

Old behavior was wrong and fixed in version 5.0.54. See bug #32400 for details.

Back !bug (aliases aren't more allowed in where clause, so error message is right).