#6133 Empty array interpolates as NULL in where condition - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#6133 new

Empty array interpolates as NULL in where condition

Reported by Mat Schaffer | December 8th, 2010 @ 04:12 PM

This works fine:

Comment.where("id NOT IN (?)", [1,2,3])

But passed an empty array like so:

Comment.where("id NOT IN (?)", [])

Generates this query which doesn't have the intended effect of including all records:

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

Seems like empty arrays should interpolate as an empty string rather than NULL.

Comments and changes to this ticket

You flagged this item as spam.
Mat Schaffer December 8th, 2010 @ 04:17 PM
Update: Used [''] as a workaround on sqlite. This doesn't seem to work on PostgreSQL.
You flagged this item as spam.
Mat Schaffer December 8th, 2010 @ 04:35 PM
Looks like both sqlite and PostgreSQL adapters (tested via heroku's console) both generate the NULL. PostgreSQL seems unhappy with the empty array though since it considers (id NOT IN ()) to be invalid.
You flagged this item as spam.
2kan December 19th, 2010 @ 11:23 AM
@Mat Why do you think that
```
id NOT IN (NULL)
```
is incorrect?

For me everything works correct.
You flagged this item as spam.
Mat Schaffer December 19th, 2010 @ 08:38 PM
Originally I was expecting NOT IN () which at least under sqlite had the intended effect of giving me all records. But I'm pretty sure postgres didn't like that either. Right now I have an "if ids.empty?" in my model. But that seemed less than awesome.

Is there maybe a better way to do this using an arel condition that I missed?
You flagged this item as spam.
2kan December 19th, 2010 @ 10:55 PM
- Tag set to “arel rails3, bug”
@Mat, Oh, I think maybe you are right, and it is a bug, because in Postgres this query will return empty set of records, so in Ruby you will get an empty array. And I think we expect all records.
You flagged this item as spam.
gmile February 12th, 2011 @ 12:59 PM
@2kan, going further, does
```
IN (NULL)
```
ever works for you? For me, it seems that MySQL doesn't want to retrieve any records, while trying to do something like (codes from head):
```
Post.all(
  :joins      => "LEFT JOIN view_logs ON posts.id = view_logs.post_id",
  :conditions => { :view_logs => { :user_id => [1, 2, nil] }
}
```
The need to retrieve using nil may appear when using, for example LEFT JOIN, where associated records with nulls may appear. I believe, the true retrieve of records with nulls in terms of SQL should look like
```
view_logs.user_id IS NULL
```
Thus, I believe, the above Post.all(...) retrieval should generate the following SQL piece:
```
... WHERE (view_logs.user_id IN (1, 2) OR view_logs.user_id IS NULL)
```
I've made an Arel patch to fix the issue, as well as a patch with a couple of Rails test to check the behavior. Could you plese take a look on them and say what you think?
- 0001-test-passing-nil-member-of-array-in-conditions.patch 1.6 KB
- 0001-generate-proper-sql-when-null-is-in-set.patch 1001 Bytes
You flagged this item as spam.
bingbing March 26th, 2011 @ 01:21 AM
gucci watches