[MDEV-18914] GROUP BY with ORDER BY Created: 2019-03-13  Updated: 2019-03-13  Resolved: 2019-03-13

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.1.38
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Laszlo Holubetz Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

MyISAM

Attachments: File test.sql    

 Description   

sample data: test table
id, name, date
1, John, 2019-03-10 16:28:58
2, john, 2019-03-12 16:29:08

SELECT *
FROM
(
SELECT *
FROM test
ORDER BY DATE DESC
) a
GROUP BY a.name

The result in:
MySQL 5.1.41
2, john, 2019-03-12 16:29:08 - correct answer

10.1.37-MariaDB-cll-lve - MariaDB Server
1 John 2019-03-10 16:28:58

I tried with GROUP BY DESC but result is same,



 Comments   
Comment by Elena Stepanova [ 2019-03-13 ]

https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

Same applies to MariaDB.

Generated at Thu Feb 08 08:47:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.