MySQL Bugs: #80131: The 'group' statement has changed it's behaviour in 5.7.10

Bug #80131	The 'group' statement has changed it's behaviour in 5.7.10
Submitted:	24 Jan 2016 21:05	Modified:	1 Feb 2016 15:43
Reporter:	sammy esmail	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.7.10	OS:	Windows (i)
Assigned to:		CPU Architecture:	Any

Description:
The select statement using "group by" used to return the top element in a list. This was a very useful feature for selecting the top or bottom of each list in a group. This is not doing this anymore.

Given thousands of serial numbers for cards being tested in the factory, some of which have been tested several times, I am usually interested in finding the last test run or the first test run for each card.

How to repeat:
So I use to write a simple Query like:

select * from (
select * from db.cards order by udate desc, utime desc)
group by serial_number;

This always returned the first record in the list for each list of a given serial_number. Not it returns the last. Using ascending also returns the last.

The database contains one or more records for each serial number.

Suggested fix:
We do tons of RF testing. To judge the quality of our work, we like to measure the first pass. Sometimes we are only interesting in measuring and analyzing the results of the last runs.  We've developed a large number of queries embedded in our dashboard using queries depending on this group by behavior.

Is there a way to allow compatibility with 5.6?

Or do we revert back MySQL to 5.6?

The behaviour of "group by" in 5.7.10 has changed from 5.6.28. "group by xyz" extracted the first element from each group based on entry into the database. However, this seems not to work on "group b" operating on a subquery like:

select * from (
select * from table group by date desc, time desc
) as A group by some_field.

The new table that group should operate on should be 'A' and not 'table'. "group by" rather still operates table.

It has been brought to my attention that the reference manual states that it is "indeterminate" as to which member of the list is returned by the "group by". All I can state is that it is a very nice feature it is was made determinate. It seems that it was very deterministic since 5.1.

So this is not bug but the right of MySQL to exercise its right to change the behavior.

Hi Sammy,

Yes, you are quite right. This is a change of behavior introduced in 5.7. So, if you need your results ordered, just use ORDER BY, after GROUP BY. This change was made in order to keep in line with ANSI SQL standards.

Not a bug.