Opened 8 years ago
Last modified 8 years ago
#26898 closed Uncategorized
annotate() with two Count('related_object') gives both as the bigger one — at Initial Version
| Reported by: | Aur Saraf | Owned by: | nobody |
|---|---|---|---|
| Component: | Database layer (models, ORM) | Version: | 1.9 |
| Severity: | Normal | Keywords: | count, annotate |
| Cc: | Triage Stage: | Unreviewed | |
| Has patch: | no | Needs documentation: | no |
| Needs tests: | no | Patch needs improvement: | no |
| Easy pickings: | no | UI/UX: | no |
Description
Minimal repro: https://github.com/sonoflilit/django-count-bug
`python
def test_bug(self):
a = A.objects.create()
B.objects.create(a=a)
B.objects.create(a=a)
C.objects.create(a=a)
# duh...
a = A.objects.annotate(b_count=Count('b'), c_count=Count('c')).get()
self.assertEquals(2, a.b_count)
# 2?! It's clearly 1!
self.assertEquals(1, a.c_count)
`
`sql
SELECT "bug_a"."id",
Count("bug_b"."id") AS "b_count",
Count("bug_c"."id") AS "c_count"
FROM "bug_a"
LEFT OUTER JOIN "bug_b"
ON ( "bug_a"."id" = "bug_b"."a_id" )
LEFT OUTER JOIN "bug_c"
ON ( "bug_a"."id" = "bug_c"."a_id" )
GROUP BY "bug_a"."id"
`
I think it should be:
`sql
SELECT "bug_a"."id",
Count(distinct "bug_b"."id") AS "b_count",
Count(distinct "bug_c"."id") AS "c_count"
FROM "bug_a"
LEFT OUTER JOIN "bug_b"
ON ( "bug_a"."id" = "bug_b"."a_id" )
LEFT OUTER JOIN "bug_c"
ON ( "bug_a"."id" = "bug_c"."a_id" )
GROUP BY "bug_a"."id"
`
Reproduces on sqlite3 as well as heroku-hosted postgresql.