diff options
Diffstat (limited to 'sql')
-rw-r--r-- | sql/item.cc | 23 | ||||
-rw-r--r-- | sql/item.h | 3 | ||||
-rw-r--r-- | sql/item_cmpfunc.cc | 318 | ||||
-rw-r--r-- | sql/item_cmpfunc.h | 27 | ||||
-rw-r--r-- | sql/sql_select.cc | 18 |
5 files changed, 366 insertions, 23 deletions
diff --git a/sql/item.cc b/sql/item.cc index ed5bd67d096..0990e2c73e9 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4158,6 +4158,21 @@ enum_field_types Item::field_type() const } +bool Item::is_datetime() +{ + switch (field_type()) + { + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_DATETIME: + case MYSQL_TYPE_TIMESTAMP: + return TRUE; + default: + break; + } + return FALSE; +} + + /* Create a field to hold a string value from an item @@ -6118,6 +6133,14 @@ void Item_cache_int::store(Item *item) } +void Item_cache_int::store(Item *item, longlong val_arg) +{ + value= val_arg; + null_value= item->null_value; + unsigned_flag= item->unsigned_flag; +} + + String *Item_cache_int::val_str(String *str) { DBUG_ASSERT(fixed == 1); diff --git a/sql/item.h b/sql/item.h index 094c45642da..43afbd30d47 100644 --- a/sql/item.h +++ b/sql/item.h @@ -847,6 +847,7 @@ public: representation is more precise than the string one). */ virtual bool result_as_longlong() { return FALSE; } + bool is_datetime(); }; @@ -2424,11 +2425,13 @@ public: Item_cache_int(): Item_cache(), value(0) {} void store(Item *item); + void store(Item *item, longlong val_arg); double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; } longlong val_int() { DBUG_ASSERT(fixed == 1); return value; } String* val_str(String *str); my_decimal *val_decimal(my_decimal *); enum Item_result result_type() const { return INT_RESULT; } + bool result_as_longlong() { return TRUE; } }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 4e4868db80c..b52a320abbf 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -380,7 +380,9 @@ void Item_bool_func2::fix_length_and_dec() if (arg_real_item->type() == FIELD_ITEM) { Field *field=((Item_field*) arg_real_item)->field; - if (field->can_be_compared_as_longlong()) + if (field->can_be_compared_as_longlong() && + !(arg_real_item->is_datetime() && + args[1]->result_type() == STRING_RESULT)) { if (convert_constant_item(thd, field,&args[1])) { @@ -395,7 +397,9 @@ void Item_bool_func2::fix_length_and_dec() if (arg_real_item->type() == FIELD_ITEM) { Field *field=((Item_field*) arg_real_item)->field; - if (field->can_be_compared_as_longlong()) + if (field->can_be_compared_as_longlong() && + !(arg_real_item->is_datetime() && + args[0]->result_type() == STRING_RESULT)) { if (convert_constant_item(thd, field,&args[0])) { @@ -514,6 +518,316 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type) } +/* + Convert date provided in a string to the int representation. + + SYNOPSIS + get_date_from_str() + thd Thread handle + str a string to convert + warn_type type of the timestamp for issuing the warning + warn_name field name for issuing the warning + error_arg [out] TRUE if string isn't a DATETIME or clipping occur + + DESCRIPTION + Convert date provided in the string str to the int representation. + if the string contains wrong date or doesn't contain it at all + then the warning is issued and TRUE returned in the error_arg argument. + The warn_type and the warn_name arguments are used as the name and the + type of the field when issuing the warning. + + RETURN + converted value. +*/ + +static ulonglong +get_date_from_str(THD *thd, String *str, timestamp_type warn_type, + char *warn_name, bool *error_arg) +{ + ulonglong value; + int error; + MYSQL_TIME l_time; + enum_mysql_timestamp_type ret; + *error_arg= TRUE; + + ret= str_to_datetime(str->ptr(), str->length(), &l_time, + (TIME_FUZZY_DATE | MODE_INVALID_DATES | + (thd->variables.sql_mode & + (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))), + &error); + if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE)) + { + value= TIME_to_ulonglong_datetime(&l_time); + *error_arg= FALSE; + } + + if (error || *error_arg) + { + make_truncated_value_warning(thd, str->ptr(), str->length(), warn_type, + warn_name); + *error_arg= TRUE; + } + return value; +} + + +/* + Check whether compare_datetime() can be used to compare items. + + SYNOPSIS + Arg_comparator::can_compare_as_dates() + a, b [in] items to be compared + const_value [out] converted value of the string constant, if any + + DESCRIPTION + Check several cases when the DATE/DATETIME comparator should be used. + The following cases are checked: + 1. Both a and b is a DATE/DATETIME field/function returning string or + int result. + 2. Only a or b is a DATE/DATETIME field/function returning string or + int result and the other item (b or a) is an item with string result. + If the second item is a constant one then it's checked to be + convertible to the DATE/DATETIME type. If the constant can't be + converted to a DATE/DATETIME then the compare_datetime() comparator + isn't used and the warning about wrong DATE/DATETIME value is issued. + In all other cases (date-[int|real|decimal]/[int|real|decimal]-date) + the comparison is handled by other comparators. + If the datetime comparator can be used and one the operands of the + comparison is a string constant that was successfully converted to a + DATE/DATETIME type then the result of the conversion is returned in the + const_value if it is provided. If there is no constant or + compare_datetime() isn't applicable then the *const_value remains + unchanged. + + RETURN + the found type of date comparison +*/ + +enum Arg_comparator::enum_date_cmp_type +Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value) +{ + enum enum_date_cmp_type cmp_type= CMP_DATE_DFLT; + Item *str_arg= 0, *date_arg= 0; + + if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM) + return CMP_DATE_DFLT; + + if (a->is_datetime()) + { + if (b->is_datetime()) + cmp_type= CMP_DATE_WITH_DATE; + else if (b->result_type() == STRING_RESULT) + { + cmp_type= CMP_DATE_WITH_STR; + date_arg= a; + str_arg= b; + } + } + else if (b->is_datetime() && a->result_type() == STRING_RESULT) + { + cmp_type= CMP_STR_WITH_DATE; + date_arg= b; + str_arg= a; + } + + if (cmp_type != CMP_DATE_DFLT) + { + if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item()) + { + THD *thd= current_thd; + ulonglong value; + bool error; + String tmp, *str_val= 0; + timestamp_type t_type= (date_arg->field_type() == MYSQL_TYPE_DATE ? + MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME); + + str_val= str_arg->val_str(&tmp); + if (str_arg->null_value) + return CMP_DATE_DFLT; + value= get_date_from_str(thd, str_val, t_type, date_arg->name, &error); + if (error) + return CMP_DATE_DFLT; + if (const_value) + *const_value= value; + } + } + return cmp_type; +} + + +int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg, + Item **a1, Item **a2, + Item_result type) +{ + enum enum_date_cmp_type cmp_type; + ulonglong const_value; + a= a1; + b= a2; + + if ((cmp_type= can_compare_as_dates(*a, *b, &const_value))) + { + thd= current_thd; + owner= owner_arg; + a_type= (*a)->field_type(); + b_type= (*b)->field_type(); + a_cache= 0; + b_cache= 0; + + if (cmp_type != CMP_DATE_WITH_DATE && + ((*b)->const_item() || (*a)->const_item())) + { + Item_cache_int *cache= new Item_cache_int(); + /* Mark the cache as non-const to prevent re-caching. */ + cache->set_used_tables(1); + if (!(*a)->is_datetime()) + { + cache->store((*a), const_value); + a_cache= cache; + a= (Item **)&a_cache; + } + else + { + cache->store((*b), const_value); + b_cache= cache; + b= (Item **)&b_cache; + } + } + is_nulls_eq= owner->functype() == Item_func::EQUAL_FUNC; + func= &Arg_comparator::compare_datetime; + return 0; + } + return set_compare_func(owner_arg, type); +} + + +/* + Retrieves correct DATETIME value from given item. + + SYNOPSIS + get_datetime_value() + thd thread handle + item_arg [in/out] item to retrieve DATETIME value from + cache_arg [in/out] pointer to place to store the caching item to + warn_item [in] item for issuing the conversion warning + is_null [out] TRUE <=> the item_arg is null + + DESCRIPTION + Retrieves the correct DATETIME value from given item for comparison by the + compare_datetime() function. + If item's result can be compared as longlong then its int value is used + and its string value is used otherwise. Strings are always parsed and + converted to int values by the get_date_from_str() function. + This allows us to compare correctly string dates with missed insignificant + zeros. If an item is a constant one then its value is cached and it isn't + get parsed again. An Item_cache_int object is used for caching values. It + seamlessly substitutes the original item. The cache item is marked as + non-constant to prevent re-caching it again. In order to compare + correctly DATE and DATETIME items the result of the former are treated as + a DATETIME with zero time (00:00:00). + + RETURN + obtained value +*/ + +static ulonglong +get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, + Item *warn_item, bool *is_null) +{ + ulonglong value; + String buf, *str= 0; + Item *item= **item_arg; + + if (item->result_as_longlong()) + { + value= item->val_int(); + *is_null= item->null_value; + if (item->field_type() == MYSQL_TYPE_DATE) + value*= 1000000L; + } + else + { + str= item->val_str(&buf); + *is_null= item->null_value; + } + if (*is_null) + return -1; + /* + Convert strings to the integer DATE/DATETIME representation. + Even if both dates provided in strings we can't compare them directly as + strings as there is no warranty that they are correct and do not miss + some insignificant zeros. + */ + if (str) + { + bool error; + enum_field_types f_type= warn_item->field_type(); + timestamp_type t_type= f_type == + MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME; + value= get_date_from_str(thd, str, t_type, warn_item->name, &error); + } + if (item->const_item()) + { + Item_cache_int *cache= new Item_cache_int(); + /* Mark the cache as non-const to prevent re-caching. */ + cache->set_used_tables(1); + cache->store(item, value); + *cache_arg= cache; + *item_arg= cache_arg; + } + return value; +} + +/* + Compare items values as dates. + + SYNOPSIS + Arg_comparator::compare_datetime() + + DESCRIPTION + Compare items values as DATE/DATETIME for both EQUAL_FUNC and from other + comparison functions. The correct DATETIME values are obtained + with help of the get_datetime_value() function. + + RETURN + If is_nulls_eq is TRUE: + 1 if items are equal or both are null + 0 otherwise + If is_nulls_eq is FALSE: + -1 a < b or one of items is null + 0 a == b + 1 a > b +*/ + +int Arg_comparator::compare_datetime() +{ + bool is_null= FALSE; + ulonglong a_value, b_value; + + /* Get DATE/DATETIME value of the 'a' item. */ + a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null); + if (!is_nulls_eq && is_null) + { + owner->null_value= 1; + return -1; + } + + /* Get DATE/DATETIME value of the 'b' item. */ + b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null); + if (is_null) + { + owner->null_value= is_nulls_eq ? 0 : 1; + return is_nulls_eq ? 1 : -1; + } + + owner->null_value= 0; + + /* Compare values. */ + if (is_nulls_eq) + return (a_value == b_value); + return a_value < b_value ? -1 : (a_value > b_value ? 1 : 0); +} + + int Arg_comparator::compare_string() { String *res1,*res2; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 11b8d34ad65..6318c7444de 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -35,12 +35,19 @@ class Arg_comparator: public Sql_alloc Item_bool_func2 *owner; Arg_comparator *comparators; // used only for compare_row() double precision; - + /* Fields used in DATE/DATETIME comparison. */ + THD *thd; + enum_field_types a_type, b_type; // Types of a and b items + Item *a_cache, *b_cache; // Cached values of a and b items + bool is_nulls_eq; // TRUE <=> compare for the EQUAL_FUNC + enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE, + CMP_DATE_WITH_STR, CMP_STR_WITH_DATE }; public: DTCollation cmp_collation; - Arg_comparator() {}; - Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {}; + Arg_comparator(): thd(0), a_cache(0), b_cache(0) {}; + Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0), + a_cache(0), b_cache(0) {}; int set_compare_func(Item_bool_func2 *owner, Item_result type); inline int set_compare_func(Item_bool_func2 *owner_arg) @@ -48,14 +55,10 @@ public: return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(), (*b)->result_type())); } - inline int set_cmp_func(Item_bool_func2 *owner_arg, + int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2, - Item_result type) - { - a= a1; - b= a2; - return set_compare_func(owner_arg, type); - } + Item_result type); + inline int set_cmp_func(Item_bool_func2 *owner_arg, Item **a1, Item **a2) { @@ -83,6 +86,10 @@ public: int compare_e_row(); // compare args[0] & args[1] int compare_real_fixed(); int compare_e_real_fixed(); + int compare_datetime(); // compare args[0] & args[1] as DATETIMEs + + static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b, + ulonglong *const_val_arg); static arg_cmp_func comparator_matrix [5][2]; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9b27daabc0e..b4d9e8b007e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8667,17 +8667,13 @@ static bool test_if_equality_guarantees_uniqueness(Item *l, Item *r) { return r->const_item() && - /* elements must be of the same result type */ - (r->result_type() == l->result_type() || - /* or dates compared to longs */ - (((l->type() == Item::FIELD_ITEM && - ((Item_field *)l)->field->can_be_compared_as_longlong()) || - (l->type() == Item::FUNC_ITEM && - ((Item_func *)l)->result_as_longlong())) && - r->result_type() == INT_RESULT)) - /* and must have the same collation if compared as strings */ - && (l->result_type() != STRING_RESULT || - l->collation.collation == r->collation.collation); + /* elements must be compared as dates */ + (Arg_comparator::can_compare_as_dates(l, r, 0) || + /* or of the same result type */ + (r->result_type() == l->result_type() && + /* and must have the same collation if compared as strings */ + (l->result_type() != STRING_RESULT || + l->collation.collation == r->collation.collation))); } /* |