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.

7 files changed, 32 insertions, 10 deletions

diff --git a/django/db/backends/base/features.py b/django/db/backends/base/features.py
index d3b148dc3d..87fc7204ee 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -266,6 +266,9 @@ class BaseDatabaseFeatures:
     # delimiter along with DISTINCT.
     supports_aggregate_distinct_multiple_argument = True
 
+    # Does the database support SQL 2023 ANY_VALUE in GROUP BY?
+    supports_any_value = False
+
     # Does the backend support indexing a TextField?
     supports_index_on_text_field = True
 
diff --git a/django/db/backends/mysql/features.py b/django/db/backends/mysql/features.py
index d0749eb5e6..6ae4c56af1 100644
--- a/django/db/backends/mysql/features.py
+++ b/django/db/backends/mysql/features.py
@@ -111,16 +111,6 @@ class DatabaseFeatures(BaseDatabaseFeatures):
                     },
                 }
             )
-        if "ONLY_FULL_GROUP_BY" in self.connection.sql_mode:
-            skips.update(
-                {
-                    "GROUP BY cannot contain nonaggregated column when "
-                    "ONLY_FULL_GROUP_BY mode is enabled on MySQL, see #34262.": {
-                        "aggregation.tests.AggregateTestCase."
-                        "test_group_by_nested_expression_with_params",
-                    },
-                }
-            )
         if self.connection.mysql_version < (8, 0, 31):
             skips.update(
                 {
@@ -297,3 +287,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
         if self.connection.mysql_is_mariadb:
             return "ONLY_FULL_GROUP_BY" not in self.connection.sql_mode
         return True
+
+    @cached_property
+    def supports_any_value(self):
+        return not self.connection.mysql_is_mariadb
diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py
index b355e7136c..fd327fabcc 100644
--- a/django/db/backends/oracle/features.py
+++ b/django/db/backends/oracle/features.py
@@ -61,6 +61,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
         END;
     """
     supports_callproc_kwargs = True
+    supports_any_value = True
     supports_over_clause = True
     supports_frame_range_fixed_distance = True
     supports_ignore_conflicts = False
diff --git a/django/db/backends/postgresql/features.py b/django/db/backends/postgresql/features.py
index 74a65fdbd9..83e6b5cf7f 100644
--- a/django/db/backends/postgresql/features.py
+++ b/django/db/backends/postgresql/features.py
@@ -162,3 +162,5 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     supports_nulls_distinct_unique_constraints = property(
         operator.attrgetter("is_postgresql_15")
     )
+
+    supports_any_value = property(operator.attrgetter("is_postgresql_16"))
diff --git a/django/db/backends/sqlite3/_functions.py b/django/db/backends/sqlite3/_functions.py
index 6d07d3d78b..b38e06eec2 100644
--- a/django/db/backends/sqlite3/_functions.py
+++ b/django/db/backends/sqlite3/_functions.py
@@ -80,6 +80,7 @@ def register(connection):
     connection.create_aggregate("STDDEV_SAMP", 1, StdDevSamp)
     connection.create_aggregate("VAR_POP", 1, VarPop)
     connection.create_aggregate("VAR_SAMP", 1, VarSamp)
+    connection.create_aggregate("ANY_VALUE", 1, AnyValue)
     # Some math functions are enabled by default in SQLite 3.35+.
     sql = "select sqlite_compileoption_used('ENABLE_MATH_FUNCTIONS')"
     if not connection.execute(sql).fetchone()[0]:
@@ -513,3 +514,8 @@ class VarPop(ListAggregate):
 
 class VarSamp(ListAggregate):
     finalize = statistics.variance
+
+
+class AnyValue(ListAggregate):
+    def finalize(self):
+        return self[0]
diff --git a/django/db/backends/sqlite3/features.py b/django/db/backends/sqlite3/features.py
index 1e995a87c0..8604adf40a 100644
--- a/django/db/backends/sqlite3/features.py
+++ b/django/db/backends/sqlite3/features.py
@@ -36,6 +36,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
     supports_aggregate_filter_clause = True
     supports_aggregate_order_by_clause = Database.sqlite_version_info >= (3, 44, 0)
     supports_aggregate_distinct_multiple_argument = False
+    supports_any_value = True
     order_by_nulls_first = True
     supports_json_field_contains = False
     supports_update_conflicts = True
diff --git a/django/db/models/aggregates.py b/django/db/models/aggregates.py
index 4174904f71..2a7ed23bc3 100644
--- a/django/db/models/aggregates.py
+++ b/django/db/models/aggregates.py
@@ -22,6 +22,7 @@ from django.db.models.functions.mixins import (
 
 __all__ = [
     "Aggregate",
+    "AnyValue",
     "Avg",
     "Count",
     "Max",
@@ -229,6 +230,20 @@ class Aggregate(Func):
         return options
 
 
+class AnyValue(Aggregate):
+    function = "ANY_VALUE"
+    name = "AnyValue"
+    arity = 1
+    window_compatible = False
+
+    def as_sql(self, compiler, connection, **extra_context):
+        if not connection.features.supports_any_value:
+            raise NotSupportedError(
+                "ANY_VALUE is not supported on this database backend."
+            )
+        return super().as_sql(compiler, connection, **extra_context)
+
+
 class Avg(FixDurationInputMixin, NumericOutputFieldMixin, Aggregate):
     function = "AVG"
     name = "Avg"