Queryset grouped by annotation with aggregates on another annotated expression crashes on MySQL with sql_mode=only_full_group_by.

[Mariusz Felisiak]

Queryset grouped by annotation with aggregates on another annotated expression crashed on MySQL with sql_mode=only_full_group_by, e.g.

    def test_group_by_nested_expression_with_params(self):
        books_qs = (
            Book.objects.annotate(greatest_pages=Greatest("pages", Value(600)))
            .values(
                "greatest_pages",
            )
            .annotate(
                min_pages=Min("pages"),
                least=Least("min_pages", "greatest_pages"),
            )
            .values_list("least", flat=True)
        )
        self.assertCountEqual(books_qs, [300, 946, 1132])

crashes with:

django.db.utils.OperationalError: (1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_django_2.aggregation_book.pages' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

[David Wobrock]

Hey there,

Took a look at what is happening and why MySQL is failing with ONLY_FULL_GROUP_BY.

In short and simplified, this statement works:

mysql> SELECT GREATEST(pages, 600), MIN(pages) FROM aggregation_book GROUP BY GREATEST(pages, 600) ORDER BY NULL;
+----------------------+------------+
| GREATEST(pages, 600) | MIN(pages) |
+----------------------+------------+
|                  600 |        300 |
|                 1132 |       1132 |
|                  946 |        946 |
+----------------------+------------+
3 rows in set (0,01 sec)

And when you try to add a third expression, that uses the two first:

mysql> SELECT GREATEST(pages, 600), MIN(pages), LEAST(MIN(pages), GREATEST(pages, 600)) AS least FROM aggregation_book GROUP BY GREATEST(pages, 600) ORDER BY NULL;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_django_tests.aggregation_book2.pages' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

MySQL is not happy, even though it seems rather straightforward that this query should work.

The resources I could find on the topic are here:

• ​A ticket from the MySQL bug tracker
• ​A related StackOverflow thread
• ​A MySQL blog post

And the blog post explains in more depth why it's not working.

---

That leaves us with a choice to make for Django's behavior I reckon :)
Some options:

1. Add an ANY_VALUE around the problematic expression

That solves the issue here for instance:

mysql> SELECT GREATEST(pages, 600), MIN(pages), ANY_VALUE(LEAST(MIN(pages), GREATEST(pages, 600))) AS least FROM aggregation_book2 GROUP BY GREATEST(pages, 600) ORDER BY NULL;
+----------------------+------------+-------+
| GREATEST(pages, 600) | MIN(pages) | least |
+----------------------+------------+-------+
|                  600 |        300 |   300 |
|                 1132 |       1132 |  1132 |
|                  946 |        946 |   946 |
+----------------------+------------+-------+
3 rows in set (0,00 sec)

However, I fear that detecting when to wrap the expression with an ANY_VALUE is a rabbit hole we don't want to go down, as we might end up implementing what the MySQL team didn't want to implement.

2. Raise awareness

We could, firstly, document the potential issue, and secondly raise a warning when such an error occurs when executing a query a Django.
That way, users are at least aware that's not entirely their or Django's fault.

3. Generally change query generation

Another type of workaround suggested by the ​MySQL blog post is to use a subquery/derived table:

mysql> SELECT greatest_pages,
           MIN(pages),
           LEAST(MIN(pages), greatest_pages) AS least
    FROM (SELECT GREATEST(pages, 600) greatest_pages,
                 pages
          FROM aggregation_book2) AS t
    GROUP BY greatest_pages
    ORDER BY NULL;
+----------------+------------+-------+
| greatest_pages | MIN(pages) | least |
+----------------+------------+-------+
|            600 |        300 |   300 |
|           1132 |       1132 |  1132 |
|            946 |        946 |   946 |
+----------------+------------+-------+
3 rows in set (0,00 sec)

So that we always try to group on a column, and not an expression.
Even though, it might be worse in terms of performances, depending the DB implementation I guess.

This change would then affect all databases I reckon, which is a much larger change, and therefore riskier.

4. Any other option! :D

I hope all of this makes sense, happy to read any thoughts on this :)
See ya!

[Simon Charette]

Thanks for the analysis David, option 1. and 2. were also the conclusion of ​my limited investigation on the subject so it's great to have cross peer validation on the subject.

I think there might be a way to implement 3. by reusing ​some of the logic used to implement masking of columns when filtering against window functions which requires two level of subquery wrapping.

