MySQL Bugs: #16883: Left join issue

Bug #16883	Left join issue
Submitted:	29 Jan 2006 18:55	Modified:	12 Apr 2006 13:57
Reporter:	Gerben Tissink	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-5.0.18-win32	OS:	Windows (Windows 2003)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
When my CMS tries to show all user i get this
======================================
DATABASE ERROR 1054 - Unknown column 'a.countrycode' in 'on clause' 
DATABASE QUERY WAS SELECT a.authorid as authorid,a.login as login,a.authorname as authorname,a.usergroup as usergroup,a.authoremail as authoremail,a.disuser as disuser,u.usergroupdesc as usergroupdesc,c.countryname as country,c.flag as flag FROM authors a, usergroups u LEFT JOIN countries c ON c.countrycode=a.countrycode WHERE u.usergroupname=a.usergroup AND u.language='en' ORDER BY a.authorname 

From a other ezCntents user:
=========================
On other Server with MYSQL 4.017-max it runns. On other with 5.015 when I like to create a user: 

How to repeat:
Load the backup 
run query
bang error 
simple as that

alternative install ezContents and "admin" => "Maintain users"

Backup of database website

Attachment: Website Backup 20060129 1946.sql (text/plain), 83.93 KiB.

There is no data in the database except the default info.
and the user admin with password admin :-)

Last part of other user was missing, he got the same message

critical to me but ... let's put it on S2 serious

Thanks for your bug report.  I was able to reproduce this under 5.0.

This bug did not occur in 4.1

mysql> SELECT a.authorid as authorid,a.login as login,a.authorname
    -> as authorname,a.usergroup as usergroup,a.authoremail as authoremail,a.dis
user as
    -> disuser,u.usergroupdesc as usergroupdesc,c.countryname as country,c.flag
as flag
    -> FROM authors a, usergroups u LEFT JOIN countries c ON
    -> c.countrycode=a.countrycode WHERE u.usergroupname=a.usergroup AND
    -> u.language='en' ORDER BY a.authorname
    -> ;
ERROR 1054 (42S22): Unknown column 'a.countrycode' in 'on clause'
mysql>

This doesn't appear to be a bug.  That is, it is the intended functionality of 5.0 since it more strictly adheres to the SQL:2003 specification in this area.  This bug report seems to be a duplicate of bug #13551.

In your particular case, try:
... FROM (authors a, usergroups u) LEFT JOIN countries c ...

The change is noted in the 5.0.12 changelog as well as mentioned specifically in the "Upgrading from MySQL 4.1 to 5.0" page in the documentation (http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html).

Thanks.(I assume you will close the call)

As earlier stated, explanation accepted call closed
Is indeed NO BUG