Re: JDBC - PreparedStatement reuse oddity when using setObject() with nulls and setTimestamp()

On Wed, 20 Feb 2013, Alastair Burr wrote:

> Apologies, it looks like there is a subtelty here which I didn't realise /
> didn't mention before, you have to insert 4 records first before you insert
> the null record. I have tested with the latest driver and can now constantly
> reproduce this, more detail below:
>

The attached patch fixes the provided test case for me. The issue is
somewhat complicated, so let's break it down...

Yes it is dependent on the number of previous prepared statement
executions and the switchover point is the setting of prepareThreshold
[1]. Once we reached the crossover point to use named statements as you
saw in the server log, then the previous execution can affect subsequent
executions of the same statement.

When sending time/timestamp/date objects to the server we do not
explicitly tell the server what type we have because the server will do
different conversions between the target type and the provided data
depending on what source type we provide. This is discussed in more
detail in the comments in AbstractJdbc2Statement's setDate/setTimestamp.
So we send these types as strings with "unknown" type to the server.

The above rule of passing unknown type for date/time/timestamp objects is
bypassed when a PreparedStatement has exceeded prepareThreshold and we've
received the true type information from the server. In this case we pass
the true information from the server back to the server.

So given all of the above, what's happening in this test case? In this
case, issuing setNull(x, Types.DATE) was providing the PreparedStatement
with an explicit type of "date", not "unknown". The PreparedStatement was
taking this as truth with the expectation that it was recevied from the
server and then using it for typing later executions that want to use a
type of "unknown", resulting in later timestamps being truncated to dates.

The patch fixes the setNull(x, Types.DATE) call to pass "unknown" as the
type. This looks correct, but while reviewing the surrounding code, I
think there may be more problems in this area. Currently setDate with
binary transfer binds an explicit date type. Binary transfer requires
explicit typing, but doesn't that suffer from the same problem posed here?

[1] http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Kris Jurka