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

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