MySQL Bugs: #88910: Two MySQL dates in 1582 appear to be the same but comparison result is false

Bug #88910	Two MySQL dates in 1582 appear to be the same but comparison result is false
Submitted:	14 Dec 2017 8:21	Modified:	20 Dec 2017 12:21
Reporter:	Ziv Gabovitch	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	calendar

Description:
I know that Gregorian calendar started on Oct 15th 1582, and during the transition from Julian calendar, 10 days had been dropped (depending on the country).

When I'm doing this query: SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d')
I'm getting this result: 1582-10-15 (the 10 days difference). 

But when I'm trying to match between such dates I'm getting the original date (Oct 5th and not 15th). For example:
SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d') = STR_TO_DATE('1582-10-15', '%Y-%m-%d')
I'm getting a false response, although you would have expected to get a true since Oct 5th actually count as Oct 15th, as we saw in the first example.

Another strange example is when using DATE_FORMAT: SELECT DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d');
Where I'm getting the original date: 1582-10-05

So it looks like the dates during a cutover doesn't have a consistent behavior.

How to repeat:
SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d')
returns: 1582-10-15

SELECT DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d');
returns: 1582-10-05

Which server version are you using?

mysql 5.7 > SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d');
+---------------------------------------+
| STR_TO_DATE('1582-10-05', '%Y-%m-%d') |
+---------------------------------------+
| 1582-10-05                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > SELECT DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d');
+----------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d') |
+----------------------------------------------------------------+
| 1582-10-05                                                     |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------+
| Variable_name           | Value                           |
+-------------------------+---------------------------------+
| innodb_version          | 5.7.21                          |
| protocol_version        | 10                              |
| slave_type_conversions  |                                 |
| tls_version             | TLSv1,TLSv1.1                   |
| version                 | 5.7.21-log                      |
| version_comment         | Source distribution 2017-NOV-10 |
| version_compile_machine | x86_64                          |
| version_compile_os      | Win64                           |
+-------------------------+---------------------------------+
8 rows in set (0.01 sec)

This issue can be reproduced on http://sqlfiddle.com/:

Variable_name	Value
innodb_version	5.6.35
protocol_version	10
slave_type_conversions	
version	5.6.35
version_comment	MySQL Community Server (GPL)
version_compile_machine	x86_64
version_compile_os	Linux

I couldn't repeat with current released version. Please provide your my.cnf (use Files tab for, private if you wish). Thanks.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT STR_TO_DATE('1582-10-05', '%Y-%m-%d');
+---------------------------------------+
| STR_TO_DATE('1582-10-05', '%Y-%m-%d') |
+---------------------------------------+
| 1582-10-05                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d');
+----------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE('1582-10-05', '%Y-%m-%d'), '%Y-%m-%d') |
+----------------------------------------------------------------+
| 1582-10-05                                                     |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I just noticed that the reported issue happens only when connecting using IDE (or sqlfiddle) - like DataGrip. When using these queries directly on the database, I'm not getting the additional 10 days diff as you mentioned. Love these inconsistencies...

Thank you for the feedback.