Year changing from negative -509 to a positive 510 in JDBC with H2 Database

Question

-509 vs 510

I am seeing some kind of altered or erroneous data, with the use of JDBC. So I observe using H2 Database version 1.4.196 on Java 8 Update 151.

Here is a complete example.

Note how we retrieve the date value three times, first as a LocalDate object, secondly as text, and thirdly as an int year number extracted from a cast LocalDate object. In the text version we can see that the year is indeed negative. Mysteriously the LocalDate has a different year number and it is positive rather than negative. Seems like a bug.

private void doIt ( )
{
    System.out.println( "BASIL - Running doIt." );
    try
    {
        Class.forName( "org.h2.Driver" );
    } catch ( ClassNotFoundException e )
    {
        e.printStackTrace( );
    }

    try (
            Connection conn = DriverManager.getConnection( "jdbc:h2:mem:" ) ;  // Unnamed throw-away in-memory database.
    )
    {
        conn.setAutoCommit( true );
        String sqlCreate = "CREATE TABLE history  ( id IDENTITY , when  DATE ); ";
        String sqlInsert = "INSERT INTO history ( when ) VALUES ( ? ) ; ";
        String sqlQueryAll = "SELECT * FROM history  ; ";

        PreparedStatement psCreate = conn.prepareStatement( sqlCreate );

        psCreate.executeUpdate( );

        PreparedStatement psInsert = conn.prepareStatement( sqlInsert );

        psInsert.setObject( 1 , LocalDate.of( 2017 , 1 , 23 ) );
        psInsert.executeUpdate( );

        psInsert.setObject( 1 , LocalDate.of( -509 , 1 , 1 ) );
        psInsert.executeUpdate( );

        PreparedStatement psQueryAll = conn.prepareStatement( sqlQueryAll );
        ResultSet rs = psQueryAll.executeQuery( );
        while ( rs.next( ) )
        {
            long l = rs.getLong( 1 );  // Identity column.
            // Retrieve the same date value in three different ways.
            LocalDate ld = rs.getObject( 2 , LocalDate.class );  // Extract a `LocalDate`, and implicitly call its `toString` method that uses standard ISO 8601 formatting.
            String s = rs.getString( 2 );  // Extract the date value as text from the database using the database-engine’s own formatting.
            int y = ( ( LocalDate ) rs.getObject( 2 , LocalDate.class ) ).getYear( );  // Extract the year number as an integer from a `LocalDate` object.
            String output = "ROW: " + l+ " | " + ld + " | when as String: " + s+ " | year: " + y ;
            System.out.println( output );
        }

        conn.close( );
    } catch ( SQLException e )
    {
        e.printStackTrace( );
    }
}

When run.

ROW: 1 | 2017-01-23 | when as String: 2017-01-23 | year: 2017

ROW: 2 | 0510-01-01 | when as String: -509-01-01 | year: 510

So there seems to be something going on with JDBC being involved. Note how the year is presented as a positive 510 rather than a negative 509. I do not understand this behavior.

I can deduce that it is an issue within JDBC rather than within LocalDate. See this example code run live in IdeOne.com showing that a LocalDate object does indeed carry and report a negative year.

LocalDate ld = LocalDate.of( -509 , 1 , 1 ) ;
System.out.println( "ld.toString(): " + ld ) ;
System.out.println( "ld.getYear(): " + ld.getYear() ) ;

Notice how we do not get converted from -509 to 510 when dealing with a LocalDate only, with no JDBC.

ld: -0509-01-01

ld.getYear(): -509

I opened a Issue ticket on the H2 project.

Answer 1

The problem is caused by the conversion from java.sql.Date to LocalDate . Because it's a negative year, the Calendar instance holding the fetched result will transform the year to 1 - year but when converting to LocalDate java is not considering the additional information (era==BC) that indicates that the year < 0 The following is the final method executed before returning the result.

try this one :

public class Test {
 public static void main(String[] args) {

            Calendar instance = Calendar.getInstance();
            instance.set(-509,1,1);

            java.sql.Date d = new Date(instance.getTime().getTime());

            System.out.println(d.toLocalDate().getYear());// 510


        }
}

Thanks Ole V.V. for your comment!!!

Answer 2

TL;DR: If the JDBC driver internally uses a java.sql.Date and converts it using java.sql.Date.toLocalDate(), a suspected bug in the deprecated Date.getYear() will (at least sometimes) cause the behaviour you observed.

This is guesswork, but I found it interesting enough to share.

I gather from SEY_91’s answer that the driver does use one or more of the legacy date and time classes, at least Calendar and GregorianCalendar. There are more conversion paths from Calendar to LocalDate, so the one via java.sql.Date is just one of them. Other conversion paths may hit the same bug, though.

Fact: The toLocalDate method relies on the deprecated getYear method. Source:

@SuppressWarnings("deprecation")
public LocalDate toLocalDate() {
    return LocalDate.of(getYear() + 1900, getMonth() + 1, getDate());
}

To see how getYear behaves with a year before the common era, I tried:

    OffsetDateTime dateTimeBce 
            = OffsetDateTime.of(-509, 1, 1, 0, 0, 0, 0, ZoneOffset.ofHours(1));
    Date d = Date.from(dateTimeBce.toInstant());
    System.out.println("d.toInstant() " + d.toInstant());
    System.out.println("d.getYear() (deprecated): " + d.getYear() 
                        + ", means " + (d.getYear() + 1900));

Since the year from getYear is “1900-based”, the expected year would be -2409. If we added 1900 to this, we would get -509, the year we started out from. However, the snippet prints:

d.toInstant() -0510-12-31T23:00:00Z
d.getYear() (deprecated): -1390, means 510

The first line shows that the Date does contain a negative year as it should (the offset conversion to UTC changes the year from -509 to -510; I chose the standard time offset of my computer’s time zone setting). The snippet uses java.util.Date, but java.sql.Date inherits the getYear method, and I have reproduced similar behaviour with java.sql.Date too.

I made a brief Internet search for any mention of the suspected bug without finding anything. We may want to try harder.

Answer 3

Bug, fixed

This problem was due to a bug in H2.

Now fixed, as of 2018-01.