Wrong .count() for GROUP BY on ordered queryset

[Maxim Petrov]

class Foo(models.Model):
    name = models.TextField()
    date = models.DateTimeField()

queryset = Foo.objects.order_by("date").values("name").annotate(models.Count("name"))

For this queryset Django use date in GROUP BY:

SELECT "foo"."name", COUNT("foo"."name") AS "name__count" FROM "foo" GROUP BY "foo"."name", "foo"."date" ORDER BY "foo"."date" ASC

But queryset.count() doesn't use it:

SELECT COUNT(*) FROM (SELECT "foo"."name" AS Col1, COUNT("foo"."name") AS "name__count" FROM "foo" GROUP BY "foo"."name")

So queryset.count() isn't equal real queryset length.

[Mariusz Felisiak]

Thanks for this report, however these queries can return different results (see also #30655).

count() calls SELECT COUNT(*) (as described in ​docs) without taking ordering into account, so in your case it returns the number of Foo's names (this behavior is in Django since 7bc57a6d71dd4d00bb09cfa67be547591fd759ce).

It can be surprising that columns from order_by() calls are included in the GROUP BY clauses, that's we it's ​documented.

To get the same results you can clear ordering or add data to the values():

Foo.objects.order_by("date").values("name").annotate(models.Count("name")).order_by()
Foo.objects.order_by("date").values("name", "date").annotate(models.Count("name"))