MySQL Bugs: #29316: Join not working when using multiple table in from clause

Bug #29316	Join not working when using multiple table in from clause
Submitted:	23 Jun 2007 13:03	Modified:	24 Jun 2007 19:46
Reporter:	Eugen Borshch	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	>5.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	join, multiple from clause

Description:
Hello, everybody!

I have the following problem, since I upgraded MySQL version till 5.0.42 join sometimes is not working when using multiple tables in from clause

How to repeat:
Tables:

show create table country;

CREATE TABLE `country` (
  `countryid` int(11) NOT NULL auto_increment,
  `image` varchar(40) default '',
  `width` int(11) default '0',
  `height` int(11) default '0',
  PRIMARY KEY  (`countryid`)
) ENGINE=MyISAM AUTO_INCREMENT=219 DEFAULT CHARSET=latin1

show create table sourcecountry;
CREATE TABLE `sourcecountry` (
  `sourcecountryid` int(11) NOT NULL auto_increment,
  `countryid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`sourcecountryid`),
  UNIQUE KEY `countryid` (`countryid`)
) ENGINE=MyISAM AUTO_INCREMENT=244 DEFAULT CHARSET=latin1

show create table dynamicmessage;
CREATE TABLE `dynamicmessage` (
  `messagecode` varchar(40) NOT NULL default '',
  `languageid` int(11) NOT NULL default '1',
  `message` text,
  UNIQUE KEY `messagecode` (`messagecode`,`languageid`),
  KEY `dynamicmessage_message_index` (`message`(200))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Simplified query:
SELECT sourcecountry.sourcecountryid FROM sourcecountry, country  left join dynamicmessage as x on (x.messagecode=concat('sourcecountry',convert(sourcecountry.sourcecountryid using latin1)) and x.languageid=1);

Returns:
Unknown column 'sourcecountry.sourcecountryid' in 'on clause'

It returns similar result if table name will not or alias will be specified within on clause. I'm getting this error for 5.0.* version on both windows and linux platform. Per my understanding this problem is caused by multiple tables in from clause, but why?

It's working with the same data in mysql 4.1 without any errors, and also working in early versions ('convert(sourcecountry.sourcecountryid using latin1)' must be changed to 'sourcecountry.sourcecountryid' this way) - 4.0, 3.23.

Please advise how to force MySQL > 5.0 to deal with this query.

Suggested fix:
Of course it's possible to use uniform query format (e.g. connect all tables using joins), but as usual it requires re-building of some code and this is undesired thing.

Thank you for a problem report. What exect version you had upgraded from? 

Please, read http://dev.mysql.com/doc/refman/5.0/en/join.html about some changes in join processing since 5.0.12 also.

Hello!

Thank you for your answer.

Actually it was 4.0.27 and currently I need to have this stuff compatible with latest MySQL 5.0.* version (let's say it's "security" requirement to use latest MySQL 5.0.* release and this requirement comes not from me...).
Why I wrote that it works with MySQL 4.1.7 - it's just because I'm using 4.1.* to check and localize problems (whether it appears for 5.0.* only or not).

Finally, what I need is to work using latest MySQL 5.0.* version without rewriting of all queries using multiple "from tables" and joins... So please advise, what's better: to wait until this bug will be solved in future versions or to change my queries in case if this future version will be not released soon

With best regards

The 5.0 behavior is correct in regards to what the SQL standard requires, the 4.x behavior was not. Making 5.0 standards compliant required substantial changes in the SQL parser and query execution engine, implementing the new correct way while keeping the old non-standard behavior as an optional backwards compatible mode was simply not possible. So there is no sense in "to wait until this bug will be solved in future versions" as this is not a 'bug' and will not be 'fixed' in any future version.

This is explained in detail in the "Join Processing Changes in MySQL 5.0.12" section of http://dev.mysql.com/doc/refman/5.0/en/join.html

Hello!

"This is explained in detail in the "Join Processing Changes in MySQL 5.0.12" section of http://dev.mysql.com/doc/refman/5.0/en/join.html" - I have not found which exactly section of this paper you are referring to.

However I did some tests and found that "JOIN" sees just last of multiple tables referenced in the from clause (also now I see that it's working in the same way in other modern DBMSs), so query like:

SELECT sourcecountry.sourcecountryid FROM  country , sourcecountry left join dynamicmessage as x on (x.messagecode=concat('sourcecountry',convert(`sourcecountry`.`sourcecountryid` using latin1)) and x.languageid=1)

will work. Unfortunately it will not work for me because as I wrote about it's just simplified query, and real query needs dynamicmessage to be joined twice (for sourcecountry and for country), so it seems to be that I'll need to rewrite these stuff :((

read the section that starts from "Previously, the comma operator"