From: Date: August 14 2006 12:54pm Subject: bk commit into 5.0 tree (holyfoot:1.2233) BUG#19491 List-Archive: http://lists.mysql.com/commits/10352 X-Bug: 19491 Message-Id: <20060814105403.48215A08456@deer.myoffice.izhnet.ru> Below is the list of changes that have just been committed into a local 5.0 repository of hf. When hf 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@stripped, 2006-08-14 15:53:55+05:00, holyfoot@stripped +12 -0 bug #19491 (CAST DATETIME as DECIMAL fails) the problem was that string DATATIME representations is converted into DECIMAL with the weird result. I decided just to implement direct TIME->DECIMAL conversion mysql-test/r/type_datetime.result@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +12 -0 result fixed mysql-test/r/type_newdecimal.result@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +8 -0 result fixed mysql-test/t/type_datetime.test@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +8 -0 testcase mysql-test/t/type_newdecimal.test@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +6 -0 testcase sql/field.cc@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +7 -0 Field_new_decimal::store_time implemented sql/field.h@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +1 -0 Field_new_decimal::store_time declared sql/item.cc@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +28 -0 decimal_from_datetime methods implemented sql/item.h@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +2 -0 decimal_from_datetime methods declared sql/item_strfunc.cc@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +35 -8 Now we handle datetime things separately looking at their field_type() sql/item_strfunc.h@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +1 -0 Item_strfunc::save_in_field declaration sql/my_decimal.cc@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +18 -0 date2my_decimal implemented sql/my_decimal.h@stripped, 2006-08-14 15:53:51+05:00, holyfoot@stripped +4 -1 date2my_decimal declared # 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: holyfoot # Host: deer.(none) # Root: /home/hf/work/mysql-5.0.19491 --- 1.320/sql/field.cc 2006-08-14 15:54:03 +05:00 +++ 1.321/sql/field.cc 2006-08-14 15:54:03 +05:00 @@ -2407,6 +2407,13 @@ } +int Field_new_decimal::store_time(TIME *ltime, timestamp_type t_type) +{ + my_decimal decimal_value; + return store_value(date2my_decimal(ltime, &decimal_value)); +} + + int Field_new_decimal::store_decimal(const my_decimal *decimal_value) { return store_value(decimal_value); --- 1.186/sql/field.h 2006-08-14 15:54:03 +05:00 +++ 1.187/sql/field.h 2006-08-14 15:54:03 +05:00 @@ -484,6 +484,7 @@ int store(const char *to, uint length, CHARSET_INFO *charset); int store(double nr); int store(longlong nr, bool unsigned_val); + int store_time(TIME *ltime, timestamp_type t_type); int store_decimal(const my_decimal *); double val_real(void); longlong val_int(void); --- 1.230/sql/item.cc 2006-08-14 15:54:03 +05:00 +++ 1.231/sql/item.cc 2006-08-14 15:54:03 +05:00 @@ -272,6 +272,34 @@ } +my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + longlong date; + if (get_date(<ime, TIME_FUZZY_DATE)) + { + my_decimal_set_zero(decimal_value); + return 0; + } + return date2my_decimal(<ime, decimal_value); +} + + +my_decimal *Item::val_decimal_from_time(my_decimal *decimal_value) +{ + DBUG_ASSERT(fixed == 1); + TIME ltime; + longlong date; + if (get_time(<ime)) + { + my_decimal_set_zero(decimal_value); + return 0; + } + return date2my_decimal(<ime, decimal_value); +} + + double Item::val_real_from_decimal() { /* Note that fix_fields may not be called for Item_avg_field items */ --- 1.205/sql/item.h 2006-08-14 15:54:03 +05:00 +++ 1.206/sql/item.h 2006-08-14 15:54:03 +05:00 @@ -605,6 +605,8 @@ my_decimal *val_decimal_from_real(my_decimal *decimal_value); my_decimal *val_decimal_from_int(my_decimal *decimal_value); my_decimal *val_decimal_from_string(my_decimal *decimal_value); + my_decimal *val_decimal_from_date(my_decimal *decimal_value); + my_decimal *val_decimal_from_time(my_decimal *decimal_value); longlong val_int_from_decimal(); double val_real_from_decimal(); --- 1.280/sql/item_strfunc.cc 2006-08-14 15:54:03 +05:00 +++ 1.281/sql/item_strfunc.cc 2006-08-14 15:54:03 +05:00 @@ -83,14 +83,18 @@ my_decimal *Item_str_func::val_decimal(my_decimal *decimal_value) { DBUG_ASSERT(fixed == 1); - char buff[64]; - String *res, tmp(buff,sizeof(buff), &my_charset_bin); - res= val_str(&tmp); - if (!res) - return 0; - (void)str2my_decimal(E_DEC_FATAL_ERROR, (char*) res->ptr(), - res->length(), res->charset(), decimal_value); - return decimal_value; + switch (field_type()) + { + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_DATETIME: + return val_decimal_from_date(decimal_value); + + case MYSQL_TYPE_TIME: + return val_decimal_from_time(decimal_value); + default:; + } + + return val_decimal_from_string(decimal_value); } @@ -117,6 +121,29 @@ my_strntoll(res->charset(), res->ptr(), res->length(), 10, NULL, &err) : (longlong) 0); +} + + +int Item_str_func::save_in_field(Field *field, bool no_conversions) +{ + TIME ltime; + if (!null_value) + { + switch (field_type()) + { + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_DATETIME: + field->set_notnull(); + if (!get_date(<ime, TIME_FUZZY_DATE)) + return field->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); + case MYSQL_TYPE_TIME: + field->set_notnull(); + if (!get_time(<ime)) + return field->store_time(<ime, MYSQL_TIMESTAMP_TIME); + default:; + } + } + return Item_func::save_in_field(field, no_conversions); } --- 1.114/sql/item_strfunc.h 2006-08-14 15:54:03 +05:00 +++ 1.115/sql/item_strfunc.h 2006-08-14 15:54:03 +05:00 @@ -34,6 +34,7 @@ longlong val_int(); double val_real(); my_decimal *val_decimal(my_decimal *); + int save_in_field(Field *field, bool no_conversions); enum Item_result result_type () const { return STRING_RESULT; } void left_right_max_length(); String *check_well_formed_result(String *str); --- 1.41/mysql-test/r/type_newdecimal.result 2006-08-14 15:54:03 +05:00 +++ 1.42/mysql-test/r/type_newdecimal.result 2006-08-14 15:54:03 +05:00 @@ -1412,3 +1412,11 @@ 1.0 2 2.0 2 drop table t1; +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +f1 +20101112000000.000014 +101112.000000 +drop table t1; --- 1.38/mysql-test/t/type_newdecimal.test 2006-08-14 15:54:03 +05:00 +++ 1.39/mysql-test/t/type_newdecimal.test 2006-08-14 15:54:03 +05:00 @@ -1108,3 +1108,9 @@ select i, count(distinct j) from t1 group by i; select i+0.0 as i2, count(distinct j) from t1 group by i2; drop table t1; + +create table t1(f1 decimal(20,6)); +insert into t1 values (CAST('10:11:12' AS date) + interval 14 microsecond); +insert into t1 values (CAST('10:11:12' AS time)); +select * from t1; +drop table t1; --- 1.11/sql/my_decimal.cc 2006-08-14 15:54:03 +05:00 +++ 1.12/sql/my_decimal.cc 2006-08-14 15:54:03 +05:00 @@ -15,6 +15,7 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ #include "mysql_priv.h" +#include #ifndef MYSQL_CLIENT /* @@ -187,6 +188,23 @@ } check_result_and_overflow(mask, err, decimal_value); return err; +} + + +my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec) +{ + longlong date; + date = (ltime->year*100L + ltime->month)*100L + ltime->day; + if (ltime->time_type > MYSQL_TIMESTAMP_DATE) + date= ((date*100L + ltime->hour)*100L+ ltime->minute)*100L + ltime->second; + if (int2my_decimal(E_DEC_FATAL_ERROR, date, FALSE, dec)) + return dec; + if (ltime->second_part) + { + dec->buf[(dec->intg-1) / 9 + 1]= ltime->second_part * 1000; + dec->frac= 6; + } + return dec; } --- 1.10/sql/my_decimal.h 2006-08-14 15:54:03 +05:00 +++ 1.11/sql/my_decimal.h 2006-08-14 15:54:03 +05:00 @@ -295,7 +295,10 @@ { return str2my_decimal(mask, str->ptr(), str->length(), str->charset(), d); } -#endif + + +my_decimal *date2my_decimal(TIME *ltime, my_decimal *dec); +#endif /*defined(MYSQL_SERVER) || defined(EMBEDDED_LIBRARY) */ inline int double2my_decimal(uint mask, double val, my_decimal *d) --- 1.31/mysql-test/r/type_datetime.result 2006-08-14 15:54:03 +05:00 +++ 1.32/mysql-test/r/type_datetime.result 2006-08-14 15:54:03 +05:00 @@ -168,3 +168,15 @@ 0000-00-00 00:00:00 0000-00-00 00:00:00 drop table t1; +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) +20060810.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) +20060810101112.000000 +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) +20060810101112.000014 +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); +CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) +101112.098700 --- 1.18/mysql-test/t/type_datetime.test 2006-08-14 15:54:03 +05:00 +++ 1.19/mysql-test/t/type_datetime.test 2006-08-14 15:54:03 +05:00 @@ -114,3 +114,11 @@ drop table t1; # End of 4.1 tests + +# +# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result +# +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));