From: Date: May 15 2007 3:32pm
Subject: bk commit into 5.0 tree (evgen:1.2482) BUG#27216
List-Archive: http://lists.mysql.com/commits/26695
X-Bug: 27216
Message-Id: <20070515133257.559EB22CB9F@moonbone.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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, 2007-05-15 17:32:52+04:00, evgen@stripped +7 -0
  Bug#27216: coalesce() returns wrong result type for dates arguments.
  
  The result field type of the COALESCE() function was determined based on
  the aggregated result type of its arguments. As all DATE/DATETIME
  fields/functions have the STRING result type the result field type
  of the COALESCE() function was always STRING for DATE/DATETIME arguments.
  
  Now the COALESCE() function aggregates field types of its arguments rather
  than result types and returns the result of aggregation as its result field
  type.
  The cached_field_type member variable is added to the Item_func_coalesce class.
  The str_to_date() function's result field type now defaults to the
  MYSQL_TYPE_DATETIME.
  The agg_field_type() function is added. It aggregates field types with help
  of the Field::field_type_merge() function.
  The create_table_from_items() function now uses the 
  item->tmp_table_field_from_field_type() function to get the proper field
  when the item is a function with the STRING result type.

  mysql-test/r/date_formats.result@stripped, 2007-05-15 17:12:55+04:00, evgen@stripped +1 -1
    A test case result is corrected after fix for bug#27216.

  mysql-test/r/type_datetime.result@stripped, 2007-05-15 17:12:20+04:00, evgen@stripped +14 -0
    Added a test case for the bug#27216: coalesce() returns wrong result type for dates arguments.

  mysql-test/t/type_datetime.test@stripped, 2007-05-15 17:12:06+04:00, evgen@stripped +13 -0
    Added a test case for the bug#27216: coalesce() returns wrong result type for dates arguments.

  sql/item_cmpfunc.cc@stripped, 2007-05-15 17:31:25+04:00, evgen@stripped +30 -0
    Bug#27216: coalesce() returns wrong result type for dates arguments.
    Now the COALESCE() function aggregates field types of its arguments rather
    than result types and returns the result of aggregation as its result field
    type.

  sql/item_cmpfunc.h@stripped, 2007-05-15 17:30:56+04:00, evgen@stripped +2 -0
    Bug#27216: coalesce() returns wrong result type for dates arguments.
    The cached_field_type member variable is added to the Item_func_coalesce class.

  sql/item_timefunc.cc@stripped, 2007-05-15 17:30:28+04:00, evgen@stripped +1 -1
    Bug#27216: coalesce() returns wrong result type for dates arguments.
    The str_to_date() function's result field type now defaults to the
    MYSQL_TYPE_DATETIME.

  sql/sql_insert.cc@stripped, 2007-05-15 17:32:25+04:00, evgen@stripped +4 -1
    Bug#27216: coalesce() returns wrong result type for dates arguments.
    The create_table_from_items() function now uses the 
    item->tmp_table_field_from_field_type() function to get the proper field
    when the item is a function with the STRING result 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:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/27216-bug-5.0-opt-mysql

--- 1.250/sql/item_cmpfunc.cc	2007-05-07 22:22:16.000000000 +0400
+++ 1.251/sql/item_cmpfunc.cc	2007-05-15 17:31:25.000000000 +0400
@@ -147,6 +147,35 @@
 }
 
 
