#26430: Coalesce in Aggregations ignored when EmptyResultSet returned
-------------------------------------+-------------------------------------
Reporter: ryanprater | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: aggregation coalesce in
Severity: Normal | queryset
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Using an empty list when using the `__in=` returns an EmptyResultSet and
prevents an Aggregate Coalesce from working properly. See below:
# Test with matched Queryset. Sum will return 50
{{{OrderItem.objects.filter(pk__in=[1]).aggregate(test=Coalesce(Sum('quantity'),
Value(0)))}}}
{{{>>>{'test': 50}}}}
# Test with unmatched Queryset. Sum will return 0
{{{OrderItem.objects.filter(pk__in=[-1]).aggregate(test=Coalesce(Sum('quantity'),
Value(0)))}}}
{{{>>> {'test':0}}}}
# Test with unmatched Queryset (using empty list). EmptyResultSet returned
because of empty list. Sum will return NONE
{{{OrderItem.objects.filter(pk__in=[]).aggregate(test=Coalesce(Sum('quantity'),
Value(0)))}}}
{{{>>> {'test': None}}}}
Simon Charette on django-users suggested the following:
''From what I understand the ORM simply doesn't perform any query in this
case
as the `pk__in` lookup cannot match any `OrderItem` and result in an
`EmptyResultSet` exception[1].''
''This exception is caught in the `Query.get_aggregation()` method where
all
aggregates are converted to `None`[2].''
''I suppose we should alter the `except EmptyResultSet` clause to account
for
`outer_query.annotation_select` items that are `Coalesce()` instances used
with
`Value()` but I'm unsure about how it should be done.''
[1]
https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/lookups.py#L221-L223
[2]
https://github.com/django/django/blob/2e0cd26ffb29189add1e0435913fd1490f52b20d/django/db/models/sql/query.py#L439-L445
See full discussion here:
https://groups.google.com/forum/#!topic/django-users/HGD3Vv3IerA
--
Ticket URL: <https://code.djangoproject.com/ticket/26430>
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 post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.c0bfc34522b083bf35967f138ca83f7f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
