2

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.

  1. Am I doing something wrong or is this the expected behaviour?

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

Fellow Stranger
  • 32,129
  • 35
  • 168
  • 232

1 Answers1

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.

Community
  • 1
  • 1
Peter Alfvin
  • 28,599
  • 8
  • 68
  • 106
  • 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)? – Fellow Stranger Dec 27 '13 at 02:07
  • You can also use `COALESCE(begin_date, X)` to convert the NULLs to something more predictable (assuming the existence of suitable `X`s of course). – mu is too short Dec 27 '13 at 02:51
  • 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). – mu is too short Dec 27 '13 at 03:24
  • @muistooshort Would you care to elaborate how an implementation with `COALESCE` in the above scenario could look like? – Fellow Stranger Dec 27 '13 at 03:53
  • It would look like [Gratzy's answer to one of the linked questions](http://stackoverflow.com/a/1498660/479863). The problem with COALESCE is also noted over there. – mu is too short Dec 27 '13 at 03:55
  • @muistooshort I will have to look into `COALSCE` in the future, my knowloedge is too limitied atm. But thank you. – Fellow Stranger Dec 27 '13 at 05:08
  • @PeterAlfvin I cannot get your example code to work ;( I guess I'll lower my ambitions with my app and restructure how the data is presented. – Fellow Stranger Dec 27 '13 at 05:09
  • Sorry about that. See updated code and please share error msg if the correction still doesn't work. – Peter Alfvin Dec 27 '13 at 05:36