+/*
+  Aggregates field types from the array of items.
+
+  SYNOPSIS:
+    agg_field_type()
+    items        array of items to aggregate the type from
+    nitems       number of items in the array
+
+  DESCRIPTION
+    This function aggregates field types from the array of items.
+    Found type supposed to be used later as the result field type
+    of a multi-argument function.
+    Aggregation itself is performed by the Field::field_type_merge()
+    function.
+
+  RETURN VALUES
+    aggregated field type.
+*/
+
+enum_field_types agg_field_type(Item **items, uint nitems)
+{
+  uint i;
+  enum_field_types res= items[0]->field_type();
+  for (i= 1 ; i < nitems ; i++)
+    res= Field::field_type_merge(res, items[i]->field_type());
+  return res;
+}
+
+
 static void my_coll_agg_error(DTCollation &c1, DTCollation &c2,
                               const char *fname)
 {
@@ -2573,6 +2602,7 @@
 
 void Item_func_coalesce::fix_length_and_dec()
 {
+  cached_field_type= agg_field_type(args, arg_count);
   agg_result_type(&hybrid_type, args, arg_count);
   switch (hybrid_type) {
   case STRING_RESULT:

--- 1.152/sql/item_cmpfunc.h	2007-05-07 22:19:37.000000000 +0400
+++ 1.153/sql/item_cmpfunc.h	2007-05-15 17:30:56.000000000 +0400
@@ -637,6 +637,7 @@
 
 class Item_func_coalesce :public Item_func_numhybrid
 {
+  enum_field_types cached_field_type;
 protected:
   Item_func_coalesce(Item *a, Item *b) :Item_func_numhybrid(a, b) {}
 public:
@@ -650,6 +651,7 @@
   enum Item_result result_type () const { return hybrid_type; }
   const char *func_name() const { return "coalesce"; }
   table_map not_null_tables() const { return 0; }
+  enum_field_types field_type() const { return cached_field_type; };
 };
 
 

--- 1.142/sql/item_timefunc.cc	2007-04-27 00:21:37.000000000 +0400
+++ 1.143/sql/item_timefunc.cc	2007-05-15 17:30:28.000000000 +0400
@@ -3291,7 +3291,7 @@
   String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format;
   maybe_null= 1;
   decimals=0;
-  cached_field_type= MYSQL_TYPE_STRING;
+  cached_field_type= MYSQL_TYPE_DATETIME;
   max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
   cached_timestamp_type= MYSQL_TIMESTAMP_NONE;
   format= args[1]->val_str(&format_str);

--- 1.230/sql/sql_insert.cc	2007-04-12 13:46:07.000000000 +0400
+++ 1.231/sql/sql_insert.cc	2007-05-15 17:32:25.000000000 +0400
@@ -2855,7 +2855,10 @@
     create_field *cr_field;
     Field *field, *def_field;
     if (item->type() == Item::FUNC_ITEM)
-      field= item->tmp_table_field(&tmp_table);
+      if (item->result_type() != STRING_RESULT)
+        field= item->tmp_table_field(&tmp_table);
+      else
+        field= item->tmp_table_field_from_field_type(&tmp_table);
     else
       field= create_tmp_field(thd, &tmp_table, item, item->type(),
                               (Item ***) 0, &tmp_field, &def_field, 0, 0, 0, 0,

--- 1.33/mysql-test/r/date_formats.result	2007-02-08 11:53:17.000000000 +0300
+++ 1.34/mysql-test/r/date_formats.result	2007-05-15 17:12:55.000000000 +0400
@@ -467,7 +467,7 @@
 create table t2 select str_to_date(a,b) from t1;
 describe t2;
 Field	Type	Null	Key	Default	Extra
-str_to_date(a,b)	binary(29)	YES		NULL	
+str_to_date(a,b)	datetime	YES		NULL	
 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
 str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
 str_to_date("2003-01-02", "%Y-%m-%d") as f3,

--- 1.41/mysql-test/r/type_datetime.result	2007-05-08 03:58:36.000000000 +0400
+++ 1.42/mysql-test/r/type_datetime.result	2007-05-15 17:12:20.000000000 +0400
@@ -346,3 +346,17 @@
 a	b	a_then_b	b_then_a	c_then_a
 2007-04-10	2007-04-11	2007-04-10	2007-04-10	2004-04-09 00:00:00
 drop procedure test27759;
+create table t1 (f1 date, f2 datetime);
+create table t2 as select coalesce(f1,f1) as f3 from t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+f3	date	YES		NULL	
+create table t3 as select coalesce(f1,f2) as f3 from t1;
+desc t3;
+Field	Type	Null	Key	Default	Extra
+f3	datetime	YES		NULL	
+create table t4 as select coalesce(f2,f2) as f3 from t1;
+desc t4;
+Field	Type	Null	Key	Default	Extra
+f3	datetime	YES		NULL	
+drop tables t1,t2,t3,t4;

--- 1.27/mysql-test/t/type_datetime.test	2007-05-08 00:01:10.000000000 +0400
+++ 1.28/mysql-test/t/type_datetime.test	2007-05-15 17:12:06.000000000 +0400
@@ -225,3 +225,16 @@
 DELIMITER ;|
 call test27759();
 drop procedure test27759;
+
+#
+# Bug#27216: coalesce() returns wrong result type for dates arguments
+#
+create table t1 (f1 date, f2 datetime);
+create table t2 as select coalesce(f1,f1) as f3 from t1;
+desc t2;
+create table t3 as select coalesce(f1,f2) as f3 from t1;
+desc t3;
+create table t4 as select coalesce(f2,f2) as f3 from t1;
+desc t4;
+drop tables t1,t2,t3,t4;
+