| Bug #25301 | Non-zero dates with year 0000 are invalid | ||
|---|---|---|---|
| Submitted: | 28 Dec 2006 0:09 | Modified: | 12 Jun 2007 5:55 |
| Reporter: | Gunar Werner | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S1 (Critical) |
| Version: | 5.0.25 | OS: | Any |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
| Tags: | bfsm_2007_01_18 | ||
[28 Dec 2006 7:42]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php See also Bug #19370 and http://lists.mysql.com/commits/9075: --- 1.19/sql-common/my_time.c 2006-07-12 11:38:19 +05:00 +++ 1.20/sql-common/my_time.c 2006-07-12 11:38:19 +05:00 @@ -69,6 +69,7 @@ Here we assume that year and month is ok ! If month is 0 we allow any date. (This only happens if we allow zero date parts in str_to_datetime()) + Disallow dates with zero year and non-zero month and/or day. RETURN 0 ok @@ -85,7 +86,8 @@ (!(flags & TIME_INVALID_DATES) && ltime->month && ltime->day > days_in_month[ltime->month-1] && (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 || - ltime->day != 29))) + ltime->day != 29)) || + (ltime->year == 0 && (ltime->month != 0 || ltime->day != 0))) { *was_cut= 2; return TRUE;
[28 Dec 2006 12:50]
Gunar Werner
So what would be the workaround for this magnificent "feature"? How does one save "February 21st of an unknown year" which was possible using "0000-02-21" all these years up until 2006-07-12 without howling over whole applications?
[29 Dec 2006 10:17]
Sveta Smirnova
Thank you for the additional information. Anyway this new behaviour is not documented anywhere except quoted ChageLog. So we'll discuss this problem again.
[29 Dec 2006 13:52]
Gunar Werner
Thanks for reconsidering your decision. This is a real and important issue. For a general discussion of the topic we should combine this and Bug #25311 which is essentially the same just from a different perspective (ALLOW_INVALID_DATES). The manual at chapter 11.3 states: "You can get MySQL to accept certain dates, such as '1999-11-31', by using the ALLOW_INVALID_DATES SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL.) This is useful when you want to store a “possibly wrong” value which the user has specified (for example, in a web form) in the database for future processing. Under this mode, MySQL verifies only that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is extremely useful for applications that need to store a birthdate for which you do not know the exact date. In this case, you simply store the date as '1999-00-00' or '1999-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD that require complete dates. (If you do not want to allow zero in dates, you can use the NO_ZERO_IN_DATE SQL mode)." So years back using MySQL 3 I tried if besides unknown day and unknown month it was possible to define an unknown year. It was, with everything working just fine, which allowed using the DATE type for any date (opposite to using three integer columns for dates). Up until that unfortunate fix of reported Bug #19370 in 5.0.25. For reasons of consistancy one would expect the possibility of using year 0000 for exactly the same reasons stated for month and day. That is, if it is unknown it can be stored anyway. Allowance of -00-00 is basic behavior. Since the same should be true for year 0000. For consistancy all null-values should be allowed with ALLOW_INVALID_DATES or none of them. Important only is that it is possible at all to store them. The application has to decide if a null-value of day, month or year stands for each, any or unknown. This way the reported "missbehavior" of "0000-01-00 15:00:06" in Bug #19370 could easily be interpretated as "each day of january of any year at 15:00:06 o'clock". There is no missinterpretation of year 0000 possible. There is no year 0 on our time scale, the year after 1 B.C. was 1 A.D. Proposed solution: The above quoted fix of "bug" 19370 should be reversed. Disallowance of dates containing zero parts should only be bound to NO_ZERO_IN_DATE SQL mode.
[8 Feb 2007 7:56]
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/19537 ChangeSet@1.2604, 2007-02-08 11:56:18+04:00, ramil@mysql.com +2 -0 Fix for bug #25301: Non-zero dates with year 0000 are invalid The 0000 year is valid. The ISO standard for "Representation of dates and times" says: "Calendar years are numbered in ascending order according to the Gregorian calendar by values in the range [0000] to [9999]." Reverted fix for 21789: DATETIME with 0000-00-00 11:22:33 should be invalid, but is accepted as it's not a bug. Fix for 19370: DateTime datatype in MySQL has two bugs in it will be reverted during 4.1 -> 5.0 merging as it was pushed to the 5.0 tree.
[6 Apr 2007 17:19]
Bugs System
Pushed into 4.1.23
[6 Apr 2007 17:21]
Bugs System
Pushed into 5.0.40
[6 Apr 2007 17:23]
Bugs System
Pushed into 5.1.18-beta
[24 Apr 2007 16:48]
Paul DuBois
Noted that these patches were reverted: #19370 was reverted in 5.0/5.1, #21789 was reverted in 4.1/5.0/5.1.
[12 Jun 2007 5:55]
Gunar Werner
I cannot find the change in the change history from 5.0.37 to 5.0.41 nor do I see any change in the current online documentation of chapter 11.3. Was the bugfix really applied?
[13 Jun 2007 6:56]
Sveta Smirnova
Yes, fix has been really pushed:
$mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 925
Server version: 5.0.41 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT MONTH("0000-02-21");
+---------------------+
| MONTH("0000-02-21") |
+---------------------+
| 2 |
+---------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
Description: Non-zero dates with a zero year part are treated as invalid and converted to "0000-00-00". While the manual states that any part of a date can be zero and former versions did do that correctly, dates are now converted. DATA LOSS: Dates like "0000-02-21" inserted with former versions of MySQL (like 5.0.18) are automatically converted to "0000-00-00" upon update of any part of the row containing that date. Using a SET SESSION sql_mode="ALLOW_INVALID_DATES" doesn't help. How to repeat: Besides using a MyISAM table with a date column as described above the following will also do: SELECT MONTH("1952-00-00"); -> 0 # correct SELECT MONTH("0000-00-00"); -> 0 # correct SELECT MONTH("0000-02-21"); -> NULL # wrong