MySQL Bugs: #18011: Problem with SELECT statement

Bug #18011	Problem with SELECT statement
Submitted:	7 Mar 2006 8:22	Modified:	7 Mar 2006 23:24
Reporter:	Winfried Kaiser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	UNIX(?)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
After Changing from a version 4 server to a version 5 server on our providers system, I suddenly receive the following error from my (PHP) program (osCommerce):

1054 - Unknown column 'p.products_id' in 'on clause'

select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '2' and p2c.categories_id = '1'

The version 4 DB still works fine, the syntax seems to be correct to me.

If I drop the ", products_to_categories p2c left join specials s on p.products_id = s.products_id " part of the SQL, I receive a proper result, so it knows "p.products_id" used in other parts of the SQL-statement.

How to repeat:
Use SQL-statment on an osCommerce DB

Thank you for the bug report. The join syntax was changed since version
5.0.12, please read the Manual:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Note: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard. The following list provides more detail about several effects of the 5.0.12 change in join processing. The term “previously” means “prior to MySQL 5.0.12.”