Re: Why does combining multiple annotations work in my case?

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+unsubscribe@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.