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 Le vendredi 11 août 2017 16:32:42 UTC-4, gaazkamm a écrit : > > > 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? -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f73b8d74-3a37-4aff-b079-b685466f22fe%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
