Bug #39911 Timestamp nanos reported incorrectly
Submitted: 7 Oct 2008 14:02 Modified: 10 Oct 2008 8:55
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.6 OS:Any
Assigned to: Mark Matthews CPU Architecture:Any

[7 Oct 2008 14:02] Mark Matthews
Description:
Even though the server doesn't support nanos or micros in the TIMESTAMP type, Connector/J attempts to parse them from strings (when stored as such), but fails, as it treats them as micros.

How to repeat:
public void testNanosRetrieval() throws Exception {
		try {
			this.rs = this.stmt
					.executeQuery("SELECT '2008-09-26 15:47:20.797283'");
			this.rs.next();

			testTimestampNanos();

			this.rs = ((com.mysql.jdbc.Connection) this.conn)
					.serverPrepareStatement(
							"SELECT '2008-09-26 15:47:20.797283'")
					.executeQuery();
			this.rs.next();

			testTimestampNanos();

			this.rs.close();
		} finally {
			closeMemberJDBCResources();
		}

	}
[8 Oct 2008 14:55] Mark Matthews
Here's a diff since the linkage between bzr, launchpad, and the bugs system seems to be down:

=== modified file 'CHANGES'
--- CHANGES	2008-09-29 18:31:30 +0000
+++ CHANGES	2008-10-07 14:15:01 +0000
@@ -76,6 +76,11 @@
 	  the default value (null), uses the platform character set, which works for ISO8859_1 (i.e. "latin1") passwords. For passwords 
 	  in other character encodings, the encoding will have to be specified with this property, as it's not possible for the driver to 
 	  auto-detect this.
+	  
+	- Fixed BUG#39911 - We don't retrieve nanos correctly when -parsing- a string for a TIMESTAMP. MySQL itself doesn't support micros
+	  or nanos in timestamp values, but if they're stored as strings, historically we try and parse the nanos portion as well. 
+	  Unfortunately we -interpreted- them as micros. This fix includes correcting that behavior, and setting the milliseconds portion of
+	  such TIMESTAMPs to a correct value as well.
 	
 03-06-08 - Version 5.1.6
 

=== modified file 'src/com/mysql/jdbc/ResultSetImpl.java'
--- src/com/mysql/jdbc/ResultSetImpl.java	2008-05-28 14:53:25 +0000
+++ src/com/mysql/jdbc/ResultSetImpl.java	2008-10-07 14:15:01 +0000
@@ -6143,9 +6143,16 @@
 							int decimalIndex = timestampValue.lastIndexOf('.');
 							
 							if (decimalIndex != -1) {
-								if ((decimalIndex + 2) <= timestampValue.length()) {
+								if ((decimalIndex + 2) <= length) {
 									nanos = Integer.parseInt(timestampValue
 										.substring(decimalIndex + 1));
+									
+									int numDigits = length - (decimalIndex + 1);
+									
+									if (numDigits < 9) {
+										int factor = (int)(Math.pow(10, 9 - numDigits));
+										nanos = nanos * factor;
+									}
 								} else {
 									throw new IllegalArgumentException(); // re-thrown
 									// further

=== modified file 'src/com/mysql/jdbc/ResultSetRow.java'
--- src/com/mysql/jdbc/ResultSetRow.java	2008-02-28 05:34:19 +0000
+++ src/com/mysql/jdbc/ResultSetRow.java	2008-10-07 14:15:01 +0000
@@ -1106,6 +1106,13 @@
 									nanos = StringUtils.getInt(
 										timestampAsBytes, decimalIndex + 1,
 										offset + length);
+									
+									int numDigits = (offset + length) - (decimalIndex + 1);
+									
+									if (numDigits < 9) {
+										int factor = (int)(Math.pow(10, 9 - numDigits));
+										nanos = nanos * factor;
+									}
 								} else {
 									throw new IllegalArgumentException(); // re-thrown
 									// further

=== modified file 'src/com/mysql/jdbc/TimeUtil.java'
--- src/com/mysql/jdbc/TimeUtil.java	2008-04-15 00:23:33 +0000
+++ src/com/mysql/jdbc/TimeUtil.java	2008-10-07 14:15:01 +0000
@@ -1122,8 +1122,13 @@
 			offsetDiff = fromOffset - toOffset;
 		}
 
+		if (secondsPart != 0) {
+			cal.set(Calendar.MILLISECOND, secondsPart / 1000000);
+		}
+		
 		long tsAsMillis = 0;
 
+		
 		try {
 			tsAsMillis = cal.getTimeInMillis();
 		} catch (IllegalAccessError iae) {
@@ -1132,6 +1137,7 @@
 		}
 
 		Timestamp ts = new Timestamp(tsAsMillis + offsetDiff);
+		
 		ts.setNanos(secondsPart);
 
 		return ts;

=== modified file 'src/testsuite/regression/ResultSetRegressionTest.java'
--- src/testsuite/regression/ResultSetRegressionTest.java	2008-05-28 14:53:25 +0000
+++ src/testsuite/regression/ResultSetRegressionTest.java	2008-10-07 14:15:01 +0000
@@ -4756,4 +4756,38 @@
 			closeMemberJDBCResources();
 		}
 	}
+	
+	/**
+	 * Tests fix for BUG#39911 - We don't retrieve nanos correctly when -parsing- a string for a TIMESTAMP.
+	 */
+	public void testBug39911() throws Exception {
+		try {
+			this.rs = this.stmt
+					.executeQuery("SELECT '2008-09-26 15:47:20.797283'");
+			this.rs.next();
+
+			testTimestampNanos();
+
+			this.rs = ((com.mysql.jdbc.Connection) this.conn)
+					.serverPrepareStatement(
+							"SELECT '2008-09-26 15:47:20.797283'")
+					.executeQuery();
+			this.rs.next();
+
+			testTimestampNanos();
+
+			this.rs.close();
+		} finally {
+			closeMemberJDBCResources();
+		}
+
+	}
+
+	private void testTimestampNanos() throws SQLException {
+		Timestamp ts = this.rs.getTimestamp(1);
+		assertEquals(797283000, ts.getNanos());
+		Calendar cal = Calendar.getInstance();
+		cal.setTime(ts);
+		assertEquals(797, cal.get(Calendar.MILLISECOND));
+	}
 }
[10 Oct 2008 8:55] Tony Bedford
An entry was added to the 5.1.7 changelog:

Connector/J failed to parse TIMESTAMP strings for nanos correctly.