Maybe handle empty list passed to `.in(...)` expression?

[cakoose]

await db.select(db.foo.id)
    .from(db.foo)
    .where(db.foo.id.in([])); 

I get a syntax error from Postgres.

QUERY: "SELECT foo.id FROM foo WHERE foo.id IN"
PARAMS: []
error: syntax error at end of input
    ...
  length: 91,
  severity: 'ERROR',
  code: '42601',
  ...

This is a common problem with query building in general.

I think it's bad for Mammoth to generate invalid SQL. The quickest "fix" for that would be to have Mammoth throw its own exception if the .in(...) argument is empty.

Another option would be for Mammoth to replace the whole expression with false. I think that would actually be quite helpful in many cases. However, in some of those cases the resulting query might actually be useless and the user might want to know (with an exception) rather than just silently wasting resources.

[martijndeh]

I think what you should do instead is use = ANY() which is equivalent to IN. ANY() doesn't throw an error if you pass in an empty list, so it's better for certain use cases (e.g. accepting user input). At some point I also switched from IN to = ANY for these use cases, which takes some getting used to, but I think your best option.

await db.select(db.foo.id)
  .from(db.foo)
  .where(db.foo.id.eq(any([]))); 

See also https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME.

What do you think? Closing this one because I think this is the right approach.