Opened 17 months ago

Last modified 17 months ago

#34527 closed Bug

Unexpected behavior with division in aggregation — at Initial Version

Reported by: Egor R Owned by: nobody
Component: Database layer (models, ORM) Version: 3.2
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I came across an unexpected behavior (code taken from a real project, and the models are renamed):

django.__version__
# '3.2.18'

MyModel.objects.all().annotate(
    score=Sum("relatedmodel__points", output_field=FloatField(), filter=Q(relatedmodel__owner=user),)
    / Count("relatedmodel__id", filter=Q(relatedmodel__owner=user)),
).values_list("score", flat=True)
# <QuerySet [None, 10.0, 8.0, None, 9.0, None]>

MyModel.objects.all().annotate(
    score=Sum("relatedmodel__points", output_field=FloatField(), filter=Q(relatedmodel__owner=user),)
).values_list("score", flat=True)
# <QuerySet [None, 40.0, 35.0, None, 37.0, None]>

MyModel.objects.all().annotate(
    score=Count("relatedmodel__id", filter=Q(relatedmodel__owner=user),),
).values_list("score", flat=True)
# <QuerySet [0, 4, 4, 0, 4, 0]>

Since we're specifying output_field=FloatField() for Sum, I expected to get 10, 8.75 and 9.25 as the results of the first query, but I'm getting 10.0/8.0/9.0.
I looked into SQL code generated by the query - there's no casting of Sum to float there, so it's somewhat understandable why it is happening. But, shouldn't Django cast Sum to float in SQL?

Explicitly casting Count as FloatField works, though:

MyModel.objects.all().annotate(
    score=Sum("relatedmodel__points", output_field=FloatField(), filter=Q(relatedmodel__owner=user),)
    / Cast(Count("relatedmodel__id", filter=Q(relatedmodel__owner=user)), FloatField())
).values_list("score", flat=True)
# <QuerySet [None, 10.0, 8.75, None, 9.25, None]>

Relevant snippets of the models:

class MyModel(models.Model):
    ...

class RelatedModel(models.Model):
    points = models.PositiveSmallIntegerField(
        "description", default=10, null=True, blank=True
    )
    mymodel = models.ForeignKey("myapp.MyModel", on_delete=models.CASCADE)
    owner = models.ForeignKey(User, on_delete=models.CASCADE)
    ...

I created an MRE, will attach it to the ticket.
Tested on Postgres 12 (real project) and 15 (MRE).

Change History (1)

by Egor R, 17 months ago

Attachment: dj_agg_bug_clean.zip added

zip of the MRE

Note: See TracTickets for help on using tickets.
Back to Top