MySQL Bugs: #95789: Contribution: clamp dates with invalid years to 0

Bug #95789	Contribution: clamp dates with invalid years to 0
Submitted:	13 Jun 2019 14:37	Modified:	16 Jul 2020 14:45
Reporter:	OCA Admin (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
This bug tracks a contribution by Stefan Breunig (Github user: breunigs) as described in http://github.com/mysql/mysql-connector-j/pull/38

How to repeat:
See description

Suggested fix:
See contribution code attached

Contribution submitted via Github - clamp dates with invalid years to 0 (*) Contribution by Stefan Breunig (Github breunigs, mysql-connector-j/pull/38#issuecomment-501564997): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_287543343.txt (text/plain), 10.94 KiB.

Hello Stefan,

Thank you for the report and contribution.

regards,
Umesh

Bug#94872 is marked as a duplicate of this one. Please look there to see the beginning of the discussion.

Posted by developer:

Hi Stefan,

Thank you very much for this contribution. We won't be able to accept it due to several reasons:

This patch is incomplete. It only validates Date values, leaving other types out. Also, it only takes into account date values with zero year, but a similar behavior should be applied to zero months and days (which are possible if NO_ZERO_IN_DATE is not present).

We also don't agree with the decision to ignore the "correct" part of the date value, E.g., why should a date value of '0000-07-16' be rounded to '0001-01-01'? But there's also no reason to just introduce a "correct" year on a wrong date.

Finally, MySQL treats this "zero date" value ('0000-00-00 00:00:00') as a special case and, as you know, we can't use it in Java, so this is the sole purpose for the existence of the connection property "zeroDateTimeBehavior", i.e., Connector/J assumes that "invalid dates" actually get converted to a zero date on the server and when we retrieve it, then we have to be able to handle that zero value. It's not Connector/J's responsibility to "fix" wrong dates.

So, I'd say, if there's something to fix here is the ability of MySQL server to accept dates with a zero year without either reporting an error or convert them to the "zero" value, as described in the documentation, and pointed out by yourself in Bug#94872 report. Maybe you'd like to report this as a server bug if you agree and it really affects you.

I hope this is clear and, again, thanks a lot for your interest in Connector/J and MySQL.