Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-22056

Sql. Jdbc. Incorrect value can be read for a column with Instant.

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • jdbc, sql

    Description

      For dates (before 1900 year), we can get the incorrect value using the ResultSet#getTimestamp method (for a column with Instant value (TIMESTAMP WITH LOCAL TIME ZONE).

      Reproducer

      @Test
      public void testTimestamps() throws SQLException {
          // Ignoring time zone.
          TimeZone.setDefault(TimeZone.getTimeZone(ZoneId.of("UTC")));
      
          try (Connection conn = DriverManager.getConnection(URL)) {
              try (Statement stmt = conn.createStatement()) {
                  stmt.execute(
                          "CREATE TABLE t (tt_id INT PRIMARY KEY, dt timestamp, ts timestamp with local time zone);"
                                  + "INSERT INTO t VALUES(0, '1581-12-31 00:00:00', '1581-12-31 00:00:00')"
                  );
              }
      
              try (Statement stmt = conn.createStatement()) {
                  try (ResultSet rs = stmt.executeQuery("select dt, ts from t")) {
                      assertTrue(rs.next());
      
                      Timestamp tsFromDateTime = rs.getTimestamp("dt");
                      Timestamp tsFromTimestamp = rs.getTimestamp("ts");
      
                      assertEquals(tsFromDateTime, tsFromTimestamp); // fails
      
                      // Expected :1581-12-31 00:00:00.0
                      // Actual   :1581-12-21 00:00:00.0
                  }
              }
          }
      }
      

      The main problem is a poor implementation of java.sql.Timestamp (and java.util.Date).
      To be specific, valueOf(LocalDateTime) and from(Instant) may create different timestamps for the same dates.

          TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
      
          Instant instant = Instant.parse("1581-12-31T00:00:00Z");
          LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.of("UTC"));
      
          Timestamp rightMillisTs = Timestamp.from(instant);
          Timestamp wrongMillisTs = Timestamp.valueOf(localDateTime);
      
          assertEquals(rightMillisTs.getTime(), wrongMillisTs.getTime()); // fails
          // Expected :-12244176000000
          // Actual   :-12243312000000
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            xtern Pavel Pereslegin
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: