#26898: annotate() with two Count('related_object') gives both as the bigger one
-------------------------------------+-------------------------------------
Reporter: SonOfLilit | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: count,annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by SonOfLilit):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old 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`.
New description:
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`.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26898#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.7c647f4515db2d0d698b350fd93a4c31%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
