[3.1.x] Fixed #32152 -- Fixed grouping by subquery aliases.

kluchrj · felixxm · commit ab951d242e3d · 2020-10-29T11:30:53.000+01:00
Regression in 42c08ee. Thanks Simon Charette for the review. Backport of 4ac2d4f from master
diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
@@ -2148,8 +2148,10 @@ def set_values(self, fields):
                         field_names.append(f)
             self.set_extra_mask(extra_names)
             self.set_annotation_mask(annotation_names)
+            selected = frozenset(field_names + extra_names + annotation_names)
         else:
             field_names = [f.attname for f in self.model._meta.concrete_fields]
+            selected = frozenset(field_names)
         # Selected annotations must be known before setting the GROUP BY
         # clause.
         if self.group_by is True:
@@ -2163,7 +2165,7 @@ def set_values(self, fields):
             # the selected fields anymore.
             group_by = []
             for expr in self.group_by:
-                if isinstance(expr, Ref) and expr.refs not in field_names:
+                if isinstance(expr, Ref) and expr.refs not in selected:
                     expr = self.annotations[expr.refs]
                 group_by.append(expr)
             self.group_by = tuple(group_by)
diff --git a/docs/releases/3.0.11.txt b/docs/releases/3.0.11.txt
@@ -4,4 +4,11 @@ Django 3.0.11 release notes
 
 *Expected November 2, 2020*
 
-Django 3.0.11 adds compatibility with Python 3.9.
+Django 3.0.11 fixes a regression in 3.0.7 and adds compatibility with Python
+3.9.
+
+Bugfixes
+========
+
+* Fixed a regression in Django 3.0.7 that didn't use ``Subquery()`` aliases in
+  the ``GROUP BY`` clause (:ticket:`32152`).
diff --git a/docs/releases/3.1.3.txt b/docs/releases/3.1.3.txt
@@ -57,3 +57,6 @@ Bugfixes
 * Fixed a regression in Django 3.1 that caused incorrect textarea layout on
   medium-sized screens in the admin change form view with the sidebar open
   (:ticket:`32127`).
+
+* Fixed a regression in Django 3.0.7 that didn't use ``Subquery()`` aliases in
+  the ``GROUP BY`` clause (:ticket:`32152`).
diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
@@ -10,7 +10,7 @@
     NullBooleanField, OuterRef, Q, Subquery, Sum, Value, When,
 )
 from django.db.models.expressions import RawSQL
-from django.db.models.functions import Length, Lower
+from django.db.models.functions import ExtractYear, Length, Lower
 from django.test import TestCase, skipUnlessDBFeature
 
 from .models import (
@@ -664,6 +664,25 @@ def test_annotation_exists_aggregate_values_chaining(self):
             datetime.date(2008, 11, 3),
         ])
 
+    @skipUnlessDBFeature('supports_subqueries_in_group_by')
+    def test_annotation_subquery_and_aggregate_values_chaining(self):
+        qs = Book.objects.annotate(
+            pub_year=ExtractYear('pubdate')
+        ).values('pub_year').annotate(
+            top_rating=Subquery(
+                Book.objects.filter(
+                    pubdate__year=OuterRef('pub_year')
+                ).order_by('-rating').values('rating')[:1]
+            ),
+            total_pages=Sum('pages'),
+        ).values('pub_year', 'total_pages', 'top_rating')
+        self.assertCountEqual(qs, [
+            {'pub_year': 1991, 'top_rating': 5.0, 'total_pages': 946},
+            {'pub_year': 1995, 'top_rating': 4.0, 'total_pages': 1132},
+            {'pub_year': 2007, 'top_rating': 4.5, 'total_pages': 447},
+            {'pub_year': 2008, 'top_rating': 4.0, 'total_pages': 1178},
+        ])
+
     @skipIf(
         connection.vendor == 'mysql' and 'ONLY_FULL_GROUP_BY' in connection.sql_mode,
         'GROUP BY optimization does not work properly when ONLY_FULL_GROUP_BY '
