From: Date: May 16 2006 3:32am Subject: bk commit into 5.1 tree (tnurnberg:1.2142) BUG#18997 List-Archive: http://lists.mysql.com/commits/6427 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.2142 06/05/16 03:32:24 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.117 06/05/16 03:32:17 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/16 03:32:17 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.42 06/05/16 03:32:17 tnurnberg@stripped +21 -10 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.52 06/05/16 03:32:17 tnurnberg@stripped +39 -18 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/16 03:32:17 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-18997e --- 1.116/sql/item_timefunc.cc 2006-05-16 01:26:44 +02:00 +++ 1.117/sql/item_timefunc.cc 2006-05-16 03:32:17 +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-16 03:32:17 +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-16 03:32:17 +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.51/mysql-test/r/func_time.result 2006-05-16 01:26:43 +02:00 +++ 1.52/mysql-test/r/func_time.result 2006-05-16 03:32:17 +02:00 @@ -736,42 +736,28 @@ last_day('2005-00-00') NULL Warnings: -Warning 1292 Truncated incorrect datetime value: '2005-00-00' +Warning 1292 Incorrect datetime value: '2005-00-00' select last_day('2005-00-01'); last_day('2005-00-01') NULL Warnings: -Warning 1292 Truncated incorrect datetime value: '2005-00-01' +Warning 1292 Incorrect datetime value: '2005-00-01' select last_day('2005-01-00'); last_day('2005-01-00') NULL Warnings: -Warning 1292 Truncated incorrect datetime value: '2005-01-00' +Warning 1292 Incorrect datetime value: '2005-01-00' select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL +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 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select timestampdiff(WEEK,_latin1'2001-02-01',_latin1'2001-05-01') AS `a1`,timestampdiff(SECOND_FRAC,_latin1'2001-02-01 12:59:59.120000',_latin1'2001-05-01 12:58:58.119999') AS `a2` -select last_day('2005-00-00'); -last_day('2005-00-00') -NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-00-00' -select last_day('2005-00-01'); -last_day('2005-00-01') -NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-00-01' -select last_day('2005-01-00'); -last_day('2005-01-00') -NULL -Warnings: -Warning 1292 Incorrect datetime value: '2005-01-00' select time_format('100:00:00', '%H %k %h %I %l'); time_format('100:00:00', '%H %k %h %I %l') 100 100 04 04 4 @@ -891,3 +877,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.41/mysql-test/t/func_time.test 2006-05-10 15:27:37 +02:00 +++ 1.42/mysql-test/t/func_time.test 2006-05-16 03:32:17 +02:00 @@ -367,20 +367,12 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); -# 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; # -# Bug #10568 -# - -select last_day('2005-00-00'); -select last_day('2005-00-01'); -select last_day('2005-01-00'); - -# # Bug #10590: %h, %I, and %l format specifies should all return results in # the 0-11 range # @@ -482,4 +474,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