Empty parameter list with IN statement lead to runtime TSQL syntax error about rezoom.sql HOT 2 CLOSED

Ultimately I decided to hack this in by sniffing the connection type at runtime, in order to satisfy Postgres's very picky typechecker. Thankfully there was already a DbType produced by the RZSQL typechecker available with the array parameter.

As of 0.6.2, behavior for IN with an empty array is:

SQLite (unchanged):

x IN ()

Postgres:

x IN (SELECT NULL::numeric WHERE FALSE)

numeric could also be text, timestamptz, bytea or uuid depending on the parameter type associated with the empty array (inferred by the RZSQL compiler, so should "just work" in terms of matching the type of x enough to satisfy PG).

All others:

x IN (SELECT NULL WHERE 1=0)

from rezoom.sql.

Good catch. I started with SQLite which accepts this syntax (it's pretty liberal about a lot of things).

The tricky part about fixing this is that the (comma,separated,list) part of these IN @whatever expressions gets generated at runtime, and currently is unaware of the backend in use. So to fix this without making a bigger change to expose the backend at runtime, it'd have to be something that works on all DBs.

At first I thought simply translating an empty array to (SELECT NULL WHERE 1=0) would work. That would be a 5-second fix, but it seems it doesn't work on Postgres because PG can't figure out the row type of the subquery.

I'll think about this a little more. Probably the best approach will be to refactor so that the backend can be used at runtime, which will be helpful in the long run anyway.

from rezoom.sql.