Issue when retrieving records with empty array

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

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)  endend

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.

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