Bug #70892 Bug of TIMEDIFF function using JDBC connector

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

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.

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:)

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.

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.

Thanks Mark.

I agree with you, what we need should be a result of time difference as varchar type, not java.sql.Time.

Hi! Any news on this? Please, add an ability to at least get the value as string via rs.getString().

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