MySQL Bugs: #34347: ORDER BY fails if result is grouped with GROUP BY

Bug #34347	ORDER BY fails if result is grouped with GROUP BY
Submitted:	6 Feb 2008 12:29	Modified:	6 Feb 2008 13:27
Reporter:	Michael Weiss	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.51	OS:	Linux (Debian AMD64)
Assigned to:		CPU Architecture:	Any
Tags:	GROUP, order

Description:
If you do something like this

SELECT col1, col2 FROM tableX GROUP BY col1 ORDER BY col2;

the resulting rows are ordered by the group column. Older versions do not show this behaviour and sort correctly.

How to repeat:
SELECT col1, col2 FROM tableX GROUP BY col1 ORDER BY col2;

Suggested fix:
none

Thank you for the bug report. Could you please provide the complete test case;
create table, insert data, queries, actual result and expected result. Thanks
in advance.

CREATE TABLE `b_ticket_dictionary` (
  `ID` int(11) NOT NULL auto_increment,
  `FIRST_SITE_ID` char(2) collate latin1_german1_ci default NULL,
  `C_TYPE` varchar(5) collate latin1_german1_ci NOT NULL default '',
  `SID` varchar(255) collate latin1_german1_ci default NULL,
  `SET_AS_DEFAULT` char(1) collate latin1_german1_ci default NULL,
  `C_SORT` int(11) default '100',
  `NAME` varchar(255) collate latin1_german1_ci NOT NULL default '',
  `DESCR` text collate latin1_german1_ci,
  `RESPONSIBLE_USER_ID` int(11) default NULL,
  `EVENT1` varchar(255) collate latin1_german1_ci default 'ticket',
  `EVENT2` varchar(255) collate latin1_german1_ci default NULL,
  `EVENT3` varchar(255) collate latin1_german1_ci default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=21 ;

--

INSERT INTO `b_ticket_dictionary` (`ID`, `FIRST_SITE_ID`, `C_TYPE`, `SID`, `SET_AS_DEFAULT`, `C_SORT`, `NAME`, `DESCR`, `RESPONSIBLE_USER_ID`, `EVENT1`, `EVENT2`, `EVENT3`) VALUES (1, 'ru', 'C', NULL, NULL, 100, 'Îøèáêè', NULL, 0, 'ticket', NULL, NULL),
(2, 'ru', 'C', NULL, NULL, 200, 'Îïëàòà çàêàçà', NULL, 0, 'ticket', NULL, NULL),
(3, 'ru', 'C', NULL, NULL, 300, 'Äîñòàâêà çàêàçà', NULL, 0, 'ticket', NULL, NULL),
(4, 'de', 'C', NULL, NULL, 400, 'Òåõíè÷åñêèå ïðîáëåìû', NULL, 0, 'ticket', NULL, NULL),
(5, 'de', 'C', NULL, NULL, 500, 'Ðåãèñòðàöèÿ', NULL, 0, 'ticket', NULL, NULL),
(6, 'de', 'C', NULL, NULL, 600, 'Äðóãîå', NULL, 0, 'ticket', NULL, NULL),
(7, 'ru', 'K', 'low', NULL, 0, 'Íèçêàÿ', NULL, 0, 'ticket', NULL, NULL),
(8, 'ru', 'K', 'middle', NULL, 0, 'Ñðåäíÿÿ', NULL, 0, 'ticket', NULL, NULL),
(9, 'ru', 'K', 'high', NULL, 0, 'Âûñîêàÿ', NULL, 0, 'ticket', NULL, NULL),
(10, 'de', 'K', NULL, NULL, 1100, 'Download', NULL, 0, 'ticket', NULL, NULL),
(11, 'de', 'K', 'beetleju', 'N', 100, 'Beetle Ju Deluxe', 'beetleju', 0, 'ticket', NULL, NULL),
(12, 'de', 'K', 'coloreggs', 'N', 200, 'Color Eggs II', 'coloreggs', 0, 'ticket', NULL, NULL),
(13, 'ru', 'S', NULL, NULL, 100, 'Ïðèíÿòî ê ðàññìîòðåíèþ', NULL, 0, 'ticket', NULL, NULL),
(14, 'ru', 'S', NULL, NULL, 200, 'Â ñòàäèè ðåøåíèÿ', NULL, 0, 'ticket', NULL, NULL),
(15, 'ru', 'S', NULL, NULL, 300, 'Íå ïðåäñòàâëÿåòñÿ âîçìîæíûì ðåøèòü', NULL, 0, 'ticket', NULL, NULL),
(16, 'ru', 'S', NULL, NULL, 400, 'Óñïåøíî ðåøåíî', NULL, 0, 'ticket', NULL, NULL),
(17, 'de', 'S', NULL, NULL, 500, 'Request accepted', NULL, 0, 'ticket', NULL, NULL),
(18, 'de', 'S', NULL, NULL, 600, 'Problem solving in progress', NULL, 0, 'ticket', NULL, NULL),
(19, 'de', 'S', NULL, NULL, 700, 'Couldn''t be solved', NULL, 0, 'ticket', NULL, NULL),
(20, 'de', 'S', NULL, NULL, 800, 'Successfully solved', NULL, 0, 'ticket', NULL, NULL);

--

SELECT ID, C_SORT FROM b_ticket_dictionary WHERE C_TYPE='K' GROUP BY ID ORDER BY C_SORT asc

Actual result:

+----+--------+
| ID | C_SORT |
+----+--------+
|  7 |      0 |
|  8 |      0 |
|  9 |      0 |
| 10 |   1100 |
| 11 |    100 |
| 12 |    200 |
+----+--------+

expected result:

+----+--------+
| ID | C_SORT |
+----+--------+
|  7 |      0 |
|  8 |      0 |
|  9 |      0 |
| 11 |    100 |
| 12 |    200 |
| 10 |   1100 |
+----+--------+

Thank you for the report.

I can not repeat described behavior with current development sources, although bug is repeatable with verion 5.0.51. Please wait next release.