Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#22629 closed Bug (duplicate)

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

Reported by: jps@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (4)

comment:1 by Baptiste Mispelon, 10 years ago

Triage Stage: UnreviewedAccepted

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.

comment:2 by jps@…, 10 years ago

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

comment:3 by Anssi Kääriäinen, 10 years ago

Resolution: duplicate
Status: newclosed

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.

comment:4 by jps@…, 10 years ago

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.

Note: See TracTickets for help on using tickets.
Back to Top