MySQL group/order behaves differently in 5.7

Question

I have a table that looks like this:

id | text      | language_id | other_id | dateCreated
 1 | something |           1 |        5 | 2015-01-02
 2 | something |           1 |        5 | 2015-01-01
 3 | something |           2 |        5 | 2015-01-01
 4 | something |           2 |        6 | 2015-01-01

and I want to get all latest rows for each language_id that have other_id 5.

my query looks like this

SELECT *  (
    SELECT *
    FROM tbl
    WHERE other_id = 5 
    ORDER BY dateCreated DESC
) AS r 
GROUP BY r.language_id

With MySQL 5.6 I get 2 rows with ID 1 and 3, which is what I want.

With MySQL 5.7.10 I get 2 rows with IDs 2 and 3 and it seems to me that the ORDER BY in the subquery is ignored.

Any ideas what might be the problem ?

score 4 · Accepted Answer · edited May 23 '17 at 12:08

You should go with the query below:

SELECT 
*
FROM tbl
INNER JOIN 
(

    SELECT 
     other_id,
     language_id,
     MAX(dateCreated) max_date_created
    FROM tbl
    WHERE other_id = 5 
    GROUP BY language_id
) AS t 
ON tbl.language_id = t.language_id AND tbl.other_id = t.other_id AND 
   tbl.dateCreated = t.max_date_created

Using GROUP BY without aggregate function will pick row in arbitrary order. You should not rely on what's row is returned by the GROUP BY. MySQL doesn't ensure this.

Quoting from this post

In a nutshell, MySQL allows omitting some columns from the GROUP BY, for performance purposes, however this works only if the omitted columns all have the same value (within a grouping), otherwise, the value returned by the query are indeed indeterminate, as properly guessed by others in this post. To be sure adding an ORDER BY clause would not re-introduce any form of deterministic behavior.

Although not at the core of the issue, this example shows how using * rather than an explicit enumeration of desired columns is often a bad idea.

Excerpt from MySQL 5.0 documentation:

When using this feature, all rows in each group should have the same values for the columns that are omitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

MySQL group/order behaves differently in 5.7

1 Answers1