bk commit into 5.0 tree (holyfoot:1.2255) BUG#19491

[holyfoot]

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-10-25 20:14:39+05:00, holyfoot@stripped +12 -0
  bug #19491 (CAST do DATETIME wrong result)

  mysql-test/r/type_datetime.result@stripped, 2006-10-25 20:14:32+05:00, holyfoot@stripped
+12 -0
    result fixed

  mysql-test/r/type_newdecimal.result@stripped, 2006-10-25 20:14:33+05:00, holyfoot@stripped
+8 -0
    result fixed

  mysql-test/t/type_datetime.test@stripped, 2006-10-25 20:14:33+05:00, holyfoot@stripped +10
-0
    testcase

  mysql-test/t/type_newdecimal.test@stripped, 2006-10-25 20:14:33+05:00, holyfoot@stripped +8
-0
    testcase

  sql/field.cc@stripped, 2006-10-25 20:14:33+05:00, holyfoot@stripped +7 -0
    Field_new_decimal::store_time implemented

  sql/field.h@stripped, 2006-10-25 20:14:33+05:00, holyfoot@stripped +1 -0
    Field_new_decimal::store_time added

  sql/item.cc@stripped, 2006-10-25 20:14:34+05:00, holyfoot@stripped +47 -0
    auxiliary methods implemented to operate with datatimes

  sql/item.h@stripped, 2006-10-25 20:14:34+05:00, holyfoot@stripped +5 -0
    auxiliary methods declared in Item to operate with datatimes

  sql/item_timefunc.cc@stripped, 2006-10-25 20:14:34+05:00, holyfoot@stripped +0 -11
    Item_date::save_in_field old implementation removed

  sql/item_timefunc.h@stripped, 2006-10-25 20:14:34+05:00, holyfoot@stripped +113 -38
    my_decimal and save_in_field methods implemented for datetime items

  sql/my_decimal.cc@stripped, 2006-10-25 20:14:35+05:00, holyfoot@stripped +19 -0
    date2my_decimal implemented

  sql/my_decimal.h@stripped, 2006-10-25 20:14:35+05:00, holyfoot@stripped +6 -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.322/sql/field.cc	2006-10-25 20:14:48 +05:00
+++ 1.323/sql/field.cc	2006-10-25 20:14:48 +05:00
@@ -2413,6 +2413,13 @@ int Field_new_decimal::store_decimal(con
 }
 
 
+int Field_new_decimal::store_time(TIME *ltime, timestamp_type t_type)
+{
+    my_decimal decimal_value;
+    return store_value(date2my_decimal(ltime, &decimal_value));
+}
+
+
 double Field_new_decimal::val_real(void)
 {
   double dbl;

--- 1.188/sql/field.h	2006-10-25 20:14:48 +05:00
+++ 1.189/sql/field.h	2006-10-25 20:14:48 +05:00
@@ -489,6 +489,7 @@ public:
   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.232/sql/item.cc	2006-10-25 20:14:48 +05:00
+++ 1.233/sql/item.cc	2006-10-25 20:14:48 +05:00
@@ -272,6 +272,34 @@ my_decimal *Item::val_decimal_from_strin
 }
 
 
+my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value)
+{
+  DBUG_ASSERT(fixed == 1);
+  TIME ltime;
+  longlong date;
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+  {
+    my_decimal_set_zero(decimal_value);
+    return 0;
+  }
+  return date2my_decimal(&ltime, decimal_value);
+}
+
+
+my_decimal *Item::val_decimal_from_time(my_decimal *decimal_value)
+{
+  DBUG_ASSERT(fixed == 1);
+  TIME ltime;
+  longlong date;
+  if (get_time(&ltime))
+  {
+    my_decimal_set_zero(decimal_value);
+    return 0;
+  }
+  return date2my_decimal(&ltime, decimal_value);
+}
+
+
 double Item::val_real_from_decimal()
 {
   /* Note that fix_fields may not be called for Item_avg_field items */
@@ -293,6 +321,25 @@ longlong Item::val_int_from_decimal()
     return 0;
   my_decimal2int(E_DEC_FATAL_ERROR, dec_val, unsigned_flag, &result);
   return result;
+}
+
+int Item::save_time_in_field(Field *field)
+{
+  TIME ltime;
+  if (get_time(&ltime))
+    return set_field_to_null(field);
+  field->set_notnull();
+  return field->store_time(&ltime, MYSQL_TIMESTAMP_TIME);
+}
+
+
+int Item::save_date_in_field(Field *field)
+{
+  TIME ltime;
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+    return set_field_to_null(field);
+  field->set_notnull();
+  return field->store_time(&ltime, MYSQL_TIMESTAMP_DATETIME);
 }
 
 

