Issue when retrieving records with empty array

Question

I have a table of around 100 Users and I also have an array of user ids. What I wanted to do is show all users who are not a part of this array of user ids. When I do something like this

 User.where('id NOT IN (?)', [9, 2, 3, 4])

It successfully returns the records where the user's id does not belong in that array. However if that array is empty like so

 User.where('id NOT IN (?)', [])

It does not return any users back and the SQL query looks like this

 SELECT "users".* FROM "users" WHERE (id NOT IN (NULL))

Does anyone know why this happens or could this be a bug? I am using Rails 3.2.5 with PostgreSQL.

Answer 1

ActiveRecord (3.2.1 at least) treats empty arrays as NULLs. The placeholders in a where call are handled by sanitize_sql. If you trace through the code for a bit, you'll come to replace_bind_variables:

def replace_bind_variables(statement, values) #:nodoc:
  raise_if_bind_arity_mismatch(statement, statement.count('?'), values.size)
  bound = values.dup
  c = connection
  statement.gsub('?') { quote_bound_value(bound.shift, c) }
end

and then quote_bound_value:

def quote_bound_value(value, c = connection) #:nodoc:
  if value.respond_to?(:map) && !value.acts_like?(:string)
    if value.respond_to?(:empty?) && value.empty?
      c.quote(nil)
    else
      value.map { |v| c.quote(v) }.join(',')
    end
  else
    c.quote(value)
  end
end

An empty Array will satisfy all four conditions to get you to c.quote(nil) and that's where your NULL comes from. All the special logic that leads to c.quote(nil) indicates that this is intentional behavior.

Saying IN (or NOT IN) with an empty list:

where c in ()

should produce an SQL error so maybe the AR people are trying to prevent that by quietly turning that bad SQL into c in (null). Note that neither of these:

select ... from t where c in (null);
select ... from t where c not in (null);

should ever produce any results due to the behavior of SQL's NULL. This is a classic newbie mistake and the AR people really should know better.

I'd prefer an exception myself: telling me that I'm about to deploy a foot-bullet would be much friendlier than just handing me a different gun.

---

Executive summary:

1. This "empty array means NULL" behavior is intentional.
2. You should never ever try where('c in (?)', []) or where('c not in (?)', []) since neither statement makes much sense.
3. Update your Ruby code to check for empty arrays and do whatever needs to be done to get the results you expect.

Answer 2

Use ruby's active record wrapper:

User.where.not(id: [])

This handles the empty array issue for you.

Answer 3

User.where('id NOT IN (?)', ids+[0])

Answer 4

In Rails 4 you can use User.where.not(id: []) which will give you the correct result. It produces:

SELECT "users".* FROM "users" WHERE (1 = 1)

Unfortunately User.where('id NOT IN (?)', []) should be equivalent but it is not. It still gives you the wrong result:

SELECT "users".* FROM "users" WHERE (id NOT IN (NULL))

References:

• https://github.com/rails/rails/issues/778
• https://github.com/rails/arel/commit/cbff1bcf
• https://github.com/rails/rails/pull/8332
• https://github.com/rails/rails/commit/8d02afeaee8993bd0fde69687fdd9bf30921e805

Answer 5

I don't know if this is the problem asked for, but I came here to find all records with an empty (serialized) array attribute. I solved it for Rails 5.0 like this:

User.where(my_array_attribute: nil)

Or for the inverse:

User.where.not(my_array_attribute: nil)