[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
-------------------------------------+-------------------------------------