MySQL Bugs: #25769: A valid timestamp value is rejected as a value for a timestamp column

Bug #25769	A valid timestamp value is rejected as a value for a timestamp column
Submitted:	23 Jan 2007 3:22	Modified:	23 Jan 2007 14:04
Reporter:	Fred Kuipers	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.27-community-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I ran into this problem while attempting to import the categorylinks.sql file for the wikipedia database dump from 11/30/2006...

The problem: The 5.0.27-community-nt server rejects perfectly valid timestamp values which have a particular pattern: 200?0?0?02????. I can provide numerous values:
- 20050403024322
- 20060702024829
....

However, the value 20050403124322 works! (The only difference between the two values is the *hour*: 12 instead of 02...)

How to repeat:
Here's the steps to reproduce (again using server 5.0.27-community-nt on Windows XP and mysql client 14.12 from the same distribution):
--

mysql> create table temp (a_timestamp timestamp(14) NOT NULL) TYPE=InnoDB;
Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql> INSERT INTO temp VALUES(20050403024322);
ERROR 1292(22007): Incorrect datetime value: '20050403024322' for column 'a_timestamp' at row 1

mysql> INSERT INTO temp VALUES(20050403124322);
Query OK, 1 row affected (0.22 sec)

--
I should note, TYPE=InnoDB is irrelevant -- omitting the clause or using MyISAM makes no difference.

By contrast, using server 5.0.24a and same client version on Linux, these statements complete with no error leading me to conclude there is a defect one of the two server versions -- presumably the newer one as there is nothing wrong with the timestamp 2005-04-03 02:43:22...

Suggested fix:
Suggested fix: Correct the recognition of timestamp values.

- 20050403024322 this one is easy to explain: daylight saving time, this hour simply didn't exist as DST switched from 2005-04-03 01:59:59 to 03:00:00  

- 20060702024829 looks interesting, but assuming a typo here i'd say the 07 is actually a 04, so 2006-04-02, which again is the DST switch date

So not a a bug but expected behavior ...

Ok... I can understand the reasoning behind it -- however, it seems like a breaking change to reject values that were previously accepted. There were 57000+ such values in the wikipedia data I processed. Is there any way to force MySQL to "fix" those values -- ie, make them 03:43:22 instead?

That said, why then, does the following statement not give an error?

mysql> select cast(20050403024322 AS datetime) as InvalidDate;
+---------------------+
| InvalidDate         |
+---------------------+
| 2005-04-03 02:43:22 |
+---------------------+
1 row in set (0.00 sec)