When we bind the in clause with an empty collection this results in a SQL exception.
Instead it should be treated as "match nothing".
ArrayList() options = new ArrayList();
Since the collection is empty, no value is provided for the "in (?)"
phrase and the DB throws an error. This requires us to break out the
query to a variable and do this:
if (options.size() > 0) query.where().in(options);
I'd like to request a feature enhancement where the in phrase is
omitted if the collection is empty.
considering an empty list as "match everything" is not the right thing
to do - or at least it is application specific bahviour and thus
should not be added to the standard ebean api.
If you do "select * from xyz where prop in ()" this too will not
result in an list of all records of "xyz". Depending on the database
you will get an sql error or nothing.
The standard behaviour for an empty list is "match nothing". I am not
sure if there exists a standard sql syntax to describe an empty list
on the right side of the "in" operator. AFAIK this depends on the
You have to query your options list (as you do) and decide if you
return an empty list or omit the phrase and return everything, but
this depends on your needs.
Well we don't want to generate a SQL exception so that's a bug (I'll log
>> The standard behaviour for an empty list is "match nothing".
I think we are all in agreement that we should get "match nothing". So
generating 1=0 is what we should do here for an empty collection in the "in"
Fixed in HEAD.