A different of approaching 3. is to think that any form of masking of annotations/aliases used for grouping purposes would result in a subquery pushdown. So to reuse your example, instead of performing a subquery pushdown to compute expressions used with aggregate queries we'd do it the other way around to have MySQL group against top level SELECT expressions which it's good at inferring dependencies from

SELECT LEAST(min_pages, greatest_pages) AS `least` FROM (
  SELECT
    GREATEST(`aggregation_book`.`pages`, 600) greatest_pages,
    MIN(`aggregation_book`.`pages`) min_pages
  FROM `aggregation_book`
  GROUP BY 1 ORDER BY NULL
) masked

This should reduce the area of impact of this change to aggregating queries that group against a value that isn't explicitly selected and would also happen to solve the last remaining known issue with using server-side parameters binding for Postgres (#34255).

[Amir Karimi]

Replying to Simon Charette:

Thanks for the analysis David, option 1. and 2. were also the conclusion of ​my limited investigation on the subject so it's great to have cross peer validation on the subject.

I think there might be a way to implement 3. by reusing ​some of the logic used to implement masking of columns when filtering against window functions which requires two level of subquery wrapping.

A different of approaching 3. is to think that any form of masking of annotations/aliases used for grouping purposes would result in a subquery pushdown. So to reuse your example, instead of performing a subquery pushdown to compute expressions used with aggregate queries we'd do it the other way around to have MySQL group against top level SELECT expressions which it's good at inferring dependencies from

SELECT LEAST(min_pages, greatest_pages) AS `least` FROM (
  SELECT
    GREATEST(`aggregation_book`.`pages`, 600) greatest_pages,
    MIN(`aggregation_book`.`pages`) min_pages
  FROM `aggregation_book`
  GROUP BY 1 ORDER BY NULL
) masked

This should reduce the area of impact of this change to aggregating queries that group against a value that isn't explicitly selected and would also happen to solve the last remaining known issue with using server-side parameters binding for Postgres (#34255).

I'm curios to know what happened with this issue. Any updates?

[Mariusz Felisiak]

Replying to Amir Karimi:

I'm curios to know what happened with this issue. Any updates?

Feel-free to work on this issue. Please don't leave comments like any updates? they don't help and cause unnecessary noise to the history.

[Jonny Park]

I think @David Wobrock's query is easier to implement and covers more cases.

For example, if we have a followinfg queryset:

        books_qs = (
            Book.objects.annotate(greatest_pages=Greatest("pages", Value(600)))
            .values(
                "greatest_pages",
            )
            .annotate(
                min_pages=Min("pages"),
                least=Least("min_pages", "greatest_pages"),
            )
        )

Creating the following query that @David Wobrock presented seems like more sense to me and covers many other cases.

SELECT greatest_pages,
           MIN(pages),
           LEAST(MIN(pages), greatest_pages) AS least
    FROM (SELECT GREATEST(pages, 600) greatest_pages,
                 pages
          FROM aggregation_book2) AS t
    GROUP BY greatest_pages
    ORDER BY NULL;

If we were to take @Simon Charette's query, it could be like this:

SELECT geatest_pages, min_pages, LEAST(min_pages, greatest_pages) AS `least` FROM (
  SELECT
    GREATEST(`aggregation_book`.`pages`, 600) greatest_pages,
    MIN(`aggregation_book`.`pages`) min_pages
  FROM `aggregation_book`
  GROUP BY 1 ORDER BY NULL
) masked

I think the position of "MIN(aggregation_book.pages) min_pages" looks awkward.
With .values_list("least", flat=True) clause present, there was a obvious reason for "MIN(aggregation_book.pages) min_pages" to be pushed down because it is a dependency for least, but without .values_list("least", flat=True) it loses it's reason to be pushed down.
I am a bit suspicious that choosing which additional item to be pushed down by looking at values_list worth it's effort considering frequency of this use case is thought to be small.

[Simon Charette]

FWIW this relates to #34992 where we had to disable allows_group_by_selected_pks on MariaDB entirely as it doesn't implement any form of functional dependence resolition.

[Simon Charette]

​A recent article on any_value and functional dependency if it can be of help to anyone working on this issue.

[ontowhee]

Is there an expression in Django for ANY_VALUE()? I did a quick search for "anyvalue” and “any.*value” but it did not come up with results. Would it be useful to support such an expression?

