MySQL Bugs: #23709: Unable to ORDER BY date with DISTINCT

Bug #23709	Unable to ORDER BY date with DISTINCT
Submitted:	27 Oct 2006 8:45	Modified:	27 Oct 2006 12:40
Reporter:	Guy Baconnière	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21	OS:	Linux (Linux, others ?)
Assigned to:		CPU Architecture:	Any
Tags:	by, distinct, order, sort, unsorted

Description:
When I try to order records by a date if disctinct is used on the query all records are sorted incorrectly.

Two workaround for now

1. Not use DISTINCT when use ORDER BY date

2. use DISTINCT but not put the PRIMARY KEY in the columns list.

How to repeat:

*** NOT SORTED DATE ?? ***

SELECT DISTINCT idNews, dteStart FROM news ORDER BY dteStart DESC;
+--------+------------+
| idNews | dteStart   |
+--------+------------+
|      1 | 2006-02-18 |
|      5 | 2006-03-06 |
|      4 | 2005-12-09 |
|      6 | 2006-05-03 |
|      7 | 2006-03-23 |
|      2 | 2006-04-15 |
|      3 | 2006-03-03 |
|      9 | 2006-05-31 |
|      0 | 2005-11-26 |
|     11 | 2006-11-01 |
|     13 | 2006-06-28 |
|     14 | 2004-02-01 |
|     15 | 2005-03-02 |
|     16 | 2006-09-21 |
|     17 | 2006-09-09 |
|     18 | 2005-10-28 |
|     19 | 2006-10-19 |
+--------+------------+
17 rows in set (0.00 sec)

*** WITHOUT DISTINCT DATE SORTED CORRECTLY ***

SELECT idNews, dteStart FROM news ORDER BY dteStart DESC;
+--------+------------+
| idNews | dteStart   |
+--------+------------+
|     11 | 2006-11-01 |
|     19 | 2006-10-19 |
|     16 | 2006-09-21 |
|     17 | 2006-09-09 |
|     13 | 2006-06-28 |
|      9 | 2006-05-31 |
|      6 | 2006-05-03 |
|      2 | 2006-04-15 |
|      7 | 2006-03-23 |
|      5 | 2006-03-06 |
|      3 | 2006-03-03 |
|      1 | 2006-02-18 |
|      4 | 2005-12-09 |
|      0 | 2005-11-26 |
|     18 | 2005-10-28 |
|     15 | 2005-03-02 |
|     14 | 2004-02-01 |
+--------+------------+
17 rows in set (0.01 sec)

*** WITHOUT PRIMARY KEY DATE SORTED CORRECTLY ***

SELECT DISTINCT dteStart FROM news ORDER BY dteStart DESC;
+------------+
| dteStart   |
+------------+
| 2006-11-01 |
| 2006-10-19 |
| 2006-09-21 |
| 2006-09-09 |
| 2006-06-28 |
| 2006-05-31 |
| 2006-05-03 |
| 2006-04-15 |
| 2006-03-23 |
| 2006-03-06 |
| 2006-03-03 |
| 2006-02-18 |
| 2005-12-09 |
| 2005-11-26 |
| 2005-10-28 |
| 2005-03-02 |
| 2004-02-01 |
+------------+

CREATE TABLE `news` (
  `idNews` int(11) NOT NULL auto_increment,
  `dteStart` date default NULL,
  PRIMARY KEY  (`idNews`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

Suggested fix:
I suspect a bug fix between 4.1.20 => 4.1.21 to have introduce this new bug not present before.

http://bugs.mysql.com/bug.php?id=16458

For SELECT ... FOR UPDATE statements that used DISTINCT or GROUP BY over all key parts of a unique index (or primary key), the optimizer unnecessarily created a temporary table, thus losing the linkage to the underlying unique index values. This caused a Result set not updatable error. (The temporary table is unnecessary because under these circumstances the distinct or grouped columns must also be unique.) (Bug#16458)

Bug found on MySQL version is 4.1.21-max-log on Linux

mysql-max-4.1.21-pc-linux-gnu-i686-icc-glibc23.tar.gz
md5 5824c2f82742de62c9e0fde5d5156ac9

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate bug #21456.

<< For maximum stability and performance, we recommend that you use the binaries we provide. >>

So we use the last binary for 4.1.x release, 4.1.21.

Do you have an estimated date for the release of 4.1.22 ?
If not we will downgrade back to 4.1.20..