Bug #60584 prepared statements truncate milliseconds
Submitted: 22 Mar 2011 11:26 Modified: 10 Oct 2012 14:26
Reporter: Sergei Golubchik Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Alexander Soklakov
Tags: 5.1

[22 Mar 2011 11:26] Sergei Golubchik
Description:
connector/j does not pass milliseconds down to mysql in prepared statements.

the program in "how to repeat" compares two different timestamp values and gets 1.

In the server general log, the query looks like

    SELECT '2011-03-22 11:54:08' = '2011-03-22 11:54:08'

while it should be

    SELECT '2011-03-22 11:54:08.001' = '2011-03-22 11:54:08'

or

    SELECT '2011-03-22 11:54:08.001000' = '2011-03-22 11:54:08'

How to repeat:
import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

class a {
   public static void main(String[] args) {
        Connection conn;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://127.0.0.1:3306/test";
            String userName = "test";
            String userPassword = "";
            conn = DriverManager.getConnection(url, userName, userPassword);
            PreparedStatement stmt = conn.prepareStatement("SELECT ? = ?");
            stmt.setTimestamp(1, new Timestamp(1300791248001L));
            stmt.setTimestamp(2, new Timestamp(1300791248000L));
            stmt.execute();
            ResultSet rs = stmt.getResultSet();
            try {
              while (rs.next()) {
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                  System.out.print(rs.getObject(i)+", ");
                }
                System.out.println("");
              }
            }
            finally {
              rs.close();
            }
        } catch (Exception e) {
            System.out.println("Exception: "+e);
            e.printStackTrace();
        }
    }
}
[22 Mar 2011 12:47] Mark Matthews
For the moment, this is on purpose, as long as Bug#50774 is not fixed, wrong results can be returned to the client when microseconds are passed in SQL.
[22 Mar 2011 13:01] Sergei Golubchik
Right, Mark, I know why it was introduced.

But
1) it looks like Bug#50774 is fixed. The fix is in 5.1.54, as far as I can see.
2) what would you recommend as a workaround? Use server-side prepared statements?
[24 Mar 2011 13:54] Mark Matthews
Server-side prepared statements will write the nanoseconds field out. Other work for supporting fractional seconds will not start until WL#946 has passed architectural review and the fractional second units are specified.
[12 Aug 2012 7:56] Leonard Lin
With MySQL 5.6 adding microsecond support, is it possible to re-visit this topic and remove truncation?
[13 Sep 2012 21:06] Mark Matthews
This was fixed with http://bazaar.launchpad.net/~mysql/connectorj/5.1/revision/1107 in 2011, it supports MySQL-5.6.4 or later.
[10 Oct 2012 11:49] Alexander Soklakov
That fix did not cover useLegacyDatetimeCode=true case. So now in progress.
[10 Oct 2012 13:55] Alexander Soklakov
This is a duplicate of Bug#40279
[10 Oct 2012 14:26] Alexander Soklakov
Please, look at Bug#40279 for further progress.