MySQL Bugs: #83989: Timestamp.toString() truncates trailing zeros in milliseconds part

Bug #83989	Timestamp.toString() truncates trailing zeros in milliseconds part
Submitted:	28 Nov 2016 14:46	Modified:	29 Nov 2016 12:21
Reporter:	Per Lindberg	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.40	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
String s = resultSet.getTimestamp(1).toString() should return a string on the format "yyyy-mm-dd hh:mm:ss.fffffffff".
In my case the MySql column is specified "timestamp datetime(3) DEFAULT NULL", and the result of .toString is typically "yyyy-mm-dd hh:mm:ss.fff".
But if the fractional part has trailing zeros, those are truncated! For example ".990" becomes ".91" and ".900" becomes ".9".

The resulting string is shorter than expected. So s.substring(0, 23) explodes with a StringIndexOutOfBoundsException.

A workaround is
while (s.length() < 23) s += "0";
but that is a garbage generator when huge tables are read. In any case, toString has a bug that violates the JDBC specification, imho.

How to repeat:
Create a table with a datetime(3) column, as in the description. Put some values in it. Read with jdbc, as in the description.

Suggested fix:
Always return trailing zeros in fraction part.

Come to think of it, it's actually worse than I first thought. Since Connector/J does not *always* return a string on the format "yyyy-mm-dd hh:mm:ss.fffffffff", but truncates it whenever it sees fit, it does not fulfill the contract in the JDBC specification/documentation. So any code that presumes the documented format, and works with another JDBC connector, can break when switching to Connector/J. That's a show-stopper. So I take the liberty to elevate the the severity to Critical.

Hi Per,

Thank you for your interest in Connector/J.

Allow me to clarify. It is not Connector/J who is formatting those timestamp values as you described. 'resultSet.getTimestamp(1)' returns a java.sql.Timestamp object which is a native JDBC class and Connector/J simply uses it as-is.

And it doesn't matter what JDBC connector you use. Unless it subclasses Timestamp and overrides the method toString(), you'll always get the same results. You don't even need a connector to check this:
    Timestamp ts = new Timestamp(System.currentTimeMillis());
    ts.setNanos(100000);
    System.out.println(ts);

If you really want the trailing zeros you could try something like this:
    (s + "000000000").substring(0, 23)

I hope that helps.

I'm closing this report as "Not a bug".

Thank you,

Aha, so it's a bug in the Java RTE, then. Even worse. :-) The toString() method of Timestamp should, according to the javadoc, return a string with all digits. But it truncates trailing zeros, leading to unpleasant surprises. Maybe I should report it...?