ruby on rails - 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

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