From ac3ef6c75925c7e543c8ee1cbd5d0cd78a004d78 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 10 Nov 2007 13:33:42 +0100 Subject: Bug#31800: Date comparison fails with timezone and slashes for greater than comparison BETWEEN was more lenient with regard to what it accepted as a DATE/DATETIME in comparisons than greater-than and less-than were. ChangeSet makes < > comparisons similarly robust with regard to trailing garbage (" GMT-1") and "missing" leading zeros. Now all three comparators behave similarly in that they throw a warning for "junk" at the end of the data, but then proceed anyway if possible. Before < > fell back on a string- (rather than date-) comparison when a warning-condition was raised in the string-to-date conversion. Now the fallback only happens on actual errors, while warning- conditions still result in a warning being to delivered to the client. mysql-test/r/select.result: Show that we compare DATE/DATETIME-like strings as date(time)s now, rather than as bin-strings. Adjust older result as "2005-09-3a" is now correctly seen as "2005-09-3" + trailing garbage, rather than as "2005-09-30". mysql-test/t/select.test: Show that we compare DATE/DATETIME-like strings as date(time)s now, rather than as bin-strings. sql-common/my_time.c: correct/clarify date-related comments, particulary for check_date(). doxygenize comment while at it. sql/item_cmpfunc.cc: get_date_from_str() no longer signals an error when all we had was a warning-condition -- and one we already gave the user a warning for at that. Preamble doxygenized. --- sql/item_cmpfunc.cc | 67 ++++++++++++++++++++++++++++++++--------------------- 1 file changed, 40 insertions(+), 27 deletions(-) (limited to 'sql/item_cmpfunc.cc') diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index a9ad5ad675d..e67ad30f9c5 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -552,26 +552,26 @@ 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. +/** + @brief Convert date provided in a string to the int representation. + + @param[in] thd thread handle + @param[in] str a string to convert + @param[in] warn_type type of the timestamp for issuing the warning + @param[in] warn_name field name for issuing the warning + @param[out] error_arg could not extract a DATE or DATETIME + + @details 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 a warning is issued. The warn_type and + the warn_name arguments are used as the name and the type of the + field when issuing the warning. If any input was discarded + (trailing or non-timestampy characters), was_cut will be non-zero. + was_type will return the type str_to_datetime() could correctly + extract. + + @return + converted value. 0 on error and on zero-dates -- check 'failure' */ static ulonglong @@ -582,26 +582,33 @@ get_date_from_str(THD *thd, String *str, timestamp_type warn_type, 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)) + + if (ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE) { - value= TIME_to_ulonglong_datetime(&l_time); + /* + Do not return yet, we may still want to throw a "trailing garbage" + warning. + */ *error_arg= FALSE; + value= TIME_to_ulonglong_datetime(&l_time); } - - if (error || *error_arg) + else { + *error_arg= TRUE; + error= 1; /* force warning */ + } + + if (error > 0) make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, str->ptr(), str->length(), warn_type, warn_name); - *error_arg= TRUE; - } + return value; } @@ -902,6 +909,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, 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 str did not contain a valid date according to the current + SQL_MODE, get_date_from_str() has already thrown a warning, + and we don't want to throw NULL on invalid date (see 5.2.6 + "SQL modes" in the manual), so we're done here. + */ } /* Do not cache GET_USER_VAR() function as its const_item() may return TRUE -- cgit v1.2.1