Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the
following command:

select count(*) 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
= '1' and p2c.categories_id = '1'

give the following error:

ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'

What's wrong with that command?
And why it worked correctly under 4.0?


I tried to eliminate the aliases and use directly the real tables names
but nothing changed.

Obviously the column exists, the following command works:

select products.products_id from products



Thanks.

--
__________________________________________________ _
__
|- giannici@neomedia.it
|ederico Giannici http://www.neomedia.it
__________________________________________________ _

Try not mixing left join and comma-joins, and use an INNER JOIN keyword
between "m.manufacturers_id, products_to_categories"

Baron

Federico Giannici wrote:
> Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the
> following command:
>
> select count(*) 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
> = '1' and p2c.categories_id = '1'
>
> give the following error:
>
> ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'
>
> What's wrong with that command?
> And why it worked correctly under 4.0?
>
>
> I tried to eliminate the aliases and use directly the real tables names
> but nothing changed.
>
> Obviously the column exists, the following command works:
>
> select products.products_id from products
>
>
>
> Thanks.
>

Hi Frederico,
the precedence between the comma-operator and JOIN changed
with 5.0.12.
See http://dev.mysql.com/doc/refman/5.0/en/join.html
Excerpt from that article:
Previously, the comma operator (,) and JOIN both had the same
precedence, so the join expression t1, t2 JOIN t3 was interpreted as
((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is
interpreted as (t1, (t2 JOIN t3)). This change affects statements that
use an ON clause, because that clause can refer only to columns in the
operands of the join, and the change in precedence changes
interpretation of what those operands are.

Example:
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2
as (t1,t2). Now the JOIN takes precedence, so the operands for the ON
clause are t2 and t3. Because t1.i1 is not a column in either of the
operands, the result is an Unknown column 't1.i1' in 'on clause' error.
To allow the join to be processed, group the first two tables explicitly
with parentheses so that the operands for the ON clause are (t1,t2) and t3:
End excerpt.

/Johan

Federico Giannici skrev:
> Since we upgraded from MySQL 4.0 to 5.0 (under OpenBSD 4.1 amd64) the
> following command:
>
> select count(*) 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
> = '1' and p2c.categories_id = '1'
>
> give the following error:
>
> ERROR 1054 (42S22): Unknown column 'p.products_id' in 'on clause'
>
> What's wrong with that command?
> And why it worked correctly under 4.0?
>
>
> I tried to eliminate the aliases and use directly the real tables names
> but nothing changed.
>
> Obviously the column exists, the following command works:
>
> select products.products_id from products
>
>
>
> Thanks.
>