MySQL Bugs: #24407: SELECT DISTINCT with ORDER BY does not sort correct

Bug #24407	SELECT DISTINCT with ORDER BY does not sort correct
Submitted:	17 Nov 2006 21:59	Modified:	19 Nov 2006 0:28
Reporter:	Michael Ranner	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21	OS:	FreeBSD (FreeBSD 4.11)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	distinct, order by, SELECT

Description:
After upgrading from 4.1.20 to 4.1.21 the result for
SELECT DISTINCT table.* FROM table ORDER BY a, b DESC, c, d
does not return a sorted result according to ORDER BY statement.

Afer removing DISTINCT or replacing * with a fieldname from the table the result is correctly sorted.

How to repeat:
SELECT DISTINCT table.* FROM table ORDER BY a, b DESC, c, d (result not sorted)
SELECT table.* FROM table ORDER BY a, b DESC, c, d (not sorted) (result sorted)
SELECT DISTINCT table.c FROM table ORDER BY a, b DESC, c, d (not sorted) (result sorted)

a ... INTEGER
b ... DATETIME
c ... VARCHAR2
d ... VARCHAR2

Copy paste failure in "How to repeat", please read this:

SELECT DISTINCT table.* FROM table ORDER BY a, b DESC, c, d (result IS NOT sorted)
SELECT table.* FROM table ORDER BY a, b DESC, c, d (result IS sorted)
SELECT DISTINCT table.c FROM table ORDER BY a, b DESC, c, d (result IS sorted)

Could you provide a full working example including CREATE TABLE and INSERT statements to reproduce this?

Working example from our production database

Attachment: inf_news.sql (application/octet-stream, text), 2.86 KiB.

I have attached a mysql dump of our working example. Try "SELECT * FROM inf_news ORDER BY sortierung, gueltig_von DESC, titel, untertitel" and "SELECT DISTINCT * FROM inf_news ORDER BY sortierung, gueltig_von DESC, titel, untertitel"

This is actually a duplicate of http://bugs.mysql.com/bug.php?id=21456
which was introduced in 4.1.21 and is fixed in the upcoming 4.1.22 release