Fixed #29542 -- Fixed invalid SQL if a Subquery from the HAVING clause is used in the GROUP BY clause.

Thanks Tim Graham for the review.

2 files changed, 20 insertions, 2 deletions

diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
index 35dd2459a6..3b91eaa35b 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -6,7 +6,7 @@ from itertools import chain
 
 from django.core.exceptions import EmptyResultSet, FieldError
 from django.db.models.constants import LOOKUP_SEP
-from django.db.models.expressions import OrderBy, Random, RawSQL, Ref
+from django.db.models.expressions import OrderBy, Random, RawSQL, Ref, Subquery
 from django.db.models.query_utils import QueryWrapper, select_related_descend
 from django.db.models.sql.constants import (
     CURSOR, GET_ITERATOR_CHUNK_SIZE, MULTI, NO_RESULTS, ORDER_DIR, SINGLE,
@@ -127,6 +127,11 @@ class SQLCompiler:
 
         for expr in expressions:
             sql, params = self.compile(expr)
+            if isinstance(expr, Subquery) and not sql.startswith('('):
+                # Subquery expression from HAVING clause may not contain
+                # wrapping () because they could be removed when a subquery is
+                # the "rhs" in an expression (see Subquery._prepare()).
+                sql = '(%s)' % sql
             if (sql, tuple(params)) not in seen:
                 result.append((sql, params))
                 seen.add((sql, tuple(params)))
diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
index c1073d6f41..021f59d2d7 100644
--- a/tests/annotations/tests.py
+++ b/tests/annotations/tests.py
@@ -4,7 +4,7 @@ from decimal import Decimal
 from django.core.exceptions import FieldDoesNotExist, FieldError
 from django.db.models import (
     BooleanField, CharField, Count, DateTimeField, ExpressionWrapper, F, Func,
-    IntegerField, NullBooleanField, Q, Sum, Value,
+    IntegerField, NullBooleanField, OuterRef, Q, Subquery, Sum, Value,
 )
 from django.db.models.expressions import RawSQL
 from django.db.models.functions import Length, Lower
@@ -585,3 +585,16 @@ class NonAggregateAnnotationTestCase(TestCase):
             qs,
             [{'jacob_name': 'Jacob Kaplan-Moss', 'james_name': 'James Bennett'}],
         )
+
+    @skipUnlessDBFeature('supports_subqueries_in_group_by')
+    def test_annotation_filter_with_subquery(self):
+        long_books_qs = Book.objects.filter(
+            publisher=OuterRef('pk'),
+            pages__gt=400,
+        ).values('publisher').annotate(count=Count('pk')).values('count')
+        publisher_books_qs = Publisher.objects.annotate(
+            total_books=Count('book'),
+        ).filter(
+            total_books=Subquery(long_books_qs, output_field=IntegerField()),
+        ).values('name')
+        self.assertCountEqual(publisher_books_qs, [{'name': 'Sams'}, {'name': 'Morgan Kaufmann'}])