Bug #45648 SELECT DISTINCT ... ORDER BY DESC returns wrong order
Submitted: 22 Jun 2009 9:20 Modified: 22 Jun 2009 9:45
Reporter: Richard Teubel Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.83 OS:Linux
Assigned to: CPU Architecture:Any
Tags: DESC, distinct, order by

[22 Jun 2009 9:20] Richard Teubel
Description:
A SELECT DISTINCT return a wrong result with using ORDER BY .. DESC.

How to repeat:
CREATE TABLE `t_distinct_desc` (
  `ID` int(11) NOT NULL auto_increment,
  `field2` varchar(20) collate latin1_german2_ci NOT NULL,
  `field3` date default NULL,
  `field4` int(11) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

INSERT INTO `t_distinct_desc` (`ID`, `field2`, `field3`, `field4`) VALUES 
  (1,'zz','2009-05-05',3),
  (2,'aa','2009-04-04',1),
  (3,'aa','2009-06-06',5),
  (4,'bb','2009-05-01',2),
  (5,'gg','2009-05-06',4);

SELECT field2
FROM t_distinct_desc
ORDER BY field3 DESC;
+--------+
| field2 |
+--------+
| aa     |
| gg     |
| zz     |
| bb     |
| aa     |
+--------+

SELECT DISTINCT field2
FROM t_distinct_desc
ORDER BY field3 DESC;

+--------+
| field2 |
+--------+
| gg     | <== first should be 'aa'
| zz     |
| bb     |
| aa     |
+--------+

ORDER BY ... ASC is ok!

SELECT field2 FROM t_distinct_desc ORDER BY field3 ASC;
+--------+
| field2 |
+--------+
| aa     |
| bb     |
| zz     |
| gg     |
| aa     |
+--------+

SELECT DISTINCT field2 FROM t_distinct_desc ORDER BY field3 ASC;
+--------+
| field2 |
+--------+
| aa     |
| bb     |
| zz     |
| gg     |
+--------+
[22 Jun 2009 9:45] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have 2 rows with field2 eq 'aa':

(2,'aa','2009-04-04',1) and  (3,'aa','2009-06-06',5). Optimizer is free to choose any of them when does DISTINCT, then order rows.

Please read at http://dev.mysql.com/doc/refman/5.1/en/select.html:

MySQL extends the use of GROUP BY to allow selecting fields that are not mentioned in the GROUP BY clause. If you are not getting the results that you expect from your query, please read the description of GROUP BY found in Section 11.12, “Functions and Modifiers for Use with GROUP BY Clauses”.