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:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15, 5.0.17-BK OS:Linux (Linux)
Assigned to: Timour Katchaounov CPU Architecture:Any

[14 Nov 2005 12:01] Domas Mituzas
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>
[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.