Opened 8 years ago

Closed 8 years ago

#26898 closed Uncategorized (duplicate)

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

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 (last modified by 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.

Change History (3)

comment:1 by Aur Saraf, 8 years ago

Description: modified (diff)

comment:2 by Aur Saraf, 8 years ago

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.

comment:3 by Simon Charette, 8 years ago

Resolution: duplicate
Status: newclosed

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

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