Why does where.not on string columns ignores null values?

Ruby on Rails
1

I notice if I create a model Car with the string attribute/column make and do this in the Rails console:

2.1.3 :002 > Car.create make: "Honda"
...
2.1.3 :008 > Car.create
...

I end up with two records, one where make is “Honda” and another where make is null.

  • Car.where(make: "Honda") returns the first car
  • Car.where(make: nil) returns the second car
  • Car.where.not(make: "Honda") returns no records

Why does Car.where.not(make: "Honda") return no records? Logically, I would expect to get all the records that don’t have make set to “Honda”, which would include records with make set to null.

Also, I haven’t quite determined yet exactly what questions are appropriate in this forum versus what are StackOverflow appropriate… if this is not preferred here please delete this and I’ll repost elsewhere.

2

@franz,

This happens because of a quirk in SQL, not specifically because of ActiveRecord.

I find if you’re getting unexpected behavior in ActiveRecord that it’s useful to use AR’s .to_sql method on the end of a query, like this:

irb(main):001:0> User.where.not(email: "geoff").to_sql
=> "SELECT \"users\".* FROM \"users\"  WHERE (\"users\".\"email\" != 'geoff')"

SQL experts, please correct me if I’m wrong, but SQL treats NULL, the absence of something, as something that can’t be equal to something that exists. You need to get a make IS NOT NULL added to your query, and you could do it like this:

Car.where.not(make: "Honda").where.not(make: nil)

Note that you cannot just merge those two conditions into one hash, as the later condition will overwrite the first. It has to be two separate where clauses.

1 Like
3

That makes sense.

So to get all the cars with the make “Honda” it would be:

Car.where(make: "Honda")

And to get all the cars that do not have the make “Honda” would be:

Car.where("make != ? or make is null", "Honda")

I think Car.where.not(make: "Honda").where.not(make: nil) is equivalent to Car.where.not(make: "Honda"):

2.1.3 :016 > Car.where.not(make: "Honda").where.not(make: nil).to_sql
 => "SELECT \"cars\".* FROM \"cars\" WHERE (\"cars\".\"make\" != 'Honda') AND (\"cars\".\"make\" IS NOT NULL)"

In both cases I get the same results even if I have other cars where the make is nil:

2.1.3 :019 > Car.where.not(make: "Honda")
  Car Load (0.1ms)  SELECT "cars".* FROM "cars" WHERE ("cars"."make" != ?)  [["make", "Honda"]]
 => #<ActiveRecord::Relation [#<Car id: 4, make: "Toyota", model: nil, created_at: "2014-11-11 21:39:53", updated_at: "2014-11-11 21:39:53">]> 
2.1.3 :020 > Car.where.not(make: "Honda").where.not(make: nil)
  Car Load (0.1ms)  SELECT "cars".* FROM "cars" WHERE ("cars"."make" != ?) AND ("cars"."make" IS NOT NULL)  [["make", "Honda"]]
 => #<ActiveRecord::Relation [#<Car id: 4, make: "Toyota", model: nil, created_at: "2014-11-11 21:39:53", updated_at: "2014-11-11 21:39:53">]> 
2.1.3 :021 >
1 Like
4

You’re right, I got that last part backwards.