MySQL Forums
Forum List  »  Quality Assurance

Order By Returning No Rows
Posted by: Damian Taylor
Date: May 21, 2012 01:45AM

Hi guys,
I'm still pretty new to MySQL so would like a second opinion on this one!
Can anyone tell me if this is a bug in MySQL or just an error in my SQL?
I have a select statement that uses the order by clause. When I run the statement, it returns no rows which I think is incorrect.
If I remove the order by clause, it returns the rows I would expect.

I know this SQL won't mean much to anyone else give you don't know what the table structures are, but here is the statement I'm running that returns no rows:

SELECT DISTINCT p.products_image, pd.products_name, m.manufacturers_name, p.products_model, p.products_quantity, p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
FROM products p
LEFT JOIN specials s on p.products_id = s.products_id
LEFT JOIN products_description pd on p.products_id = pd.products_id
LEFT JOIN manufacturers m on p.manufacturers_id = m.manufacturers_id
JOIN products_to_categories p2c on p.products_id = p2c.products_id
JOIN products_attributes p2a on p.products_id = p2a.products_id
JOIN products_options po on p2a.options_id = po.products_options_id
JOIN products_options_values pov on p2a.options_values_id = pov.products_options_values_id
WHERE p.products_status = 1
and pd.language_id = '1'
and p2c.categories_id = '17'
GROUP BY p.products_id
HAVING(FIND_IN_SET('Sizes80x32', GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, ' ', ''), pov.products_options_values_name))))
order by p.products_sort_order, pd.products_name
limit 12

If I just remove the 'order by', it works perfectly:
SELECT DISTINCT p.products_image, pd.products_name, m.manufacturers_name, p.products_model, p.products_quantity, p.products_id, p.products_type, p.master_categories_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping, p.products_qty_box_status
FROM products p
LEFT JOIN specials s on p.products_id = s.products_id
LEFT JOIN products_description pd on p.products_id = pd.products_id
LEFT JOIN manufacturers m on p.manufacturers_id = m.manufacturers_id
JOIN products_to_categories p2c on p.products_id = p2c.products_id
JOIN products_attributes p2a on p.products_id = p2a.products_id
JOIN products_options po on p2a.options_id = po.products_options_id
JOIN products_options_values pov on p2a.options_values_id = pov.products_options_values_id
WHERE p.products_status = 1
and pd.language_id = '1'
and p2c.categories_id = '17'
GROUP BY p.products_id
HAVING(FIND_IN_SET('Sizes80x32', GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, ' ', ''), pov.products_options_values_name))))
limit 12

Is this a known MySQL issue?

Thanks for looking!

Options: ReplyQuote


Subject
Views
Written By
Posted
Order By Returning No Rows
2706
May 21, 2012 01:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.