Opened 11 years ago

Closed 18 months ago

Last modified 18 months ago

#19726 closed Cleanup/optimization (invalid)

Ordering on booleans works different with SQLite and Postgres

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: herwin@…, josh.smeaton@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

(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.

Change History (8)

comment:1 by herwin@…, 11 years ago

Cc: herwin@… added

comment:2 by Russell Keith-Magee, 11 years ago

Resolution: wontfix
Status: newclosed

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.

comment:3 by anonymous, 10 years ago

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.

comment:4 by Felipe, 8 years ago

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.

comment:5 by Josh Smeaton, 8 years ago

Cc: josh.smeaton@… added
Resolution: wontfix
Status: closednew
Triage Stage: UnreviewedAccepted
Version: 1.4master

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 in #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.

Last edited 7 years ago by Tim Graham (previous) (diff)

comment:6 by Tim Graham, 8 years ago

Type: UncategorizedCleanup/optimization

comment:7 by bcail, 18 months ago

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?

comment:8 by Simon Charette, 18 months ago

Resolution: invalid
Status: newclosed

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.

Last edited 18 months ago by Simon Charette (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top