Why does combining multiple annotations work in my case?

[Marcin Gałązka]

The documentation advises against combining multiple annotations in one query: https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#combining-multiple-aggregations due to the infamous bug #10060: https://code.djangoproject.com/ticket/10060

And indeed, having tried the counter-example from the documentation, I can easily reproduce the bug.

However... To my astonishment... I produced a **working** example of combining multiple annotations. An example that, IIUC the aforementioned docu and bug report, should **not** be working.

Not that I'm complaining, actually my life has just became much, MUCH easier thanks to this query working... I only can't understand WHY is it working?

Here's my case: (not minimalised too much to improve the chances of failure):

file models.py:

from django.db.models import Model, CharField, PositiveSmallIntegerField, PositiveIntegerField, ForeignKey, CASCADE

# Create your models here.


class Province(Model):
name = CharField(max_length=64, primary_key=True)


class Municipality(Model):
name = CharField(max_length=64, primary_key=True)
province = ForeignKey(Province, on_delete=CASCADE)


class Ward(Model):
no=PositiveSmallIntegerField()
municipality = ForeignKey(Municipality, on_delete=CASCADE)
spoiled_votes = PositiveIntegerField()
did_not_vote = PositiveIntegerField()

class Meta:
unique_together = ('no', 'municipality')


class Candidate(Model):
first_name = CharField(max_length=64)
last_name = CharField(max_length=64)

class Meta:
unique_together=('first_name', 'last_name')


class Votes(Model):
ward = ForeignKey(Ward, on_delete=CASCADE)
candidate = ForeignKey(Candidate, on_delete=CASCADE)
amount = PositiveIntegerField()

class Meta:
unique_together = ('ward', 'candidate')

File tests.py:

from django.test import TestCase
from random import seed, randrange
from worksapp.models import Province, Municipality, Ward, Candidate, Votes
from django.db.models import Subquery, Sum, OuterRef, PositiveIntegerField
from operator import attrgetter

# Create your tests here.


class AnnotateTest(TestCase):
def test(self):
seed(1)

provinces = [
Province(name='Province'+str(i))
for i in range(0, 10)
]
Province.objects.bulk_create(provinces)

municipalities = [
Municipality(name='Municipality'+str(i), province=provinces[i//10])
for i in range(0, 100)
]
Municipality.objects.bulk_create(municipalities)

wards = [
Ward(
no=i%10, municipality=municipalities[i//10], pk=i,
spoiled_votes=randrange(0, 100), did_not_vote=randrange(0, 1000)
) for i in range(0, 1000)
]
Ward.objects.bulk_create(wards)

candidates = [
Candidate(first_name='name'+str(i), last_name='surname'+str(i), pk=i)
for i in range(0, 10)
]
Candidate.objects.bulk_create(candidates)

votes = [
Votes(ward=wards[i//10], candidate=candidates[i%10], amount=randrange(0, 100), pk=i)
for i in range(0, 10000)
]
Votes.objects.bulk_create(votes)

multiple_annotate = Municipality.objects.annotate(
cand3votes=Subquery(
Votes.objects.filter(
candidate__first_name='name3',
candidate__last_name='surname3',
ward__municipality=OuterRef('pk')
).values('ward__municipality').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
cand7votes=Subquery(
Votes.objects.filter(
candidate__first_name='name7',
candidate__last_name='surname7',
ward__municipality=OuterRef('pk')
).values('ward__municipality').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
spoiled_votes=Sum('ward__spoiled_votes'),
votes_not_cast=Sum('ward__did_not_vote')
).get(name='Municipality'+str(83))

self.assertEqual(multiple_annotate.cand3votes, sum(map(attrgetter('amount'), votes[8303:8403:10])))
self.assertEqual(multiple_annotate.cand7votes, sum(map(attrgetter('amount'), votes[8307:8407:10])))
self.assertEqual(multiple_annotate.spoiled_votes, sum(map(attrgetter('spoiled_votes'), wards[830:840])))
self.assertEqual(multiple_annotate.votes_not_cast, sum(map(attrgetter('did_not_vote'), wards[830:840])))

multiple_annotate = Province.objects.annotate(
cand0votes=Subquery(
Votes.objects.filter(
candidate__first_name='name0',
candidate__last_name='surname0',
ward__municipality__province=OuterRef('pk')
).values('ward__municipality__province').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
cand9votes=Subquery(
Votes.objects.filter(
candidate__first_name='name9',
candidate__last_name='surname9',
ward__municipality__province=OuterRef('pk')
).values('ward__municipality__province').annotate(res=Sum('amount')).values('res'),
output_field=PositiveIntegerField()
),
spoiled_votes=Sum('municipality__ward__spoiled_votes'),
votes_not_cast=Sum('municipality__ward__did_not_vote')
).get(name='Province5')

self.assertEqual(multiple_annotate.cand0votes, sum(map(attrgetter('amount'), votes[5000:6000:10])))
self.assertEqual(multiple_annotate.cand9votes, sum(map(attrgetter('amount'), votes[5009:6009:10])))
self.assertEqual(multiple_annotate.spoiled_votes, sum(map(attrgetter('spoiled_votes'), wards[500:600])))
self.assertEqual(multiple_annotate.votes_not_cast, sum(map(attrgetter('did_not_vote'), wards[500:600])))

To my astonishement the test PASSES. Why?

[Simon Charette]

Hello there,

The issue tracked by #10060 only manifests itself when two different multi-valued relationships

are used to perform aggregated annotations.

In both of your provided examples you only perform SUM()s on a single multi-valued relationship

(ward and then munipality__ward) while the documentation examples spans results over two

different multi-valued relationships (authors and store).

Hope that makes things a bit clearer to you,

Best,

Simon