diff options
| -rw-r--r-- | mysql-test/r/func_date_add.result | 26 | ||||
| -rw-r--r-- | mysql-test/t/func_date_add.test | 23 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 13 | ||||
| -rw-r--r-- | sql/share/errmsg.txt | 2 |
4 files changed, 60 insertions, 4 deletions
diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result index 50889943b56..15ec80426ea 100644 --- a/mysql-test/r/func_date_add.result +++ b/mysql-test/r/func_date_add.result @@ -45,3 +45,29 @@ visitor_id mts 465931136 2000-03-18 16:09:53 1092858576 2000-03-19 01:34:45 drop table t1; +set sql_mode='traditional'; +create table t1 (d date); +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +ERROR 22008: Datetime function: datetime field overflow +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +ERROR 22008: Datetime function: datetime field overflow +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +set sql_mode=''; +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +Warnings: +Warning 1437 Datetime function: datetime field overflow +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +Warnings: +Warning 1437 Datetime function: datetime field overflow +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +select * from t1; +d +NULL +NULL +NULL +NULL +NULL +NULL +drop table t1; diff --git a/mysql-test/t/func_date_add.test b/mysql-test/t/func_date_add.test index b768e4fec61..e01fce30577 100644 --- a/mysql-test/t/func_date_add.test +++ b/mysql-test/t/func_date_add.test @@ -41,4 +41,27 @@ select visitor_id,max(ts) as mts from t1 group by visitor_id having DATE_ADD(mts,INTERVAL 3 MONTH) < NOW(); drop table t1; +# +# Bug #10627: Invalid date turned to NULL from date_sub/date_add in +# traditional mode +# +set sql_mode='traditional'; +create table t1 (d date); +--error S22008 +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +--error S22008 +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +# No warnings/errors from the next two +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +set sql_mode=''; +# These will all work now, and we'll end up with some NULL entries in the +# table and some warnings. +insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); +insert into t1 (d) select date_add('2000-01-01',interval 8000 year); +insert into t1 values (date_add(NULL, INTERVAL 1 DAY)); +insert into t1 values (date_add('2000-01-04', INTERVAL NULL DAY)); +select * from t1; +drop table t1; + # End of 4.1 tests diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index a6fbbee6f23..dfbfca3b078 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -1938,7 +1938,7 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) daynr= calc_daynr(ltime->year,ltime->month,1) + days; /* Day number from year 0 to 9999-12-31 */ if ((ulonglong) daynr >= MAX_DAY_NUMBER) - goto null_date; + goto invalid_date; get_date_from_daynr((long) daynr, <ime->year, <ime->month, <ime->day); break; @@ -1949,13 +1949,13 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) sign * (long) interval.day); /* Daynumber from year 0 to 9999-12-31 */ if ((ulong) period >= MAX_DAY_NUMBER) - goto null_date; + goto invalid_date; get_date_from_daynr((long) period,<ime->year,<ime->month,<ime->day); break; case INTERVAL_YEAR: ltime->year+= sign * (long) interval.year; if ((ulong) ltime->year >= 10000L) - goto null_date; + goto invalid_date; if (ltime->month == 2 && ltime->day == 29 && calc_days_in_year(ltime->year) != 366) ltime->day=28; // Was leap-year @@ -1966,7 +1966,7 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) period= (ltime->year*12 + sign * (long) interval.year*12 + ltime->month-1 + sign * (long) interval.month); if ((ulong) period >= 120000L) - goto null_date; + goto invalid_date; ltime->year= (uint) (period / 12); ltime->month= (uint) (period % 12L)+1; /* Adjust day if the new month doesn't have enough days */ @@ -1982,6 +1982,11 @@ bool Item_date_add_interval::get_date(TIME *ltime, uint fuzzy_date) } return 0; // Ok +invalid_date: + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_DATETIME_FUNCTION_OVERFLOW, + ER(ER_DATETIME_FUNCTION_OVERFLOW), + "datetime"); null_date: return (null_value=1); } diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index 459560ccac8..c157d823483 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5386,3 +5386,5 @@ ER_TRG_IN_WRONG_SCHEMA eng "Trigger in wrong schema" ER_STACK_OVERRUN_NEED_MORE eng "Thread stack overrun: %ld bytes used of a %ld byte stack, and %ld bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack." +ER_DATETIME_FUNCTION_OVERFLOW 22008 + eng "Datetime function: %-.32s field overflow" |