MySQL Forums
Forum List  »  Quality Assurance

Critical bug in order by in union clause? How fix it?
Posted by: Andrei Mart
Date: November 14, 2019 02:29PM

I have two sql clauses in union, read the documentation

https://dev.mysql.com/doc/refman/5.7/en/union.html

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

this worked in version 5.6 but not worked in 5.7 and above.

My SQL is -
( select c.id, c.title, c.price, c.hits from catalog_items c
inner join catalog_items_category cic on cic.catalog_items_id = c.id where c.is_active = 1 and c.price > 0 and c.quantity > 0 and
cic.catalog_category_id = 51 order by c.price ASC )
union
( select c.id, c.title, c.price, c.hits from catalog_items c inner join catalog_items_category cic on cic.catalog_items_id =
c.id where c.is_active = 1 and c.quantity <= 0 and cic.catalog_category_id = 51 order by c.price ASC )


As result - two select*s not ordering , but in version 5.6 - it worked

Options: ReplyQuote


Subject
Views
Written By
Posted
Critical bug in order by in union clause? How fix it?
993
November 14, 2019 02:29PM


Sorry, only registered users may post in this forum.

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.