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?
1522
November 14, 2019 02:29PM


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.