MySQL Bugs: #91065: zeroDateTimeBehavior=CONVERT_TO_NULL should not apply to 00:00:00 TIME columns

Bug #91065	zeroDateTimeBehavior=CONVERT_TO_NULL should not apply to 00:00:00 TIME columns
Submitted:	29 May 2018 21:56	Modified:	14 Dec 2018 22:46
Reporter:	Josh Landin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Given:
 - Using MySQL Connector/J 8.0.11
 - Set zeroDateTimeBehavior=CONVERT_TO_NULL
 - A table field theTimeField of-type TIME with value "00:00:00" (a valid time-of-day)

Results:
 - theTimeField value is returned as null.
 - This behavior is new with the introduction of the class ZeroDateTimeToNullValueFactory (see createFromTime method).

Expected:
 - theTime should be an instance of java.sql.Time with hours/minutes/seconds set to zero.

Other/Previous Behavior:
 - Under Connector/J 5.1, when using zeroDateTimeBehavior=convertToNull, zero TIME columns are returned as java.sql.Time with hours/minutes/seconds as zero. 
 - Under Connector/J 8.0, when using zeroDateTimeBehavior=ROUND, zero TIME columns are returned as java.sql.Time with hours/minutes/seconds as zero.
 - Under Connector/J 8.0, when using zeroDateTimeBehavior=EXCEPTION (the default), zero TIME columns are returned as java.sql.Time with hours/minutes/seconds as zero.

Also worth noting that the MySQL Connector/J documentation does not explicitly discuss TIME values, but does imply action on "invalid" values only (contrary to the above behavior):
    What should happen when the driver encounters DATETIME values that are composed 
    entirely of zeros (used by MySQL to represent invalid dates)? Valid values 
    are "exception", "round" and "convertToNull". 
    -- From: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-propertie...

How to repeat:
DDL:
  CREATE TABLE `theTable` (`theTimeField` time DEFAULT NULL);

Java:
  st.executeUpdate("INSERT INTO theTable (theTimeField) VALUES ('00:00:00')");
  ResultSet rs = st.executeQuery("SELECT theTimeField FROM theTable");
  rs.next();
  Time theTime = rs.getTime("theTimeField");
  // theTime is null, but should be java.sql.Time set to midnight.

Suggested fix:
Remove the following method override:
  com.mysql.cj.result.ZeroDateTimeToNullValueFactory.createFromTime(int, int, int, int)

Hello Josh Landin,

Thank you for the bug report and testcase.
Verified this behavior on MySQL Connector / J 8.0.11.

Thanks,
Chiranjeevi.

Any estimate on fix version/timing?

Guys, any plans on getting this fixed soon?

Posted by developer:
 
Added the following entry to the Connector/J 8.0.14 changelog:

"When the connection property zeroDateTimeBehavior was set to CONVERT_TO_NULL, Connector/J converted a TIME type value of 00:00:00 to null. With this fix, it returns a java.sql.Time instance of zero hours, minutes, and seconds, as expected."