← Back to Upcase

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

(Eric ) #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.

(Geoff Harcourt) #2


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.

(Eric ) #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 > 

(Geoff Harcourt) #4

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