why does this query fail in MySQL 5.1.56?

Question

The following query fails in MySQL 5.1.56:

SELECT 
shop_id, products.product_id AS
product_id, brand, title, price, image, image_width, image_height

FROM products, users LEFT JOIN

(
    SELECT fav5.product_id AS product_id, SUM(CASE 
    WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
    WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
    ELSE 0
    END) AS favorites_count
    FROM favorites fav5
    GROUP BY fav5.product_id 

) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876 AND users.user_id!=products.product_id

The error is

#1054 - Unknown column 'products.product_id' in 'on clause'

This modification without the user table does not fail:

SELECT 
shop_id, products.product_id AS
product_id, brand, title, price, image, image_width, image_height

FROM products LEFT JOIN

(
    SELECT fav5.product_id AS product_id, SUM(CASE 
    WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
    WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
    ELSE 0
    END) AS favorites_count
    FROM favorites fav5
    GROUP BY fav5.product_id 

) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876

Neither query fails in MySQL 5.0.67. (I exported the database from 5.0.67 and imported into 5.1.56 so the structure should be identical.)

The products table does have a product_id column, of type int(10). The favorites table also has a product_id column of type int(10). What is going on?

score 2 · Accepted Answer · edited May 23 '17 at 11:48

2

As easy as swap from tables order:

FROM users, products LEFT JOIN

Be careful, you are mixing join notations.

edited May 23 '17 at 11:48

Community

1
1

answered Nov 04 '12 at 20:02

dani herrera

48,760
8
117
177

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

JOIN processing operator precedence has changed in MySQL in 5.1. It's a common problem for people upgrading from 5.0 MySQL LEFT JOIN after 5.0.12 changes - How to rewrite query

edited May 23 '17 at 12:12

Community

1
1

answered Nov 04 '12 at 20:06

Gavin Towey

3,132
15
11

score 1 · Answer 3 · answered Nov 04 '12 at 20:12

This is your original query, reformatted a little and with two parentheses added:

SELECT shop_id, products.product_id AS
       product_id, brand, title, price, image, image_width, image_height
  FROM products, 
       (  -- Parenthesis added
       users LEFT JOIN
       (
            SELECT fav5.product_id AS product_id, SUM(CASE 
            WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
            WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
            ELSE 0
            END) AS favorites_count
            FROM favorites fav5
            GROUP BY fav5.product_id 
        ) AS fav6 ON products.product_id=fav6.product_id
    )  -- Parenthesis added
WHERE products.product_id= 46876 AND users.user_id!=products.product_id

The parentheses indicate how the SQL parser is interpreting the query, and there is no products table within the added parenthesis.

It is a bad idea to mix the old style and new (as in, since SQL-92) style joins.

Use:

SELECT shop_id, products.product_id AS
       product_id, brand, title, price, image, image_width, image_height
  FROM products JOIN users ON users.user_id != products.product_id
  LEFT JOIN
       (SELECT fav5.product_id AS product_id,
               SUM(CASE WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
                        WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
                        ELSE 0
                   END) AS favorites_count
          FROM favorites fav5
         GROUP BY fav5.product_id 
       ) AS fav6 ON products.product_id=fav6.product_id
 WHERE products.product_id = 46876

The != join is going to be slow (it practically a Cartesian product).

thanks this explanation was helpful. it looks like if I don't mix join notations I can avoid my original problem in the future. — jela, Nov 04 '12 at 20:28
Generally, yes. You should know of the existence of the notation using a comma-separated list of table expressions in the FROM clause so that you can understand it when you see it. You should not write that style of join unless you are forced to by outdated coding standards. Use the explicit JOIN/ON notation by default. — Jonathan Leffler, Nov 04 '12 at 20:35

why does this query fail in MySQL 5.1.56?

3 Answers3