List:Commits« Previous MessageNext Message »
From:eugene Date:September 20 2007 2:35pm
Subject:bk commit into 5.0 tree (evgen:1.2529) BUG#27216
View as plain text  
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-09-20 18:35:16+04:00, evgen@stripped +11 -0
  Bug#27216: functions with parameters of different date types may return wrong
  type of the result.
  
  There is several functions that accepts parameters of a different types.
  The result field type of such functions 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 affected functions was always STRING for DATE/DATETIME arguments.
  Affected functions are COALESCE, IF, IFNULL, CASE, LEAST/GREATEST, CASE.
  
  Now affected functions aggregate field types of their arguments rather
  than result types and return the result of aggregation as their result field
  type.
  The cached_field_type member variable is added to the number of classes to
  hold the aggregated result field type.
  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-09-19 15:58:59+04:00, evgen@stripped +1 -1
    A test case result corrected after fixing bug#27216.

  mysql-test/r/type_datetime.result@stripped, 2007-09-20 12:25:01+04:00, evgen@stripped +61 -0
    Added a test case for the bug#27216: functions with parameters of different
    date types may return wrong type of the result.

  mysql-test/t/type_datetime.test@stripped, 2007-09-20 12:24:21+04:00, evgen@stripped +38 -0
    Added a test case for the bug#27216: functions with parameters of different
    date types may return wrong type of the result.

  sql/item_cmpfunc.cc@stripped, 2007-09-20 18:32:12+04:00, evgen@stripped +36 -4
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    Now the fix_length_and_dec functions of the
    Item_func_ifnull,Item_func_if, Item_func_case, Item_func_coalesce
    classes are use agg_field_type function to find out the type of their 
    result field.

  sql/item_cmpfunc.h@stripped, 2007-09-20 13:36:04+04:00, evgen@stripped +7 -1
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The cached_field type is added to the Item_func_case, Item_func_if and
    Item_func_coalesce classes.
    The field_type function is added to the Item_func_ifnull, Item_func_if,
    Item_func_coalesce, Item_func_case classes.

  sql/item_func.cc@stripped, 2007-09-20 12:45:17+04:00, evgen@stripped +1 -0
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The Item_func_min_max::fix_length_and_dec function now uses the agg_field_type
    function to find out the correct type of the result field.

  sql/item_func.h@stripped, 2007-09-20 12:44:02+04:00, evgen@stripped +3 -1
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The cached_field_type variable is added to the Item_func_min_max class.

  sql/item_timefunc.cc@stripped, 2007-09-20 12:42:19+04:00, evgen@stripped +1 -1
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The result of the str_to_date function now defaults to the
    DATETIME type.

  sql/mysql_priv.h@stripped, 2007-09-20 12:40:59+04:00, evgen@stripped +1 -0
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The prototype of the agg_field_type function is added.

  sql/protocol.cc@stripped, 2007-09-20 12:34:43+04:00, evgen@stripped +1 -0
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The Protocol_simple::store function is now used to store fields of NEWDATE type.

  sql/sql_insert.cc@stripped, 2007-09-20 12:32:15+04:00, evgen@stripped +4 -1
    Bug#27216: functions with parameters of different date types may return wrong
    type of the result.
    The create_table_from_items function now uses the
    tmp_table_field_from_field_type function to get field for items with the
    STRING result type.

diff -Nrup a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
--- a/mysql-test/r/date_formats.result	2007-05-16 12:44:34 +04:00
+++ b/mysql-test/r/date_formats.result	2007-09-19 15:58:59 +04:00
@@ -481,7 +481,7 @@ str_to_date(a,b)
 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,
diff -Nrup a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
--- a/mysql-test/r/type_datetime.result	2007-06-06 00:25:01 +04:00
+++ b/mysql-test/r/type_datetime.result	2007-09-20 12:25:01 +04:00
@@ -427,3 +427,64 @@ f1
 Warnings:
 Warning	1292	Incorrect datetime value: '2007010100000' for column 'f1' at row 1
 drop table t1;
