To null or NOT to NULL

23 Aug 2018 • ruby

Here’s the story of how ActiveRecord’s where.not(field: nil) may still happen to return nil in Postgres.

This funny thing happened to me today when I was checking Honeybadger errors having my cup of morning coffee.

The essence of the error looked like this:

irb(main):001:0> HotelRef.where.not(data: nil).
irb(main):002:0*   pluck(:data).
irb(main):003:0*   map { |h| h.dig('name') }
NoMethodError: undefined method 'dig' for nil:NilClass

Hmmm. I moved my coffee away and decided to dive into details:

irb(main):004:0> ap HotelRef.where.not(data: nil).pluck(:id, :data)
[
    [0] [
        [0] 123,
        [1] {
            "name" => "some_name"
        }
    ],
    [1] [
        [0] 456,
        [1] {
            "name" => "other_name"
        }
    ],
    [2] [
        [0] 123456,
        [1] nil       # WHAT ???!!!
    ]
]

O_o

Let’s take a look at the field definition:

CREATE TABLE public.hotel_refs
(
  ...
  data json,
  ...
);

Now let’s try in pure SQL:

my_db=> select id, data from hotel_refs
my_db-> where id=123456 and data is not null;
   id    | data
---------+------
  123456 | null
(1 row)

But wait…

my_db=> select id, data from hotel_refs where data is null limit 1;
   id    | data
---------+------
      12 |
(1 row)

Okay, finally we can see that null that we’ve got in the first case is not a DB’s NULL, actually. It is just a valid JSON object like in:

irb(main):005:0> nil.to_json
=> "null"
 ruby  postgres  json  null

Comment

Has something on your mind? Tweet it!

Related Posts