#34527: Unexpected behavior with division in aggregation
-------------------------------------+-------------------------------------
Reporter: Egor R | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Egor R:
Old 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).
New 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).
PS Real project uses django-tenants, and I included it in the MRE as I
tried to track down the bug. Can retest and create MRE without django-
tenants if needed.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34527#comment:1>
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/01070187cdf3fc2f-7acb9a5c-7ac9-4def-a6db-bd958034b19a-000000%40eu-central-1.amazonses.com.
