MySQL Bugs: #36189: Order by is not being evaluated when a group by exists on the primary key

Bug #36189	Order by is not being evaluated when a group by exists on the primary key
Submitted:	17 Apr 2008 22:44	Modified:	9 Jul 2008 20:24
Reporter:	Brett Garland	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Linux (RHES 4 and RHES 5)
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, order by, primary key

Description:
Last week, I upgraded most of our servers to 5.0.51a from 5.0.45 ( I left one dev server at 5.0.45).  Now my developers are seeing a problem with queries using a group by on the primary key with order by on another column.  I checked this on 5.0.45 and it worked fine. 

How to repeat:
CREATE TABLE `test2` (
  `id` int(11) NOT NULL auto_increment,
  `SomeText` varchar(30) default NULL,
  `SomeDate` datetime NOT NULL,
  `CreationDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ModificationDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

Insert some date...
  I'll put it the data in the attachment since it's too long (~250 records).

Query that causes issues in 5.0.51a...
select id,somedate from test2 group by id order by somedate desc;

Sample output...
| 203 | 2007-12-03 00:00:00 | 
| 204 | 2007-12-04 00:00:00 | 
| 205 | 2007-11-29 00:00:00 | 
| 206 | 2007-11-30 00:00:00 | 
| 207 | 2007-12-01 00:00:00 | 
| 208 | 2007-12-02 00:00:00 | 
| 209 | 2007-12-13 00:00:00 | 
| 210 | 2007-12-06 00:00:00 | 
| 211 | 2007-12-07 00:00:00 | 
| 212 | 2007-12-08 00:00:00 | 
| 213 | 2007-12-09 00:00:00 | 
| 214 | 2007-12-13 00:00:00 |

Query that works fine in 5.0.51a
select creationdate,somedate from test2 group by creationdate order by somedate desc;

Is there anything esle I can give you?

Thanks

Sample Data

Attachment: sample_data.txt (text/plain), 20.81 KiB.

Thank you for a problem report. Please, send the results of:

explain select id,somedate from test2 group by id order by somedate desc;

on your system.

Here ya go.

mysql> explain select id,somedate from test2 group by id order by somedate desc;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |  100 | Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Have the same bug on 5.0.51a. And everything works fine on 5.0.27.

Query "select * from `services_categories` where `parent` is null group by `id` order by `position` asc, `id` asc" on 5.0.51a sorts wrong (i.e., resulting order of `position` field is: 1, 14, 5, 3, ...; indeed it sorts by `id`, just if as there is no ORDER BY clause, and only a GROUP BY is).

EXPLAIN says:
1	| SIMPLE	| services_categories	| ref	| parent	| parent	| const	| 17	| Using where

And this gives "Using where; Using filesort" on 5.0.27, where all works fine.

Changing query to use some idiotic and useless join removes the bug. Query:

select SC.* from `services_categories` SC left join `services_categories` SC2 on SC.`id` = SC2.`id` where SC.`parent` is null group by SC.`id` order by SC.`position` asc, SC.`id` asc

works fine. This can be used as a work-around.

I'd like to know whether a fix for this bug is planned in 5.0 community edition? I have a lot of code which is impacted by it and changing all queries to include a 'dummy' join is not a viable work around for me.

thanks

Request to upgrade this to higher severity than Non-critical.  This is a major issue that will affect thousands of users.  Not being able to sort grouped queries correctly without adding dummy code is a sever bug.

Sorry, but I can not repeat the behaviour described with a newer version, 5.0.62:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.62-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE `test2` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `SomeText` varchar(30) default NULL,
    ->   `SomeDate` datetime NOT NULL,
    ->   `CreationDate` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `ModificationDate` timestamp NOT NULL default CURRENT_TIMESTAMP on upda
te
    -> CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test2(id, `SomeDate`) values(1, '2007-01-01');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test2(id, `SomeDate`) values(2, '2006-01-01');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test2(id, `SomeDate`) values(3, '2008-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select id,somedate from test2 group by id order by somedate desc;
+----+---------------------+
| id | somedate            |
+----+---------------------+
|  3 | 2008-01-01 00:00:00 |
|  1 | 2007-01-01 00:00:00 |
|  2 | 2006-01-01 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> explain select id,somedate from test2 group by id order by somedate desc;

+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL |    3
 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
1 row in set (0.03 sec)

So you are saying that somehow this has been fixed in version 5.0.62?