[Django] #28657: Strangely odd behaviour when ordering QuerySet by BooleanField.
Django
-------------------------------------+-------------------------------------
Reporter: Leonardo | Owner: nobody
Arroyo |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) | Keywords: queryset, boolean,
Severity: Normal | order, ordering
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I have recently encountered problems with duplicated results in pagination
and trying to pin down the problem this seems to be a problem in the
Django queryset. I'll attempt to explain the problem. Take a look at the
following Queryset:
{{{
>>> p = Project.objects.all().order_by("closed")
>>> p
<QuerySet [<Project: Campanha de Incentivo - Alimentando a Solidariedade
(Outubro)>, <Project: Campanha de Incentivo - Projeto Fora das 4 Paredes>,
<Project: projeto luciele>, <Project: Campanha de Incentivo - Menos é
Mais>, <Project: Campanha de Incentivo - Projeto Conviver>, <Project:
Campanha de Incentivo - Aula de Música>, <Project: RPINCLUSÃO -
Informações, Acessibilidade e Inclusão>, <Project: Ong ong>, <Project:
Campanha de Incentivo - Projeto Alimentando Vidas>, <Project: Bola na
Rede>, <Project: Campanha de Incentivo - Projeto Bolas de Pelos>,
<Project: Mãos Solidárias>, <Project: Campanha de Incentivo - GRUPO DE
ATITUDE SOCIAL - Moradores de Rua>, <Project: APAE pede Socorro>,
<Project: Campanha de Incentivo - Anjos da Madrugada>, <Project: Pintura
de Casas Emergenciais com o TETO - São Paulo>, <Project: Campanha de
Incentivo - Aos Olhos do Pai>, <Project: Legião da Boa Vontade>, <Project:
Campanha de Incentivo - Você é a gota que faltava, Doe Sangue!>, <Project:
Campanha de Incentivo - Sonhar com Esperança>, '...(remaining elements
truncated)...']>
}}}
The 'closed' field here is a Boolean. Everything seems to be correct
except for this:
{{{
>>> p[0]
<Project: Campanha de Incentivo - Anjos da Madrugada>
>>> p[1]
<Project: Bola na Rede>
}}}
When I get items from the queryset, they don't match the queryset __repr__
order. What is even weirder is that neither the __repr__ order nor the
__getitem__ order matches the PostgreSQL query.
{{{
>>> print(p.query)
SELECT "ovp_projects_project"."id", "ovp_projects_project"."image_id",
"ovp_projects_project"."address_id", "ovp_projects_project"."owner_id",
"ovp_projects_project"."organization_id", "ovp_projects_project"."name",
"ovp_projects_project"."slug", "ovp_projects_project"."published",
"ovp_projects_project"."highlighted",
"ovp_projects_project"."applied_count",
"ovp_projects_project"."max_applies",
"ovp_projects_project"."max_applies_from_roles",
"ovp_projects_project"."public_project",
"ovp_projects_project"."minimum_age",
"ovp_projects_project"."hidden_address",
"ovp_projects_project"."crowdfunding",
"ovp_projects_project"."published_date", "ovp_projects_project"."closed",
"ovp_projects_project"."closed_date", "ovp_projects_project"."deleted",
"ovp_projects_project"."deleted_date",
"ovp_projects_project"."created_date",
"ovp_projects_project"."modified_date", "ovp_projects_project"."details",
"ovp_projects_project"."description" FROM "ovp_projects_project" ORDER BY
"ovp_projects_project"."closed" ASC
}}}
When hitting the database with such query, only removing a couple of
fields to make it easier to read, this is what I get:
[[Image(blob:https://imgur.com/c888a1a0-cf27-44f4-b3d5-3a91174eb9ab)]]
These are 3 different orders. Now, there's one little thing I can do to
fix the QuerySet, which is iterating over it.
{{{
>>> for x in p:
... print(x.pk)
...
81
74
75
91
83
61
(...)
}}}
Suddenly the queryset now matches the result from my PostgreSQL query and
everything is now correct.
{{{
>>> p
<QuerySet [<Project: Pintura de Casas Emergenciais com o TETO - São
Paulo>, <Project: Campanha de Incentivo - Anjos da Madrugada>, <Project:
Ong ong>, <Project: projeto luciele>, <Project: Campanha de Incentivo -
Projeto Alimentando Vidas>, <Project: Campanha de Incentivo - Menos é
Mais>, <Project: Campanha de Incentivo - Alimentando a Solidariedade
(Outubro)>, <Project: Campanha de Incentivo - Aula de Música>, <Project:
Campanha de Incentivo - Projeto Fora das 4 Paredes>, <Project: Campanha
de Incentivo - Padoca Artesanal>, <Project: Campanha de Incentivo -
EmpoderA>, <Project: Campanha de Incentivo - Moto Sopa>, <Project:
Campanha Incentivo - Projeto Vale Jequitinhonha>, <Project: Campanha de
Incentivo - Eternas Crianças>, <Project: Campanha de Incentivo - Festa de
Natal>, <Project: Treinador voluntário de futebol - Águias Livres>,
<Project: Fundação Gol de Letra - Faça parte deste time!>, <Project:
Campanha de Incentivo - Ganhando Almas>, <Project: Campanha de
Incentivo - Acolchoados de Ponto Cruz e Patchwork>, <Project: Campanha de
Incentivo - Show de Encantamento Solidário>, '...(remaining elements
truncated)...']>
>>> p[0]
<Project: Pintura de Casas Emergenciais com o TETO - São Paulo>
>>> p[1]
<Project: Campanha de Incentivo - Anjos da Madrugada>
}}}
I'm trying to find out where the problem is really happening but I have
limited knowledge about django ORM internals, so I haven't gotten that far
yet. Currently I'm hacking around it by iterating over the queryset before
paginating it and passing it to my view.
Things get even weirder if I try to order by 2 boolean fields, with issues
such as:
{{{
>>> p = Project.objects.all().order_by("closed", "-highlighted")
>>> p
<QuerySet [<Project: Campanha Incentivo - Projeto Vale Jequitinhonha>,
<Project: Campanha de Incentivo - Alimentando a Solidariedade (Outubro)>,
<Project: Campanha de Incentivo - Festa do Dia Das Crianças>, <Project:
Campanha de Incentivo - Cartas para o "Papai Noel">, <Project: Pintura de
Casas Emergenciais com o TETO - São Paulo>, <Project: Campanha de
Incentivo - Padoca Artesanal>, <Project: Campanha de Incentivo -
EmpoderA>, <Project: Construção de Casas Emergenciais com o TETO - São
Paulo - Estrutura e entrega da casa>, <Project: Campanha de Incentivo -
Doação de Brinquedos>, <Project: Campanha de Incentivo - Pequena ajuda,
grande transformação>, <Project: Campanha de Incentivo - Aula de Música>,
<Project: Campanha de Incentivo - Aos Olhos do Pai>, <Project: APAE pede
Socorro>, <Project: Campanha de Incentivo - GRUPO DE ATITUDE SOCIAL -
Moradores de Rua>, <Project: Mãos Solidárias>, <Project: Campanha de
Incentivo - Projeto Bolas de Pelos>, <Project: Campanha de Incentivo -
Projeto Conviver>, <Project: Bola na Rede>, <Project: Campanha de
Incentivo - Você é a gota que faltava, Doe Sangue!>, <Project: projeto
luciele>, '...(remaining elements truncated)...']>
>>> p[0], p[0].pk
(<Project: Pintura de Casas Emergenciais com o TETO - São Paulo>, 81)
>>> p[1], p[1].pk
(<Project: Pintura de Casas Emergenciais com o TETO - São Paulo>, 81)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28657>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset, boolean, | Triage Stage:
order, ordering | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "django-boolean-bug.png" added.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset, boolean, | Triage Stage:
order, ordering | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
[[Image(https://code.djangoproject.com/attachment/ticket/28657/django-
boolean-bug.png)]]
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28657#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: queryset, boolean, | Triage Stage:
order, ordering | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
My hunch is that PostgreSQL is "at fault", not Django. When you execute
`order_by()` with a boolean field, the results with the same value (`True`
or `False`) are ordered randomly. If you want a deterministic order, you
could do something like `.order_by('closed', 'pk')`.
In the future, please see TicketClosingReasons/UseSupportChannels to help
confirm a bug before creating a ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/28657#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: queryset, boolean, | Triage Stage:
order, ordering | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Leonardo Arroyo):
This was indeed the problem. For some reason I assumed ordering by a
boolean field would yield consistent results when LIMITing it on
PostgreSQL. It does not. Ordering by pk + boolean fixes it.
Thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/28657#comment:3>