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)