Re: Truncation bug when retrieving timestamps with fractional

Rhett,

Thanks for the good test case.  I have committed a fix for this bug.

thanks,
--Barry

Rhett Sutphin wrote:
> Hi,
>
> I believe I've discovered a bug in the JDBC implementation that ships
> with PostgreSQL 7.3:  When loading a timestamp, the fractional seconds
> may be truncated.  Specifically, if there are more than three fractional
> digits, they are truncated to three.  If there are three or two, they
> are truncated by one.  If there is one, it is left alone.
>
>
> ------------------------------------------------------------------------
>
> // package test.jdbc;
>
> import java.sql.*;
>
> //
> // This test requires a table in the database pointed to
> // by DB_URL with the following structure:
> //   CREATE TABLE timestamp_bug (
> //     id INTEGER,
> //     ts TIMESTAMP
> //   );
> //
> public final class Postgresql73TimestampBug {
>     private static String TABLE_NAME = "timestamp_bug";
>     private static String DB_URL  = "jdbc:postgresql://localhost:5432/test";
>     private static String DB_USER = "test";
>     private static String DB_PASS = "test";
>
>     public static void main(String[] args) throws Exception {
>         // load driver
>         Class.forName("org.postgresql.Driver");
>         // open connection to postgres
>         Connection jdbc;
>         jdbc = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
>
>         test(Timestamp.valueOf("2003-04-05 06:55:44.012345678"), jdbc, 1);
>         test(Timestamp.valueOf("2003-04-05 06:55:44.123456789"), jdbc, 2);
>         test(Timestamp.valueOf("2003-04-05 06:55:44.123"), jdbc,       3);
>         test(Timestamp.valueOf("2003-04-05 06:55:44.12"), jdbc,        4);
>         test(Timestamp.valueOf("2003-04-05 06:55:44.1"), jdbc,         5);
>         test(Timestamp.valueOf("2003-04-05 06:55:44"), jdbc,           6);
>     }
>
>     public static void test(Timestamp toStore, Connection jdbc, int key) throws SQLException {
>         PreparedStatement stmt;
>         // delete test entry
>         stmt = jdbc.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE id=?");
>         stmt.setInt(1, key);
>         stmt.executeUpdate();
>
>         // insert test entry
>         stmt = jdbc.prepareStatement("INSERT INTO " + TABLE_NAME + " (id, ts) VALUES (?, ?)");
>         stmt.setInt(1, key);
>         stmt.setTimestamp(2, toStore);
>         stmt.executeUpdate();
>
>         // select test entry to read back
>         stmt = jdbc.prepareStatement("SELECT ts FROM " + TABLE_NAME + " WHERE id=?");
>         stmt.setInt(1, key);
>         ResultSet results = stmt.executeQuery();
>         results.next();
>
>         Timestamp loaded = results.getTimestamp("ts");
>
>         if (toStore.equals(loaded))
>             System.out.println(""+key+") SAME:");
>         else
>             System.out.println(""+key+") DIFFERENT:");
>         System.out.println(" Timestamp as stored: " + toStore);
>         System.out.println(" Timestamp as loaded: " + loaded);
>     }
> }
>
>
> ------------------------------------------------------------------------
>
>
> The attached class (Postgresql73TimestampBug) demonstrates this
> problem.  (See the comment at the top of the class for table
> requirements.)  On my machine, the output is:
>
> 1) DIFFERENT:
>  Timestamp as stored: 2003-04-05 06:55:44.012345678
>  Timestamp as loaded: 2003-04-05 06:55:44.012
> 2) DIFFERENT:
>  Timestamp as stored: 2003-04-05 06:55:44.123456789
>  Timestamp as loaded: 2003-04-05 06:55:44.123
> 3) DIFFERENT:
>  Timestamp as stored: 2003-04-05 06:55:44.123
>  Timestamp as loaded: 2003-04-05 06:55:44.12
> 4) DIFFERENT:
>  Timestamp as stored: 2003-04-05 06:55:44.12
>  Timestamp as loaded: 2003-04-05 06:55:44.1
> 5) SAME:
>  Timestamp as stored: 2003-04-05 06:55:44.1
>  Timestamp as loaded: 2003-04-05 06:55:44.1
> 6) SAME:
>  Timestamp as stored: 2003-04-05 06:55:44.0
>  Timestamp as loaded: 2003-04-05 06:55:44.0
>
> Examining contents of the table into which these timestamps were stored
> (using psql) reveals that the truncation is happening during loading:
>
>  id |             ts
> ----+----------------------------
>   1 | 2003-04-05 06:55:44.012345
>   2 | 2003-04-05 06:55:44.123456
>   3 | 2003-04-05 06:55:44.123
>   4 | 2003-04-05 06:55:44.12
>   5 | 2003-04-05 06:55:44.10
>   6 | 2003-04-05 06:55:44
>
> I am running OS X 10.2.3 with JDK 1.3.1.  I am using Marc Liyanage's
> prebuilt PostgreSQL 7.3 distribution.  I have tried the JDBC driver that
> is included with that distro as well as pg73jdbc2.jar and
> pg73jdbc2ee.jar from the PostgreSQL JDBC download site.  All behave the
> same.
>
> Please let me know if I can provide any additional information.
>
> Thanks,
> Rhett
>
> --
> Rhett Sutphin
> Research Assistant (Software)
> Coordinated Laboratory for Computational Genomics
>   and the Center for Macular Degeneration
> University of Iowa - Iowa City, IA 52242 - USA
> 4111 MEBRF - email: rhett-sutphin@uiowa.edu
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly