MySQL Bugs: #35181: MySQL 5.0.51 and 51a are severely broken when a GROUP BY and ORDER BY are applie

Bug #35181	MySQL 5.0.51 and 51a are severely broken when a GROUP BY and ORDER BY are applie
Submitted:	10 Mar 2008 12:52	Modified:	19 Mar 2008 13:36
Reporter:	Tony Beyers	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.51	OS:	Linux (RHEL 5)
Assigned to:		CPU Architecture:	Any

Description:
I am a system administrator and my client sent me this:

MySQL 5.0.51 and 51a are severely broken when a GROUP BY and ORDER BY are applied to the same column and MODx unfortunately depends heavily on queries that do this.

How to repeat:

MySQL 5.0.51 and 51a are severely broken when a GROUP BY and ORDER BY are applied to the same column and MODx unfortunately depends heavily on queries that do this.

Thank you for a problem report. Please, send a complete test case that demonstrates the behaviour described.

I can confirm this bug on 5.0.51a-community-nt and I've got a simplified test case for it.

This script will create & populate the table:

--
-- Create schema test
--

CREATE DATABASE IF NOT EXISTS test;
USE test;

--
-- Definition of table `test_table`
--

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `menuindex` int(10) unsigned NOT NULL,
  `title` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test_table`
--

INSERT INTO `test_table` (`id`,`menuindex`,`title`) VALUES 
 (1,10,'Item 1'),
 (2,9,'Item 2'),
 (3,8,'Item 3'),
 (4,7,'Item 4'),
 (5,6,'Item 5'),
 (6,5,'Item 6'),
 (7,4,'Item 7'),
 (8,3,'Item 8'),
 (9,2,'Item 9'),
 (10,1,'Item 10');

If you run that, then run this query:

use test;
select * from test.test_table
group by id
order by id asc;

you will notice that it produces the same results, in the same order, as this query:

use test;
select * from test.test_table
group by id
order by id desc;

This doesn't happen on older version of MySQL (I tested it on 5.0.27-community-nt) - older versions produce the same rows, but sorted appropriately by id (asc or desc).

SQL script to create reduced test case for bug - will create test db and 1 table within.

Attachment: test_backup 20080312 1544.sql (application/octet-stream, text), 663 bytes.

Not sure if this is related to this issue, but the bug look spookily similar??:

http://bugs.mysql.com/bug.php?id=32202

Thank you for the feedback.

Yes, this bug is duplicate of bug #32202. Please wait next release.