[Django] #34527: Unexpected behavior with division in aggregation

[Django]

#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 django-updates+unsubscr...@googlegroups.com.
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.