MySQL Bugs: #38061: select from view w/group by ignores view order

Thank you for your help! If the status of the bug report you submitted changes, you will be notified. You may return here and check on the status or update your report at any time. That URL for your bug report is: http://bugs.mysql.com/38061.

Bug #38061	select from view w/group by ignores view order
Submitted:	11 Jul 2008 21:16	Modified:	14 Jul 2008 2:35
Reporter:	Derek Price	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	view group by order by

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.

Thank you for the bug report.

How Giuseppe already commented:

This is expected behavior.
http://dev.mysql.com/doc/refman/5.0/en/select.html

"If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns."

"If ORDER BY occurs within a subquery and also is applied in the outer query, the outermost ORDER BY takes precedence. "

The above statements mean that you are sorting by subkey, which has duplicates. In case of two rows having the same value in the sorting column, the returned row is not guaranteed to be in any specific order.

Okay, I guess the previous version of our application was relying on undocumented/unspecified behavior in previous MySQL versions.  Anyhow, I've worked out a work around already, so it's no skin off my back.  Thanks.