bk commit into 5.0 tree (holyfoot:1.2233) 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-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(&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 */

--- 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(&ltime, TIME_FUZZY_DATE))
+          return field->store_time(&ltime, MYSQL_TIMESTAMP_DATETIME);
+      case MYSQL_TYPE_TIME:
+        field->set_notnull();
+        if (!get_time(&ltime))
+          return field->store_time(&ltime, 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 <time.h>
 
 #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));