My thought is, option 2 seems to be the lowest effort and risk to implement (the other options can potentially be added in later if there is a good solution). It can raise an error and suggest that the user apply such an expression to the offending column. This way, django is not making an arbitrary decision on wrapping the column with ANY_VALUE(). I haven’t dived into what it would take to support such an expression, so this may be naive. Any thoughts here?

[Simon Charette]

There's none but you can ​easily write your own and circumvent the problems MySQL exhibits

from django.db.models.aggregate import Aggregate

class AnyValue(Aggregate):
    function = "ANY_VALUE" 

Given MySQL, Postgres 16+, and Oracle 19c+ ​support it it might be worth considering adding it to core and documenting that it must be used under some circumstances on MySQL? It can also be useful under other circumstances on Postgres and Oracle which adhere more closely to the spec regarding GROUP BY rules.

[ontowhee]

Should a new ticket be created for adding support for ANY_VALUE()? For MySQL, since ANY_VALUE() is not an aggregate function, would it be implemented as an Aggregate or Func or a different expression type?

Just thinking out loud here. If option 2 is the path forward,

• Would the warning be raised before the query is evaluated? That means the query needs to detect if there are nonaggregated expressions. I'll need to dig more to understand how that might work.
• Or, raise the warning after the query is evaluated by catching the OperationalError in the ​CursorWrapper?

    codes_for_warnings = (
        1055,  # Expression not in GROUP BY and contains nonaggregated colum
    )
    ...

    def execute(self, query, args=None):
        try:
            # args is None means no string interpolation
            return self.cursor.execute(query, args)
        except Database.OperationalError as e:
            # Map some error codes to IntegrityError, since they seem to be
            # misclassified and Django would prefer the more logical place.
            if e.args[0] in self.codes_for_integrityerror:
                raise IntegrityError(*tuple(e.args))
            else if e.args[0] in self.codes_for_warnings:
                warnings.warn(
                    "%s "
                    "Consider wrapping the nonaggregated expression using AnyValue." %(e),
                    RuntimeWarning,
                )
                return
            raise

I'm going to dig around and see if the warning can be raised before the query is evaluated. I might start looking through the resolve_expression() functions, since there seem to be patterns of raising errors there.

[Simon Charette]

Should a new ticket be created for adding support for ANY_VALUE()?

I think this that this ticket can be re-purposed for the introduction of AnyValue(Aggregate) given that the reported problem here is that MySQL query planer isn't smart enough to transitively infer the presence of members in the GROUP BY clause and that explicit usage of ANY_VALUE solves that.

For MySQL, since ANY_VALUE() is not an aggregate function, would it be implemented as an Aggregate or Func or a different expression type?

It's effectively not an aggregate function per-se on MySQL but more of a sentinel to tell the query planner to ignore the only_full_group_by check only for a single expression but in the Django sense it must be defined as an Aggregate subclass otherwise it will be included in the GROUP BY clause. In other words, it should be implemented as an Aggregate subclass even if MySQL doesn't follow the SQL spec by clearly defining what kind of function ANY_VALUE is.

Just thinking out loud here. If option 2 is the path forward,

I think that raising awareness is the way to go here but I don't think that the proposed implementation of capturing errors and emitting warning is the way to go here. First because it's brittle given we don't know the extent of this problem and the MySQL implementation could change and secondly because it's not an approach we've taken with this class of problems.

IMO this problem is very similar to how we let inappropriate casting errors bubble through and expect users to use CAST where necessary from the error messages over trying to detect such errors and point them directly at django.db.models.functions.Cast. In other words I think that it's better to have the user go through the following chain of thoughts

• Encounter type / only_full_group_by error
• Do their research on the subject and learn about CAST / ANY_VALUE
• Search for Django CAST / ANY_VALUE and endup on the Cast / AnyValue docs

than committing to building a bullet proof solution that hides away these details from users. For all we know MySQL could finally implement functional dependency detection properly and all of our efforts (and bugs trying to get it right but failing) would be wasted efforts.

For these reasons I believe that raising awareness is effectively the way to go but I think the way to do so is

1. Introduce an AnyValue(Aggregate)
2. Make sure the documentation mentions that its usage might be necessary on MySQL when mixing aggregate and non-aggregate functions when sql_mode=only_full_group_by in a .. note
3. Link to AnyValue documentation from the ​aggregating annotations section of the docs

[ontowhee]

Thank you so much for the detailed clarifications! It helps me understand the context. The chain of thoughts for the user that you outlined makes a lot of sense, and it is what I would prefer in my own workflow, too.

