From: Date: April 25 2006 7:07am Subject: bk commit into 4.1 tree (tnurnberg:1.2462) BUG#18997 List-Archive: http://lists.mysql.com/commits/5450 X-Bug: 18997 Message-Id: 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) +1000-01-01 00: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