MySQL Bugs: #49475: unknown column error despite parenthesizing FROM clause

Bug #49475	unknown column error despite parenthesizing FROM clause
Submitted:	5 Dec 2009 16:20	Modified:	6 Dec 2009 17:53
Reporter:	Birol Aygun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.32	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	left join, Unknown column

Description:
SQL query: 

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 =  '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id =  '4'
AND p2c.categories_id =  '134'
MySQL said: 

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

All columns exist. Has ben running on 4.1.20 and earlier versions for many years. 

How to repeat:
Enter www.ashford.com.tr.
Select "donanim" on left menu or on main frame. Make some following selections from following menus. You'll hit this or a very similar error. It's from osCommerce package.

Please, check if the following queries will work better:

SELECT COUNT( p.products_id ) AS total
FROM products_description pd 
JOIN 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 =  '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id =  '4'
AND p2c.categories_id =  '134';

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 =  '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id =  '4'
AND p2c.categories_id =  '134';

See http://dev.mysql.com/doc/refman/5.0/en/join.html for the details.

Please, check if the following queries will work better:

SELECT COUNT( p.products_id ) AS total
FROM products_description pd 
JOIN 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 =  '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id =  '4'
AND p2c.categories_id =  '134';

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 =  '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id =  '4'
AND p2c.categories_id =  '134';

See http://dev.mysql.com/doc/refman/5.0/en/join.html for the details.

I have enclosed the results of the four queries. Now I'll need to compare them and understand the differences and to relate them the results.

Thanks again.
Birol

I do not see any results enclosed or uploaded. My question remains: do you get the same error message with any of my queries?

I guess the messages didn't go through.
Queries 2 and 4 worked. The others didn't. Results below.

Query 1 result: 
MySQL said:  
#1054 - Unknown column 'p.products_id' in 'on clause'

Query 2 result: It worked. Result is total = 1:

Query 3:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM products_description pd

Query 4: It worked. Total = 1.

If you send me an e-mail to which I can reply, I can send more details.
I shall now analyze the results.

Thanks for your prompt and detailed help.

Birol

So, this is not a bug. Use query that works and see that manual page I mentioned for explanations of differences in join processing since 5.10.12.