| List: | Internals | « Previous MessageNext Message » | |
| From: | Jim Winstead | Date: | July 14 2005 7:07pm |
| Subject: | bk commit into 5.0 tree (jimw:1.1923) BUG#5906 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of jimw. When jimw 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.1923 05/07/14 10:07:03 jimw@stripped +15 -0 Fix number to date conversion so it always honors the NO_ZERO_DATE, NO_ZERO_IN_DATE, and INVALID_DATES bits of SQL_MODE. (Bug #5906) sql/field.cc 1.272 05/07/14 10:07:00 jimw@stripped +92 -118 Update conversion of numbers to date, datetime, and timestamp to use number_to_datetime() and report errors and warnings correctly and consistently. sql-common/my_time.c 1.14 05/07/14 10:07:00 jimw@stripped +49 -40 Expand check_date() to check NO_ZERO_DATE and NO_ZERO_IN_DATE, and use it from number_to_datetime() as well as str_to_datetime(). Also, make number_to_datetime() return -1 on error so we can distinguish between a violation of NO_ZERO_DATE and other errors. mysql-test/t/timezone2.test 1.8 05/07/14 10:07:00 jimw@stripped +5 -0 Add new test for checking DST gap handling for numbers converted to timestamps. mysql-test/t/strict.test 1.14 05/07/14 10:06:59 jimw@stripped +15 -0 Add new regression test mysql-test/r/type_datetime.result 1.26 05/07/14 10:06:59 jimw@stripped +6 -6 Update results mysql-test/r/timezone2.result 1.12 05/07/14 10:06:59 jimw@stripped +9 -0 Update results mysql-test/r/strict.result 1.19 05/07/14 10:06:59 jimw@stripped +13 -0 Update results mysql-test/r/ps_7ndb.result 1.33 05/07/14 10:06:59 jimw@stripped +12 -4 Update results mysql-test/r/ps_6bdb.result 1.33 05/07/14 10:06:59 jimw@stripped +4 -4 Update results mysql-test/r/ps_5merge.result 1.30 05/07/14 10:06:59 jimw@stripped +8 -8 Update results mysql-test/r/ps_4heap.result 1.29 05/07/14 10:06:59 jimw@stripped +4 -4 Update results mysql-test/r/ps_3innodb.result 1.33 05/07/14 10:06:59 jimw@stripped +4 -4 Update results mysql-test/r/ps_2myisam.result 1.30 05/07/14 10:06:59 jimw@stripped +4 -4 Update results libmysql/libmysql.c 1.223 05/07/14 10:06:59 jimw@stripped +3 -2 Enable fuzzy date handling when converting strings and numbers to datetime fields. include/my_time.h 1.10 05/07/14 10:06:59 jimw@stripped +1 -1 Pass flags to number_to_datetime() so it can check things like NO_ZERO_DATE. # 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: jimw # Host: rama.(none) # Root: /home/jimw/my/mysql-5.0-5906 --- 1.222/libmysql/libmysql.c 2005-07-12 01:58:14 -07:00 +++ 1.223/libmysql/libmysql.c 2005-07-14 10:06:59 -07:00 @@ -3600,7 +3600,7 @@ case MYSQL_TYPE_TIMESTAMP: { MYSQL_TIME *tm= (MYSQL_TIME *)buffer; - (void) str_to_datetime(value, length, tm, 0, &err); + (void) str_to_datetime(value, length, tm, TIME_FUZZY_DATE, &err); *param->error= test(err) && (param->buffer_type == MYSQL_TYPE_DATE && tm->time_type != MYSQL_TIMESTAMP_DATE); break; @@ -3718,7 +3718,8 @@ case MYSQL_TYPE_DATETIME: { int error; - value= number_to_datetime(value, (MYSQL_TIME *) buffer, 1, &error); + value= number_to_datetime(value, (MYSQL_TIME *) buffer, TIME_FUZZY_DATE, + &error); *param->error= test(error); break; } --- 1.271/sql/field.cc 2005-07-03 17:44:30 -07:00 +++ 1.272/sql/field.cc 2005-07-14 10:07:00 -07:00 @@ -4469,13 +4469,12 @@ bool in_dst_time_gap; THD *thd= table->in_use; + /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */ have_smth_to_conv= (str_to_datetime(from, len, &l_time, - ((table->in_use->variables.sql_mode & - MODE_NO_ZERO_DATE) | - MODE_NO_ZERO_IN_DATE), - &error) > + (table->in_use->variables.sql_mode & + MODE_NO_ZERO_DATE), &error) > MYSQL_TIMESTAMP_ERROR); - + if (error || !have_smth_to_conv) { error= 1; @@ -4488,16 +4487,15 @@ { if (!(tmp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap))) { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, from, len, MYSQL_TIMESTAMP_DATETIME, !error); - error= 1; } else if (in_dst_time_gap) { set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_INVALID_TIMESTAMP, + ER_WARN_INVALID_TIMESTAMP, from, len, MYSQL_TIMESTAMP_DATETIME, !error); error= 1; } @@ -4522,8 +4520,8 @@ int error= 0; if (nr < 0 || nr > 99991231235959.0) { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DATA_OUT_OF_RANGE, nr, MYSQL_TIMESTAMP_DATETIME); nr= 0; // Avoid overflow on buff error= 1; @@ -4541,35 +4539,37 @@ bool in_dst_time_gap; THD *thd= table->in_use; - if (number_to_datetime(nr, &l_time, 0, &error)) + /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */ + long tmp= number_to_datetime(nr, &l_time, MODE_NO_ZERO_IN_DATE, &error); + if (tmp < 0) + { + error= 2; + } + if (tmp == 0 && thd->variables.sql_mode & MODE_NO_ZERO_DATE) + { + error= 2; + } + + if (!error && tmp) { if (!(timestamp= TIME_to_timestamp(thd, &l_time, &in_dst_time_gap))) { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, - nr, MYSQL_TIMESTAMP_DATETIME, 1); + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DATA_OUT_OF_RANGE, + nr, MYSQL_TIMESTAMP_DATETIME, 1); error= 1; } - if (in_dst_time_gap) { set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_INVALID_TIMESTAMP, - nr, MYSQL_TIMESTAMP_DATETIME, !error); + ER_WARN_INVALID_TIMESTAMP, + nr, MYSQL_TIMESTAMP_DATETIME, 1); error= 1; } - } - else if (error) - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - WARN_DATA_TRUNCATED, - nr, MYSQL_TIMESTAMP_DATETIME, 1); - if (!error && timestamp == 0 && - (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE)) - { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + } else if (error) + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATETIME, 1); - } #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) @@ -4579,7 +4579,7 @@ else #endif longstore(ptr,(uint32) timestamp); - + return error; } @@ -5152,14 +5152,14 @@ TIME l_time; uint32 tmp; int error; - + if (str_to_datetime(from, len, &l_time, TIME_FUZZY_DATE | (table->in_use->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | MODE_INVALID_DATES)), &error) <= MYSQL_TIMESTAMP_ERROR) { - tmp=0; + tmp= 0; error= 2; } else @@ -5190,56 +5190,50 @@ if (nr < 0.0 || nr > 99991231.0) { tmp=0L; - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARN_DATA_OUT_OF_RANGE, nr, MYSQL_TIMESTAMP_DATE); error= 1; } else tmp=(long) rint(nr); - /* - We don't need to check for zero dates here as this date type is only - used in .frm tables from very old MySQL versions - */ - -#ifdef WORDS_BIGENDIAN - if (table->s->db_low_byte_first) - { - int4store(ptr,tmp); - } - else -#endif - longstore(ptr,tmp); - return error; + return Field_date::store(tmp); } int Field_date::store(longlong nr) { - long tmp; - int error= 0; - if (nr >= LL(19000000000000) && nr < LL(99991231235959)) - nr=nr/LL(1000000); // Timestamp to date - if (nr < 0 || nr > LL(99991231)) + TIME not_used; + int error; + longlong initial_nr= nr; + + nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE | + (table->in_use->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | + MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error); + + if (nr < 0) { - tmp=0L; - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, - nr, MYSQL_TIMESTAMP_DATE, 0); - error= 1; + nr= 0; + error= 2; } - else - tmp=(long) nr; + + if (error) + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + error == 2 ? ER_WARN_DATA_OUT_OF_RANGE : + WARN_DATA_TRUNCATED, initial_nr, + MYSQL_TIMESTAMP_DATETIME, 1); #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) { - int4store(ptr,tmp); + int4store(ptr, nr); } else #endif - longstore(ptr,tmp); + longstore(ptr, nr); return error; } @@ -5363,7 +5357,7 @@ MODE_INVALID_DATES))), &error) <= MYSQL_TIMESTAMP_ERROR) { - tmp=0L; + tmp= 0L; error= 2; } else @@ -5372,7 +5366,7 @@ if (error) set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, from, len, MYSQL_TIMESTAMP_DATE, 1); - + int3store(ptr,tmp); return error; } @@ -5383,7 +5377,7 @@ if (nr < 0.0 || nr > 99991231235959.0) { int3store(ptr,(int32) 0); - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, nr, MYSQL_TIMESTAMP_DATE); return 1; } @@ -5393,52 +5387,28 @@ int Field_newdate::store(longlong nr) { - int32 tmp; - int error= 0; - if (nr >= LL(100000000) && nr <= LL(99991231235959)) - nr=nr/LL(1000000); // Timestamp to date - if (nr < 0L || nr > 99991231L) + TIME l_time; + long tmp; + int error; + if ((tmp= number_to_datetime(nr, &l_time, + (TIME_FUZZY_DATE | + (table->in_use->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), + &error) < 0)) { - tmp=0; - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, nr, - MYSQL_TIMESTAMP_DATE, 1); - error= 1; + tmp= 0L; + error= 2; } else - { - uint month, day; + tmp= l_time.day + l_time.month*32 + l_time.year*16*32; - tmp=(int32) nr; - if (tmp) - { - if (tmp < YY_PART_YEAR*10000L) // Fix short dates - tmp+= (uint32) 20000000L; - else if (tmp < 999999L) - tmp+= (uint32) 19000000L; - - month= (uint) ((tmp/100) % 100); - day= (uint) (tmp%100); - if (month > 12 || day > 31) - { - tmp=0L; // Don't allow date to change - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, nr, - MYSQL_TIMESTAMP_DATE, 1); - error= 1; - } - else - tmp= day + month*32 + (tmp/10000)*16*32; - } - else if (table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE) - { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, - 0, MYSQL_TIMESTAMP_DATE); - error= 1; - } - } - int3store(ptr, tmp); + if (error) + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + error == 2 ? ER_WARN_DATA_OUT_OF_RANGE : + WARN_DATA_TRUNCATED,nr,MYSQL_TIMESTAMP_DATE, 1); + + int3store(ptr,tmp); return error; } @@ -5565,7 +5535,7 @@ int error; ulonglong tmp= 0; enum enum_mysql_timestamp_type func_res; - + func_res= str_to_datetime(from, len, &time_tmp, (TIME_FUZZY_DATE | (table->in_use->variables.sql_mode & @@ -5578,7 +5548,7 @@ error= 1; // Fix if invalid zero date if (error) - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, ER_WARN_DATA_OUT_OF_RANGE, from, len, MYSQL_TIMESTAMP_DATETIME, 1); @@ -5615,20 +5585,24 @@ TIME not_used; int error; longlong initial_nr= nr; - - nr= number_to_datetime(nr, ¬_used, 1, &error); - if (error) - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - WARN_DATA_TRUNCATED, initial_nr, - MYSQL_TIMESTAMP_DATETIME, 1); - else if (nr == 0 && table->in_use->variables.sql_mode & MODE_NO_ZERO_DATE) + nr= number_to_datetime(nr, ¬_used, (TIME_FUZZY_DATE | + (table->in_use->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | + MODE_NO_ZERO_DATE | + MODE_INVALID_DATES))), &error); + + if (nr < 0) { - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - ER_WARN_DATA_OUT_OF_RANGE, - initial_nr, MYSQL_TIMESTAMP_DATE, 1); - error= 1; + nr= 0; + error= 2; } + + if (error) + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, + error == 2 ? ER_WARN_DATA_OUT_OF_RANGE : + WARN_DATA_TRUNCATED, initial_nr, + MYSQL_TIMESTAMP_DATETIME, 1); #ifdef WORDS_BIGENDIAN if (table->s->db_low_byte_first) --- 1.11/mysql-test/r/timezone2.result 2005-04-01 19:17:13 -08:00 +++ 1.12/mysql-test/r/timezone2.result 2005-07-14 10:06:59 -07:00 @@ -40,6 +40,12 @@ Warnings: Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 insert into t1 (i, ts) values +(unix_timestamp(20030330015959),20030330015959), +(unix_timestamp(20030330023000),20030330023000), +(unix_timestamp(20030330030000),20030330030000); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 +insert into t1 (i, ts) values (unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00'); insert into t1 (i, ts) values (unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'), @@ -51,6 +57,9 @@ select * from t1; i ts 1046473200 2003-02-28 23:00:00 +1048985999 2003-03-30 00:59:59 +1048986000 2003-03-30 01:00:00 +1048986000 2003-03-30 01:00:00 1048985999 2003-03-30 00:59:59 1048986000 2003-03-30 01:00:00 1048986000 2003-03-30 01:00:00 --- 1.7/mysql-test/t/timezone2.test 2005-04-01 19:17:13 -08:00 +++ 1.8/mysql-test/t/timezone2.test 2005-07-14 10:07:00 -07:00 @@ -48,6 +48,11 @@ (unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'), (unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'), (unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00'); +# Values around and in spring time-gap +insert into t1 (i, ts) values + (unix_timestamp(20030330015959),20030330015959), + (unix_timestamp(20030330023000),20030330023000), + (unix_timestamp(20030330030000),20030330030000); # Normal value with DST insert into t1 (i, ts) values (unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00'); --- 1.29/mysql-test/r/ps_2myisam.result 2005-06-27 04:12:07 -07:00 +++ 1.30/mysql-test/r/ps_2myisam.result 2005-07-14 10:06:59 -07:00 @@ -2976,25 +2976,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.32/mysql-test/r/ps_3innodb.result 2005-06-27 04:12:07 -07:00 +++ 1.33/mysql-test/r/ps_3innodb.result 2005-07-14 10:06:59 -07:00 @@ -2959,25 +2959,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.28/mysql-test/r/ps_4heap.result 2005-06-27 04:12:07 -07:00 +++ 1.29/mysql-test/r/ps_4heap.result 2005-07-14 10:06:59 -07:00 @@ -2960,25 +2960,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.29/mysql-test/r/ps_5merge.result 2005-06-27 04:12:07 -07:00 +++ 1.30/mysql-test/r/ps_5merge.result 2005-07-14 10:06:59 -07:00 @@ -2896,25 +2896,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 @@ -5908,25 +5908,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.32/mysql-test/r/ps_6bdb.result 2005-06-27 04:12:07 -07:00 +++ 1.33/mysql-test/r/ps_6bdb.result 2005-07-14 10:06:59 -07:00 @@ -2959,25 +2959,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.9/include/my_time.h 2004-12-15 16:14:59 -08:00 +++ 1.10/include/my_time.h 2005-07-14 10:06:59 -07:00 @@ -53,7 +53,7 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time, uint flags, int *was_cut); longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, - my_bool fuzzy_date, int *was_cut); + uint flags, int *was_cut); ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *time); ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *time); ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *time); --- 1.32/mysql-test/r/ps_7ndb.result 2005-06-27 04:12:07 -07:00 +++ 1.33/mysql-test/r/ps_7ndb.result 2005-07-14 10:06:59 -07:00 @@ -5,6 +5,8 @@ a int, b varchar(30), primary key(a) ) engine = 'NDB' ; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't1' create table t9 ( c1 tinyint, c2 smallint, c3 mediumint, c4 int, @@ -18,6 +20,8 @@ c32 set('monday', 'tuesday', 'wednesday'), primary key(c1) ) engine = 'NDB' ; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't9' delete from t1 ; insert into t1 values (1,'one'); insert into t1 values (2,'two'); @@ -1334,6 +1338,8 @@ a int, b varchar(30), primary key(a) ) engine = 'NDB' ; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't2' insert into t2(a,b) select a, b from t1 ; prepare stmt1 from 'update t1 set a=? where b=? and a in (select ? from t2 @@ -1524,6 +1530,8 @@ drop table if exists t2 ; create table t2 (id int auto_increment primary key) ENGINE= 'NDB' ; +Warnings: +Warning 1266 Using storage engine MyISAM for table 't2' prepare stmt1 from ' select last_insert_id() ' ; insert into t2 values (NULL) ; execute stmt1 ; @@ -2959,25 +2967,25 @@ Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 Warnings: Warning 1264 Out of range value adjusted for column 'c13' at row 1 -Warning 1265 Data truncated for column 'c14' at row 1 +Warning 1264 Out of range value adjusted for column 'c14' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value adjusted for column 'c16' at row 1 Warning 1264 Out of range value adjusted for column 'c17' at row 1 --- 1.13/sql-common/my_time.c 2005-01-04 08:21:51 -08:00 +++ 1.14/sql-common/my_time.c 2005-07-14 10:07:00 -07:00 @@ -56,11 +56,14 @@ } /* - check date. + Check datetime value for validity according to flags. - SYNOPOSIS - bool check_date() - time Date to check. + SYNOPSIS + check_date() + ltime - Date to check. + not_zero_date - ltime is not the zero date + flags - flags to check + was_cut - set to whether the value was truncated NOTES Here we assume that year and month is ok ! @@ -69,18 +72,35 @@ RETURN 0 ok - 1 errro + 1 error */ -bool check_date(MYSQL_TIME *ltime) +bool check_date(const MYSQL_TIME *ltime, bool not_zero_date, ulong flags, + int *was_cut) { - if (ltime->month && ltime->day > days_in_month[ltime->month-1]) + + if (not_zero_date) { - if (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 || - ltime->day != 29) - return 1; + if ((((flags & TIME_NO_ZERO_IN_DATE) || !(flags & TIME_FUZZY_DATE)) && + (ltime->month == 0 || ltime->day == 0)) || + (!(flags & TIME_INVALID_DATES) && + ltime->month && ltime->day > days_in_month[ltime->month-1] && + (ltime->month != 2 || calc_days_in_year(ltime->year) != 366 || + ltime->day != 29))) + { + *was_cut= 2; + return TRUE; + } } - return 0; + else if (flags & TIME_NO_ZERO_DATE) + { + /* + We don't set *was_cut here to signal that the problem was a zero date + and not an invalid date + */ + return TRUE; + } + return FALSE; } @@ -100,7 +120,7 @@ TIME_INVALID_DATES Allow 2000-02-31 was_cut 0 Value ok 1 If value was cut during conversion - 2 Date part was withing ranges but date was wrong + 2 Date part was within ranges but date was wrong DESCRIPTION At least the following formats are recogniced (based on number of digits) @@ -168,8 +188,6 @@ *was_cut= 1; DBUG_RETURN(MYSQL_TIMESTAMP_NONE); } - if (flags & TIME_NO_ZERO_IN_DATE) - flags&= ~TIME_FUZZY_DATE; is_internal_format= 0; /* This has to be changed if want to activate different timestamp formats */ @@ -385,22 +403,10 @@ if (year_length == 2 && not_zero_date) l_time->year+= (l_time->year < YY_PART_YEAR ? 2000 : 1900); - if (!not_zero_date && (flags & TIME_NO_ZERO_DATE)) - { - /* - We don't set *was_cut here to signal that the problem was a zero date - and not an invalid date - */ - goto err; - } - if (number_of_fields < 3 || l_time->year > 9999 || l_time->month > 12 || l_time->day > 31 || l_time->hour > 23 || - l_time->minute > 59 || l_time->second > 59 || - (!(flags & TIME_FUZZY_DATE) && (l_time->month == 0 || - l_time->day == 0) && - not_zero_date)) + l_time->minute > 59 || l_time->second > 59) { /* Only give warning for a zero date if there is some garbage after */ if (!not_zero_date) /* If zero date */ @@ -418,15 +424,12 @@ goto err; } + if (check_date(l_time, not_zero_date, flags, was_cut)) + goto err; + l_time->time_type= (number_of_fields <= 3 ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME); - if (not_zero_date && !(flags & TIME_INVALID_DATES) && check_date(l_time)) - { - *was_cut= 2; /* Not correct date */ - goto err; - } - for (; str != end ; str++) { if (!my_isspace(&my_charset_latin1,*str)) @@ -881,9 +884,10 @@ number_to_datetime() nr - datetime value as number time_res - pointer for structure for broken-down representation - fuzzy_date - indicates whenever we allow fuzzy dates - was_cut - set ot 1 if there was some kind of error during - conversion or to 0 if everything was OK. + flags - flags to use in validating date, as in str_to_datetime() + was_cut 0 Value ok + 1 If value was cut during conversion + 2 Date part was within ranges but date was wrong DESCRIPTION Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMSS, @@ -893,12 +897,13 @@ This function also checks if datetime value fits in DATETIME range. RETURN VALUE + -1 Timestamp with wrong values + anything else DATETIME as integer in YYYYMMDDHHMMSS format Datetime value in YYYYMMDDHHMMSS format. - If input value is not valid datetime value then 0 is returned. */ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res, - my_bool fuzzy_date, int *was_cut) + uint flags, int *was_cut) { long part1,part2; @@ -952,13 +957,17 @@ if (time_res->year <= 9999 && time_res->month <= 12 && time_res->day <= 31 && time_res->hour <= 23 && time_res->minute <= 59 && time_res->second <= 59 && - (fuzzy_date || (time_res->month != 0 && time_res->day != 0) || nr==0)) + !check_date(time_res, (nr != 0), flags, was_cut)) return nr; + /* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */ + if (!nr && flags & TIME_NO_ZERO_DATE) + return LL(-1); + err: *was_cut= 1; - return LL(0); + return LL(-1); } --- 1.25/mysql-test/r/type_datetime.result 2005-05-04 05:53:05 -07:00 +++ 1.26/mysql-test/r/type_datetime.result 2005-07-14 10:06:59 -07:00 @@ -100,12 +100,12 @@ insert into t1 values (20030102030460),(20030102036301),(20030102240401), (20030132030401),(20031302030401),(100001202030401); Warnings: -Warning 1265 Data truncated for column 't' at row 1 -Warning 1265 Data truncated for column 't' at row 2 -Warning 1265 Data truncated for column 't' at row 3 -Warning 1265 Data truncated for column 't' at row 4 -Warning 1265 Data truncated for column 't' at row 5 -Warning 1265 Data truncated for column 't' at row 6 +Warning 1264 Out of range value adjusted for column 't' at row 1 +Warning 1264 Out of range value adjusted for column 't' at row 2 +Warning 1264 Out of range value adjusted for column 't' at row 3 +Warning 1264 Out of range value adjusted for column 't' at row 4 +Warning 1264 Out of range value adjusted for column 't' at row 5 +Warning 1264 Out of range value adjusted for column 't' at row 6 select * from t1; t 0000-00-00 00:00:00 --- 1.18/mysql-test/r/strict.result 2005-05-13 01:22:21 -07:00 +++ 1.19/mysql-test/r/strict.result 2005-07-14 10:06:59 -07:00 @@ -1235,3 +1235,16 @@ ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead create table t1(a varbinary(65537)); ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead +set @@sql_mode='traditional'; +create table t1 (d date); +insert into t1 values ('2000-10-00'); +ERROR 22007: Incorrect date value: '2000-10-00' for column 'd' at row 1 +insert into t1 values (1000); +ERROR 22007: Incorrect date value: '1000' for column 'd' at row 1 +insert into t1 values ('2000-10-01'); +update t1 set d = 1100; +ERROR 22007: Incorrect date value: '1100' for column 'd' at row 1 +select * from t1; +d +2000-10-01 +drop table t1; --- 1.13/mysql-test/t/strict.test 2005-05-13 01:22:21 -07:00 +++ 1.14/mysql-test/t/strict.test 2005-07-14 10:06:59 -07:00 @@ -1093,3 +1093,18 @@ create table t1(a varchar(65537)); --error 1074 create table t1(a varbinary(65537)); + +# +# Bug #5906: handle invalid date due to conversion +# +set @@sql_mode='traditional'; +create table t1 (d date); +--error 1292 +insert into t1 values ('2000-10-00'); +--error 1292 +insert into t1 values (1000); +insert into t1 values ('2000-10-01'); +--error 1292 +update t1 set d = 1100; +select * from t1; +drop table t1;
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (jimw:1.1923) BUG#5906 | Jim Winstead | 14 Jul |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.818 sec. using MySQL 5.1.14-beta-log