From: Date: May 15 2006 11:41pm Subject: bk commit into 5.1 tree (tnurnberg:1.2397) BUG#18997 List-Archive: http://lists.mysql.com/commits/6416 X-Bug: 18997 Message-Id: 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) +2049-12-31 23: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) +1989-12-31 23: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