Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Wrong Data Retrieval For Very Old Date Entries #17507

Open
Drophoff opened this issue Oct 28, 2024 · 5 comments
Open

Wrong Data Retrieval For Very Old Date Entries #17507

Drophoff opened this issue Oct 28, 2024 · 5 comments
Labels
C: DB: H2 C: Functionality E: All Editions P: Medium T: Defect
Milestone
Version 3.20.0

Comments

@Drophoff
Copy link

Drophoff commented Oct 28, 2024 •
edited
Loading

Expected behavior

If I write an old date with the value 0001-01-01 into a database, I expect the identical value to be returned in a corresponding query. This does not seem to be the case at the moment.

Actual behavior

The JOOQ data records do not match the expected values. 0001-01-03 is returned instead of the expected value 0001-01-01.

Even changing the data query in the following way does not change the result:

List<org.jooq.Record> rsJooq = context.selectFrom("TBL_SAMPLE").fetch();
for (org.jooq.Record date : rsJooq) {
    System.out.printf("%s %n", date.toString());
}

Steps to reproduce the problem

In the following example, four data records are generated and read using Java/SQL and Java/JOOQ.

try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:test2", "sa", "sa")) {
    Statement statement = connection.createStatement();
    statement.execute("CREATE TABLE TBL_SAMPLE(SAMPLE_DATE DATE)");
    statement.execute("INSERT INTO TBL_SAMPLE(SAMPLE_DATE) VALUES"
		          + " DATE '0001-01-01', DATE '1893-04-01',"
		          + " DATE '1893-04-02', DATE '1582-10-14'");

    DSLContext context = DSL.using(connection);
    List<LocalDate> rsJooq = context.selectFrom("TBL_SAMPLE").fetchInto(LocalDate.class);
    for (LocalDate date : rsJooq) {
        System.out.printf("%s %n", date.toString());
    }

    try (ResultSet rsPlain = statement.executeQuery("SELECT SAMPLE_DATE FROM TBL_SAMPLE")) {
        while (rsPlain.next()) {
            System.out.printf("%s %n", rsPlain.getObject(1, LocalDate.class));
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

JOOQ returns the following values:

  • 0001-01-03
  • 1893-04-01
  • 1893-04-02
  • 1582-10-04

Java/SQL , on the other hand, returns the following values:

  • 0001-01-01
  • 1893-04-01
  • 1893-04-02
  • 1582-10-14

jOOQ Version

jooq community (3.19.14)

Database product and version

h2 (2.3.232)

Java Version

openjdk 17 (17.0.4.1)

JDBC / R2DBC driver name and version (include name if unofficial driver)

No response
@lukaseder lukaseder added this to the Version 3.20.0 milestone Nov 7, 2024
@lukaseder
Copy link
Member

Thanks for your report. This seems to be a bug in H2 that won't be addressed there:

You can reproduce it with JDBC directly:

try (Statement s = connection.createStatement();
    ResultSet rs = s.executeQuery("select date '0001-01-01'")
) {
    while (rs.next())
        System.out.println(rs.getDate(1) + "/" + rs.getString(1) + "/" + rs.getObject(1, LocalDate.class));
}

try (PreparedStatement s = connection.prepareStatement("select ?")) {
    s.setDate(1, Date.valueOf("0001-01-01"));

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getDate(1) + "/" + rs.getString(1) + "/" + rs.getObject(1, LocalDate.class));
    }

    s.setObject(1, LocalDate.parse("0001-01-01"));

    try (ResultSet rs = s.executeQuery()) {
        while (rs.next())
            System.out.println(rs.getDate(1) + "/" + rs.getString(1) + "/" + rs.getObject(1, LocalDate.class));
    }
}

In my time zone (CET), this produces:

0001-01-03/0001-01-01/0001-01-01
0001-12-31/0000-12-29/0000-12-29
0001-01-03/0001-01-01/0001-01-01

A related issue:

We should probably migrate towards using LocalDate bindings internally on dialects that support the type. There are still many bugs in JDBC drivers, so this won't be a simple change.

@lukaseder
Copy link
Member

Also related:

@katzyn
Copy link

katzyn commented Nov 7, 2024

java.sql.Date uses Julian calendar for old dates, java.time.LocalDate and standard-compliant databases use Gregorian calendar for all dates (also known as proleptic Gregorian calendar). 0001-01-01 in Gregorian is the same date as 0001-01-03 in Julian, it isn't a bug. But you can't use java.sql.Date.toLocalDate() for such old date, this method doesn't really support dates before 1582-10-15.

@lukaseder
Copy link
Member

it isn't a bug

OK, so it's a feature

@katzyn
Copy link

katzyn commented Nov 7, 2024

With some time zones there is also an unrelated bug caused by a bug in JDK.

But legacy datetime API in Java is broken in all possible ways. For example, there are four time zones where you can't represent midnight with java.sql.Time:

TimeZone.setDefault(TimeZone.getTimeZone("America/Bahia_Banderas"));
System.out.println(java.sql.Time.valueOf("0:00:00").toString());
01:00:00

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: DB: H2 C: Functionality E: All Editions P: Medium T: Defect
Projects
None yet
Milestone
Version 3.20.0
Development

No branches or pull requests
3 participants
@lukaseder @Drophoff @katzyn