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 12:17	Modified:	19 Feb 2007 6:05
Reporter:	Raja Sekhar j
Status:	Not a Bug
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.21-BK, 5.0.15-Standard	OS:	Linux (Linux)
Assigned to:	Ramil Kalimullin	Target Version:

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.