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:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1 OS:Any (*)
Assigned to: Tatiana Azundris Nuernberg

[10 Apr 2006 21:41] Hartmut Holzgraefe
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
[11 Apr 2006 1:39] Miguel Solorzano
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.