Bug #25414 LEFT JOIN does not find a column
Submitted: 4 Jan 2007 12:59 Modified: 4 Jan 2007 13:48
Reporter: Matthias Urlichs Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.30, 5.1.11 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[4 Jan 2007 12:59] Matthias Urlichs 
Description:
Some join statements seem to get confused WRT which columns actually exist.

How to repeat:
Import this database definition:

DROP TABLE IF EXISTS `answers`;
CREATE TABLE `answers` (
  `question` tinyint(3) unsigned NOT NULL default '0'
) ENGINE=MyISAM AUTO_INCREMENT=227 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `number` tinyint(3) unsigned NOT NULL default '0'
) ENGINE=MyISAM AUTO_INCREMENT=192 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user` varchar(255) character set latin1 collate latin1_bin NOT NULL default ''
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

Then do:

mysql>         SELECT    1
    ->         FROM      questions, users
    ->         LEFT JOIN answers ON answers.question = questions.number
    -> ;
ERROR 1054 (42S22): Unknown column 'questions.number' in 'on clause'
mysql> 

The error goes away if you skip the "users" table in teh FROM clause.

Suggested fix:
no idea.
[4 Jan 2007 13:24] Matthias Urlichs 
I think this is a serious problem; we have not found a sensible workaround.
[4 Jan 2007 13:41] Matthias Urlichs 
typo in synopsis
[4 Jan 2007 13:48] Valeriy Kravchuk 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (namely, http://dev.mysql.com/doc/refman/5.0/en/join.html, "Join Processing Changes in MySQL 5.0.12") and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php.