mysql ignores view order when selecting with group by
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| MySQL Server |
Unknown
|
Unknown
|
|||
| mysql-dfsg-5.0 (Ubuntu) |
Invalid
|
Undecided
|
Unassigned | ||
Bug Description
Binary package hint: mysql-server
I already reported this to MySQL <http://
----- Begin <http://
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`,`
(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://
| Derek Price (derek-ximbiot) wrote | #1 |
| Giuseppe Maxia (giuseppe-maxia) wrote | #2 |
| Giuseppe Maxia (giuseppe-maxia) wrote | #3 |
| Changed in mysql-dfsg-5.0: | |
| status: | New → Invalid |
| Chad Miller (cmiller) wrote | #4 |
| Derek Price (derek-ximbiot) wrote | #5 |
Oh, I'm using the MySQL version from Hardy Heron (8.04LTS), with latest updates:
$ lsb_release -rd
Description: Ubuntu 8.04.1
Release: 8.04
$ dpkg --status mysql-server
Package: mysql-common
...
Source: mysql-dfsg-5.0
Version: 5.0.51a-3ubuntu5.1