Re: Strange behavior after setting timestamp field to null - A bug?

[Kris Jurka <books(at)ejurka(dot)com>]

On Mon, 8 Feb 2010, Jeenicke, Martti wrote:

> Hi there,
>
> we have noticed an odd problem/bug when working with timestamp fields in
> prepared statements. The problem arises when setting the timestamp field
> to null. The test class to reproduce the behavior produces the following
> output:
>
> 08.02.2010 16:36:20
> 08.02.2010 16:36:20
> 08.02.2010 16:36:20
> 08.02.2010 17:36:20

I've looked into this a little bit.  The problem is how the data gets 
typed when it is sent to the server.  When calling setTimestamp, the 
driver doesn't know whether the server type that will be used will be with 
or without a timezone.  (The SQL Standard and the JDBC API don't match up 
well here.)  So it sends the data as type "unknown" and lets the server 
figure out how to deal with it because it has additional type information. 
When calling setNull, the driver thought it was safe to type it as 
timestamp with timezone to try and help type inference because NULL values 
look the same with or without timezones.  This is looked OK, but you've 
caught the case here where it is not.  By default, the fifth execution of 
a PreparedStatement will establish a more permanent execution plan that 
will then be re-used for later executions.  So the fifth execution in your 
test is a setNull case and that is effectively establishing the types that 
a later execution will use as well.  So later executions fail to pass the 
data as "unknown" and are instead passing it as "timestamp with tz" which 
does not match up with your table, so you get the mystery drift.

The attached patch fixes things for me in a simple test, but I'd like to 
look at it a little more before I commit it.

Kris Jurka

Attachment: fix-setnull-unknown.patch
Description: Text document