From ddb85294159185c5bd5cae34c9ef735ff8409bfe Mon Sep 17 00:00:00 2001 From: ontowhee <82607723+ontowhee@users.noreply.github.com> Date: Sat, 15 Mar 2025 19:23:28 -0700 Subject: 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. --- docs/ref/models/querysets.txt | 54 ++++++++++++++++++++++++++++++++++++ docs/releases/6.0.txt | 4 +++ docs/topics/db/aggregation.txt | 62 ++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 120 insertions(+) (limited to 'docs') 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: ``__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 ` 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 ` 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 ` 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. -- cgit v1.3