Null-safe operator in mysql

Needed to add a default_scope to a model today that filtered out model instances created by online testing. We wanted those instances to be created and stored on the database so that we could verify the process worked, however we wanted to exclude them from the "real" instances in the db.

Good case for a default_scope on the model like this:

class mymodel < ActiveRecord::Base
default_scope :conditions => "purpose <> 'test'"
end

So rows with "test" purpose should be ignored in regular fetches of the Mymodel instances, but all other purposes should be used.

First I ran into the problem mentioned here: https://rails.lighthouseapp.com/projects/8994/tickets/2181

But then tests didn't pass because Mymodel.count was wrong. It kept missing counting rows where the purpose had not been set.

Turns out that the <> operator in mysql is not null-safe. My code does not count the rows where the purpose had not been set at all and was null.

Two equivalent ways to fix this. Change the :condition expression in the default_scope to either of:

• !(purpose <=> 'test') -- Here <=> is the null-safe equality operator.
  
• purpose is null or purpose <> 'test' -- More explicit and easier to read.

We went with the more explicit and easier to read one.