[3.0.x] Fixed #31568 -- Fixed alias reference when aggregating over multiple subqueries.

691def10a0197d83d2d108bd9043b0916d0f09b4 made all Subquery() instances
equal to each other which broke aggregation subquery pushdown which
relied on object equality to determine which alias it should select.

Subquery.__eq__() will be fixed in an another commit but
Query.rewrite_cols() should haved used object identity from the start.

Refs #30727, #30188.

Thanks Makina Corpus for the report.

Backport of adfbf653dc1c1d0e0dacc4ed46602d22ba28b004 from master

3 files changed, 26 insertions, 2 deletions

diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index 329a6e16c0..f550d5e28b 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -393,7 +393,7 @@ class Query(BaseExpression):
             else:
                 # Reuse aliases of expressions already selected in subquery.
                 for col_alias, selected_annotation in self.annotation_select.items():
-                    if selected_annotation == expr:
+                    if selected_annotation is expr:
                         new_expr = Ref(col_alias, expr)
                         break
                 else:
diff --git a/docs/releases/3.0.7.txt b/docs/releases/3.0.7.txt
index 5457e59b3d..0f1188724a 100644
--- a/docs/releases/3.0.7.txt
+++ b/docs/releases/3.0.7.txt
@@ -15,3 +15,6 @@ Bugfixes
 * Fixed a regression in Django 3.0 where ``QuerySet.values()`` and
   ``values_list()`` crashed if a queryset contained an aggregation and a
   subquery annotation (:ticket:`31566`).
+
+* Fixed a regression in Django 3.0 where aggregates used wrong annotations when
+  a queryset has multiple subqueries annotations (:ticket:`31568`).
diff --git a/tests/aggregation/test_filter_argument.py b/tests/aggregation/test_filter_argument.py
index 0c8829efdf..650cb8e460 100644
--- a/tests/aggregation/test_filter_argument.py
+++ b/tests/aggregation/test_filter_argument.py
@@ -2,7 +2,8 @@ import datetime
 from decimal import Decimal
 
 from django.db.models import (
-    Avg, Case, Count, F, OuterRef, Q, StdDev, Subquery, Sum, Variance, When,
+    Avg, Case, Count, Exists, F, Max, OuterRef, Q, StdDev, Subquery, Sum,
+    Variance, When,
 )
 from django.test import TestCase
 from django.test.utils import Approximate
@@ -120,3 +121,23 @@ class FilteredAggregateTests(TestCase):
             cnt=Count('pk', filter=Q(earliest_book_year=2008)),
         )
         self.assertEqual(aggs['cnt'], 2)
+
+    def test_filtered_aggregate_ref_multiple_subquery_annotation(self):
+        aggregate = Book.objects.values('publisher').annotate(
+            has_authors=Exists(
+                Book.authors.through.objects.filter(book=OuterRef('pk')),
+            ),
+            authors_have_other_books=Exists(
+                Book.objects.filter(
+                    authors__in=Author.objects.filter(
+                        book_contact_set=OuterRef(OuterRef('pk')),
+                    )
+                ).exclude(pk=OuterRef('pk')),
+            ),
+        ).aggregate(
+            max_rating=Max(
+                'rating',
+                filter=Q(has_authors=True, authors_have_other_books=False),
+            )
+        )
+        self.assertEqual(aggregate, {'max_rating': 4.5})