[Bug 424067] New: MySQL ignores ORDER BY when query has GROUP BY primary key

6 Sep 2008

      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.

[Bug 424067] New: MySQL ignores ORDER BY when query has GROUP BY primary key

bugzilla_noreply＠novell.com