Opened 3 years ago

Last modified 4 months ago

#19726 new Cleanup/optimization

Ordering on booleans works different with SQLite and Postgres

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: master
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 (6)

comment:1 Changed 3 years ago by herwin@…

  • Cc herwin@… added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 3 years ago by russellm

  • Resolution set to wontfix
  • Status changed from new to closed

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 Changed 2 years ago 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.

comment:4 Changed 5 months ago 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.

comment:5 Changed 5 months ago by jarshwah

  • Cc josh.smeaton@… added
  • Resolution wontfix deleted
  • Status changed from closed to new
  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.4 to master

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.

Last edited 5 months ago by shaib (previous) (diff)

comment:6 Changed 4 months ago by timgraham

  • Type changed from Uncategorized to Cleanup/optimization
Note: See TracTickets for help on using tickets.
Back to Top