Re: Bug: Cannot pass null in Parameter in Query for ISNULL

Hi Oliver,

Thanks for your reply. Onfortunately I cannot see a solution in your
elaboration. However I am still confident that there will be one at
some point in the future.

From an application perspective it is of course nonsense to code
variations of the same SQL statement depending on parameters having
specific values as required by the workaround. In case of JPA this
option does not even exist.

A Java application programmer has to go through extreme hassle in such
a simple case. That hassle is not worth it.

I would like to repeat that JDBC drivers with all other major database
engines do not have this problem. To achieve their level of quality
was apparently worthwhile for them.

It appears that PostreSQL/JDBC do things in a way that hurts. The kind
of picture that you are projecting looks horrible to me and I can
understand your frustration. I hope you are not suggesting that I
discuss this with PostgreSQL DB engine engineers. I really can't do
that so I will keep requesting a fix here.

Would it be possible that you open a request/ticket/issue where this
can be addressed formally? This could be on the driver side or on the
DB engine side - as long as the outcome is a resolution.

Kind Regards

Bernard

On Wed, 30 Nov 2011 21:22:33 +1300, you wrote:

>On 30 November 2011 20:36, <bht(at)actrix(dot)gen(dot)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)