---

I have WIP in this ​branch. I’ve added AnyValue, along with some tests.

One thing I'm not sure about is, in MySQL, the expression ANY_VALUE(LEAST(MIN(pages), GREATEST(pages, 600))) is valid, but creating the expression in django with AnyValue(Least("min_pages", "greatest_page")) leads to the following error during the resolving phase,

Cannot compute AnyValue(’Least(F(min_pages), F(greatest_pages))’): ‘Least(F(min_pages), F(greatest_pages))’ is an aggregate

For Postgresql and Oracle, it makes sense to raise this error, because ANY_VALUE is considered an aggregate function, and nested aggregates are not allowed. However, for MySQL, would it make sense to not raise the error? Would that mean changing how ​resolve_expression is checking the contains_aggregate attribute? The resolving phase is backend agnostic. Trying to catch it in the compilation phase in as_mysql() would be too late, because the error would have already been raise. I don't have a clear path forward at this point, but will dig around and think about this more. Any ideas and suggestions are welcomed, of course!

---

I'm also a bit stuck on understanding how to write the SQL statement for ANY_VALUE(…) FILTER(…) OVER(…). I was running statements directly in MySQL to better understand the different combinations of the FILTER, OVER, GROUP BY clauses, trying add support according to the chart in the ​article, but I'm getting syntax errors.

Here is a simple example I was running,

CREATE TABLE person(id int, first_name varchar(255), dob date);
INSERT INTO person(id, first_name, dob)
VALUES(1, 'alice', '2025-01-01'),
(2, 'alice', '2024-02-02'),
(3, 'alice', '2025-03-03'),
(4, 'bob', '2025-04-04'),
(5, 'charlie', '2025-05-05');

These statement worked in MySQL.

SELECT ANY_VALUE(id) FROM person;
SELECT ANY_VALUE(id) FROM person GROUP BY first_name;

These statements did not work. They returned a syntax error. I expected the last one, ANY_VALUE(..) FILTER(...) OVER(...), to be valid, according to the chart, but was surprised to see the syntax error. All the statements work in Postgresql.

SELECT ANY_VALUE(id) OVER() FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) OVER() FROM person;
SELECT ANY_VALUE(id) FILTER(WHERE id > 1) OVER(partition by first_name) FROM person;

MySQL does not support ​FILTER clause, and django will emulate using ​CASE instead. I wonder if the chart in the article is accurate, or if the FILTER clause in ANY_VALUE is a special case.

If anyone can see what I'm doing wrong here, and give an example of a valid statement with ANY_VALUE(..) FILTER(...) OVER(...), that would be helpful!

[ontowhee]

​Draft PR

I opened a draft PR. There are some CI test failures, but I would love to get someone's opinion on to make sure this is going in the right direction. This table shows what is implemented. It strays a bit from the ​chart:

+-----------------------------+---------------+------------+------------+
|                             | MySQL         | Oracle     | PostgreSQL |
+-----------------------------+---------------+------------+------------+
| ANY_VALUE()                 | Yes           | Yes        | Yes        |
| ANY_VALUE() FILTER()        | No, uses CASE | No         | Yes        |
| ANY_VALUE() OVER()          | No            | Yes        | Yes        |
| ANY_VALUE() FILTER() OVER() | No, uses CASE | No         | Yes        |
+-----------------------------+---------------+------------+------------+

• The over clause has not been implemented for AnyValue(). The tests are using the Window function and partition_by parameter to achieve this.
  • Question: Is this a good approach? Or should AnyValue() implement over clause?
• The filter clause is leveraging the existing filter implementation for expressions. For MySQL, this means it creates CASE() instead of FILTER(). MySQL does not support ​filter clause.
• The chart in the article shows a lightning bolt MySQL. I may be misunderstanding what that means, but I have not been able to directly run a “ANY_VALUE() FILTER() OVER()” expression. I tried to keep the scope of this ticket simple by not addressing this one for now.
• For the specific case described in this ticket, instead of creating the django expression AnyValue(Least("min_pages", "greatest_page")) , the tests are creating Least("min_pages", AnyValue("greatest_page")). It wraps AnyValue() on "greatest_page" instead of the entire expression. This avoids the error that is raised Cannot compute ... is an aggregate error in resolve_expression, and it seems to make sense now that all the columns have an "aggregate" function operating on them.