+#
+# Bug#27216: functions with parameters of different date types may
+#            return wrong type of the result.
+#
+create table t1 (f1 date, f2 datetime, f3 varchar(20));
+create table t2 as select coalesce(f1,f1) as f4 from t1;
+desc t2;
+Field	Type	Null	Key	Default	Extra
+f4	date	YES		NULL	
+create table t3 as select coalesce(f1,f2) as f4 from t1;
+desc t3;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t4 as select coalesce(f2,f2) as f4 from t1;
+desc t4;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t5 as select coalesce(f1,f3) as f4 from t1;
+desc t5;
+Field	Type	Null	Key	Default	Extra
+f4	varbinary(20)	YES		NULL	
+create table t6 as select coalesce(f2,f3) as f4 from t1;
+desc t6;
+Field	Type	Null	Key	Default	Extra
+f4	varbinary(20)	YES		NULL	
+create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
+desc t7;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
+from t1;
+desc t8;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t9 as select case when 1 then cast('01-01-01' as date)
+when 0 then cast('01-01-01' as date) end as f4 from t1;
+desc t9;
+Field	Type	Null	Key	Default	Extra
+f4	date	YES		NULL	
+create table t10 as select case when 1 then cast('01-01-01' as datetime)
+when 0 then cast('01-01-01' as datetime) end as f4 from t1;
+desc t10;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t11 as select if(1, cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t11;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t12 as select least(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t12;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+create table t13 as select ifnull(cast('01-01-01' as datetime),
+cast('01-01-01' as date)) as f4 from t1;
+desc t13;
+Field	Type	Null	Key	Default	Extra
+f4	datetime	YES		NULL	
+drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
+###################################################################
diff -Nrup a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
--- a/mysql-test/t/type_datetime.test	2007-06-06 00:25:02 +04:00
+++ b/mysql-test/t/type_datetime.test	2007-09-20 12:24:21 +04:00
@@ -282,3 +282,41 @@ select * from t1 where f1 between 200201
 select * from t1 where f1 between 2002010 and 20070101000000;
 select * from t1 where f1 between 20020101 and 2007010100000;
 drop table t1;
+
+--echo #
+--echo # Bug#27216: functions with parameters of different date types may
+--echo #            return wrong type of the result.
+--echo #
+create table t1 (f1 date, f2 datetime, f3 varchar(20));
+create table t2 as select coalesce(f1,f1) as f4 from t1;
+desc t2;
+create table t3 as select coalesce(f1,f2) as f4 from t1;
+desc t3;
+create table t4 as select coalesce(f2,f2) as f4 from t1;
+desc t4;
+create table t5 as select coalesce(f1,f3) as f4 from t1;
+desc t5;
+create table t6 as select coalesce(f2,f3) as f4 from t1;
+desc t6;
+create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
+desc t7;
+create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
+  from t1;
+desc t8;
+create table t9 as select case when 1 then cast('01-01-01' as date)
+  when 0 then cast('01-01-01' as date) end as f4 from t1;
+desc t9;
+create table t10 as select case when 1 then cast('01-01-01' as datetime)
+  when 0 then cast('01-01-01' as datetime) end as f4 from t1;
+desc t10;
+create table t11 as select if(1, cast('01-01-01' as datetime),
+  cast('01-01-01' as date)) as f4 from t1;
+desc t11;
+create table t12 as select least(cast('01-01-01' as datetime),
+  cast('01-01-01' as date)) as f4 from t1;
+desc t12;
+create table t13 as select ifnull(cast('01-01-01' as datetime),
+  cast('01-01-01' as date)) as f4 from t1;
+desc t13;
+drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
+--echo ###################################################################
diff -Nrup a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
--- a/sql/item_cmpfunc.cc	2007-07-16 01:03:32 +04:00
+++ b/sql/item_cmpfunc.cc	2007-09-20 18:32:12 +04:00
@@ -147,6 +147,36 @@ static int agg_cmp_type(THD *thd, Item_r
 }
 
 
+/**
+  @brief Aggregates field types from the array of items.
+
+  @param[in] items  array of items to aggregate the type from
+  @paran[in] nitems number of items in the array
+
+  @details This function aggregates field types from the array of items.
+    Found type is 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.
+
+  @note The term "aggregation" is used here in the sense of inferring the
+    result type of a function from its argument types.
+
+  @return aggregated field type.
+*/
+
+enum_field_types agg_field_type(Item **items, uint nitems)
+{
+  uint i;
+  if (!nitems || items[0]->result_type() == ROW_RESULT )
+    return (enum_field_types)-1;
+  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)
 {
@@ -2009,9 +2039,7 @@ Item_func_ifnull::fix_length_and_dec()
   default:
     DBUG_ASSERT(0);
   }
-  cached_field_type= args[0]->field_type();
-  if (cached_field_type != args[1]->field_type())
-    cached_field_type= Item_func::field_type();
+  cached_field_type= agg_field_type(args, 2);
 }
 
 
@@ -2159,11 +2187,13 @@ Item_func_if::fix_length_and_dec()
   {
     cached_result_type= arg2_type;
     collation.set(args[2]->collation.collation);
+    cached_field_type= args[2]->field_type();
   }
   else if (null2)
   {
     cached_result_type= arg1_type;
     collation.set(args[1]->collation.collation);
+    cached_field_type= args[1]->field_type();
   }
   else
   {
@@ -2177,6 +2207,7 @@ Item_func_if::fix_length_and_dec()
     {
       collation.set(&my_charset_bin);	// Number
     }
+    cached_field_type= agg_field_type(args + 1, 2);
   }
 
   if ((cached_result_type == DECIMAL_RESULT )
@@ -2556,7 +2587,7 @@ void Item_func_case::fix_length_and_dec(
       agg_arg_charsets(collation, agg, nagg, MY_COLL_ALLOW_CONV, 1))
     return;
   
-  
+  cached_field_type= agg_field_type(agg, nagg);
   /*
     Aggregate first expression and all THEN expression types
     and collations when string comparison
@@ -2695,6 +2726,7 @@ my_decimal *Item_func_coalesce::decimal_
 
 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:
diff -Nrup a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
--- a/sql/item_cmpfunc.h	2007-08-31 03:23:36 +04:00
+++ b/sql/item_cmpfunc.h	2007-09-20 13:36:04 +04:00
@@ -640,6 +640,7 @@ public:
 class Item_func_coalesce :public Item_func_numhybrid
 {
 protected:
+  enum_field_types cached_field_type;
   Item_func_coalesce(Item *a, Item *b) :Item_func_numhybrid(a, b) {}
 public:
   Item_func_coalesce(List<Item> &list) :Item_func_numhybrid(list) {}
@@ -652,13 +653,13 @@ public:
   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; }
 };
 
 
 class Item_func_ifnull :public Item_func_coalesce
 {
 protected:
-  enum_field_types cached_field_type;
   bool field_type_defined;
 public:
   Item_func_ifnull(Item *a, Item *b) :Item_func_coalesce(a,b) {}
@@ -677,6 +678,7 @@ public:
 class Item_func_if :public Item_func
 {
   enum Item_result cached_result_type;
+  enum_field_types cached_field_type;
 public:
   Item_func_if(Item *a,Item *b,Item *c)
     :Item_func(a,b,c), cached_result_type(INT_RESULT)
@@ -686,6 +688,7 @@ public:
   String *val_str(String *str);
   my_decimal *val_decimal(my_decimal *);
   enum Item_result result_type () const { return cached_result_type; }
+  enum_field_types field_type() const { return cached_field_type; }
   bool fix_fields(THD *, Item **);
   void fix_length_and_dec();
   uint decimal_precision() const;
@@ -722,6 +725,7 @@ class Item_func_case :public Item_func
   uint ncases;
   Item_result cmp_type;
   DTCollation cmp_collation;
+  enum_field_types cached_field_type;
 public:
   Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg)
     :Item_func(), first_expr_num(-1), else_expr_num(-1),
@@ -749,6 +753,7 @@ public:
   uint decimal_precision() const;
   table_map not_null_tables() const { return 0; }
   enum Item_result result_type () const { return cached_result_type; }
+  enum_field_types field_type() const { return cached_field_type; }
   const char *func_name() const { return "case"; }
   void print(String *str);
   Item *find_item(String *str);
@@ -1382,6 +1387,7 @@ public:
   bool subst_argument_checker(byte **arg) { return TRUE; }
   Item *compile(Item_analyzer analyzer, byte **arg_p,
                 Item_transformer transformer, byte *arg_t);
+  enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; }
 };
 
 
diff -Nrup a/sql/item_func.cc b/sql/item_func.cc
--- a/sql/item_func.cc	2007-08-03 20:59:12 +04:00
+++ b/sql/item_func.cc	2007-09-20 12:45:17 +04:00
@@ -2243,6 +2243,7 @@ void Item_func_min_max::fix_length_and_d
   else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT))
     max_length= my_decimal_precision_to_length(max_int_part+decimals, decimals,
                                             unsigned_flag);
+  cached_field_type= agg_field_type(args, arg_count);
 }
 
 
diff -Nrup a/sql/item_func.h b/sql/item_func.h
--- a/sql/item_func.h	2007-07-19 17:38:47 +04:00
+++ b/sql/item_func.h	2007-09-20 12:44:02 +04:00
@@ -692,7 +692,8 @@ class Item_func_min_max :public Item_fun
   /* An item used for issuing warnings while string to DATETIME conversion. */
   Item *datetime_item;
   THD *thd;
-
+protected:
+  enum_field_types cached_field_type;
 public:
   Item_func_min_max(List<Item> &list,int cmp_sign_arg) :Item_func(list),
     cmp_type(INT_RESULT), cmp_sign(cmp_sign_arg), compare_as_dates(FALSE),
@@ -705,6 +706,7 @@ public:
   enum Item_result result_type () const { return cmp_type; }
   bool result_as_longlong() { return compare_as_dates; };
   uint cmp_datetimes(ulonglong *value);
+  enum_field_types field_type() const { return cached_field_type; }
 };
 
 class Item_func_min :public Item_func_min_max
diff -Nrup a/sql/item_timefunc.cc b/sql/item_timefunc.cc
--- a/sql/item_timefunc.cc	2007-08-03 14:28:38 +04:00
+++ b/sql/item_timefunc.cc	2007-09-20 12:42:19 +04:00
@@ -3310,7 +3310,7 @@ void Item_func_str_to_date::fix_length_a
   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);
