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 Version 1

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 (1)

comment:1 by Aur Saraf, 8 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top