Correcting different behaviour when ordering dates with nil value in Postgres and SQLite3 with AR

Question

I'm on Rails 4 and have a begin_date:date attribute on a Project model. I'm using ActiveRecord, and the following request returns different results on Postgres vs SQLite when there are nil values:

User.first.projects.order(begin_date: :desc, end_date: :desc)

In Postgres nil values are treated as of higher value than existing value (hence come first), while in SQLite records with nil value comes last.

Am I doing something wrong or is this the expected behaviour?
How could I reconstruct the above query such that it renders equal results (in regard of nil values) on both Postgres and SQLite?

PS. Please, if you have strong opinions that I should use the same db technology in all environments: I thank you for your consideration, but I know about this and this question is not about that subject.

I would prefer to rank them lower than records with existing values. — Fellow Stranger
– Fellow Stranger, Commented Dec 27, 2013 at 1:53

Community · Accepted Answer · 2017-05-23 12:11:33Z

3

Since the default behavior is different across databases, you need to specify the treatment of NULL explicitly.

There are a several ways to do this, but the most portable seems to be described in ORDER BY DATE showing NULLS first then most recent dates and SQL how to make null values come last when sorting ascending, which use a CASE statement to explicitly handle the treatment of null values.

In your case, it would like something like:

order('case when begin_date is null then 1 else 0 end,
     begin_date desc, case when end_date is null then 1 else 0 end, end_date desc')

depending on where you want to nulls to sort. You'd swap the 1s and the 0s if you wanted the nulls to be sorted first.

Another approach would be to sort by an expression with converts nulls into something non-null. I'm not sure what the SQL standard is in this regard in terms of ISNULL, NVL, IFNULL and COALESCE or even which of this is common to both sqllite and postgres.

This recent thread discusses the lack of (and need for) a mechanism within Rails to support this through order.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Dec 27, 2013 at 1:55

Peter Alfvin

29.6k9 gold badges72 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Fellow Stranger Over a year ago

Hm, interesting. And a bit sad. Do you possibly have any explicit code suggestion for my case, when it comes to order by two columns as well (i.e. if first equal, order by second)?

mu is too short Over a year ago

You can also use COALESCE(begin_date, X) to convert the NULLs to something more predictable (assuming the existence of suitable Xs of course).

mu is too short Over a year ago

Yes and AFAIK everyone else is starting to track the standard on this one little tiny thing. Finally. So you can COALESCE in both SQLite and PostgreSQL (and even MySQL).

Fellow Stranger Over a year ago

@muistooshort Would you care to elaborate how an implementation with COALESCE in the above scenario could look like?

mu is too short Over a year ago

It would look like Gratzy's answer to one of the linked questions. The problem with COALESCE is also noted over there.

|

Collectives™ on Stack Overflow

Correcting different behaviour when ordering dates with nil value in Postgres and SQLite3 with AR

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related