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 22:22	Modified:	17 May 2004 14:24
Reporter:	ideacode
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:
Assigned to:	Sergei Golubchik	Target Version:

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