Ordering on booleans works different with SQLite and Postgres

[anonymous]

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

[Russell Keith-Magee]

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.

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

[Felipe]

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.

[Josh Smeaton]

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.

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

[Simon Charette]

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.