[MDEV-3738] LP:1029835 - Incorect results in union & subqueries Created: 2012-07-27  Updated: 2012-10-04  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Gabriel PREDA (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1029835.xml     File LPexportBug1029835_different_results.sql    

 Description   

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.

 Comments   
Comment by Gabriel PREDA (Inactive) [ 2012-07-27 ]

Re: Incorect results in union & subqueries

Comment by Gabriel PREDA (Inactive) [ 2012-07-27 ]

Table definition and data
LPexportBug1029835_different_results.sql

Comment by Gabriel PREDA (Inactive) [ 2012-07-27 ]

Re: Incorect results in union & subqueries
Correction.

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

Comment by Elena Stepanova [ 2012-07-27 ]

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.

Comment by Gabriel PREDA (Inactive) [ 2012-07-27 ]

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.

Comment by Gabriel PREDA (Inactive) [ 2012-07-27 ]

Re: Incorect results in union & subqueries
Thanx.

My fault.

Comment by Rasmus Johansson (Inactive) [ 2012-07-27 ]

Launchpad bug id: 1029835

Generated at Thu Feb 08 06:50:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.