The following query on the attached table & data yields different results in MySQL 5.5 & MariaDB 5.5:
SELECT id, date_i, date_ontop, date_until
FROM
(
SELECT id, date_i, date_ontop, date_until
FROM news
WHERE
date_i <= CURRENT_TIMESTAMP
AND
date_ontop >= CURRENT_TIMESTAMP
ORDER BY date_i DESC
) AS ontop
UNION
SELECT id, date_i, date_ontop, date_until
FROM
(
SELECT id, date_i, date_ontop, date_until
FROM news
WHERE
date_i <= CURRENT_TIMESTAMP
AND
( date_until >= CURRENT_TIMESTAMP OR date_until IS NULL)
AND
( date_ontop IS NULL OR date_ontop < CURRENT_TIMESTAMP )
ORDER BY date_i DESC
) AS therest
LIMIT 0, 18
In MariaDB 5.2 it gives the same results as MySQL 5.5.
It may be related to Bug#1010116 because if I switch optimizer_switch='derived_merge=off' it give the correct results.
Re: Incorect results in union & subqueries
Correction.
Event with that optimizer_switch ONLY some of the top IDs are in correct order...
Gabriel PREDA (Inactive)
added a comment - Re: Incorect results in union & subqueries
Correction.
Event with that optimizer_switch ONLY some of the top IDs are in correct order...
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.
Elena Stepanova
added a comment - 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.
Gabriel PREDA (Inactive)
added a comment - 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.
Bug
Re: Incorect results in union & subqueries