Ryan Allbaugh <[EMAIL PROTECTED]> wrote on 12/19/2005 04:41:39 PM: > I am using MySQL 5.0.15 on windows and cannot run this query: > > SELECT a.*,b.name, c.fullname,d.fullname > FROM access_authorization a, building b > LEFT JOIN users c ON a.createdby=c.id > LEFT JOIN users d ON a.modifiedby=d.id > WHERE a.sortcode=b.sortcode AND > a.sortcode like '1,2,1,6%' LIMIT 0, 25 > > I receive: > ERROR 1054 (42S22): Unknown column 'a.createdby' in 'on clause' > > But this query DOES work under my linux mysql 5.0.0-alpha! > > I can modify the SQL Statement to the following and it works fine: > > SELECT a.*,b.name, c.fullname,d.fullname FROM access_authorization a > LEFT JOIN users c ON a.createdby=c.id > LEFT JOIN users d ON a.modifiedby=d.id > RIGHT JOIN building b ON a.sortcode=b.sortcode > WHERE a.sortcode like '1,2,1,6%' LIMIT 0, 25 > > > But I have a lot of SQL statements like this and I do not want to have > to modify them all. Does anyone have any ideas on what is wrong? > > I've been able to reproduce the problem with some generic tables, so I > wont include my table definitions here. >
What is wrong is that the all versions of MySQL before 5.0.12 were buggy in their evaluation of implicit CROSS JOINs (comma separated lists of table names) in combination with explicit JOINS (INNER JOIN...ON..., LEFT JOIN...ON..., RIGHT JOIN...ON..., etc). In order to get MySQL to operate as specified by the SQL:2003 specification, the comma operator (what you are using to create your implicit CROSS JOIN) was demoted in evaluation precedent. This change happened with 5.0.12 and is thoroughly documented here: http://dev.mysql.com/doc/refman/5.0/en/join.html Unfortunately you are going to need to edit your queries to bring them in line with the SQL standard in order to achieve their previous functionality. May I suggest to move to explicit JOIN statements and stop using the commas to create lists of tables? <RANT> HEY Documentation team!! This is about the 500th example this year of someone using that dang-blasted comma-separated list format to make JOINS who is having problems with their query. Can you PLEASE fix *all* the examples in the manual to NOT use this form except towards the bottom of certain pages where you could describe it as an option along with all of the baggage it now carries. The "implicit CROSS JOIN" catches another one! </RANT> Shawn Green Database Administrator Unimin Corporation - Spruce Pine
