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

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

The different lines are results of querying the timestamp field after
different set and setNull operations. Note that the last line shows that
the timestamp is stored incorrectly even though the timestamp and
timezone does not change in the test.

The output can be reproduced by running the class at the end of this
mail. I tried it using both the 8.3-603.jdbc3 and 8.4-701.jdbc4 driver
in java 1.6 on Windows Vista 32 bit and AIX 6.1. Is that a bug or am I
getting something wrong? Any help would be appreciated.

Regards,

Martti

---

package postgrestest;

import java.sql.*;
import java.util.Calendar;
import java.util.TimeZone;
import java.text.DateFormat;

public class PostgresTest {
private static final String passwd = "mje";
private static final String user = "mje";
private static final String schema = "mje";
private static final String url =
"jdbc:postgresql://localhost:5432/mydatabase";

private Connection con;
private PreparedStatement insertStatement;
private PreparedStatement selectStatement;

public static void main(String[] args) throws SQLException {
TimeZone.setDefault(TimeZone.getTimeZone("Europe/Moscow"));

PostgresTest postgresTest = new PostgresTest();
try {
postgresTest.play();
} finally {
postgresTest.cleanup();
}
}

public PostgresTest() throws SQLException {
con = DriverManager.getConnection(url, user, passwd);
setupTestTable();
}

private void setupTestTable() throws SQLException {
try {
con.createStatement().execute("DROP TABLE " +schema + ".tab1");
} catch (SQLException e) {
// probably table does not exist
}
con.createStatement().execute("CREATE TABLE " + schema + ".tab1(id
integer, testDate timestamp)");
}

private void cleanup() throws SQLException {
con.close();
}

private void play() throws SQLException {
insertStatement = con.prepareStatement("INSERT INTO " + schema +
".tab1(testdate,id) values (?,?)");
selectStatement = con.prepareStatement("SELECT testdate FROM " +
schema + ".tab1 WHERE id = ?");

int id = 1;

TimeZone timezone = TimeZone.getTimeZone("GMT");
Calendar cal = Calendar.getInstance(timezone);

java.util.Date date = new java.util.Date();
Timestamp now = new java.sql.Timestamp(date.getTime());

insertNotNull(id, cal, now);
id++;

insertNull(id);
id++;

insertNotNull(id, cal, now);
id++;

insertNotNull(id, cal, now);
id++;

insertNull(id);
id++;

insertNotNull(id, cal, now);
}

private void insertNull(int id) throws SQLException {
insertStatement.setNull(1, Types.TIMESTAMP);
insertStatement.setInt(2, id);
insertStatement.execute();
}

private void insertNotNull(int id, Calendar cal, Timestamp now) throws
SQLException {
insertStatement.setTimestamp(1, now,cal);
insertStatement.setInt(2, id);
insertStatement.execute();

selectValue(id);
}

private void selectValue(int id) throws SQLException {
selectStatement.setInt(1, id);
ResultSet resultSet = selectStatement.executeQuery();
resultSet.next();


System.out.println(DateFormat.getDateTimeInstance().format(resultSet.get
Timestamp(1)));
}
}

-------------------------------------------------------

Martti Jeenicke
Senior Software Engineer &
Certified Scrum Master

martti(dot)jeenicke(at)coremedia(dot)com

CoreMedia AG
Return on Engagement
content | conversation | conversion

Ludwig-Erhard-Str. 18
20459 Hamburg, Germany
www.coremedia.com

Executive Board: Gerrit Kolb (CEO), Dr. Klemens Kleiminger (CFO)
Supervisory Board: Prof. Dr. Florian Matthes (Chairman)
Trade Register: Amtsgericht Hamburg, HR B 76277

"Jeenicke, Martti" <martti(dot)jeenicke(at)coremedia(dot)com> wrote:

> The different lines are results of querying the timestamp field
> after different set and setNull operations. Note that the last
> line shows that the timestamp is stored incorrectly even though
> the timestamp and timezone does not change in the test.

Confirmed. Your program gave me this:

Feb 8, 2010 6:49:21 PM
Feb 8, 2010 6:49:21 PM
Feb 8, 2010 6:49:21 PM
Feb 8, 2010 12:49:21 PM

and psql shows this afterward:

mydatabase=# select * from mje.tab1 ;
id | testdate
----+-------------------------
1 | 2010-02-08 18:49:21.784
2 |
3 | 2010-02-08 18:49:21.784
4 | 2010-02-08 18:49:21.784
5 |
6 | 2010-02-08 12:49:21.784
(6 rows)

The first three are correct for current GMT. The last is local time
here. I tried closing out the result set properly by adding these
line to the end of the selectValue method, with no change in
results.

resultSet.next();
resultSet.close();

I was also confused by the result of changing the column to
TIMESTAMP WITH TIME ZONE -- all timestamps were the same, but they
were two hours later than local time and four hours earlier than
GMT!?!

This was on kubuntu with Java 1.6.0_17.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> I was also confused by the result of changing the column to
> TIMESTAMP WITH TIME ZONE -- all timestamps were the same, but they
> were two hours later than local time and four hours earlier than
> GMT!?!

Never mind that last -- psql shows local time, but the Java program
is showing it as a time nine hours later than local, rather than
the six which should be GMT. (Don't ask how I misread that before.)

-Kevin

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

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