Today I encountered an issue where 2 + 2 was only equalling 3 when fetching data using ActiveRecord in Rails.

def permanent_staff
  People.staff_like.where(
    contract_type: Settings.permanent_staff_contract_types
  )
end

def non_permanent_staff
  People.staff_like.where.not(
    contract_type: Settings.permanent_staff_contract_types
  )
end

People.staff_like.count == permanent_staff.count + non_permanent_staff.count
=> false # (huh!?)

After a little bit of digging I realised some People were being excluded because they didn’t have a contract type:

People.staff_like.count == permanent_staff.count + non_permanent_staff.count +
  People.staff_like.where(contract_type: nil).count
=> true

I assumed those with a NULL contract type would be included in non_permanent_staff but it turns out that’s not how databases work (at least not how PostrgreSQL or Sequel Server work, but I’m pretty sure this is “standard” behaviour). As soon as the query optimiser sees contract_type NOT IN (... it filters out any results that don’t have a contract_type.

My solution doesn’t feel like idiomatic Ruby or Rails but I solved it with the following method:

def non_permanent_staff
  People.staff_like.where.not(
    contract_type: Settings.permanent_staff_contract_types
  ).or(People.staff_like.where(contract_type: nil))
end

The generated SQL is a bit ugly (the staff_like scope conditions gets included twice) but I’m sure the database can optimise that… better the DB deal with the data collection than Ruby.

If this method proves confusing to future-me I may just put a NOT NULL constraint on contract_type in the database and be done with it. It’s a string we’re importing from a legacy system so the default value would just be “.