Still problems with left joins and mysql 5.1.x

Hi folks,

I still have problems with some queries (I hope, this is the last one!) in
my highly customised osCommerce shop with mysql-5.1.x
The same query works well in mysql 4.0.x and results in total=1!

With mysql 5.1.x I get the following errormessage:
ERROR: 1054 - Unknown column 'p.products_id' in 'on clause'

This is the query:
8<--------8<--------8<--------8<--------8<--------8<--------
select count(p.products_id) as total
from
products_description pd,
shops sh,
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,
product_types pt
where
pt.product_type_id = p.product_type_id and
p.products_status = '1' and
sh.shop_id = p.shop_id and
p.products_id = p2c.products_id and
pd.products_id = p2c.products_id and
pd.language_id = '2' and
p2c.categories_id = '1769'
8<--------8<--------8<--------8<--------8<--------8<--------

As far as I could find out already, It could have something to do with the
new Server SQL Mode STRICT_TRANS_TABLES, but didn't really understand why
this would be relevant for a select or count query.

Now my question is:
How can this query be re-written to work in mysql-5.1.x ?
(Maybe not using joins? Is this possible? My tries still fail ;-))

Many thanks in advance,
It would be a very big help If someone could send me a probabely working
version for 5.1.x of this query.

Yours Henri

I have had a similar problem with joins in 5 that work ok in 4. From the
5 help file this would seem to be due the a change in the optimization
used to make it standards compliment. In your case it is dropping your
column. In my case moving the position of the join in the query got it
working in 5 but not in 4.

Henri Schomäcker wrote:
> Hi folks,
>
> I still have problems with some queries (I hope, this is the last one!) in
> my highly customised osCommerce shop with mysql-5.1.x
> The same query works well in mysql 4.0.x and results in total=1!
>
> With mysql 5.1.x I get the following errormessage:
> ERROR: 1054 - Unknown column 'p.products_id' in 'on clause'
>
> This is the query:
> 8<--------8<--------8<--------8<--------8<--------8<--------
> select count(p.products_id) as total
> from
> products_description pd,
> shops sh,
> 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,
> product_types pt
> where
> pt.product_type_id = p.product_type_id and
> p.products_status = '1' and
> sh.shop_id = p.shop_id and
> p.products_id = p2c.products_id and
> pd.products_id = p2c.products_id and
> pd.language_id = '2' and
> p2c.categories_id = '1769'
> 8<--------8<--------8<--------8<--------8<--------8<--------
>
> As far as I could find out already, It could have something to do with the
> new Server SQL Mode STRICT_TRANS_TABLES, but didn't really understand why
> this would be relevant for a select or count query.
>
> Now my question is:
> How can this query be re-written to work in mysql-5.1.x ?
> (Maybe not using joins? Is this possible? My tries still fail ;-))
>
> Many thanks in advance,
> It would be a very big help If someone could send me a probabely working
> version for 5.1.x of this query.
>
> Yours Henri
>

Ian Pawson wrote:

> I have had a similar problem with joins in 5 that work ok in 4. From the
> 5 help file this would seem to be due the a change in the optimization
> used to make it standards compliment. In your case it is dropping your
> column. In my case moving the position of the join in the query got it
> working in 5 but not in 4.

Hi Ian,

Fortunately, I got a similar answer in a german mysql newsgroup.
In my queries, there were two left joins and the other table mappings were
made by where statements.
I got the hint to replace the where mappings by inner joins which have to
stand before the left joins and NOT to separate the joins by kommata.
These queries now also work on mysql 4 & 5.
I also read the statement, that this new behaviour should/would be made
backwards-compatible again so old queries would work again. I directly
tried it with the newest mysql version but my queries didn't work.
So I edited the queries like I wrote and now everything is fine.

Many thanks for your answer.
Yours Henri