MySQL Bugs: #2898: Unexpected result: max() with group by

Bug #2898	Unexpected result: max() with group by
Submitted:	20 Feb 2004 0:21	Modified:	20 Feb 2004 12:11
Reporter:	Kevin Leung	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1-alpha	OS:	FreeBSD (FreeBSD 4.9-STABLE)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
select product_id, max(sale_date) as s_date, sale_price, sale_offer from Sale group by product_id;

product_id, and max(sale_date) contains the correct result, but "Group by" cannot figure out correct sale_price and sale_offer.

How to repeat:
create table Sale (
	product_id char(7) not null,
	sale_date datetime not null,
	sale_price decimal(7,2) unsigned not null,
	sale_offer decimal(0,2) not null,
	primary key (product_id, sale_date)
);

insert into Sale values ('PRO0001', FROM_UNIXTIME(1071837194), 0  , 0.10);
insert into Sale values ('PRO0001', FROM_UNIXTIME(1071937194), 0  , 0.02);

select product_id, max(sale_date) as s_date, sale_price, sale_offer from Sale group by product_id;

Output:
========
+------------+---------------------+------------+------------+
| product_id | s_date              | sale_price | sale_offer |
+------------+---------------------+------------+------------+
| PRO0001    | 2003-12-21 00:19:54 |       0.00 |       0.10 |
+------------+---------------------+------------+------------+

Expected Result:
=================
+------------+---------------------+------------+------------+
| product_id | s_date              | sale_price | sale_offer |
+------------+---------------------+------------+------------+
| PRO0001    | 2003-12-21 00:19:54 |       0.00 |       0.02 |
+------------+---------------------+------------+------------+

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

MySQL extends GROUP BY to allow you to SELECT columns that are not part of the GROUP BY and are not used in aggregate functions, but this comes with the side-effect that you have no control over which row is used to produce the values for those columns when there is more than one row which could provide the value.

See:

http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.htm

You can sidestep this with subqueries and such...