Bug #19370 DateTime datatype in MySQL has two bugs in it
Submitted: 26 Apr 2006 10:17 Modified: 19 Feb 2007 5:05
Reporter: Raja Sekhar j Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21-BK, 5.0.15-Standard OS:Linux (Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[26 Apr 2006 10:17] Raja Sekhar j
Description:
The is a bug in DateTime datatype in MySQL.I would like to explain this bug using an example:

1.Create a table with a field and data type is datetime

create table temp(testdate datetime);

Now fire the following insert command and see what it does

insert into  temp values ('0000-01-00 15:00:06');

insert into  temp values ('0000-00-00 15:00:06');

Both the above insert queries will run and insert data in to the database with out any problem.

How to repeat:
Create a table using the following syntax:

create table temp(testdate datetime);

Now fire the following insert command and see what it does

insert into  temp values ('0000-01-00 15:00:06');

insert into  temp values ('0000-00-00 15:00:06');

Suggested fix:
add a wrapper class to check for the above conditions.
[26 Apr 2006 11:12] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.21-BK on Linux:

mysql> show variables like 'sql%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
| sql_notes     | ON    |
| sql_warnings  | ON    |
+---------------+-------+
3 rows in set (0.00 sec)

mysql> create table temp(testdate datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into  temp values ('0000-01-00 15:00:06');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  temp values ('0999-00-00 15:00:06');
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp;
+---------------------+
| testdate            |
+---------------------+
| 0000-01-00 15:00:06 |
| 0999-00-00 15:00:06 |
+---------------------+
2 rows in set (0.00 sec)

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/datetime.html):

"- As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES. See Section 5.2.5, “The Server SQL Mode”, for more information.

Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still allows you to store invalid dates such as '2002-04-31'. To ensure that a date is valid, you should perform a check in your application."

But, as we can see, without a strict mode, dates are not converted to 0000-00-00 and no warnings are generated. It is either a bug or, if it is intended behaviour now (it is the same in 5.1.10-BK, but 5.1 manual is different!), a documentation request.
[5 Jul 2006 13:26] Chad MILLER
Patch:  http://lists.mysql.com/commits/6868
[12 Jul 2006 6:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9075
[12 Jul 2006 19:39] Timothy Smith
Thanks, Ramil.  OK to push.
[3 Aug 2006 16:43] Magnus Blåudd
Pushed to 5.0.25
[7 Aug 2006 8:16] Jon Stephens
The 5.1 Manual merely confirms that the post-5.0.2 behaviour is also the expected behaviour in 5.1. The pre-5.0.2 behaviour is not mentioned in the 5.1 Manual since it doesn't apply to any 5.1.x release.

Q1. Is this fix to be applied to 5.1 also, since it (5.1.12-bk-20060726) displays the same behaviour as described in the initial bug report?

Q2. What will be the (new) result of the inserts shown? If truncation of the values is to take place when not in strict mode, will warnings be generated?

Thanks.
[10 Aug 2006 6:46] Timothy Smith
Jon,

A1:  Yes, it will be in 5.1 as well, once it gets merged up (should be in 5.1.12, but has not been merged into 5.1 yet).

A2:  The patch fixes just the first example, not the second.  I mean, the current behavior is:

insert into  temp values ('0000-01-00 15:00:06') ==>
- insert 0000-00-00 00:00:00, and issue warning:
Warning | 1264 | Out of range value adjusted for column 'testdate' at row 1

insert into  temp values ('0000-00-00 15:00:06'); ==>
- insert 0000-00-00 15:00:06, with no warning (same as before)

I need to get clarification from the architecture folks about the intended behavior in the second case.  I will open a second bug for it, if such is deemed necessary.

Regards,

Timothy
[14 Aug 2006 20:41] Konstantin Osipov
Merged into 5.1.12
[18 Aug 2006 14:11] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.
[22 Aug 2006 22:28] Timothy Smith
See also bug #21789 (DATETIME with 0000-00-00 11:22:33 should be invalid, but is accepted), which will cover the 2nd part of this bug report.

This date wasn't clearly defined as invalid, and thus was not fixed under this bug, bug 19370.  However, it is an invalid date, and will be fixed in a separate patch under bug #12789.

Thanks,

Timothy
[22 Aug 2006 23:10] Timothy Smith
Sorry, in the last comment I referenced an incorrect bug ID.  It should just be bug #21789.
[29 Dec 2006 10:30] Sveta Smirnova
Changed status to "Verified", because patch for this bug rejects dates like "0000-02-21" (bug #25301). This should be fixed or documented.
[19 Feb 2007 5:05] Ramil Kalimullin
See #25301: Non-zero dates with year 0000 are invalid.
Patch reverted.