diff -Nrup a/sql/mysql_priv.h b/sql/mysql_priv.h
--- a/sql/mysql_priv.h	2007-08-16 20:56:08 +04:00
+++ b/sql/mysql_priv.h	2007-09-20 12:40:59 +04:00
@@ -1220,6 +1220,7 @@ my_bool mysql_rm_tmp_tables(void);
 
 /* item_func.cc */
 extern bool check_reserved_words(LEX_STRING *name);
+extern enum_field_types agg_field_type(Item **items, uint nitems);
 
 /* strfunc.cc */
 ulonglong find_set(TYPELIB *lib, const char *x, uint length, CHARSET_INFO *cs,
diff -Nrup a/sql/protocol.cc b/sql/protocol.cc
--- a/sql/protocol.cc	2007-05-16 12:44:41 +04:00
+++ b/sql/protocol.cc	2007-09-20 12:34:43 +04:00
@@ -824,6 +824,7 @@ bool Protocol_simple::store(const char *
 	      field_types[field_pos] == MYSQL_TYPE_DECIMAL ||
               field_types[field_pos] == MYSQL_TYPE_BIT ||
               field_types[field_pos] == MYSQL_TYPE_NEWDECIMAL ||
+              field_types[field_pos] == MYSQL_TYPE_NEWDATE ||
 	      (field_types[field_pos] >= MYSQL_TYPE_ENUM &&
 	       field_types[field_pos] <= MYSQL_TYPE_GEOMETRY));
   field_pos++;
diff -Nrup a/sql/sql_insert.cc b/sql/sql_insert.cc
--- a/sql/sql_insert.cc	2007-08-30 23:11:50 +04:00
+++ b/sql/sql_insert.cc	2007-09-20 12:32:15 +04:00
@@ -3129,7 +3129,10 @@ static TABLE *create_table_from_items(TH
     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,
Thread
bk commit into 5.0 tree (evgen:1.2529) BUG#27216eugene20 Sep