MySQL Bugs: #60584: prepared statements truncate milliseconds

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:	Assigned Account	CPU Architecture:	Any
Tags:	5.1

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();
        }
    }
}

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.

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?

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.

With MySQL 5.6 adding microsecond support, is it possible to re-visit this topic and remove truncation?

This was fixed with http://bazaar.launchpad.net/~mysql/connectorj/5.1/revision/1107 in 2011, it supports MySQL-5.6.4 or later.

That fix did not cover useLegacyDatetimeCode=true case. So now in progress.

This is a duplicate of Bug#40279

Please, look at Bug#40279 for further progress.