Fixed #34262 -- Added support for AnyValue for SQLite, MySQL, Oracle, and Postgresql 16+.

Thanks Simon Charette for the guidance and review. Thanks Tim Schilling for the
documentation review. Thanks David Wobrock for investigation and solution proposals.

1 files changed, 62 insertions, 0 deletions

diff --git a/docs/topics/db/aggregation.txt b/docs/topics/db/aggregation.txt
index cd53f73c74..bd90c4b5fe 100644
--- a/docs/topics/db/aggregation.txt
+++ b/docs/topics/db/aggregation.txt
@@ -679,3 +679,65 @@ no books can be found:
 Under the hood, the :ref:`default <aggregate-default>` argument is implemented
 by wrapping the aggregate function with
 :class:`~django.db.models.functions.Coalesce`.
+
+.. _aggregation-mysql-only-full-group-by:
+
+Aggregating with MySQL ``ONLY_FULL_GROUP_BY`` enabled
+-----------------------------------------------------
+
+When using the ``values()`` clause to group query results for annotations in
+MySQL with the ``ONLY_FULL_GROUP_BY`` SQL mode enabled, you may need to apply
+:class:`~django.db.models.AnyValue` if the annotation includes a mix of
+aggregate and non-aggregate expressions.
+
+Take the following example:
+
+.. code-block:: pycon
+
+    >>> from django.db.models import F, Count, Greatest
+    >>> Book.objects.values(greatest_pages=Greatest("pages", 600)).annotate(
+    ...     num_authors=Count("authors"),
+    ...     pages_per_author=F("greatest_pages") / F("num_authors"),
+    ... ).aggregate(Avg("pages_per_author"))
+
+This creates groups of books based on the SQL column ``GREATEST(pages, 600)``.
+One unique group consists of books with 600 pages or less, and other unique
+groups will consist of books with the same pages. The ``pages_per_author``
+annotation is composed of aggregate and non-aggregate expressions,
+``num_authors`` is an aggregate expression while ``greatest_page`` isn't.
+
+Since the grouping is based on the ``greatest_pages`` expression, MySQL may be
+unable to determine that ``greatest_pages`` (used in the ``pages_per_author``
+expression) is functionally dependent on the grouped column. As a result, it
+may raise an error like:
+
+.. code-block:: pytb
+
+    OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY
+    clause and contains nonaggregated column 'book_book.pages' which is not
+    functionally dependent on columns in GROUP BY clause; this is incompatible
+    with sql_mode=only_full_group_by")
+
+To avoid this, you can wrap the non-aggregate expression with
+:class:`~django.db.models.AnyValue`.
+
+.. code-block:: pycon
+
+    >>> from django.db.models import F, Count, Greatest
+    >>> Book.objects.values(
+    ...     greatest_pages=Greatest("pages", 600),
+    ... ).annotate(
+    ...     num_authors=Count("authors"),
+    ...     pages_per_author=AnyValue(F("greatest_pages")) / F("num_authors"),
+    ... ).aggregate(Avg("pages_per_author"))
+    {'pages_per_author__avg': 532.57143333}
+
+Other supported databases do not encounter the ``OperationalError`` in the
+example above because they can detect the functional dependency. In general,
+``AnyValue`` is useful when dealing with select list columns that involve
+non-aggregate functions or complex expressions not recognized by the database
+as functionally dependent on the columns in the grouping clause.
+
+.. versionchanged:: 6.0
+
+    The :class:`~django.db.models.AnyValue` aggregate was added.