https://bugzilla.novell.com/show_bug.cgi?id=424067 Summary: MySQL ignores ORDER BY when query has GROUP BY primary key Product: openSUSE 11.0 Version: Final Platform: Other OS/Version: Other Status: NEW Severity: Major Priority: P5 - None Component: Basesystem AssignedTo: bnc-team-screening@forge.provo.novell.com ReportedBy: suse-beta@cboltz.de QAContact: qa@suse.de Found By: --- After migrating a typo3 website to a server with openSUSE 11.0, I noticed that the news feed shows the oldest instead of the newest news :-( After some googling, I found out that this is a known problem with MySQL 5.0.51, see http://www.typo3.net/index.php?id=13&action=list_post&tid=71875&page=1 SELECT tt_news.* FROM tt_news WHERE tt_news.pid IN (2) AND 1=1 AND tt_news.sys_language_uid IN (0,-1) AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 AND tt_news.hidden=0 AND (tt_news.starttime<=1220715540) AND (tt_news.endtime=0 OR tt_news.endtime>1220715540) AND (tt_news.fe_group='' OR tt_news.fe_group IS NULL OR tt_news.fe_group='0' OR (tt_news.fe_group LIKE '%,0,%' OR tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR tt_news.fe_group LIKE '-1,%' OR tt_news.fe_group LIKE '%,-1' OR tt_news.fe_group='-1')) GROUP BY tt_news.uid ORDER BY datetime DESC This query gives the same result and order when replacing "DESC" with "ASC". The bug is still reproducable with the following simplified query: SELECT tt_news.* FROM tt_news GROUP BY tt_news.uid ORDER BY datetime DESC # or ASC ------------------------------------------------------------------------------- So far, so good. But I don't expect you to do a typo3 installation just to find a MySQL bug ;-) I narrowed down the problem to a simple test table: CREATE TABLE `grouporder` ( `i` int(11) NOT NULL, `x` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `grouporder` (`i`, `x`) VALUES (1, 1), (2, 2); SELECT * FROM grouporder GROUP BY i ORDER BY x ASC -> Result: 1, 2 SELECT * FROM grouporder GROUP BY i ORDER BY x DESC -> Result: 1, 2 BTW: i must be defined as _primary key_ or _unique key_ to make this bug happen. It does not happen when I drop the primary key and create a "normal" index on the `i` column. -- Configure bugmail: https://bugzilla.novell.com/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are on the CC list for the bug.