[Bug 247727] [NEW] mysql ignores view order when selecting with group by

[Derek Price]

Public bug reported:

Binary package hint: mysql-server

I already reported this to MySQL
<http://bugs.mysql.com/bug.php?id=38061&thanks=4>.

----- Begin <http://bugs.mysql.com/bug.php?id=38061&thanks=4> -----
Description:
Under 5.0.51a, SELECT w/GROUP BY ignores the order of an underlying view.  Under 5.0.22,
it did not.

How to repeat:
For example, using the following simple DB in both MySQL versions:

DROP TABLE IF EXISTS `source`;
CREATE TABLE `source` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `data` varchar(45) NOT NULL,
  `subkey` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO source (`id`,`data`,`subkey`) VALUES 
 (1,'a',1),
 (2,'b',1);

DROP TABLE IF EXISTS `source_view`;
DROP VIEW IF EXISTS `source_view`;
CREATE VIEW `source_view` AS SELECT * FROM source ORDER BY id DESC;

Under 5.0.22, "SELECT * FROM source_view GROUP BY subkey;" yields:

2, 'b', 1

Under 5.0.51a, the same query yields:

1, 'a', 1

Suggested fix:
A simple workaround for the problem as presented above is to skip the intermediate view
entirely and just implement it as a subselect, but there is no workaround if the breaking
select demonstrated above was implemented in a second view, since views do not allow
subselects.

I think that restoring the 5.0.22 behavior would be the right thing to do.
----- End <http://bugs.mysql.com/bug.php?id=38061&thanks=4> -----

** Affects: mysql-dfsg-5.0 (Ubuntu)
     Importance: Undecided
         Status: New

-- 
mysql ignores view order when selecting with group by
https://bugs.launchpad.net/bugs/247727
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to mysql-dfsg-5.0 in ubuntu.