--- 1.206/sql/item.h	2006-10-25 20:14:48 +05:00
+++ 1.207/sql/item.h	2006-10-25 20:14:48 +05:00
@@ -605,8 +605,13 @@ public:
   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();
+
+  int save_time_in_field(Field *field);
+  int save_date_in_field(Field *field);
 
   virtual Field *get_tmp_table_field() { return 0; }
   /* This is also used to create fields in CREATE ... SELECT: */

--- 1.124/sql/item_timefunc.cc	2006-10-25 20:14:48 +05:00
+++ 1.125/sql/item_timefunc.cc	2006-10-25 20:14:48 +05:00
@@ -1294,17 +1294,6 @@ String *Item_date::val_str(String *str)
 }
 
 
-int Item_date::save_in_field(Field *field, bool no_conversions)
-{
-  TIME ltime;
-  if (get_date(&ltime, TIME_FUZZY_DATE))
-    return set_field_to_null(field);
-  field->set_notnull();
-  field->store_time(&ltime, MYSQL_TIMESTAMP_DATE);
-  return 0;
-}
-
-
 longlong Item_date::val_int()
 {
   DBUG_ASSERT(fixed == 1);

--- 1.68/sql/item_timefunc.h	2006-10-25 20:14:48 +05:00
+++ 1.69/sql/item_timefunc.h	2006-10-25 20:14:48 +05:00
@@ -339,12 +339,20 @@ public:
     decimals=0;
     max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
   }
-  int save_in_field(Field *to, bool no_conversions);
   Field *tmp_table_field(TABLE *t_arg)
   {
     return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
   }  
   bool result_as_longlong() { return TRUE; }
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_date(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_date_in_field(field);
+  }
 };
 
 
@@ -361,21 +369,57 @@ public:
     return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
   }
   bool result_as_longlong() { return TRUE; }
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_date(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_date_in_field(field);
+  }
+};
+
+
+class Item_str_timefunc :public Item_str_func
+{
+public:
+  Item_str_timefunc() :Item_str_func() {}
+  Item_str_timefunc(Item *a) :Item_str_func(a) {}
+  Item_str_timefunc(Item *a,Item *b) :Item_str_func(a,b) {}
+  Item_str_timefunc(Item *a, Item *b, Item *c) :Item_str_func(a, b ,c) {}
+  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+  void fix_length_and_dec()
+  {
+    decimals=0;
+    max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+  Field *tmp_table_field(TABLE *t_arg)
+  {
+    return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+  }
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_time(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_time_in_field(field);
+  }
 };
 
 
 /* Abstract CURTIME function. Children should define what time zone is used */
 
-class Item_func_curtime :public Item_func
+class Item_func_curtime :public Item_str_timefunc
 {
   longlong value;
   char buff[9*2+32];
   uint buff_length;
 public:
-  Item_func_curtime() :Item_func() {}
-  Item_func_curtime(Item *a) :Item_func(a) {}
-  enum Item_result result_type () const { return STRING_RESULT; }
-  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+  Item_func_curtime() :Item_str_timefunc() {}
+  Item_func_curtime(Item *a) :Item_str_timefunc(a) {}
   double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
   longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
   String *val_str(String *str);
@@ -602,10 +646,10 @@ class Item_func_convert_tz :public Item_
 };
 
 
-class Item_func_sec_to_time :public Item_str_func
+class Item_func_sec_to_time :public Item_str_timefunc
 {
 public:
-  Item_func_sec_to_time(Item *item) :Item_str_func(item) {}
+  Item_func_sec_to_time(Item *item) :Item_str_timefunc(item) {}
   double val_real()
   {
     DBUG_ASSERT(fixed == 1);
@@ -615,17 +659,12 @@ public:
   String *val_str(String *);
   void fix_length_and_dec()
   { 
+    Item_str_timefunc::fix_length_and_dec();
     collation.set(&my_charset_bin);
     maybe_null=1;
     decimals= DATETIME_DEC;
-    max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
   }
-  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
   const char *func_name() const { return "sec_to_time"; }
-  Field *tmp_table_field(TABLE *t_arg)
-  {
-    return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
-  }
   bool result_as_longlong() { return TRUE; }
 };
 
@@ -759,6 +798,15 @@ public:
   }
   bool result_as_longlong() { return TRUE; }
   longlong val_int();
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_date(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_date_in_field(field);
+  }
 };
 
 
@@ -777,6 +825,15 @@ public:
   }
   bool result_as_longlong() { return TRUE; }
   longlong val_int();
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_time(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_time_in_field(field);
+  }
 };
 
 
@@ -794,12 +851,21 @@ public:
   }
   bool result_as_longlong() { return TRUE; }
   longlong val_int();
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_date(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_date_in_field(field);
+  }
 };
 
