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.

3 files changed, 120 insertions, 0 deletions

diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt
index c7e8b974a7..ef6ceb36ad 100644
--- a/docs/ref/models/querysets.txt
+++ b/docs/ref/models/querysets.txt
@@ -3943,6 +3943,60 @@ when the queryset (or grouping) contains no entries.
 Keyword arguments that can provide extra context for the SQL generated
 by the aggregate.
 
+``AnyValue``
+~~~~~~~~~~~~
+
+.. versionadded:: 6.0
+
+.. class:: AnyValue(expression, output_field=None, filter=None, default=None, **extra)
+
+    Returns an arbitrary value from the non-null input values.
+
+    * Default alias: ``<field>__anyvalue``
+    * Return type: same as input field, or ``output_field`` if supplied. If the
+      queryset or grouping is empty, ``default`` is returned.
+
+    Usage example:
+
+    .. code-block:: pycon
+
+        >>> # Get average rating for each year along with a sample headline
+        >>> # from that year.
+        >>> from django.db.models import AnyValue, Avg, F, Q
+        >>> sample_headline = AnyValue("headline")
+        >>> Entry.objects.values(
+        ...     pub_year=F("pub_date__year"),
+        ... ).annotate(
+        ...     avg_rating=Avg("rating"),
+        ...     sample_headline=sample_headline,
+        ... )
+
+        >>> # Get a sample headline from each year with rating greater than 4.5.
+        >>> sample_headline = AnyValue(
+        ...     "headline",
+        ...     filter=Q(rating__gt=4.5),
+        ... )
+        >>> Entry.objects.values(
+        ...     pub_year=F("pub_date__year"),
+        ... ).annotate(
+        ...     avg_rating=Avg("rating"),
+        ...     sample_headline=sample_headline,
+        ... )
+
+    Supported on SQLite, MySQL, Oracle, and PostgreSQL 16+.
+
+    .. admonition:: MySQL with ``ONLY_FULL_GROUP_BY`` enabled
+
+        When the ``ONLY_FULL_GROUP_BY`` SQL mode is enabled on MySQL it may be
+        necessary to use ``AnyValue`` if an aggregation includes a mix of
+        aggregate and non-aggregate functions. Using ``AnyValue`` allows the
+        non-aggregate function to be referenced in the select list when
+        database cannot determine that it is functionally dependent on the
+        columns in the `group by`_ clause. See the :ref:`aggregation
+        documentation <aggregation-mysql-only-full-group-by>` for more details.
+
+        .. _group by: https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html
+
 ``Avg``
 ~~~~~~~
 
diff --git a/docs/releases/6.0.txt b/docs/releases/6.0.txt
index b950412e20..ade85a2173 100644
--- a/docs/releases/6.0.txt
+++ b/docs/releases/6.0.txt
@@ -212,6 +212,10 @@ Models
 * :class:`~django.db.models.JSONField` now supports
   :ref:`negative array indexing <key-index-and-path-transforms>` on SQLite.
 
+* The new :class:`~django.db.models.AnyValue` aggregate returns an arbitrary
+  value from the non-null input values. This is supported on SQLite, MySQL,
+  Oracle, and PostgreSQL 16+.
+
 Pagination
 ~~~~~~~~~~
 
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.