bk commit into 4.1 tree (tnurnberg:1.2462) BUG#18997

[Tatjana A Nuernberg]

Below is the list of changes that have just been committed into a local
4.1 repository of azundris. When azundris does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2462 06/04/25 07:07:40 tnurnberg@stripped +3 -0
  Bug#18997: DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year
value
  
  my_time.c->str_to_datetime() parses 2-digit years and 4-digit years
  correctly and applies Y2K-magic to 2-digit years only.  Information
  on whether the underlying string had a 2-digit- or a 4-digit-year is
  contained in the resulting st_mysql_time/MYSQL_TIME struct only
  implicitly (if (year<1000) then it was entered as four digits, as magic
  would have been performed on any 2-digit year, raising it to 1900+).
  
  In item_timefunc.cc->Item_date_add_interval::get_date() calls
  my_time.c->calc_daynr() which again performs magic if (year < 200).
  calc_daynr() is called from all over the place; there may be callers
  which in certain situations rely on the magic to be performed.
  
  DATE and DATETIME on the other hand are not guaranteed to work with
  years < 1000, cf. http://dev.mysql.com/doc/refman/4.1/en/datetime.html
  
  Rather than have Item_date_add_interval::get_date() work as expected
  for (year >= 200), but return incorrect results for (year < 200), years
  earlier than 1000 in parameter OR result will now result in NULL being
  returned for cleaner semantics and easier debugging.

  sql/item_timefunc.cc
    1.99 06/04/25 07:06:01 tnurnberg@stripped +6 -1
    Bug#18997: DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year
value
    
    my_time.c->str_to_datetime() parses 2-digit years and 4-digit years
    correctly and applies Y2K-magic to 2-digit years only.  Information
    on whether the underlying string had a 2-digit- or a 4-digit-year is
    contained in the resulting st_mysql_time/MYSQL_TIME struct only
    implicitly (if (year<1000) then it was entered as four digits, as magic
    would have been performed on any 2-digit year, raising it to 1900+).
    
    In item_timefunc.cc->Item_date_add_interval::get_date() calls
    my_time.c->calc_daynr() which again performs magic if (year < 200).
    calc_daynr() is called from all over the place; there may be callers
    which in certain situations rely on the magic to be performed.
    
    DATE and DATETIME on the other hand are not guaranteed to work with
    years < 1000, cf. http://dev.mysql.com/doc/refman/4.1/en/datetime.html
    
    Rather than have Item_date_add_interval::get_date() work as expected
    for (year >= 200), but return incorrect results for (year < 200), years
    earlier than 1000 in parameter OR result will now result in NULL being
    returned for cleaner semantics and easier debugging.
    
    This is somewhat conservative; it works according to the specs and affects
    as little as possible so not to break anything.

  mysql-test/t/func_time.test
    1.33 06/04/25 07:06:01 tnurnberg@stripped +14 -0
    add test for Bug#18997: date_add()/date_sub should fail for in-values and
    out-values featuring a year before 1000.

  mysql-test/r/func_time.result
    1.38 06/04/25 07:06:01 tnurnberg@stripped +27 -0
    add test for Bug#18997: date_add()/date_sub should fail for in-values and
    out-values featuring a year before 1000.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	tnurnberg
# Host:	salvation.intern.azundris.com
# Root:	/home/mysql-4.1-18997

--- 1.98/sql/item_timefunc.cc	2006-04-13 07:55:41 +02:00
+++ 1.99/sql/item_timefunc.cc	2006-04-25 07:06:01 +02:00
@@ -1884,6 +1884,9 @@
   if (date_sub_interval)
     sign = -sign;
 
+  if (ltime->year < 1000)
+    goto null_date;
+
   null_value=0;
   switch (int_type) {
   case INTERVAL_SECOND:
@@ -1971,7 +1974,9 @@
   default:
     goto null_date;
   }
-  return 0;					// Ok
+
+  if (ltime->year >= 1000)
+    return 0;					// Ok
 
  null_date:
   return (null_value=1);

--- 1.37/mysql-test/r/func_time.result	2005-06-24 11:04:43 +02:00
+++ 1.38/mysql-test/r/func_time.result	2006-04-25 07:06:01 +02:00
@@ -626,3 +626,30 @@
 NULL
 Warnings:
 Warning	1292	Truncated incorrect datetime value: '2005-01-00'
+select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+select date_sub("1000-01-01 00:00:01",INTERVAL 1 SECOND);
+date_sub("1000-01-01 00:00:01",INTERVAL 1 SECOND)
[phone]:00:00
+select date_sub("1000-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("1000-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+select date_sub("0999-01-01 00:00:01",INTERVAL 1 SECOND);
+date_sub("0999-01-01 00:00:01",INTERVAL 1 SECOND)
+NULL
+select date_add("0999-01-01 23:59:59",INTERVAL 1 SECOND);
+date_add("0999-01-01 23:59:59",INTERVAL 1 SECOND)
+NULL
+select date_add("0999-01-01 23:59:59",INTERVAL 2 SECOND);
+date_add("0999-01-01 23:59:59",INTERVAL 2 SECOND)
+NULL
+select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
+date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)
+NULL

--- 1.32/mysql-test/t/func_time.test	2005-07-28 02:21:42 +02:00
+++ 1.33/mysql-test/t/func_time.test	2006-04-25 07:06:01 +02:00
@@ -315,4 +315,18 @@
 select last_day('2005-00-01');
 select last_day('2005-01-00');
 
+#
+# Bug #18997
+#
+
+select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("1000-01-01 00:00:01",INTERVAL 1 SECOND);
+select date_sub("1000-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0999-01-01 00:00:01",INTERVAL 1 SECOND);
+select date_add("0999-01-01 23:59:59",INTERVAL 1 SECOND);
+select date_add("0999-01-01 23:59:59",INTERVAL 2 SECOND);
+select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
+
 # End of 4.1 tests