Re: Bug: Cannot pass null in Parameter in Query for ISNULL - Mailing list pgsql-jdbc

On 30 November 2011 20:36,  <bht@actrix.gen.nz> wrote:
> Hi Oliver,
>
> Your response is not entirely unexpected however perplexing in light
> of the fact that other JDBC drivers don't have this bug. I have tested
> them.

It's not a bug; this is not a case where the driver is behaving
incorrectly. It might be convenient for your application if the driver
supported this case differently, but in general JDBC drivers aren't
required to handle it, and it is technically difficult to handle it in
the postgresql driver. (And presumably in other drivers that you
haven't tested - the warning in the JDBC javadoc didn't come from
nowhere)

> The reason for not being able to use workarounds is that we are using
> JPA which is a layer that is not accessible for modification.

Have you asked your JPA vendor for a fix? Arguably, it's a
compatibility bug in your JPA layer - the JPA layer is doing something
that the JDBC javadoc explicitly says to avoid doing.

> Don't you think that it would be worth the trouble spending some extra
> driver coding, to detect and allow this scenario and pass the
> perfectly valid and correct query to the database?

No, I don't think it's worth the trouble, TBH. It's not going to be a
simple driver modification, because the error isn't even being
generated in the driver. The limitation is embedded deep in the
details of how statements are prepared and executed at the protocol
level. The short version: when a statement is prepared, the driver
gives the general, parameterized, form of the statement to the server,
along with the desired type of each parameter. Parameters where the
driver has no type information are passed as unknowns. During the
server's parsing of the statement, it attempts to infer types for any
parameters of unknown type. If the server can't infer a type for a
parameter from the context of the query, then the server generates the
error you're seeing. I can't see any way around that in the driver
without either inventing type information (would you like magical
unicorns with that?), or transforming the query in the driver, which
means you'll probably need a full-blown SQL parser in the driver too.
That way lies madness.

A more sensible approach might be something like a server-side change
to allow the case where the server can't infer a type for a parameter,
but the parameter was only used in contexts where the type didn't
matter (e.g. it is in an IS NULL expression). This probably has
implications for the parameter Bind step too (you'd have to skip
parsing non-null values since you don't have a type to use for
parsing). If you do go down that route, then the -hackers list is
probably a better venue for discussing the details.

Did you try the PREPARE case I suggested? That is more like what the
driver is doing than the literal text substitution of parameters that
you tried.

A workaround that might work (I haven't tried) would be to attach an
explicit cast to the parameter. That's basically doing the same as
your JPA layer *should* be doing, but doing it in the query text
rather than at the API level.

(I find it mildly amusing that this is the reverse of the usual
complaint, which is where applications call setString() and are then
upset when the driver actually tries to treat the parameter as a
String and runs headlong into a type mismatch.. we even have a driver
option that makes setString() *not* set type information as a
workaround!)

Oliver

(PS: posting to a public list from an address that bounces direct mail
is a bit obnoxious)