| Bug #18997 | DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value | ||
|---|---|---|---|
| Submitted: | 10 Apr 2006 21:41 | Modified: | 4 Jun 2007 18:34 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any (*) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[11 Apr 2006 1:39]
MySQL Verification Team
Thank you for the bug report.
[25 Apr 2006 5:07]
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/5450
[15 May 2006 21:43]
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/6416
[16 May 2006 1:32]
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/6427
[16 May 2006 1:49]
Tatiana Azundris Nuernberg
fixed in 5.1.11-beta.
if input year for date_add() / date_sub() with INTERVAL is low enough for
calc_daynr() to possibly return incorrect results (calc_daynr() has no information
on whether the year is low because it was a two-digit year ('77) or because it
was a really low four-digit year (0077) and will indiscriminately try to turn the
value into a four-digit year by adding 1900 or 2000 respectively), the functions
will now throw NULL.
[26 May 2006 13:56]
Paul DuBois
Noted in 5.1.11 changelog, 5.1 upgrade section.
For dates with 4-digit year parts less than 200, an implicit
conversion to add a century was applied for date arithmetic performed
with DATE_ADD(), DATE_SUB(), + INTERVAL, and - INTERVAL. (For
example, DATE_ADD('0050-01-01 00:00:00', INTERVAL 0 SECOND) became
'2050-01-01 00:00:00'.) Now these operations return NULL rather than
an incorrect non-NULL value.
[4 Oct 2006 7:14]
Roberto Spadim
why don't make anything like with: sql_mode= ALLOW_INVALID_DATES 0055 = 0055 without ALLOW_INVALID_DATES 0055 = 2055 or 1955 what you say?
[26 Mar 2007 9:40]
Michael Widenius
Made a better bug fix for date handling. Moved out year-2000 handling from calc_daynr() to more appropriate places, as we in calc_daynr() don't know the original lenght for the day part. This fixes that date arithmetic now works properly with dates in the range of 0000-9999 (in other words, fixes wrong date handling for years 0000-0199 the previous bug fix introduced) Fix should be in 5.1.18
[10 Apr 2007 15:42]
Tatiana Azundris Nuernberg
queued in 5.0.40-marvel 5.1.17-marvel
[18 Apr 2007 15:46]
Bugs System
Pushed into 5.1.18-beta
[24 Apr 2007 1:19]
Paul DuBois
Noted in 5.1.18 changelog.
For dates with 4-digit year parts less than 200, an incorrect
implicit conversion to add a century was applied for date arithmetic
performed with DATE_ADD(), DATE_SUB(), + INTERVAL, and - INTERVAL.
(For example, DATE_ADD('0050-01-01 00:00:00', INTERVAL 0 SECOND)
became '2050-01-01 00:00:00'.)
Setting report back to Patch Queued: Will this fix be applied to 5.0?
[24 Apr 2007 1:22]
Paul DuBois
Removed the note from the 5.1 upgrade section because the dates are now not converted to NULL, they are left alone.
[14 May 2007 12:52]
Tatiana Azundris Nuernberg
pushed to 5.0.42-maint
[22 May 2007 17:01]
Bugs System
Pushed into 5.1.19-beta
[22 May 2007 17:02]
Bugs System
Pushed into 5.0.44
[4 Jun 2007 18:34]
Paul DuBois
Noted in 5.0.44 changelog. Moved 5.1 changelog entry from 5.1.18 to 5.1.19.
Description: DATE_ADD and DATE_SUB apply year2K magic not only to 2 digit year numbers but also to 4 digit ones if the year is < 0200 How to repeat: mysql> select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); +---------------------------------------------------+ | date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) | +---------------------------------------------------+ | 2049-12-31 23:59:59 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND); +---------------------------------------------------+ | date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND) | +---------------------------------------------------+ | 2098-12-31 23:59:59 | +---------------------------------------------------+ 1 row in set (0.01 sec) mysql> select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND); +---------------------------------------------------+ | date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND) | +---------------------------------------------------+ | 0199-12-31 23:59:59 | +---------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: either enforce the documented year 1000 limit for date values ( http://bugs.mysql.com/bug.php?id=18996 ) or make things work as expected