Empty list for in clause
Daryl Stultz
Consider this:
ArrayList() options = new ArrayList();
Ebean.find(User.class).where().in("name", options).findList();
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.
/Daryl
Mario Ivankovits
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
database.
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.
Ciao,
Mario
Daryl Stultz
On Feb 26, 10:40 am, Mario Ivankovits <mario.ivankov...@gmail.com>
wrote:
> Hi!
>
> considering an empty list as "match everything" is not the right thing
> to do
I think I had a momentary lapse of sanity there. Thinking back to
experiments with OpenJPA, I found that OpenJPA would render something
like:
where (1<>0)
when rendering an empty list. I was probably thinking it rendered
where (1=1). Looking at my code, I test if the collection is empty
before applying it to the code, so I don't know what I was thinking.
So Ebean's solution (where id in (?)) causes PostgreSQL to throw an
error, which I'm ok with since in my case, I usually want to match
everything. It seems as though Ebean should render a query that
doesn't crash or it should throw an exception (rather than let the
database throw it). I'll leave that up to you, I don't feel that
strong about it.
/Daryl
Rob Bygrave
>> The standard behaviour for an empty list is "match nothing".
Daryl Stultz
On Feb 27, 5:03 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> 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"
> clause.
Yeah, that's what I meant! Not 1<>0! Another brain fart...
/Daryl
Daryl Stultz
On Feb 27, 9:23 pm, Daryl Stultz <kungfumachin...@gmail.com> wrote:
> On Feb 27, 5:03 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
>
> > 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"
> > clause.
Fix confirmed. (Yay!)
/Daryl