Fixed #32478 -- Included nested columns referenced by subqueries in GROUP BY on aggregations.

Regression in fb3f034f1c63160c0ff13c609acd01c18be12f80.

Refs #31094, #31150.

Thanks Igor Pejic for the report.

3 files changed, 27 insertions, 4 deletions

diff --git a/django/db/models/expressions.py b/django/db/models/expressions.py
index ffc3a7fda5..e5291ab8d3 100644
--- a/django/db/models/expressions.py
+++ b/django/db/models/expressions.py
@@ -1127,6 +1127,9 @@ class Subquery(Expression):
     def external_aliases(self):
         return self.query.external_aliases
 
+    def get_external_cols(self):
+        return self.query.get_external_cols()
+
     def as_sql(self, compiler, connection, template=None, query=None, **extra_context):
         connection.ops.check_expression_support(self)
         template_params = {**self.extra, **extra_context}
@@ -1141,7 +1144,7 @@ class Subquery(Expression):
     def get_group_by_cols(self, alias=None):
         if alias:
             return [Ref(alias, self)]
-        external_cols = self.query.get_external_cols()
+        external_cols = self.get_external_cols()
         if any(col.possibly_multivalued for col in external_cols):
             return [self]
         return external_cols
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 3b2211b3b3..5ca3f38e15 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -1072,7 +1072,7 @@ class Query(BaseExpression):
     def get_external_cols(self):
         exprs = chain(self.annotations.values(), self.where.children)
         return [
-            col for col in self._gen_cols(exprs)
+            col for col in self._gen_cols(exprs, include_external=True)
             if col.alias in self.external_aliases
         ]
 
@@ -1707,12 +1707,17 @@ class Query(BaseExpression):
         return targets, joins[-1], joins
 
     @classmethod
-    def _gen_cols(cls, exprs):
+    def _gen_cols(cls, exprs, include_external=False):
         for expr in exprs:
             if isinstance(expr, Col):
                 yield expr
+            elif include_external and callable(getattr(expr, 'get_external_cols', None)):
+                yield from expr.get_external_cols()
             else:
-                yield from cls._gen_cols(expr.get_source_expressions())
+                yield from cls._gen_cols(
+                    expr.get_source_expressions(),
+                    include_external=include_external,
+                )
 
     @classmethod
     def _gen_col_aliases(cls, exprs):
diff --git a/tests/aggregation/tests.py b/tests/aggregation/tests.py
index 0bced06ce9..49123396dd 100644
--- a/tests/aggregation/tests.py
+++ b/tests/aggregation/tests.py
@@ -1311,6 +1311,21 @@ class AggregateTestCase(TestCase):
         #     self.assertSequenceEqual(books_qs, [book])
         # self.assertEqual(ctx[0]['sql'].count('SELECT'), 2)
 
+    @skipUnlessDBFeature('supports_subqueries_in_group_by')
+    def test_aggregation_nested_subquery_outerref(self):
+        publisher_with_same_name = Publisher.objects.filter(
+            id__in=Subquery(
+                Publisher.objects.filter(
+                    name=OuterRef(OuterRef('publisher__name')),
+                ).values('id'),
+            ),
+        ).values(publisher_count=Count('id'))[:1]
+        books_breakdown = Book.objects.annotate(
+            publisher_count=Subquery(publisher_with_same_name),
+            authors_count=Count('authors'),
+        ).values_list('publisher_count', flat=True)
+        self.assertSequenceEqual(books_breakdown, [1] * 6)
+
     def test_aggregation_random_ordering(self):
         """Random() is not included in the GROUP BY when used for ordering."""
         authors = Author.objects.annotate(contact_count=Count('book')).order_by('?')