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
31005	Incorrect GROUP BY aggregate values when filtering by annotation on reverse-related LEFT OUTER JOIN	Oroku Saki	nobody	"Given the following models:

{{{
#!python
from django.db import models


class Account(models.Model):
    name = models.CharField(max_length=255)


class Ticket(models.Model):
    account = models.ForeignKey(Account)


class StatusChange(Model):
    ticket = models.ForeignKey(Ticket)
    created_on = models.DateField(auto_now_add=True)
}}}

Create some objects:

{{{
#!python
a = Account.objects.create(name='One')
t = a.ticket_set.create()
t.statuschange_set.create()
t.statuschange_set.create()
a = Account.objects.create(name='Two')
t = a.ticket_set.create()
t.statuschange_set.create()
t.statuschange_set.create()
t.statuschange_set.create()
}}}

To summarize, you've created 2 Accounts, each with 1 Ticket, and each Ticket with 2 and 3 StatusChange records per Ticket, respectively.

Now, query for some reporting info:

{{{
#!python
from datetime import timedelta

from django.utils import timezone
from django.db.models import Max, Count


# Make a Ticket queryset annotated with the last update to a related StatusChange
qs = Ticket.objects.annotate(
    updated_at=Max(
        'statuschange__created_on',
        output_field=models.DateField()
    )
)

# Filter by updated_at (just filter by < now to make sure no records are filtered out)
qs = qs.filter(updated_at__lte=timezone.now())

# Now, count the rows, grouped by Account name
qs.order_by('account__name').values('account__name').annotate(Count('pk'))
}}}

**The expected output is:**

{{{
#!python
<QuerySet [{'account__name': 'One', 'pk__count': 1}, {'account__name': 'Two', 'pk__count': 1},]>
}}}

Since we know that there is just 1 Ticket per Account.

**Instead, I'm seeing:**

{{{
#!python
<QuerySet [{'account__name': 'One', 'pk__count': 2}, {'account__name': 'Two', 'pk__count': 3},]>
}}}

Because the {{{LEFT OUTER JOIN}}} in the query is causing the second {{{Count}}} annotation to count once per outer-related row."	Uncategorized	closed	Database layer (models, ORM)	1.11	Normal	duplicate			Unreviewed	0	0	0	0	0	0