Opened 3 years ago
Last modified 3 years 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).
zip of the MRE