.annotate with two foreign-key count attributes counts wrong on SQLite

[jps@…]

When using .annote on a model with two foreign-key relations and counting both of them, the count comes out wrong on SQLite.

Example code:

a1 = Alfa.objects.create()
a2 = Alfa.objects.create()
b1 = Bravo.objects.create()

d  = Delta.objects.create()
d.alfas.add(a1)
d.alfas.add(a2)
d.bravos.add(b1)

d_count = Delta.objects.annotate(num_alfas=Count('alfas'), num_bravos=Count('bravos')).get(id=d.id)

self.assertEquals(2, d_count.num_alfas,  "Expected to find two alfa objects counted")
self.assertEquals(1, d_count.num_bravos, "Expected to find one bravo object counted")

Test code is also available at
​https://github.com/shezi/django/commit/5a565bd532bbf5d055304835adefbf40aaf467e2

The reason for the bug is that SQLite handles the generated LEFT OUTER JOIN differently than other DBMSs. In SQLite, the second LEFT OUTER JOIN is added onto the already-joined table, such that IDs from the first join get duplicated.

One workaround is to add  distinct=True to the second count argument. This, of course, changes both the meaning and the execution speed for the query.

[Baptiste Mispelon]

Hi,

I can reproduce the issue on sqlite and postgres.
In case your link stops working, here are the models that I used:

class Alfa(models.Model):
    pass


class Bravo(models.Model):
    pass


class Delta(models.Model):
    alfas = models.ManyToManyField(Alfa)
    bravos = models.ManyToManyField(Bravo)

Thanks.

[jps@…]

I'd submit the test as a pull request, but I wasn't sure about format and how you want it integrated.

[Anssi Kääriäinen]

This is a known (and rather big) deficiency in Django's ORM. Closing as duplicate of #10060 (see also #15049, #16603).

The problem is that Django's ORM should use subqueries for m2m relations when filtering or aggregating. Unfortunately that hasn't ever been implemented, and thus Django's aggregation support has this bug.

[jps@…]

There is, however, no documentation or testing for any of this. And it took me quite a while to figure out that my code wasn't the problem, but Django's is. Would it be possible to add this behaviour to the documentation, at least? As the last commenter on #10060 remarks, the documentation as it is doesn't really help when encountering the problem.

Also, ticket #10060 hasn't been updated in 16 months, so that looks dead, too.