MySQL Bugs: #23349: SELECT DISTINCT and ORDER BY doesn't sort results

Bug #23349	SELECT DISTINCT and ORDER BY doesn't sort results
Submitted:	16 Oct 2006 22:32	Modified:	16 Oct 2006 23:34
Reporter:	Kevin Heatwole	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21	OS:	Linux (CentOS 4.4)
Assigned to:		CPU Architecture:	Any

Description:
I use a shared hosting account to host my database and recently my scripts are not sorting database results properly.  It appears that ORDER BY no longer sorts if you used DISTINCT in the query.  I think this probably started happening when they upgraded MySQL to 4.1.21.

My scripts rely on this working and it is leading to all sorts of problems in my database (selecting the wrong data and then using the wrong data as if it were the right data).

How to repeat:
Create a simple table and insert a few test rows in a random order and then do a select distinct order by query to see that rows are not sorted.

Here is the SQL to create the test table followed by the select that doesn't work and a select that does:

CREATE TABLE `atest` (
  `id` smallint(5) NOT NULL auto_increment,
  `name` varchar(128) NOT NULL default '',
  `num` smallint(5) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `atest` (`id`, `name`, `num`) VALUES (1, 'two', 2), (2, 'one', 1), (3, 'four', 4), (4, 'three', 3);

SELECT DISTINCT * FROM atest ORDER BY num;
id  name    num
1   two 2
2   one 1
3   four    4
4   three   3

SELECT * FROM atest ORDER BY num;
id  name    num
2   one 1
1   two 2
4   three   3
3   four    4

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `atest` (
    ->   `id` smallint(5) NOT NULL auto_increment,
    ->   `name` varchar(128) NOT NULL default '',
    ->   `num` smallint(5) NOT NULL default '0',
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO `atest` (`id`, `name`, `num`) VALUES (1, 'two', 2), (2, 'one', 1),
    -> (3, 'four', 4), (4, 'three', 3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT * FROM atest ORDER BY num;
+----+-------+-----+
| id | name  | num |
+----+-------+-----+
|  2 | one   |   1 |
|  1 | two   |   2 |
|  4 | three |   3 |
|  3 | four  |   4 |
+----+-------+-----+
4 rows in set (0.01 sec)

mysql> SELECT * FROM atest ORDER BY num;
+----+-------+-----+
| id | name  | num |
+----+-------+-----+
|  2 | one   |   1 |
|  1 | two   |   2 |
|  4 | three |   3 |
|  3 | four  |   4 |
+----+-------+-----+
4 rows in set (0.00 sec)

mysql>

SELECT * FROM atest ORDER BY num;
id    name    num
2     one     1
1     two     2
4     three   3
3     four    4
SELECT DISTINCT * FROM atest ORDER BY num;
id    name    num
2     one     1
1     two     2
4     three   3
3     four    4

It works for me in our current 4.1 development source.  I would think our tests would catch this, also.  ( cd mysql-test; ./mysql-test-run.pl distinct; )

(Oops.  Collision.)