[Django] #26434: Inconsistent results of QuerySet count() method using PostgreSQL backend prior and post the QuerySet evaluation

Django

unread,

Mar 31, 2016, 9:58:36 AM3/31/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) | Keywords: postgresql queryset
Severity: Normal | count annotate aggreagate order_by
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Using a PostgreSQL backend database, in some cases with QuerySets
involving '''annotations''', '''values''' or '''values_list''' and
ordering('''order_by''') clauses the resulting QuerySet '''count()'''
method fails to predict the real row result unless that QuerySet is
evaluated.

For instance:

{{{
#!python
q = BundleVersion.objects.order_by('-id').values_list("port__id",
"asset_bundle__id").annotate(max=models.Max("id"))
}}}

This QuerySet using PostgreSQL would, in fact, group the results by
'''BundleVersion.id''' instead of the tuple ("port!__id",
"asset_bundle!__id") due to the '''order_by''' clause using a column not
in the '''values_list'''. This is a documented behavior.

The problem is that if '''q''' is not evaluated yet, calling
'''q.count()''' will return an amount of rows as if the grouping was done
on the tuple ("port!__id", "asset_bundle!__id"). '''After''' evaluating
'''q''', or calling '''len(q)''', the result of '''q.count()''' will be
the expected, as if the grouping was done using ''''BundleVersion.id''''.

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

Django

unread,

Mar 31, 2016, 11:01:26 AM3/31/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0

Comment:

Could you provide a test for Django's test suite in
`tests/queries/tests.py` that demonstrates the issue?

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

Django

unread,

Apr 1, 2016, 1:07:23 PM4/1/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by kamandol):

Replying to [comment:1 timgraham]:

> Could you provide a test for Django's test suite in
`tests/queries/tests.py` that demonstrates the issue?

Here is the commit with the demonstration:

https://github.com/sp-ricard-
valverde/django/commit/c50afafdb02880e4c941c2a72a215bee80de3aed

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:2>

Django

unread,

Apr 2, 2016, 7:41:31 AM4/2/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by yakky):

Tests looks legitimate, but I don't get this one https://github.com/sp-
ricard-valverde/django/commit/c50afafdb02880e4c941c2a72a215bee80de3aed
#diff-ce9b52a66d03e851a9828377263dc04bR3864 ; or rather I don't get why is
labeled as 'OK' as it the concatenation of the previous ones

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:3>

Django

unread,

Apr 2, 2016, 10:43:54 AM4/2/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by yakky):

* cc: github@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:4>

Django

unread,

Apr 4, 2016, 6:03:13 AM4/4/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by kamandol):

Replying to [comment:3 yakky]:

> Tests looks legitimate, but I don't get this one https://github.com/sp-
ricard-valverde/django/commit/c50afafdb02880e4c941c2a72a215bee80de3aed
#diff-ce9b52a66d03e851a9828377263dc04bR3864 ; or rather I don't get why is
labeled as 'OK' as it the concatenation of the previous ones

Sorry, my bad, it's a bad choice for a test title. What it demonstrates is
that the second assert in that test is '''successful''' even though a new
QuerySet is built and, according to the other failed tests it should fail
too.

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:5>

Django

unread,

Apr 4, 2016, 6:09:44 AM4/4/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by kamandol):

Updated test comments and clearer test names

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:6>

Django

unread,

Apr 4, 2016, 12:55:12 PM4/4/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |

order_by |
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by timgraham):

* stage: Unreviewed => Accepted

Comment:

If you could put the tests on a branch of you fork other than "master"
that seems a bit safer. There is probably no need for `skipUnlessDBEngine`
- is there a reason the queries shouldn't also work on other databases?
Anyway, the usual way to skip tests is based on database features.

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:7>

Django

unread,

Apr 4, 2016, 1:00:49 PM4/4/16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by kamandol):

I will change the branch as requested.

You're right, the faulty behavior was detected on PostgreSQL, but on MySQL
worked correctly so the test should pass. Could not test other db engines
though. I will remove that extra piece of code.

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:8>

