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

On Mon, 8 Feb 2010, Kris Jurka wrote:

> 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.
>

I've committed this patch to CVS.

Kris Jurka