MySQL Bugs: #31118: MIN with Group by returns now row related data

Bug #31118	MIN with Group by returns now row related data
Submitted:	20 Sep 2007 19:33	Modified:	20 Sep 2007 19:55
Reporter:	James Coberly	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.19-beta-log MySQL Community Server	OS:	Solaris
Assigned to:		CPU Architecture:	Any
Tags:	data, GROUP, min, row

Description:
When grouping with MIN in values,  the resultset producing the MIN value is not properly associated when selecting furhter columns in the dataset.

Take a data set,  with identical product numbers, and try to select the least cost part supplier by  MIN(cost) group by product

You would think the resultset would be:
"product","MIN(cost)","supplier"
2,3.00000,"SUPB"
1,5.00000,"SUPA"

The result set returned is:
"product","MIN(cost)","supplier"
2,3.00000,"SUPA"
1,5.00000,"SUPB"

How to repeat:
CREATE TABLE  `scrap`.`test_tab` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `product` int(10) NOT NULL,
  `cost` decimal(10,5) NOT NULL,
  `supplier` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

and populate the table:

"id","product","cost","supplier"
1,2,3.50000,"SUPA"
11,2,3.00000,"SUPB"
21,1,5.50000,"SUPB"
31,1,5.00000,"SUPA"

When you run the query:
SELECT product , MIN(cost), supplier from test_tab group by product order by cost
You would think the resultset would be:
"product","MIN(cost)","supplier"
2,3.00000,"SUPB"
1,5.00000,"SUPA"

The result set returned is:
"product","MIN(cost)","supplier"
2,3.00000,"SUPA"
1,5.00000,"SUPB"

As you can see, it grabbed the appropriate MIN value,  but associated the supplier from the second line (highest cost)

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.