| Bug #14920 | Ordering aggregated result sets with composite primary keys corrupts resultset | ||
|---|---|---|---|
| Submitted: | 14 Nov 2005 12:01 | Modified: | 2 Dec 2005 3:48 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.15, 5.0.17-BK | OS: | Linux (Linux) |
| Assigned to: | Timour Katchaounov | CPU Architecture: | Any |
[24 Nov 2005 8:10]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32645
[24 Nov 2005 16:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32689
[30 Nov 2005 10:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32855
[1 Dec 2005 15:59]
Timour Katchaounov
Fixed in 5.0.17
[2 Dec 2005 3:48]
Paul DuBois
Noted in 5.0.17 changelog.
Description: When aggregate function is applied on a resultset made of table with composite primary key, ORDER BY corrupts resultset, by replacing one row with another. Without ORDER BY correct result set is returned. If SELECT .. FROM ( SELECT ... GROUP BY ... ) ORDER BY is done - correct result set is returned. How to repeat: Did try with VARCHAR/VARBINARY/INT, MyISAM, InnoDB, 5.0.15, 5.0.17-BK. Could not reproduce on 4.x versions. VARCHAR field size did actually matter, at different configurations, different offsets would make MySQL behave right/erroneous. mysql> show create table y \G *************************** 1. row *************************** Table: y Create Table: CREATE TABLE `y` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into y values (1,1),(1,2),(1,3),(4,5),(4,2),(5,1),(5,4),(7,1),(7,7); Query OK, 9 rows affected (0.00 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select a,min(b) m from y group by a order by m; +---+------+ | a | m | +---+------+ | 5 | 1 | | 7 | 1 | <-------- there's no a=1 and double a=7 | 7 | 1 | | 4 | 2 | +---+------+ 4 rows in set (0.01 sec) mysql>