CODEWITHSUNDEEP
pythondjangosql-order-by
funnydman
funnydman

Reputation: 11356

Why order_by has miscellaneous behavior with nulls in databases?

There is a difference between the behaviour of ORDER_BY in cases where value is null, for instance, Postgres treats these values as greatest. I guess there is no "correct" behaviour and it depends on a particular case. I found this after some of the tests on the SQLite started to fail.

For example:

# Postgres
>>> Person.objects.order_by('age').values('age')
>>> <QuerySet [{'age': 10}, {'age': 30}, {'age': None}]>

# SQLite
>>> Person.objects.order_by('age').values('age')
>>> <QuerySet [{'age': None}, {'age': 10}, {'age': 30}]>

It is possible to bring behaviour on all databases to one style? And by the way, why there are differences in behaviour?

Upvotes: 0

Views: 57

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477170

Because there is no consensus on how a database should order NULL values.

You can make the order non-ambiguous by specifying if the NULLs should be put first or last by using an F object, and call .asc(..) [Django-doc] or .desc(..) [Django-doc] on it, and set the nulls_first=… or nulls_last=… to True. For example:

from django.db.models import F

Person.objects.order_by(F('age').asc(nulls_last=True)).values('age')

This will then result in a query that looks like (SQLite):

SELECT age
FROM person
ORDER BY age IS NULL, age;

Since False/0 is ordered before True/1, it will thus order ages that are not NULL before the ages who are NULL.

The same query in Postgres looks like:

SELECT age
FROM person
ORDER BY age ASC NULLS LAST;

Upvotes: 3

Related Questions