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