bk commit into 5.1 tree (tnurnberg:1.2397) BUG#18997

[Tatjana A Nuernberg]

Below is the list of changes that have just been committed into a local
5.1 repository of tnurnberg. When tnurnberg 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.2397 06/05/15 23:41:07 tnurnberg@stripped +5 -0
  Bug#18997: DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year
value
  
  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.

  sql/item_timefunc.cc
    1.115 06/05/15 23:41:01 tnurnberg@stripped +3 -0
    throw NULL when year in date_add() / date_sub() would be affected by
    2 digit -> 4 digit magic.

  sql-common/my_time.c
    1.20 06/05/15 23:41:01 tnurnberg@stripped +1 -1
    use new const YY_MAGIC_BELOW, apply 2-digit -> 4-digit magic only to years
    below this threshold.

  mysql-test/t/func_time.test
    1.41 06/05/15 23:41:01 tnurnberg@stripped +21 -3
    test where 2 digit -> 4 digit year magic kicks in, and whether we throw NULL when
    it happens

  mysql-test/r/func_time.result
    1.51 06/05/15 23:41:01 tnurnberg@stripped +36 -0
    test where 2 digit -> 4 digit year magic kicks in, and whether we throw NULL when
    it happens

  include/my_time.h
    1.13 06/05/15 23:41:01 tnurnberg@stripped +3 -0
    new define YY_MAGIC_BELOW: if year is below this threshold, magic kicks in in
    calc_daynr(). the idea is to convert two-digit years to four-digit ones, adding
    1900 to values >= YY_PART_YEAR or adding 2000 otherwise.
    current value of YY_MAGIC_BELOW derived from original code in calc_daynr()

# 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-5.1-18997

--- 1.114/sql/item_timefunc.cc	2006-05-10 21:44:05 +02:00
+++ 1.115/sql/item_timefunc.cc	2006-05-15 23:41:01 +02:00
@@ -1963,6 +1963,9 @@
   if (date_sub_interval)
     interval.neg = !interval.neg;
 
+  if (ltime->year < YY_MAGIC_BELOW)
+    return (null_value=1);
+
   return (null_value= date_add_interval(ltime, int_type, interval));
 }
 

--- 1.12/include/my_time.h	2006-01-18 20:41:03 +01:00
+++ 1.13/include/my_time.h	2006-05-15 23:41:01 +02:00
@@ -38,7 +38,10 @@
 #define MY_TIME_T_MAX LONG_MAX
 #define MY_TIME_T_MIN LONG_MIN
 
+/* two-digit years < this are 20..; >= this are 19.. */
 #define YY_PART_YEAR	   70
+/* apply above magic to years < this */
+#define YY_MAGIC_BELOW     200
 
 /* Flags to str_to_datetime */
 #define TIME_FUZZY_DATE		1

--- 1.19/sql-common/my_time.c	2005-12-15 16:59:29 +01:00
+++ 1.20/sql-common/my_time.c	2006-05-15 23:41:01 +02:00
@@ -689,7 +689,7 @@
 
   if (year == 0 && month == 0 && day == 0)
     DBUG_RETURN(0);				/* Skip errors */
-  if (year < 200)
+  if (year < YY_MAGIC_BELOW)
   {
     if ((year=year+1900) < 1900+YY_PART_YEAR)
       year+=100;

--- 1.50/mysql-test/r/func_time.result	2006-04-12 22:14:52 +02:00
+++ 1.51/mysql-test/r/func_time.result	2006-05-15 23:41:01 +02:00
@@ -732,6 +732,7 @@
 count(*)
 3
 DROP TABLE t1;
+End of 4.1 tests
 explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
 timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01
12:58:58.119999') as a2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -872,3 +873,38 @@
 1	2005-06-01	3	2005-07-15
 3	2005-07-01	3	2005-07-15
 DROP TABLE t1,t2;
+End of 5.0 tests
+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_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
+date_add("0199-12-31 23:59:59",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)
+0199-12-31 23:59:59
+select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
+date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND)
+0200-01-01 00:00:00
+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
+select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
+date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR)
+0001-01-01 23:59:59
+select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND)
[phone]:59:59
+select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND)
[phone]:59:59
+select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
+date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND)
+NULL
+End of 5.1 tests

--- 1.40/mysql-test/t/func_time.test	2006-04-11 19:03:30 +02:00
+++ 1.41/mysql-test/t/func_time.test	2006-05-15 23:41:01 +02:00
@@ -352,8 +352,7 @@
 SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND
d<=FROM_DAYS(TO_DAYS(@TMP)+1);
 DROP TABLE t1;
 
-
-# End of 4.1 tests
+--echo End of 4.1 tests
 
 explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,
 			timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01
12:58:58.119999') as a2;
@@ -468,4 +467,23 @@
  
 DROP TABLE t1,t2;
 
-# End of 5.0 tests
+--echo End of 5.0 tests
+
+#
+# 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_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);
+select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);
+select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);
+select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);
+select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);
+
+
+--echo End of 5.1 tests