MySQL Bugs: #19370: DateTime datatype in MySQL has two bugs in it

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

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.

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.

Patch:  http://lists.mysql.com/commits/6868

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

Thanks, Ramil.  OK to push.

Pushed to 5.0.25

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.

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

Merged into 5.1.12

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.

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

Sorry, in the last comment I referenced an incorrect bug ID.  It should just be bug #21789.

Changed status to "Verified", because patch for this bug rejects dates like "0000-02-21" (bug #25301). This should be fixed or documented.

See #25301: Non-zero dates with year 0000 are invalid.
Patch reverted.