Django

unread,

Jun 9, 2019, 8:20:18 AM6/9/19

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |

Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Can Sarıgöl):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/11454 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:9>

Django

unread,

Jun 10, 2019, 2:14:25 AM6/10/19

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: master

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* has_patch: 1 => 0
* version: 1.9 => master

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:10>

Django

unread,

Jun 10, 2019, 2:22:29 AM6/10/19

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Can Sarıgöl):

I want to discuss when collecting group by columns why are order by
columns added into extensions.

For example:
Model.objects.values("col_a").annotate(max=Max("col_b")).order_by('col_c')
current query:

{{{
select col_a, MAX(col_b) as max
from Model
group by col_a, col_c
order by col_c
}}}

I think the expected behavior has should be like that:
django.db.utils.ProgrammingError: column "Model.col_c" must appear in the
GROUP BY clause or be used in an aggregate function

Because this behavior changes my group by and result. if I'm lucky, my
result doesn't change.

If we check test_annotation_with_value, 'name' column is being added into
group_by because Book model has Meta.ordering.

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:11>

Django

unread,

Jun 10, 2019, 10:41:34 AM6/10/19

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Can Sarıgöl):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:12>

Django

unread,

Jan 21, 2020, 6:48:21 AM1/21/20

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: nobody
Type: Bug | Status: new

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:13>

Django

unread,

May 16, 2025, 3:27:46 AMMay 16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet count() method using PostgreSQL backend
prior and post the QuerySet evaluation
-------------------------------------+-------------------------------------

Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* owner: nobody => Michal Mládek
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:14>

Django

unread,

May 16, 2025, 4:31:49 AMMay 16

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* summary:

Inconsistent results of QuerySet count() method using PostgreSQL
backend prior and post the QuerySet evaluation

=>
Inconsistent results of QuerySet.count() when ordering is not a subset
of explicit grouping.

Comment:

FWIW the issue is not Postgres specific (I reproduced against SQLite).

The problem appears to be caused by `sql.Query.get_aggregation`'s call to
`clear_ordering(force=False)`. It seems like the later should skip
clearing when `isinstance(self.group_by, tuple)` and `self.order_by` is
not a subset of `self.group_by` as `force=False` must only clear if doing
to preserves the semantic of the query which it doesn't if `order_by`
includes members missing from the group by (as they would normally be
added).

I adjusted the ticket summary accordingly.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:15>

Django

unread,

May 16, 2025, 7:39:07 AMMay 16

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

Hello Simon,

Thank you very much for clarifying the bug — especially for pointing out
that it’s not Postgres-specific, and also for the hint about a subset. I
think everything will go much more smoothly and quickly now.

Replying to [comment:15 Simon Charette]:

> FWIW the issue is not Postgres specific (I reproduced against SQLite).
>
> The problem appears to be caused by `sql.Query.get_aggregation`'s

[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L528-L531

call] to `clear_ordering(force=False)`. It seems like

[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L2313-L2316

the later should skip clearing] when `isinstance(self.group_by, tuple)`

and `self.order_by + self.extra_order_by` is not a subset of
`self.group_by` as `force=False` must only clear if doing so preserves the
semantic of the query which clearly doesn't if the ordering includes

members missing from the group by

([https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/compiler.py#L164-L169
as they would normally be added at a later stage]).

>
> I adjusted the ticket summary accordingly.
--

Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:16>

Django

unread,

May 16, 2025, 12:29:16 PMMay 16

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

Replying to [comment:15 Simon Charette]:

Using `order_by('...')` on a column that is also used implicitly for
grouping in an aggregation queryset is a misuse of the ORM AFAIK. So what
should we do about it?

Should we implement **a patch** that triggers a **warning** if
`order_by('...')` includes a column that ends up in the `annotate(...)`, I
mean `GROUP BY ...` clause? Or should we instead modify the behavior of
`queryset.count()` to preserve ordering in such edge cases?

