This summer, I had the opportunity to participate in Google Summer of Code 2026 with the MariaDB Foundation. My accepted proposal proposed the addition of two SQL functions to MariaDB: ANY_VALUE() and GROUPING(). These are both functions present in the SQL standard (ANY_VALUE: SQL:2023 feature T626; GROUPING: SQL:2008 feature T431) as well as in MySQL, so my goal was to improve compatibility with the standard and bridge compatibility gaps with MySQL.

Throughout the journey, I learned a lot about MariaDB’s parser and optimizer internals, uncovered many interesting similiarities and differences between MariaDB and MySQL, and even found several preexisting bugs in both DBMSs. Here, I’d like to detail some of those learnings and provide a report on what’s left for my project.

`ANY_VALUE()`

In SQL, ANY_VALUE() is an aggregate value that non-deterministically returns an arbitrary value from the input set.

What’s the point?

You may ask, what would be the use of a non-deterministic function in an SQL query? At first I asked myself the same thing. But consider the table below:

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(64),
  department VARCHAR(64),
  building   VARCHAR(64),
  salary     INT
);

id	name	department	building	salary
1	Alice	Engineering	East	120000
2	Bob	Engineering	East	95000
3	Carol	Sales	West	88000
4	Dave	Sales	West	88000

Let’s suppose that in this imaginary company, each department occupies exactly one building. Then, we might want to run a query such as:

SELECT department, building, MAX(salary)
FROM employees
GROUP BY department;

To find the salary of the highest paid employee in each department, along with the building they work in.

However, when executing this query with sql_mode=only_full_group_by, MariaDB errors with the following message:

ERROR 1055 (42000) at line 11: 'db.employees.building' isn't in GROUP BY

ONLY_FULL_GROUP_BY requires that every non-aggregated column in the SELECT-list must be present in the grouping key. This makes sense: there could be many different values a nonaggregated column takes on in a particular group. Since the GROUP BY query only returns one row per group, the engine doesn’t know which value the nonaggregated column should choose in each group. By default, nondeterministic behavior is a no-no, so the optimizer blocks the query.

You might be thinking, why not then group on (department, building)? If the employees table has an index on department, but not on building, grouping by department alone can be more efficient, as the engine just walks the index in order and aggregates on the fly.

With ANY_VALUE, we can rewrite the query as follows:

SELECT department, ANY_VALUE(building), MAX(salary)
FROM employees
GROUP BY department;

MySQL output:

department	ANY_VALUE(building)	MAX(salary)
Engineering	East	120000
Sales	West	88000

ANY_VALUE signals to the server that for each department group, just return any value within that group for the building column; we don’t care which. In other words, ANY_VALUE around an ungrouped column immunizes it from the ONLY_FULL_GROUP_BY restriction.

Implementation

MySQL, COALESCE, and what ANY_VALUE really is

Naturally, the first thing I did was refer to MySQL’s source code to get a peek at how they were making ANY_VALUE work.

class Item_func_any_value final : public Item_func_coalesce {
 public:
  Item_func_any_value(const POS &pos, Item *a) : Item_func_coalesce(pos, a) {}
  Item_func_any_value(Item *a) : Item_func_coalesce(a) {}
  const char *func_name() const override { return "any_value"; }
  bool aggregate_check_group(uchar *arg) override;
  bool aggregate_check_distinct(uchar *arg) override;
  bool collect_item_field_or_view_ref_processor(uchar *arg) override;

 private:
  bool m_phase_post{false};
};

What I found funny is that MySQL essentially implements ANY_VALUE as a special case of the SQL function COALSECE with exactly one argument! It actually is a lot more reasonable than it seems at first glance. Although ANY_VALUE is technically defined as an aggregate function by the standard, all it really needs to do is tell the resolver to ignore its argument when performing the ONLY_FULL_GROUP_BY check. You can verify this by simply removing ONLY_FULL_GROUP_BY and selecting an unaggregated, ungrouped column: you’ll get an arbitrary value back. Thus, all ANY_VALUE needs to do at execution time is behave as an identity function, which you get from COALESCE for free. Sometimes the simplest solution is the right one!

MariaDB, in_sum_func, and trying to fit a square peg in a round hole

From the realization above, the logical thing to do in MariaDB is to subclass Item_func_coalesce, write some mtr tests, and call it a day. This would work and it’s the approach I took initially, but I found myself patching a dozen locations across sql_select.cc and item.cc and I couldn’t help but wonder if this was the right direction.

if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
  !outer_fixed && !thd->lex->in_sum_func &&
  select &&
  select->cur_pos_in_select_list != UNDEF_POS &&
  select->join)
{
  select->join->non_agg_fields.push_back(this, thd->mem_root);
  marker= select->cur_pos_in_select_list;
}

MariaDB handles ONLY_FULL_GROUP_BY a bit differently. When preparing the query, it proceses each item in the query recursively, and if that item is not aggregated, adds it to the non_agg_fields list. Later, when all the items are processed, it walks through non_agg_fields and checks to see if each item is present in GROUP BY. Whether the current item is aggregated or not is predicated on thd->lex->in_sum_func, a pointer to Item_sum.

So I added a new field, thd->lex->in_any_value, of type Item_func_any_value *, and patched every location with !thd->lex->in_sum_func to add && !thd->lex->in_any_value, essentially tricking the engine into thinking that an item surrounded by ANY_VALUE is aggregated. But this approach revealed multiple issues:

ANY_VALUE could be used in WHERE, which could be confused with the SQL ANY operator
ANY_VALUE couldn’t be used as a window function
ANY_VALUE wouldn’t collapse rows in a implicit grouping query (e.g. SELECT ANY_VALUE(b) FROM t should return a single row)

Also, there’s some complicated logic for handling aggregate functions in nested subqueries which I would have to copy over into ANY_VALUE. I realized that as I was trying to disguise a regular function as an aggregate, I was changing the surrounding code to accommodate a square peg in a round hole. Worse, I was slowly reimplementing all the features of Item_sum one at a time.

So I pivoted and made Item_sum_any_value a subclass of Item_sum_min_max, finally giving it the status of “aggregate function” it so desired. This made the rest of the implementation straightforward, and while it did introduce some incompatibilities with MySQL, the differing behavior was actually correct according to the standard, and so we made the decision to keep it.

This made me realize that although MariaDB originally started as a fork of MySQL, the internals have become quite different over the years, and a solution that works for MySQL won’t necessarily work for MariaDB. I think the overarching lesson here is to

My Google Summer of Code 2026

`ANY_VALUE()`

What’s the point?

Implementation

MySQL, COALESCE, and what ANY_VALUE really is

MariaDB, in_sum_func, and trying to fit a square peg in a round hole

What does non-deterministic mean, anyways?