annotate() with two Count('related_object') gives both as the bigger one

[Aur Saraf]

Minimal repro: ​https://github.com/sonoflilit/django-count-bug

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)

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:

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.

[Aur Saraf]

If I get some mentoring, I may be able to find time during the weekend to solve it and submit my first django pull request.

[Simon Charette]

This is a ​document caveat of the ORM when combining multiple aggregations as JOIN are used instead of subqueries (see #10060).