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.

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? — Craig Ringer, Oct 18 '12 at 02:07
@CraigRinger: It'll be a Rails/ActiveRecord problem not a `pg` problem. AR handles the placeholders itself. — mu is too short, Oct 18 '12 at 02:26
@CraigRinger: The AR people are surprising bad at SQL sometimes, they're doing this on purpose. Insert "son, I am disappoint" image here. — mu is too short, Oct 18 '12 at 02:55
@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. — Craig Ringer, Oct 18 '12 at 03:07

score 32 · Answer 1 · answered Jun 07 '13 at 01:22

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:

On a vaguely related note, User.where(:id => [[], [3]]) gives you invalid sql: "SELECT `users`.* FROM `users` WHERE `users`.`id` IN (, 3)" — Jonathan Swartz, Oct 29 '14 at 14:02

mu is too short · Accepted Answer · 2014-10-01T17:05:29.633

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:

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

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. — gabe, Feb 21 '18 at 19:05
@gabe 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. — mu is too short, Feb 21 '18 at 19:55
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://stackoverflow.com/a/40155467/34315. — gabe, Feb 21 '18 at 20:38

score 9 · Answer 3 · edited Sep 13 '16 at 14:37

9

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

edited Sep 13 '16 at 14:37

Petter Friberg

21,252
9
60
109

answered Sep 13 '16 at 14:34

Pilipenok Oleg

91
1
1

2

Please consider adding a clarifying comment as to ***why*** this might be the correct answer. – David L Sep 13 '16 at 14:55
@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). – bragboy Jan 02 '18 at 07:08

Danielle · Answer 4 · 2016-10-20T13:37:26.110

2

Use ruby's active record wrapper:

User.where.not(id: [])

This handles the empty array issue for you.

edited Oct 20 '16 at 13:37

answered Oct 20 '16 at 13:12

Danielle

860
10
13

score 1 · Answer 5 · answered Mar 12 '17 at 10:05

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)

Issue when retrieving records with empty array

5 Answers5

Linked