IN with empty array fails

[dirkmc]

eg:

Post.find({ channel_id: [] }

This generates SQL like the following, which fails:

select * from posts where channel_id in ();

This is probably not unreasonable behaviour, but it would be nice if orm detected an empty array and omitted that clause from the WHERE

[notheotherben]

It does pose the issue of a query such as Post.find({ channel_id: [] }, function(err, posts) { }); being run, which would obviously execute the query you've provided - from which SQL will return no results. If ORM were to omit that part of the query however, it would generate select * from posts; which would return all the items in the table...

[dirkmc]

Yes you're right actually. If id in (1,2) returns two rows, then logically id in () should return zero rows.

[dresende]

Perhaps just making a false query:

SELECT * FROM posts WHERE false; // or 1=0, ...

Because you IN empty array should return false... (don't forget this can be nested)

[dresende]

You can actually have IN look in an empty set and return empty results (for that condition). What you can't is explicitly say IN (). If you do this it just works, no errors:

SELECT * FROM posts WHERE author_id IN (SELECT id FROM authors WHERE FALSE);

I'm making a change to sql-query to avoid column IN () and instead pass FALSE.

[notheotherben]

Sounds like a good solution to me

[dirkmc]

Looks good, thanks!