MySQL Bugs: #3681: DISTINCT with ORDER BY does not returned ordered results

Bug #3681	DISTINCT with ORDER BY does not returned ordered results
Submitted:	6 May 2004 20:22	Modified:	17 May 2004 12:24
Reporter:	ideacode	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Data selection with DISTINCT keyword does not return ordered results when ORDER BY clause is present. Removal of the DISTINCT keyword returns ordered results. Removal of the ORDER BY clause returns results in the same order as when both DISTINCT and ORDER BY are present.

May be related to Bug #275.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  pk     INT PRIMARY KEY,
  name   VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL
);
INSERT INTO t1 VALUES
  (1, 'Gamma',     '123'),
  (2, 'Gamma Ext', '123A'),
  (3, 'Alpha',     '001'),
  (4, 'Beta',      '200C');
SELECT DISTINCT t1.name AS 'Building Name',t1.number AS 'Building Number' FROM t1 ORDER BY t1.name ASC;

# expected results:
+---------------+-----------------+
| Building Name | Building Number |
+---------------+-----------------+
| Alpha         | 001             |
| Beta          | 200C            |
| Gamma         | 123             |
| Gamma Ext     | 123A            |
+---------------+-----------------+
4 rows in set (0.00 sec)

# actual results (appear to be table order):
+---------------+-----------------+
| Building Name | Building Number |
+---------------+-----------------+
| Gamma         | 123             |
| Gamma Ext     | 123A            |
| Alpha         | 001             |
| Beta          | 200C            |
+---------------+-----------------+
4 rows in set (0.00 sec)

Suggested fix:
Possibly related to Bug #275 (in the optimizer).
Interim workaround: drop the DISTINCT keyword or use a temporary table.

Verified against 4.1.2; Only occurs when ordering by the underlying column name rather than the column alias.  Thank you for the report.

Noting that it also only occurs if the ordered column is not indexed.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

fixed in 4.1.2