| Bug #70892 | Bug of TIMEDIFF function using JDBC connector | ||
|---|---|---|---|
| Submitted: | 13 Nov 2013 2:07 | Modified: | 28 Feb 2022 9:23 |
| Reporter: | Tom Gao | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.27 | OS: | Any |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
| Tags: | connector, jdbc, timediff | ||
[13 Nov 2013 5:39]
Todd Farmer
Hi tom tom, Thanks for the bug report. The problem you cite isn't specific to TIMEDIFF - it seems to be generic behavior for handling TIME data types in MySQL. These data types can have values from '-838:59:59.000000' to '838:59:59.000000', per the documentation: http://dev.mysql.com/doc/refman/5.6/en/time.html However, java.sql.Time objects do not allow hours which exceed 23: http://docs.oracle.com/javase/6/docs/api/java/sql/Time.html The test case you provided can be simplified to the following: ResultSet rslt = c.createStatement().executeQuery("SELECT CAST('838:59:59' AS TIME) t"); while (rslt.next()){ System.out.println(rslt.getString(1)); } The same SQLException will be presented. Looking at the code in com.mysql.jdbc.ResultSetImpl.getTimeFromString(), there appears to be the following check which throws this SQLException message: if ((timeAsString.length() != 5) && (timeAsString.length() != 8)) { throw SQLError.createSQLException(Messages .getString("ResultSet.Bad_format_for_Time____267") //$NON-NLS-1$ + timeAsString + Messages.getString("ResultSet.___in_column__268") + columnIndex, SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } Allowing only string lengths of 5 ("12:45") or 8 ("12:45:23") causes problems beyond simply limiting the range of values in a TIME column; it also adversely affects new fractional second capabilities of MySQL Server 5.6. A value of "12:34:56.123456" triggers the same SQLException. I think that's definitely a bug needing resolution. Of course, it's possible to use string lengths which pass the check listed above, but which don't comply with java.sql.Time range limitations. Using a value of "25:12:34" will produce a different SQLException and stack trace: java.sql.SQLException: Illegal hour value '25' for java.sql.Time type in value '25:12:12. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) at com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:1050) at com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:1058) at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:6114) at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5845) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692) I'm unclear how we should expect Connector/Java to handle MySQL TIME data types which exceed limits of java.sql.Time objects, but I'll check with those more familiar with the JDBC specification.
[13 Nov 2013 6:39]
Tom Gao
Sounds great. The function - 'TIMEDIFF' runs well in mysql management tool so I think there should be an error of Connector/J. Looking forward to new information about it and thanks for your advice:)
[13 Nov 2013 6:46]
Tom Gao
BTW, it seems that the problem located in the function - 'TIMEDIFF' because I use it to calculate how much time my task used. And 'TIMEDIFF' should return a value as string like '25:10:10' (it means '25hours,10minutes,25 seconds') But when I tried to get the value, it was converted to java.sql.Time(why not string?) and then it throws out an exception. Hope authors of Connector/Java could help review that. Thanks a lot.
[13 Nov 2013 14:17]
Mark Matthews
For those interested in the internals, look at the bottom of the stack: at com.mysql.jdbc.ResultSetImpl.getTimeFromString(ResultSetImpl.java:6114) at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5845) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692) That means the *server* is telling the client this is a SQL TIME type, when really, it isn't. I'm not sure there's an easy fix for this, it will have to be something like try to handle it as a TIME type, if it's not conformant, return as a VARCHAR.
[14 Nov 2013 7:53]
Tom Gao
Thanks Mark. I agree with you, what we need should be a result of time difference as varchar type, not java.sql.Time.
[27 Mar 2018 10:58]
Dmitriy Tseyler
Hi! Any news on this? Please, add an ability to at least get the value as string via rs.getString().
[28 Feb 2022 9:23]
Alexander Soklakov
Posted by developer:
Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.
With the latest Connector/J 8.0 it's still impossible to get the value as a java.sql.Time because it doesn't support such values range. But it's possible to get the result as a java.time.Duration or as a String:
@Test
public void testBug70892() throws Exception {
this.rs = this.stmt.executeQuery("select TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01') as time_used from dual");
this.rs.next();
Object obj = this.rs.getObject(1, Duration.class);
assertTrue(obj instanceof Duration);
assertEquals("PT838H59M59S", obj.toString());
assertEquals("838:59:59", this.rs.getString(1));
assertThrows(SQLException.class,
"The value '838:59:59' is an invalid TIME value. JDBC Time objects represent a wall-clock time and not a duration as MySQL treats them. If you are treating this type as a duration, consider retrieving this value as a string and dealing with it according to your requirements.",
() -> this.rs.getObject(1));
}
Description: In j2ee environment, when I use timediff to calculate the time difference of two time, the result will be converted to type of 'java.sql.Time' How to repeat: ResultSet result = getConnection().prepareCall("select TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01') as time_used from dual").executeQuery(); while (result.next()) { System.out.println("current is" + result.getObject(1)); } It wil throw out a sql exception error as below: --------------------------------------------------- java.sql.SQLException: Bad format for Time '838:59:59' in column 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:1012) at com.mysql.jdbc.ByteArrayRow.getTimeFast(ByteArrayRow.java:226) at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:6171) at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5903) at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5051) at com.mchange.v2.c3p0.impl.NewProxyResultSet.getObject(NewProxyResultSet.java:2365) at com.active.security.service.TestTimeDiff.selectTimeDiff(TestTimeDiff.java:16) at com.active.security.controller.common.LoginController.index(LoginController.java:78) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) Suggested fix: I'm not sure about how to fix it. But I just try to used it as below: CONCAT(TIMEDIFF('2013-12-09 06:22:01','2013-08-07 05:22:01'),'') , I use function-'CONCAT' to convert it to string. Then it won't be converted to java.sql.Time