MySQL Bugs: #110242: zeroDateTimeBehavior=CONVERT_TO_NULL should apply to TIMSTAMP when date is zero

Bug #110242	zeroDateTimeBehavior=CONVERT_TO_NULL should apply to TIMSTAMP when date is zero
Submitted:	28 Feb 2023 23:56	Modified:	8 May 2024 11:51
Reporter:	Raphael Azzolini	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Given:
  - Using MySQL Connector/J 8.0.32
  - Set zeroDateTimeBehavior=CONVERT_TO_NULL
  - A table field datetime_field of type DATETIME with value “0000-00-00 01:01:01” (zero date with time)

Results:
  - “java.sql.SQLException: Zero date value prohibited” is thrown when reading the value in ResultSet.getTimestamp()

Expected:
  - datetime_field should be returned as null

Other/Previous Behavior:
  - Under Connector/J 5.1, when using zeroDateTimeBehavior=convertToNull, DATETIME columns with zero date and time (such as “0000-00-00 01:01:01”) are returned as null

How to repeat:
DDL:
  CREATE TABLE `my_table` (`datetime_field` datetime DEFAULT NULL);

Java:
  st.executeUpdate(“INSERT INTO my_table (datetime_field) VALUES (‘0000-00-00 01:01:01’)“);
  ResultSet rs = st.executeQuery(“SELECT datetime_field FROM my_table”);
  rs.next();
  Timestamp timestamp = rs.getTimestamp(“datetime_field”);
  // timestamp should be null, but java.sql.SQLException is thrown instead

Suggested fix:
- Create a new boolean method “isZeroDate()” in com.mysql.cj.protocol.InternalDate
- The implementation of the method is the same as “isZero()“, but the class com.mysql.cj.protocol.InternalTimestamp won’t override it to include the time values
- Use the method “isZeroDate()” instead of “isZero()” in com.mysql.cj.result.AbstractDateTimeValueFactory when validating the ZeroDatetimeBehavior

One correction on "Other/Previous Behavior":

Under Connector/J 5.1, DATETIME columns with zero date and time (such as "0000-00-00 01:01:01") are returned as with a different date. "0000-00-00 01:01:01" will be returned as "0002-11-30 01:01:01".

This behaviour is not dependent on zeroDateTimeBehavior=CONVERT_TO_NULL.

Hello. Is there any ETA for the bug fix?

Hello Raphael,

Thank you for the bug report.
Discussed your issue with Connector/J developer and concluded that this is not a bug. 
The option `zeroDateTimeBehavior` only handles the true "zero" values, as listed in the bottom of the page <https://dev.mysql.com/doc/refman/8.4/en/date-and-time-types.html>. Connector/J is not able to handle date-only "zero" values.

If anything, this could be a feature request for adding another connection property specifically for this issue, however, most likely it won't go through since the server sql_mode `NO_ZERO_DATE` will be included in the strict SQL mode at some point in the future and Connector/J is required to enable it by the JDBC specification.

Regards,
Ashwini Patil