Re: Incorect results in union & subqueries

Table definition and data
LPexportBug1029835_different_results.sql

Re: Incorect results in union & subqueries
Correction.

Event with that optimizer_switch ONLY some of the top IDs are in correct order...

Re: Incorect results in union & subqueries
Hi,

Please check MySQL documentation in regard to exectations about ORDER BY inside UNION subqueries (http://dev.mysql.com/doc/refman/5.5/en/union.html). It is very specific about the type of query you attempt to use:

<quote>

use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

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

</quote>

The fact that it used to work as you wanted it to was just a pure luck, you rely on a non-existing feature. Luckily, in your case the solution looks fairly obvious, you just need to move the ORDER BY from the subqueries to the upper level.

Re: Incorect results in union & subqueries
Tested on Percona Server 5.5.25a-27.1, Release rel27.1, Revision 277 and it is not present there.

Re: Incorect results in union & subqueries
Thanx.

My fault.

Launchpad bug id: 1029835