-class Item_func_makedate :public Item_str_func
+class Item_func_makedate :public Item_date_func
 {
 public:
-  Item_func_makedate(Item *a,Item *b) :Item_str_func(a,b) {}
+  Item_func_makedate(Item *a,Item *b) :Item_date_func(a,b) {}
   String *val_str(String *str);
   const char *func_name() const { return "makedate"; }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
@@ -812,8 +878,16 @@ public:
   {
     return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
   }
-  bool result_as_longlong() { return TRUE; }
   longlong val_int();
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    return  val_decimal_from_date(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    return save_date_in_field(field);
+  }
 };
 
 
@@ -845,45 +919,46 @@ public:
   }
   void print(String *str);
   const char *func_name() const { return "add_time"; }
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    DBUG_ASSERT(fixed == 1);
+    if (cached_field_type == MYSQL_TYPE_TIME)
+      return  val_decimal_from_time(decimal_value);
+    if (cached_field_type == MYSQL_TYPE_DATETIME)
+      return  val_decimal_from_date(decimal_value);
+    return Item_str_func::val_decimal(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    if (cached_field_type == MYSQL_TYPE_TIME)
+      return save_time_in_field(field);
+    if (cached_field_type == MYSQL_TYPE_DATETIME)
+      return save_date_in_field(field);
+    return Item_str_func::save_in_field(field, no_conversions);
+  }
 };
 
-class Item_func_timediff :public Item_str_func
+class Item_func_timediff :public Item_str_timefunc
 {
 public:
   Item_func_timediff(Item *a, Item *b)
-    :Item_str_func(a, b) {}
+    :Item_str_timefunc(a, b) {}
   String *val_str(String *str);
   const char *func_name() const { return "timediff"; }
-  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
   void fix_length_and_dec()
   {
-    decimals=0;
-    max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+    Item_str_timefunc::fix_length_and_dec();
     maybe_null= 1;
   }
-  Field *tmp_table_field(TABLE *t_arg)
-  {
-      return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
-  }
 };
 
-class Item_func_maketime :public Item_str_func
+class Item_func_maketime :public Item_str_timefunc
 {
 public:
   Item_func_maketime(Item *a, Item *b, Item *c)
-    :Item_str_func(a, b ,c) {}
+    :Item_str_timefunc(a, b ,c) {}
   String *val_str(String *str);
   const char *func_name() const { return "maketime"; }
-  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
-  void fix_length_and_dec()
-  {
-    decimals=0;
-    max_length=MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
-  }
-  Field *tmp_table_field(TABLE *t_arg)
-  {
-    return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
-  }
 };
 
 class Item_func_microsecond :public Item_int_func

--- 1.41/mysql-test/r/type_newdecimal.result	2006-10-25 20:14:48 +05:00
+++ 1.42/mysql-test/r/type_newdecimal.result	2006-10-25 20:14:48 +05:00
@@ -1412,3 +1412,11 @@ i2	count(distinct j)
 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
+20101112.000000
+drop table t1;

--- 1.38/mysql-test/t/type_newdecimal.test	2006-10-25 20:14:48 +05:00
+++ 1.39/mysql-test/t/type_newdecimal.test	2006-10-25 20:14:48 +05:00
@@ -1108,3 +1108,11 @@ insert into t1 values (1,1), (1,2), (2,3
 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-10-25 20:14:48 +05:00
+++ 1.12/sql/my_decimal.cc	2006-10-25 20:14:48 +05:00
@@ -15,6 +15,8 @@
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
 #include "mysql_priv.h"
+#include <time.h>
+
 
 #ifndef MYSQL_CLIENT
 /*
@@ -187,6 +189,23 @@ int str2my_decimal(uint mask, const char
   }
   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-10-25 20:14:48 +05:00
+++ 1.11/sql/my_decimal.h	2006-10-25 20:14:48 +05:00
@@ -295,7 +295,12 @@ int string2my_decimal(uint mask, const S
 {
   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.32/mysql-test/r/type_datetime.result	2006-10-25 20:14:48 +05:00
+++ 1.33/mysql-test/r/type_datetime.result	2006-10-25 20:14:48 +05:00
@@ -179,3 +179,15 @@ a
 2006-06-06 15:55:55
 DROP PREPARE s;
 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.19/mysql-test/t/type_datetime.test	2006-10-25 20:14:48 +05:00
+++ 1.20/mysql-test/t/type_datetime.test	2006-10-25 20:14:48 +05:00
@@ -125,3 +125,13 @@ PREPARE s FROM 'SELECT a FROM t1 WHERE a
 EXECUTE s;
 DROP PREPARE s;
 DROP TABLE t1;
+
+
+#
+# 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));
+