Case-When aggregation over aggregated fields doesn't work since 4.2

[Denis Roldán]

This same QuerySet was working on 4.1.X and prior versions and stopped working on 4.2.X:

annotated_users  = users_qs.annotate(
            total_things=Subquery(
                OtherModel.objects.filter(user_id=OuterRef("pk")).annotate(
                    total_objs=F("total")
                ).values("total_objs")
            )
)

annotated_users.aggregate(
            sum_total_objs=Sum("total_things"),
            avg_conversion_rate=Case(
                When(
                    sum_total_objs=0,
                    then=0,
                ),
                default=Round(
                    (Sum("sum_total_confirmed_objs") / Sum("sum_total_objs")) * 100, 2
                ),
                output_field=FloatField(),
            )
)

As you can see sum_total_objs is an aggregated field that is also used on a second field to calculate the conversion rate. To avoid a zero division problem, we were using a Case-When clause over that field. It works well on any 4.1 and prior versions but stopped working since 4.2, raising a FieldError like:

Cannot resolve keyword 'sum_total_objs' into field

The bug is reproducible with an extra test on the django aggregation test suite:

    def test_referenced_group_by_aggregation_over_annotation(self):
        total_books_qs = (
            Book.objects.filter(authors__pk=OuterRef("pk"))
            .order_by()
            .values("pk")
            .annotate(total=Count("pk"))
            .values("total")
        )
        
        annotated_authors = Author.objects.annotate(
            total_books=Subquery(total_books_qs.annotate(
                    total_books=F("total")
            ).values("total_books")),
            total_books_a=Subquery(total_books_qs.filter(
                name__istartswith="a"
            ).annotate(
                    total_books_a=F("total")
            ).values("total_books_a")),
        ).values(
            "pk",
            "total_books",
            "total_books_a",
        ).order_by("-total_books")
        
        totals = annotated_authors.aggregate(
            sum_total_books=Sum("total_books"),
            sum_total_books_a=Sum("total_books_a"),
            a_over_total_rate=Case(
                When(
                    sum_total_books=0,
                    then=0,
                ),
                default=Round(
                    (Sum("total_books_a") / Sum("total_books")) * 100, 2
                ),
                output_field=FloatField(),
            ),
        )
        
        self.assertEqual(totals['sum_total_books'], 3)
        self.assertEqual(totals['sum_total_books_a'], 0)
        self.assertEqual(totals['a_over_total_rate'], 0)

Thanks for the support!

[Mariusz Felisiak]

Can you reproduce your issue with Django 4.2.1 (see 511dc3db539122577aaba71f5a24d65d5adab092)? If yes, please share your models.

[Denis Roldán]

Replying to Mariusz Felisiak:

Can you reproduce your issue with Django 4.2.1 (see 511dc3db539122577aaba71f5a24d65d5adab092)? If yes, please share your models.

Correct. It doesn't work on Django 4.2.1 neither.

I can reproduce the issue with a test on aggregation/tests.py

    def test_referenced_group_by_aggregation_over_annotation(self):
        total_books_qs = (
            Book.objects.filter(authors__pk=OuterRef("pk"))
            .order_by()
            .values("pk")
            .annotate(total=Count("pk"))
            .values("total")
        )

        annotated_authors = Author.objects.annotate(
            total_books=Subquery(total_books_qs.annotate(
                    total_books=F("total")
            ).values("total_books")),
            total_books_a=Subquery(total_books_qs.filter(
                name__istartswith="a"
            ).annotate(
                    total_books_a=F("total")
            ).values("total_books_a")),
        ).values(
            "pk",
            "total_books",
            "total_books_a",
        ).order_by("-total_books")

        totals = annotated_authors.aggregate(
            sum_total_books=Sum("total_books"),
            sum_total_books_a=Sum("total_books_a"),
            a_over_total_rate=Case(
                When(
                    sum_total_books=0,
                    then=0,
                ),
                default=Round(
                    (Sum("total_books_a") / Sum("total_books")) * 100, 2
                ),
                output_field=FloatField(),
            ),
        )

        self.assertEqual(totals['sum_total_books'], 3)
        self.assertEqual(totals['sum_total_books_a'], 0)
        self.assertEqual(totals['a_over_total_rate'], 0)

[Denis Roldán]

You can find the test here: ​https://github.com/django/django/pull/16838

[Mariusz Felisiak]

Thanks for the report.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.
Reproduced at 59262c294d26d2aa9346284519545c0f988bf353.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2ee0174:

Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

[Mariusz Felisiak <felisiak.mariusz@…>]

In e5c844d6:

Fixed #34551 -- Fixed QuerySet.aggregate() crash when referencing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Denis Roldán and Mariusz for the test.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 57f499e4:

[4.2.x] Refs #34551 -- Fixed QuerySet.aggregate() crash on precending aggregation reference.

Regression in 1297c0d0d76a708017fe196b61a0ab324df76954.

Refs #31679.

Backport of 2ee01747c32a7275a7a1a5f7862acba7db764921 from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In c78a442:

[4.2.x] Fixed #34551 -- Fixed QuerySet.aggregate() crash when referencing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Denis Roldán and Mariusz for the test.

Backport of e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4 from main

[Lorenzo Peña]

Good day. Just a heads up. Some aggregations still break on Django 4.2.2 in my company's codebase. I am trying to create a reproducible example into a new ticket, but wanted to drop word already.

[Lorenzo Peña]

Cross referencing just in case: https://code.djangoproject.com/ticket/34706

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3b4a5712:

Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 4ccca9ee:

[5.0.x] Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

Backport of 3b4a571275d967512866012955eb0b3ae486d63c from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In 803caec:

[4.2.x] Fixed #34798 -- Fixed QuerySet.aggregate() crash when referencing expressions containing subqueries.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7,
complements e5c844d6f2a4ac6ae674d741b5f1fa2a688cedf4.

Refs #28477, #34551.

Thanks Haldun Komsuoglu for the report.

Backport of 3b4a571275d967512866012955eb0b3ae486d63c from main