In case of implementing a warning, this doesn't look like a bug - it would
be better treated as a feature request, I believe.

Also, the documentation already warns about this interaction quite
clearly:
https://docs.djangoproject.com/en/5.2/topics/db/aggregation/#interaction-
with-order-by — and that warning has been present since the LTS version
Django 3.2.

> Hello Simon,
>
> Thank you very much for clarifying the bug — especially for pointing out
that it’s not Postgres-specific, and also for the hint about a subset. I
think everything will go much more smoothly and quickly now.
>
> Replying to [comment:15 Simon Charette]:
> > FWIW the issue is not Postgres specific (I reproduced against SQLite).
> >
> > The problem appears to be caused by `sql.Query.get_aggregation`'s
[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L528-L531
call] to `clear_ordering(force=False)`. It seems like
[https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/query.py#L2313-L2316
the later should skip clearing] when `isinstance(self.group_by, tuple)`
and `self.order_by + self.extra_order_by` is not a subset of
`self.group_by` as `force=False` must only clear if doing so preserves the
semantic of the query which clearly doesn't if the ordering includes
members missing from the group by
([https://github.com/django/django/blob/994dc6d8a1bae717baa236b65e11cf91ce181c53/django/db/models/sql/compiler.py#L164-L169
as they would normally be added at a later stage]).
> >
> > I adjusted the ticket summary accordingly.
--

Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:17>

Django

unread,

May 16, 2025, 2:25:48 PMMay 16

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> Using order_by('...') on a column that is also used implicitly for
grouping in an aggregation queryset is a misuse of the ORM AFAIK. So what
should we do about it?

I share your sentiment that a warning should be emitted but that likely
warrant a separate ticket inspired by #14357, #32546 (there might be one
already I just can't find it now).

I think we should keep this one focused on making sure the right results
are returned in the mean time as it's caused by an over-eager optimization
(pruning an `ORDER BY` for performance reasons).

The way things are currently setup the ''not quite correct'' example is
wrong as the `order_by("name")` is elided when `count()` is called but not
when the full query is materialized.

The example you linked is about aggregate function annotations but
[https://dryorm.xterm.info/ticket-26434 you'll notice that the gotcha is
not honored when aggregation is used].
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:18>

Django

unread,

May 16, 2025, 2:27:24 PMMay 16

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* cc: Simon Charette (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:19>

Django

unread,

May 21, 2025, 1:55:39 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* Attachment "main_logic.patch" added.

Django

unread,

May 21, 2025, 1:57:18 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

I added patch in attachment, I am still working on in it. I think I am one
step closer to the solution.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:20>

Django

unread,

May 21, 2025, 1:58:11 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* Attachment "main_logic.patch" removed.

Django

unread,

May 21, 2025, 2:00:03 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* Attachment "main_logic.patch" added.

Django

unread,

May 21, 2025, 2:03:38 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* Attachment "main_logic.patch" removed.

Django

unread,

May 21, 2025, 2:03:48 AMMay 21

to django-...@googlegroups.com

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* Attachment "main_logic.patch" added.

Django

unread,

May 21, 2025, 4:19:59 AMMay 21

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Michal Mládek):

* Attachment "main_logic.2.patch" added.

Django

unread,

May 21, 2025, 4:20:47 AMMay 21

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

Now the patch is done. I am continue with regression tests...
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:21>

Django

unread,

May 26, 2025, 12:53:25 PMMay 26

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:22>

Django

unread,

May 26, 2025, 3:53:15 PMMay 26

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 0 => 1

Comment:

Left a few comments on the PR, the patch is quite invasive and could be
reduced to a subtle changes in how `sql.Query.get_aggregation` calls
`clear_ordering` as described in comment:15.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:23>

Django

unread,

Jun 3, 2025, 1:20:51 AMJun 3

to django-...@googlegroups.com

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Michal Mládek):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:24>

Django

unread,

Jun 8, 2025, 8:12:09 AMJun 8

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

I left there 2 PRs because I reached 2 similar solutions, one of them
seems more detailed but more power/time consuming and the other is simple
and straightforward.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:25>

