When would you use MySQL's ANY_VALUE() function?

In a previous tip, I made the case for why you should keep MySQL's ONLY_FULL_GROUP_BY mode enabled, and how to fix queries that don't comply with it.

In today's tip, I want to add one more tool to your toolbelt when dealing with ambiguous columns in a GROUP BY query: the ANY_VALUE() function.

MySQL does its best to ensure that every column in a SELECT statement is either part of the GROUP BY clause, wrapped in an aggregate function, or is functionally dependent on one of the grouped columns.

These three scenarios cover most cases, but you may run into some situations where you actually know better than the database engine.

For example, let's say you have a denormalized pending_orders table, with columns for customer_id, and customer_email.

This query would fail in ONLY_FULL_GROUP_BY mode:

SELECT  customer_id,
        customer_name,
        COUNT(*) AS order_count
FROM    pending_orders
GROUP BY customer_id;

Within this transient pending_orders table, we know that every customer_id will map to the same customer_name, but this is not expressed as a unique index, so MySQL isn't aware of that functional dependency between the two columns.

We could "make the query happy" by including customer_name in the GROUP BY clause. It would still give us the correct result set, but it is technically unnecessary and comes with a slight performance penalty.

We could add the unique index to let it know about the functional dependency, but it would really serve no other purpose, and it would add ongoing overhead to the database to maintain that index.

Instead, in this fairly rare scenario, my preferred approach is to use the ANY_VALUE() function to explicitly tell MySQL that I know what I'm doing:

SELECT  customer_id,
        ANY_VALUE(customer_name) AS customer_name,
        COUNT(*) AS order_count
FROM    pending_orders
GROUP BY customer_id;

ANY_VALUE() is an aptly-named function that tells the database engine you don't care which value it returns for that column, as long as it is one of the values in the group. This is a safe way to bend the rules of ONLY_FULL_GROUP_BY mode, and it makes your intent clear to anyone reading the code, without jumping through extra hoops for the database engine.

Here to help,

Joel

P.S. Would you like some help moving your project to a more strict MySQL mode? Don't keep putting it off, let's see how we can help.