#34527: Unexpected behavior with division in aggregation
-------------------------------------+-------------------------------------
Reporter: Egor R | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/34527>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070187cdef15da-2c09b22e-45dc-48fd-8bb3-043cca380bcb-000000%40eu-central-1.amazonses.com.
