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-10 14:46:06+05:00, holyfoot@stripped +5 -0
  Bug #19491 (CASTing a DATETIME to DECIMAL truncates microsecond)
  
  Problem was that we tried to get DECIMAL from string DATETIME representation
  using string2my_decimal conversion.
  I added the code to produce the DECIMAL from the TIME struct directly
  for DATETIME family items

  mysql-test/r/type_datetime.result@stripped, 2006-08-10 14:46:01+05:00, holyfoot@stripped +9
-0
    result fixed

  mysql-test/t/type_datetime.test@stripped, 2006-08-10 14:46:01+05:00, holyfoot@stripped +8
-0
    testcase added

  sql/item.cc@stripped, 2006-08-10 14:46:01+05:00, holyfoot@stripped +24 -0
    val_decimal_from_date implemented

  sql/item.h@stripped, 2006-08-10 14:46:02+05:00, holyfoot@stripped +1 -0
    function added to produce decimal value from date/time

  sql/item_strfunc.cc@stripped, 2006-08-10 14:46:02+05:00, holyfoot@stripped +8 -6
    now we produce decimal depending on Item_*::field_type

# 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.230/sql/item.cc	2006-08-10 14:46:13 +05:00
+++ 1.231/sql/item.cc	2006-08-10 14:46:13 +05:00
@@ -272,6 +272,30 @@
 }
 
 
+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;
+  }
+  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, decimal_value))
+    return decimal_value;
+  if (ltime.second_part)
+  {
+    decimal_value->buf[2]= ltime.second_part * 100000;
+    decimal_value->frac= 4;
+  }
+  return 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-10 14:46:13 +05:00
+++ 1.206/sql/item.h	2006-08-10 14:46:13 +05:00
@@ -605,6 +605,7 @@
   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);
   longlong val_int_from_decimal();
   double val_real_from_decimal();
 

--- 1.280/sql/item_strfunc.cc	2006-08-10 14:46:13 +05:00
+++ 1.281/sql/item_strfunc.cc	2006-08-10 14:46:13 +05:00
@@ -85,12 +85,14 @@
   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;
+  enum_field_types ftype= field_type();
+
+  if ((ftype == MYSQL_TYPE_DATE) ||
+      (ftype == MYSQL_TYPE_TIME) ||
+      (ftype == MYSQL_TYPE_DATETIME))
+    return  val_decimal_from_date(decimal_value);
+
+  return val_decimal_from_string(decimal_value);
 }
 
 

--- 1.31/mysql-test/r/type_datetime.result	2006-08-10 14:46:13 +05:00
+++ 1.32/mysql-test/r/type_datetime.result	2006-08-10 14:46:13 +05:00
@@ -168,3 +168,12 @@
 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.001400

--- 1.18/mysql-test/t/type_datetime.test	2006-08-10 14:46:13 +05:00
+++ 1.19/mysql-test/t/type_datetime.test	2006-08-10 14:46:13 +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));