Bug #27777 LEFT OUTER JOIN doesn't see column in ON clause
Submitted: 12 Apr 2007 8:09 Modified: 12 Apr 2007 12:15
Reporter: Cedric Wider Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.30 OS:Linux
Assigned to: CPU Architecture:Any

[12 Apr 2007 8:09] Cedric Wider
Description:
When executing a statement containing a LEFT OUTER JOIN  in mysql5.0.30 mysql reports an error that a column doesn't exist. Executing the same statement on 4.1.20 works perfectly.

Here is a snippet of what mysqldump -d shows:
-- MySQL dump 10.10
--
-- Host: xxx    Database: xxx
-- ------------------------------------------------------
-- Server version       5.0.30-log

...

--
-- Table structure for table `TBL_SENDER`
--

DROP TABLE IF EXISTS `TBL_SENDER`;
CREATE TABLE `TBL_SENDER` (
  `msg_id` int(11) NOT NULL auto_increment,
  `sender_id` int(10) unsigned NOT NULL default '999999',
  `sender_link` char(255) NOT NULL default '',
  `msg_size` int(10) unsigned NOT NULL default '0',
 
......

  `auth_type` int(11) NOT NULL default '0',
  PRIMARY KEY  (`msg_id`),
  UNIQUE KEY `msg_id` (`msg_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4201 DEFAULT CHARSET=utf8;

-- Dump completed on 2007-04-12  7:14:59

As you can see there is a column sender_id

The query I execute:
SELECT count(*) 
FROM  TBL_SENDER 
JOIN  TBL_MESSAGE ON  TBL_SENDER.msg_id =  TBL_MESSAGE.msg_id 
JOIN  TBL_RECIPIENT ON  TBL_SENDER.msg_id =  TBL_RECIPIENT.msg_id, 
TBL_USER LEFT OUTER JOIN   TBL_SPAM ON  TBL_SENDER.sender_id =  TBL_SPAM.spam_id  
AND  TBL_SPAM.user_id = recipient_id 

WHERE  
TBL_SPAM.spam_id IS NULL 
AND recipient_id = 1  
and  TBL_SENDER.sender_id =  TBL_USER.user_id  
AND  TBL_RECIPIENT.delete_id = 0  
AND  TBL_MESSAGE.delete_id = 0  
AND draft = 0  
AND stop_msg_delivery = 0 ;

...and the error message that mysql reports:
ERROR 1054 (42S22): Unknown column 'TBL_SENDER.sender_id' in 'on clause'

How to repeat:
I am not sure, but maybe constructing a similar setup and executing the above query might give some hints
[12 Apr 2007 8:11] Cedric Wider
I just reformatted the query to this:

SELECT count(*) 
FROM  TBL_USER, TBL_SENDER 
JOIN  TBL_MESSAGE ON  TBL_SENDER.msg_id =  TBL_MESSAGE.msg_id 
JOIN  TBL_RECIPIENT ON  TBL_SENDER.msg_id =  TBL_RECIPIENT.msg_id 
LEFT OUTER JOIN   TBL_SPAM ON  TBL_SPAM.spam_id = TBL_SENDER.sender_id  
AND  TBL_SPAM.user_id = recipient_id 

WHERE  
TBL_SPAM.spam_id IS NULL 
AND recipient_id = 1  
and  TBL_SENDER.sender_id =  TBL_USER.user_id  
AND  TBL_RECIPIENT.delete_id = 0  
AND  TBL_MESSAGE.delete_id = 0  
AND draft = 0  
AND stop_msg_delivery = 0 ;

and this works...
[12 Apr 2007 11:10] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is, likely, not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/join.html:

"Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
..."

Doesn't it explain your results?
[12 Apr 2007 12:15] Cedric Wider
Thank you Valeriy for you fast and patient reply. Of course it perfectly explains my results.
Thanks again for not just replying "RTFM"