[Django] #19726: Ordering on booleans works different with SQLite and Postgres
Django
----------------------------------------------+--------------------
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
(And I expect all other databases behave like Postgres, but I haven't
checked this)
When using a model with a boolean field, you can retreive a QuerySet and
order it on the boolean field. In Postgres, the true comes first, in
SQLite, false comes first.
Expected problem: SQLite uses integers for storing the booleans, even
though the field type is called bool. 0 means false, 1 means true. So
sorting on a boolean field behaves like a numeric sort, where 0 comes
before 1.
Though the bug is actually caused by the strange behaviour of SQLite, it's
far from optimal to get different behaviour just by switching the database
backend.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by herwin@…):
* cc: herwin@… (added)
* needs_docs: => 0
* needs_tests: => 0
* needs_better_patch: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:1>
Django
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.4
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by russellm):
* status: new => closed
* resolution: => wontfix
Comment:
This is one of those situations where the bug report is 100% correct, but
we mark the bug wontfix anyway.
Although Django's ORM is an abstraction over the database providing some
measure of database independence, it doesn't mean you can completely stop
caring about the underlying data store. There are many subtle differences
between backends, ranging from handling of different datatypes, ordering,
all the way to performance considerations.
"Fixing" this sort of problem would require a lot of code, would probably
make the SQLite backend more fragile (since it would be more complex), and
would ultimately only help one specific type of use case -- the developer
who switches databases between development and production. I'm not
convinced this is a cost worth assuming, so I'm marking this wontfix.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:2>
Django
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) | Resolution: wontfix
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by anonymous):
I'd just like to follow up with another scenario which might not have been
considered - developers building reusable apps, or entire projects
designed to be setup and deployed by others (e.g. Sentry).
This is vastly different to "one specific type of use case -- the
developer who switches databases between development and production", as
this assumes that the developer is working on one app/project and are also
the ones who deploy the project.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:3>
Django
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: closed
Component: Database layer | Version: 1.4
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felipeochoa):
I just solved this by annotating the model as follows:
{{{
Transaction.objects.annotate(
submitted_as_0_1=Case(When(submitted=True, then=Value(1)),
default=Value(0),
output_field=PositiveSmallIntegerField())
).order_by('submitted_as_0_1')
}}}
Maybe we can extend `order_by` to automate this translation: There could
be a double-underscore extension like `__as_0_1` that one could use in
`order_by` fields that would be automatically converted into this
annotation. I haven't benchmarked the performance impact of this change,
but since it's an opt-in feature, users can make their own decisions.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:4>
Django
Reporter: anonymous | Owner: nobody
Type: Uncategorized | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
* status: closed => new
* version: 1.4 => master
* resolution: wontfix =>
* stage: Unreviewed => Accepted
Comment:
Reopening based on some discussion here:
https://groups.google.com/forum/#!topic/django-developers/h5ok_KeXYW4
Basically, order_by needs to support __lookup syntax via F() support for
__lookup syntax. That ticket is tracked here
https://code.djangoproject.com/ticket/24747.
Once that is done, we can add a transform to boolean field that can be
used for consistent ordering. A transform can be added now and used
directly in the order_by:
{{{
class ConsistentOrdering(Transform):
# implementation
Transaction.objects.order_by(ConsistentOrdering('submitted').desc())
}}}
But I don't think we should close this ticket until both the transform are
created and order_by can leverage __lookup syntax.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:5>
Django
Reporter: anonymous | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* type: Uncategorized => Cleanup/optimization
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:6>
Django
Reporter: anonymous | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by bcail):
>>In Postgres, the true comes first
I've been looking at this issue, and there's something weird. When I sort
by a boolean field in postgres, I see false values first (postgresql 13):
{{{
playground=# \d stock_availability;
Table "public.stock_availability"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
product_id | integer | | not null |
available | boolean | | not null |
Indexes:
"stock_availability_pkey" PRIMARY KEY, btree (product_id)
playground=# select * from stock_availability;
product_id | available
------------+-----------
100 | t
200 | f
300 | t
400 | t
500 | t
600 | t
700 | f
800 | f
playground=# select * from stock_availability order by available;
product_id | available
------------+-----------
200 | f
700 | f
800 | f
100 | t
600 | t
500 | t
300 | t
400 | t
}}}
What am I missing?
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:7>
Django
Reporter: anonymous | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: dev
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* status: new => closed
* resolution: => invalid
Comment:
You're not missing anything bcail.
From the oldest versions of PostgreSQL (9.0.21 released in 2015), SQLite
(3.9.0 released in 2015), and MySQL (5.6.25 released in 2015) I could get
to run none of them exhibit the behaviour reported here.
All of them ordered false values before true ones so I believe this issue
might have been invalid all that time.
Thanks for surfacing bcail.
--
Ticket URL: <https://code.djangoproject.com/ticket/19726#comment:8>