Understanding MySQL's ONLY_FULL_GROUP_BY mode

Older versions of MySQL would let you write a query where you could group by one column but then include values from non-grouped columns in the select statement.

The SQL specification never allowed for this, but MySQL was a bit more permissive, at least until version 5.7. In that version, MySQL started to enforce the SQL standard more strictly, which meant that queries which previously worked might now throw an error.

This behavior is behind a SQL mode called ONLY_FULL_GROUP_BY, which means you could turn it off to restore the old behavior, but I want to talk about why you should go through the effort to fix your queries.

Let's work through an example. To make it concrete, here's some sample data:

Before moving on, notice how each message has a unique id, but they are all part of the same thread_id. Also note that each thread only has two participants, a sender_id and a receiver_id, although the role flips depending on who sent the message.

Now let's look at the original query that might have worked before MySQL 5.7:

-- original query
SELECT *, max(id) as most_recent
FROM messages
GROUP BY thread_id
ORDER BY most_recent DESC;

With our example data, there are 4 messages within the same thread, but when we group by thread_id, we expect to get only one row for that thread.

So in that one grouped row, thread_id will be 1, and most_recent will be 4, which is the maximum id in that thread.

But since we're using * to select all columns, what is the properly grouped value for the other columns, like sender_id, receiver_id, and content? Because those fields aren't in the group logic or wrapped in an aggregate function, there's no one true answer and MySQL would essentially return the "first" row, which is not always what you expect. In this context, "first" means "whichever row the engine happens to pull first", which is often, but not always, the one found earliest in the index.

So how do we fix it?

The first thing you should be asking is "How did this ever work in the first place?" Well, if our code actually worked with those arbitrarily selected results in the other columns, this tells me that it either wasn't using the other columns, or the data is predictable enough between rows in the group that it doesn't matter which one is returned.

In our case, it was a little bit of both. So we can use that knowledge to rewrite the query to be more explicit about what we want.

We definitely don't need the id column in the result set, since we already have most_recent, which is the maximum id for that thread. We also don't need content, since we aren't displaying the message content in this query. We only need to know who the two participants in the thread are, and the concept of sender and receiver doesn't directly apply.

Consider this revised query:

-- revised query
SELECT thread_id, 
       MAX(id) AS most_recent, 
       MIN(sender_id) AS participant_1, 
       MAX(receiver_id) AS participant_2
FROM messages
GROUP BY thread_id
ORDER BY most_recent DESC;

This new query isn't violating the SQL standard, it's not including columns that aren't used by code, and we're being explicit about how to pick the participants in the thread. Because every row in a thread has exactly the same pair of user IDs, applying MIN() to one column and MAX() to the other guarantees we return those two distinct IDs, no matter which participant was sender or receiver on any given message.

If you've disabled ONLY_FULL_GROUP_BY in your MySQL configuration, I encourage you to take a fresh look at your queries and see if you can take a similar approach to simplify them and make them more explicit.

Here to help,

Joel

P.S. And if you need help to fix this across your project, we can help.