id,summary,reporter,owner,description,type,status,component,version,severity,resolution,keywords,cc,stage,has_patch,needs_docs,needs_tests,needs_better_patch,easy,ui_ux 26430,Coalesce in Aggregations ignored when EmptyResultSet returned,Ryan Prater,Simon Charette,"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",Bug,closed,"Database layer (models, ORM)",1.9,Normal,fixed,aggregation coalesce in queryset,Simon Charette Hannes Ljungberg Anton Agestam pope1ni,Ready for checkin,1,0,0,0,0,0