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.

Frankiefrankincense

That looks like a Rails issue or possibly a Pg gem issue; it's treating an empty array as NULL. Very odd - and bad behavour. Can you test a prepared statement with the Pg gem directly to see if it treats array parameters that way or if it's the Rails/ActiveRecord level doing it? – Totter

@CraigRinger: It'll be a Rails/ActiveRecord problem not a pg problem. AR handles the placeholders itself. – Lobster

@CraigRinger: The AR people are surprising bad at SQL sometimes, they're doing this on purpose. Insert "son, I am disappoint" image here. – Lobster

@muistooshort Yeah, it's not a tool I'd choose. Then again Hibernate (at least as popular) has its own share of exciting quirks, like defaulting to ignoring the DEFAULT nextval(...) of a serial column and instead generating IDs from its own hibernate_sequence shared across all tables. Woo! With AR handing its own placeholders I can't help but wonder about SQL injection and security, though; it doesn't seem ... super robust. – Totter

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.

Lobster

Yep, the sane thing would certainly be an exception here. – Totter

@CraigRinger: I, as usual, blame MySQL :) – Lobster

I understood now why my tests are failing. :) – Weft

I disagree with wanting an exception. If I want scope that just-works whether I pass an empty array or not, it's more elegant to construct an AR scope where I don't have to do empty array guards. – Disparagement

@Disparagement You're welcome to disagree but "just works" is often a short cut to hidden bugs, I'd strongly prefer a tool that tells me to make sense over one that tries to make sense of nonsense and silently does the wrong thing. – Lobster

I agree the magic can cause side-effects but in the case I stumbled on this SO thread, I was intentionally passing an empty array as a default value for a scope (not accidentally). I guess I would agree with an exception as long as the exception told you to use https://mcmap.net/q/441437/-issue-when-retrieving-records-with-empty-array. – Disparagement

Answer 2

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

Heteroplasty

On a vaguely related note, User.where(:id => [[], [3]]) gives you invalid sql: "SELECT users.* FROM users WHERE users.id IN (, 3)" – Deflected

Answer 3

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

Jayme

Please consider adding a clarifying comment as to why this might be the correct answer. – Dian

@DavidL: This ensures that the array will not be empty. This also presumes that there is no id with value = 0 (in Rails it is often the case). – Thereabouts

Answer 4

Use ruby's active record wrapper:

User.where.not(id: [])

This handles the empty array issue for you.

Fragile

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)

Disoblige