MySQL Bugs: #23462: Group BY and MAX not selecting MAX result

Bug #23462	Group BY and MAX not selecting MAX result
Submitted:	19 Oct 2006 13:42	Modified:	14 Jun 2007 14:21
Reporter:	Joseph Wilk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	mysql 5.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
SQL GROUP BY AND MAX SQL ISSUE.

Following the example from http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group.html

I'm tried to use the GROUP BY and MAX function to select the Maximum Column per Group. 

SELECT article,
       timestamp as old,
       MAX(timestamp) AS timestamp
FROM   shop_new
GROUP BY article

Using this SQL I get the result ->
article	old	timestamp
1	2	4
2	6	15
3	1	5

It appears that the the max timestamp field is correct but the old value can be seen to not match the timestamp field. Hence the articles we have been given do not have the max value. Its almost as if the max value is just added on

I would expect the result
article	old	timestamp
1	4	4
2	15	15
3	5	5

How to repeat:

CREATE TABLE `shop_new` (
  `article` int(11) default NULL,
  `timestamp` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `shop_new` (`article`, `timestamp`) VALUES 
  (1,2),
  (1,4),
  (2,6),
  (2,15),
  (3,1),
  (3,5);
COMMIT;

SELECT article,
       timestamp as old,
       MAX(timestamp) AS timestamp
FROM   shop_new
GROUP BY article

Suggested fix:
? Sorry no idea!

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html, to be precise) and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php

This is a bug, and it is fixed with adding LIMIT x (x is any number)

Sorry for previous comment, but with addition of LIMIT does not solve problem.

This is not a bug. Please, re-read the manual, http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html, carefully:

" Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

Exactly the case here.