Django

unread,

Oct 17, 2025, 11:28:44 AMOct 17

to django-...@googlegroups.com

Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:26>

Django

unread,

Oct 27, 2025, 12:52:55 PMOct 27

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin

order_by |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_tests: 1 => 0
* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:27>

Django

unread,

Oct 27, 2025, 3:11:31 PMOct 27

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"ea3a71c2d09f8281d8a50ed20e40e1fb13db5cd9" ea3a71c2]:
{{{#!CommitTicketReference repository=""
revision="ea3a71c2d09f8281d8a50ed20e40e1fb13db5cd9"
Fixed #26434 -- Removed faulty clearing of ordering field when missing
from explicit grouping.

Co-authored-by: Simon Charette <chare...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:28>

Django

unread,

Oct 28, 2025, 4:40:09 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

This caused a [https://djangoci.com/job/django-
oracle/database=oracle19,label=oracle,python=python3.14/lastCompletedBuild/testReport/
regression] on Oracle but not only. `group_by` is a tuple of `Col`
expressions, `order_by` is a list of strings, so `{*self.order_by,
*self.extra_order_by}` will never be a subset of `group_by`.

Previously:

{{{#!sql
SELECT
...
FROM "AGGREGATION_REGRESS_BOOK"
WHERE "AGGREGATION_REGRESS_BOOK"."ID" IN (
SELECT MAX(U0."ID") AS "ID__MAX"
FROM "AGGREGATION_REGRESS_BOOK" U0
GROUP BY U0."CONTACT_ID"
)
ORDER BY "AGGREGATION_REGRESS_BOOK"."ID" ASC
}}}
with this patch

{{{#!sql
SELECT
...
FROM "AGGREGATION_REGRESS_BOOK"
WHERE "AGGREGATION_REGRESS_BOOK"."ID" IN (
SELECT MAX(U0."ID") AS "ID__MAX"
FROM "AGGREGATION_REGRESS_BOOK" U0
GROUP BY U0."CONTACT_ID"
ORDER BY U0."CONTACT_ID" ASC
)
ORDER BY "AGGREGATION_REGRESS_BOOK"."ID" ASC
}}}

As far as I'm aware, we would need to translate string aliases (`order_by`
and `extra_order_by`) to `Col` expressions but this may be expensive here
(or at least non-trivial).
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:29>

Django

unread,

Oct 28, 2025, 10:21:21 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> we would need to translate string aliases (order_by and extra_order_by)

to Col expressions but this may be expensive here (or at least non-
trivial).

It's not only a matter of being expensive and non trivial it simply cannot
be done in the way the ORM currently resolves `order_by` members at the
last minute. Trying to eagerly resolve `order_by`, instead of lazily at
compilation time, could break tons of queries as it would change the
`JOIN` reuse logic and produce `JOIN` left overs when `order_by` is called
to explicitly clear (the ORM doesn't have a way to elide `JOIN`s when they
are added see ticket:35865#comment:6).

It might be worth reverting and working on a solution more localized to
`get_aggregation` as it's terminal (it's the method called by `aggregate`
and thus `count`) so we know the query cannot be altered further from
there and thus we could eagerly resolve `order_by` from there.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:30>

Django

unread,

Oct 28, 2025, 10:38:45 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin
order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

Very helpful. I'll prepare a reversion.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:31>

Django

unread,

Oct 28, 2025, 10:47:45 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek

Type: Bug | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |

order_by |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* resolution: fixed =>
* stage: Ready for checkin => Accepted
* status: closed => new

Comment:

[https://github.com/django/django/pull/20014 Revert PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:32>

Django

unread,

Oct 28, 2025, 11:40:13 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed

* status: new => closed

Comment:

In [changeset:"43933a1dca07047e95ec990d9289d0212668009e" 43933a1d]:
{{{#!CommitTicketReference repository=""
revision="43933a1dca07047e95ec990d9289d0212668009e"
Reverted "Fixed #26434 -- Removed faulty clearing of ordering field when
missing from explicit grouping."

This reverts commit ea3a71c2d09f8281d8a50ed20e40e1fb13db5cd9.

The implementation was flawed, as self.group_by contains Cols, not
aliases.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:33>

Django

unread,

Oct 28, 2025, 11:40:28 AMOct 28

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek

Type: Bug | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |

Has patch: 0 | Needs documentation: 0

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

Changes (by Jacob Walls):

* has_patch: 1 => 0
* resolution: fixed =>
* status: closed => new

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:34>

Django

unread,

Oct 29, 2025, 4:59:49 AMOct 29

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

I'm not sure what is expected from me at this point - there’s no single
comment addressing the solution in PR #19524
or the question(s) I raised there. That PR was rejected without a clear
explanation what is wrong, even though the issue has now been reopened and
is being discussed again.

A previously functional but incorrect implementation was first accepted
and then reverted. The proposed fix in PR #19524
, which resolved the issue by performing an early order_by, was dismissed
in favor of the flawed implementation from PR #19519
.

Could someone please clarify what exactly was wrong with the approach in
#19524, and what I should avoid when preparing a new attempt?
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:35>

Django

unread,

Oct 29, 2025, 7:30:03 AMOct 29

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

comment:30 points out what will not work in that PR: we cannot eagerly
resolve `order_by()` aliases while the query is still being built.
comment:30 suggests to move the implementation to `get_aggregation()`
where we know the query is terminal (will not be chained to anything else
afterward). Thanks for staying on top of it.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:36>

Django

unread,

Oct 31, 2025, 4:14:13 AMOct 31

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Michal Mládek):

Ah, I see — so even though my implementation with eagerly resolving
order_by() aliases could technically work (since it was operating on a
cloned query), it’s not ideal because it also affects other ORM components
that rely on clear_ordering() being called in specific contexts.
That makes sense — since the underlying issue is within get_aggregation(),
moving the logic there should be straightforward. Thanks for clarifying!
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:37>

Django

unread,

Dec 7, 2025, 6:18:09 PM (9 days ago) Dec 7

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |
order_by |

Has patch: 1 | Needs documentation: 0

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

Changes (by Simon Charette):

* has_patch: 0 => 1

Comment:

The proposed patch might require a few stylistict changes (comments,
overly asserting test) but otherwise LGTM.
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:38>

Django

unread,

Dec 12, 2025, 1:51:36 PM (4 days ago) Dec 12

to django-...@googlegroups.com

Needs tests: 1 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

* needs_tests: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:39>

Django

unread,

Dec 12, 2025, 3:54:08 PM (4 days ago) Dec 12

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin

order_by |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0

* needs_tests: 1 => 0
* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:40>

Django

unread,

Dec 12, 2025, 7:27:46 PM (4 days ago) Dec 12

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Accepted
count annotate aggreagate |

order_by |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:41>

Django

unread,

Dec 13, 2025, 10:29:39 AM (3 days ago) Dec 13

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: postgresql queryset | Triage Stage: Ready for
count annotate aggreagate | checkin

order_by |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:42>

Django

unread,

Dec 15, 2025, 3:24:02 PM (23 hours ago) Dec 15

to django-...@googlegroups.com

#26434: Inconsistent results of QuerySet.count() when ordering is not a subset of
explicit grouping.
-------------------------------------+-------------------------------------
Reporter: kamandol | Owner: Michal
| Mládek

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Changes (by Jacob Walls <jacobtylerwalls@…>):

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

Comment:

In [changeset:"2ce5cb0f7a4618dfdc5f5c10e53e2e9b9543d298" 2ce5cb0f]:
{{{#!CommitTicketReference repository=""
revision="2ce5cb0f7a4618dfdc5f5c10e53e2e9b9543d298"

Fixed #26434 -- Removed faulty clearing of ordering field when missing
from explicit grouping.

Co-authored-by: Simon Charette <chare...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26434#comment:43>

Reply all

Reply to author

Forward