[Django] #32828: Wrong .count() for GROUP BY on ordered queryset

[Django]

#32828: Wrong .count() for GROUP BY on ordered queryset
-------------------------------------+-------------------------------------
Reporter: Maxim | Owner: nobody
Petrov |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
{{{#!python
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:
{{{#!sql
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:
{{{#!sql
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/32828>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32828: Wrong .count() for GROUP BY on ordered queryset
-------------------------------------+-------------------------------------

Reporter: Maxim Petrov | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

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

`count()` calls `SELECT COUNT(*)` (as described in
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#count
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
[https://docs.djangoproject.com/en/3.2/topics/db/aggregation/#interaction-
with-default-ordering-or-order-by 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"))
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32828#comment:1>