diff options
| -rw-r--r-- | mysql-test/r/cast.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 41 | ||||
| -rw-r--r-- | mysql-test/t/func_time.test | 20 | ||||
| -rw-r--r-- | sql/field.cc | 16 | ||||
| -rw-r--r-- | sql/item.h | 16 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 154 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 14 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 60 | ||||
| -rw-r--r-- | sql/item_timefunc.h | 12 |
9 files changed, 293 insertions, 42 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 68687670e17..2538fbfd61d 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -192,7 +192,7 @@ cast("2001-1-1" as datetime) = "2001-01-01 00:00:00" 1 select cast("1:2:3" as TIME) = "1:02:03"; cast("1:2:3" as TIME) = "1:02:03" -0 +1 select cast(NULL as DATE); cast(NULL as DATE) NULL diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 02f3d2f7273..93d4542bf5b 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -630,3 +630,44 @@ 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 +create table t1(f1 date, f2 time, f3 datetime); +insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); +insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); +select f1 from t1 where f1 between "2006-1-1" and 20060101; +f1 +2006-01-01 +select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +f1 +2006-01-01 +select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +f1 +2006-01-01 +select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +f2 +12:01:02 +select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +f2 +12:01:02 +select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +f3 +2006-01-01 12:01:01 +select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +f3 +2006-01-01 12:01:01 +select f1 from t1 where "2006-1-1" between f1 and f3; +f1 +2006-01-01 +select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +f1 +2006-01-01 +select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +f1 +Warnings: +Warning 1292 Truncated incorrect date value: 'zzz' +select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); +f1 +2006-01-01 +select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); +f1 +2006-01-02 +drop table t1; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 01e4e47d318..5e0b35522cf 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -322,4 +322,24 @@ select last_day('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')); +# +# Bug#16377 result of DATE/TIME functions were compared as strings which +# can lead to a wrong result. +# +create table t1(f1 date, f2 time, f3 datetime); +insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); +insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); +select f1 from t1 where f1 between "2006-1-1" and 20060101; +select f1 from t1 where f1 between "2006-1-1" and "2006.1.1"; +select f1 from t1 where date(f1) between "2006-1-1" and "2006.1.1"; +select f2 from t1 where f2 between "12:1:2" and "12:2:2"; +select f2 from t1 where time(f2) between "12:1:2" and "12:2:2"; +select f3 from t1 where f3 between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f3 from t1 where timestamp(f3) between "2006-1-1 12:1:1" and "2006-1-1 12:1:2"; +select f1 from t1 where "2006-1-1" between f1 and f3; +select f1 from t1 where "2006-1-1" between date(f1) and date(f3); +select f1 from t1 where "2006-1-1" between f1 and 'zzz'; +select f1 from t1 where makedate(2006,1) between date(f1) and date(f3); +select f1 from t1 where makedate(2006,2) between date(f1) and date(f3); +drop table t1; # End of 4.1 tests diff --git a/sql/field.cc b/sql/field.cc index a64eaad7308..ec4d4b4e4f5 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -6841,7 +6841,11 @@ create_field::create_field(Field *old_field,Field *orig_field) bool Field::set_warning(const uint level, const uint code, int cuted_increment) { - THD *thd= table->in_use; + /* + If this field was created only for type conversion purposes it + will have table == NULL. + */ + THD *thd= table ? table->in_use : current_thd; if (thd->count_cuted_fields) { thd->cuted_fields+= cuted_increment; @@ -6876,7 +6880,8 @@ Field::set_datetime_warning(const uint level, const uint code, timestamp_type ts_type, int cuted_increment) { if (set_warning(level, code, cuted_increment)) - make_truncated_value_warning(table->in_use, str, str_length, ts_type); + make_truncated_value_warning(table ? table->in_use : current_thd, + str, str_length, ts_type); } @@ -6905,8 +6910,8 @@ Field::set_datetime_warning(const uint level, const uint code, { char str_nr[22]; char *str_end= longlong10_to_str(nr, str_nr, -10); - make_truncated_value_warning(table->in_use, str_nr, str_end - str_nr, - ts_type); + make_truncated_value_warning(table ? table->in_use : current_thd, + str_nr, str_end - str_nr, ts_type); } } @@ -6935,7 +6940,8 @@ Field::set_datetime_warning(const uint level, const uint code, /* DBL_DIG is enough to print '-[digits].E+###' */ char str_nr[DBL_DIG + 8]; uint str_len= my_sprintf(str_nr, (str_nr, "%g", nr)); - make_truncated_value_warning(table->in_use, str_nr, str_len, ts_type); + make_truncated_value_warning(table ? table->in_use : current_thd, + str_nr, str_len, ts_type); } } diff --git a/sql/item.h b/sql/item.h index 66da7ad4e74..eca8ee184a1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -327,6 +327,14 @@ public: cleanup(); delete this; } + /* + result_as_longlong() must return TRUE for Items representing DATE/TIME + functions and DATE/TIME table fields. + Those Items have result_type()==STRING_RESULT (and not INT_RESULT), but + their values should be compared as integers (because the integer + representation is more precise than the string one). + */ + virtual bool result_as_longlong() { return FALSE; } }; @@ -450,6 +458,10 @@ public: Item *get_tmp_table_item(THD *thd); void cleanup(); inline uint32 max_disp_length() { return field->max_length(); } + bool result_as_longlong() + { + return field->can_be_compared_as_longlong(); + } friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; @@ -973,6 +985,10 @@ public: } Item *real_item() { return *ref; } void print(String *str); + bool result_as_longlong() + { + return (*ref)->result_as_longlong(); + } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index bfbb3355004..e92e1d30ca4 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -25,6 +25,8 @@ #include <m_ctype.h> #include "sql_select.h" +static bool convert_constant_item(THD *thd, Field *field, Item **item); + static Item_result item_store_type(Item_result a,Item_result b) { if (a == STRING_RESULT || b == STRING_RESULT) @@ -64,6 +66,7 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems) SYNOPSIS: agg_cmp_type() + thd thread handle type [out] the aggregated type items array of items to aggregate the type from nitems number of items in the array @@ -78,24 +81,133 @@ static void agg_result_type(Item_result *type, Item **items, uint nitems) If all items are constants the type will be aggregated from all items. If there are some non-constant items then only types of non-constant items will be used for aggregation. + If there are DATE/TIME fields/functions in the list and no string + fields/functions in the list then: + The INT_RESULT type will be used for aggregation instead of orginal + result type of any DATE/TIME field/function in the list + All constant items in the list will be converted to a DATE/TIME using + found field or result field of found function. + + Implementation notes: + The code is equvalent to: + 1. Check the list for presense of a STRING field/function. + Collect the is_const flag. + 2. Get a Field* object to use for type coercion + 3. Perform type conversion. + 1 and 2 are implemented in 2 loops. The first searches for a DATE/TIME + field/function and checks presense of a STRING field/function. + The second loop works only if a DATE/TIME field/function is found. + It checks presense of a STRING field/function in the rest of the list. + + TODO + 1) The current implementation can produce false comparison results for + expressions like: + date_time_field BETWEEN string_field_with_dates AND string_constant + if the string_constant will omit some of leading zeroes. + In order to fully implement correct comparison of DATE/TIME the new + DATETIME_RESULT result type should be introduced and agg_cmp_type() + should return the DATE/TIME field used for the conversion. Later + this field can be used by comparison functions like Item_func_between to + convert string values to ints on the fly and thus return correct results. + This modification will affect functions BETWEEN, IN and CASE. + + 2) If in the list a DATE field/function and a DATETIME field/function + are present in the list then the first found field/function will be + used for conversion. This may lead to wrong results and probably should + be fixed. */ -static void agg_cmp_type(Item_result *type, Item **items, uint nitems) + +static void agg_cmp_type(THD *thd, Item_result *type, Item **items, uint nitems) { uint i; - type[0]= items[0]->result_type(); + Item::Type res; + char *buff= NULL; + uchar null_byte; + Field *field= NULL; + + /* Search for date/time fields/functions */ + for (i= 0; i < nitems; i++) + { + if (!items[i]->result_as_longlong()) + { + /* Do not convert anything if a string field/function is present */ + if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT) + { + i= nitems; + break; + } + continue; + } + if ((res= items[i]->real_item()->type()) == Item::FIELD_ITEM) + { + field= ((Item_field *)items[i]->real_item())->field; + break; + } + else if (res == Item::FUNC_ITEM) + { + field= items[i]->tmp_table_field_from_field_type(0); + if (field) + buff= alloc_root(thd->mem_root, field->max_length()); + if (!buff || !field) + { + if (field) + delete field; + if (buff) + my_free(buff, MYF(MY_WME)); + field= 0; + } + else + field->move_field(buff, &null_byte, 0); + break; + } + } + if (field) + { + /* Check the rest of the list for presense of a string field/function. */ + for (i++ ; i < nitems; i++) + { + if (!items[i]->const_item() && items[i]->result_type() == STRING_RESULT && + !items[i]->result_as_longlong()) + { + field= 0; + break; + } + } + } /* Reset to 0 on first occurence of non-const item. 1 otherwise */ bool is_const= items[0]->const_item(); + /* + If the first item is a date/time function then its result should be + compared as int + */ + if (field) + { + /* Suppose we are comparing dates and some non-constant items are present. */ + type[0]= INT_RESULT; + is_const= 0; + } + else + type[0]= items[0]->result_type(); - for (i= 1 ; i < nitems ; i++) + for (i= 0; i < nitems ; i++) { if (!items[i]->const_item()) { - type[0]= is_const ? items[i]->result_type() : - item_cmp_type(type[0], items[i]->result_type()); + Item_result result= field && items[i]->result_as_longlong() ? + INT_RESULT : items[i]->result_type(); + type[0]= is_const ? result : item_cmp_type(type[0], result); is_const= 0; } else if (is_const) type[0]= item_cmp_type(type[0], items[i]->result_type()); + else if (field) + convert_constant_item(thd, field, &items[i]); + } + + if (res == Item::FUNC_ITEM && field) + { + delete field; + my_free(buff, MYF(MY_WME)); } } @@ -929,31 +1041,9 @@ void Item_func_between::fix_length_and_dec() */ if (!args[0] || !args[1] || !args[2]) return; - agg_cmp_type(&cmp_type, args, 3); - if (cmp_type == STRING_RESULT && - agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV)) - return; - - /* - Make a special case of compare with date/time and longlong fields. - They are compared as integers, so for const item this time-consuming - conversion can be done only once, not for every single comparison - */ - if (args[0]->type() == FIELD_ITEM) - { - Field *field=((Item_field*) args[0])->field; - if (field->can_be_compared_as_longlong()) - { - /* - The following can't be recoded with || as convert_constant_item - changes the argument - */ - if (convert_constant_item(thd, field,&args[1])) - cmp_type=INT_RESULT; // Works for all types. - if (convert_constant_item(thd, field,&args[2])) - cmp_type=INT_RESULT; // Works for all types. - } - } + agg_cmp_type(thd, &cmp_type, args, 3); + if (cmp_type == STRING_RESULT) + agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV); } @@ -1477,7 +1567,7 @@ void Item_func_case::fix_length_and_dec() for (nagg= 0; nagg < ncases/2 ; nagg++) agg[nagg+1]= args[nagg*2]; nagg++; - agg_cmp_type(&cmp_type, agg, nagg); + agg_cmp_type(current_thd, &cmp_type, agg, nagg); if ((cmp_type == STRING_RESULT) && agg_arg_charsets(cmp_collation, agg, nagg, MY_COLL_CMP_CONV)) return; @@ -1958,7 +2048,7 @@ void Item_func_in::fix_length_and_dec() uint const_itm= 1; THD *thd= current_thd; - agg_cmp_type(&cmp_type, args, arg_count); + agg_cmp_type(thd, &cmp_type, args, arg_count); if (cmp_type == STRING_RESULT && agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV)) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index ade09113c63..08d9de6ffd6 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -43,8 +43,11 @@ public: int set_compare_func(Item_bool_func2 *owner, Item_result type); inline int set_compare_func(Item_bool_func2 *owner_arg) { - return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(), - (*b)->result_type())); + Item_result ar= (*a)->result_as_longlong() && (*b)->const_item() ? + INT_RESULT : (*a)->result_type(); + Item_result br= (*b)->result_as_longlong() && (*a)->const_item() ? + INT_RESULT : (*b)->result_type(); + return set_compare_func(owner_arg, item_cmp_type(ar, br)); } inline int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2, @@ -57,8 +60,11 @@ public: inline int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2) { - return set_cmp_func(owner_arg, a1, a2, item_cmp_type((*a1)->result_type(), - (*a2)->result_type())); + Item_result ar= (*a1)->result_as_longlong() && (*a2)->const_item() ? + INT_RESULT : (*a1)->result_type(); + Item_result br= (*a2)->result_as_longlong() && (*a1)->const_item() ? + INT_RESULT : (*a2)->result_type(); + return set_cmp_func(owner_arg, a1, a2, item_cmp_type(ar, br)); } inline int compare() { return (this->*func)(); } diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 5fdbd968df1..f7b4c9dd630 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2306,6 +2306,20 @@ String *Item_datetime_typecast::val_str(String *str) } +longlong Item_datetime_typecast::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + if (get_arg0_date(<ime,1)) + { + null_value= 1; + return 0; + } + + return TIME_to_ulonglong_datetime(<ime); +} + + bool Item_time_typecast::get_time(TIME *ltime) { bool res= get_arg0_time(ltime); @@ -2320,6 +2334,17 @@ bool Item_time_typecast::get_time(TIME *ltime) } +longlong Item_time_typecast::val_int() +{ + TIME ltime; + if (get_time(<ime)) + { + null_value= 1; + return 0; + } + return ltime.hour * 10000L + ltime.minute * 100 + ltime.second; +} + String *Item_time_typecast::val_str(String *str) { DBUG_ASSERT(fixed == 1); @@ -2359,6 +2384,14 @@ String *Item_date_typecast::val_str(String *str) return 0; } +longlong Item_date_typecast::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + if (args[0]->get_date(<ime, TIME_FUZZY_DATE)) + return 0; + return (longlong) (ltime.year * 10000L + ltime.month * 100 + ltime.day); +} /* MAKEDATE(a,b) is a date function that creates a date value @@ -2395,6 +2428,33 @@ err: } +longlong Item_func_makedate::val_int() +{ + DBUG_ASSERT(fixed == 1); + TIME l_time; + long daynr= (long) args[1]->val_int(); + long yearnr= (long) args[0]->val_int(); + long days; + + if (args[0]->null_value || args[1]->null_value || + yearnr < 0 || daynr <= 0) + goto err; + + days= calc_daynr(yearnr,1,1) + daynr - 1; + /* Day number from year 0 to 9999-12-31 */ + if (days >= 0 && days < MAX_DAY_NUMBER) + { + null_value=0; + get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day); + return (longlong) (l_time.year * 10000L + l_time.month * 100 + l_time.day); + } + +err: + null_value= 1; + return 0; +} + + void Item_func_add_time::fix_length_and_dec() { enum_field_types arg0_field_type; diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 163b1591e52..95f1dfa75d1 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -339,6 +339,7 @@ public: { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; @@ -354,6 +355,7 @@ public: { return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; @@ -383,6 +385,7 @@ public: TIME representation using UTC-SYSTEM or per-thread time zone. */ virtual void store_now_in_TIME(TIME *now_time)=0; + bool result_as_longlong() { return TRUE; } }; @@ -588,6 +591,7 @@ public: { return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } }; /* @@ -715,6 +719,8 @@ public: max_length= 10; maybe_null= 1; } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -731,6 +737,8 @@ public: { return (new Field_time(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; @@ -746,6 +754,8 @@ public: { return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; class Item_func_makedate :public Item_str_func @@ -764,6 +774,8 @@ public: { return (new Field_date(maybe_null, name, t_arg, &my_charset_bin)); } + bool result_as_longlong() { return TRUE; } + longlong val_int(); }; |