bk commit into 5.0 tree (mhansson:1.2498) BUG#27153

[mhansson]

Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin 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-07-30 14:08:54+02:00, mhansson@stripped +5 -0
  Bug #27153: create table from subquery with IF statements is varbinary rather than datetime
  
  The result field type of the IF() function was determined based on the
  aggregation of the argument fields' result types. As this type is always
  either STRING for if, the type is not preserved.
  
  Fixed by doing aggregation on the field types of the arguments instead, and by
  adding the field cached_field_type to Item_func_if, and return this from
  field_type(). This field is initialized in fix_length_and_dec.
  
  See also bug#27216.

  mysql-test/r/func_if.result@stripped, 2007-07-30 14:08:48+02:00, mhansson@stripped +10 -0
    Bug #27153: Test result

  mysql-test/t/func_if.test@stripped, 2007-07-30 14:08:48+02:00, mhansson@stripped +10 -1
    Bug #27153: Test case

  sql/item_cmpfunc.cc@stripped, 2007-07-30 14:08:49+02:00, mhansson@stripped +14 -0
    Bug #27153: 
    
    Added function agg_field_type from bug#27216. It is expected to automerge.

  sql/item_cmpfunc.h@stripped, 2007-07-30 14:08:49+02:00, mhansson@stripped +3 -0
    Bug #27153: Added field cached_field_type and overrode field_type() to return this.

  sql/sql_insert.cc@stripped, 2007-07-30 14:08:49+02:00, mhansson@stripped +4 -1
    Bug #27153: Part of the fix bug#27216, which will automerge.

# 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:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/bug27153/my50-bug27153

--- 1.252/sql/item_cmpfunc.cc	2007-05-17 21:08:45 +02:00
+++ 1.253/sql/item_cmpfunc.cc	2007-07-30 14:08:49 +02:00
@@ -146,6 +146,14 @@ static int agg_cmp_type(THD *thd, Item_r
   return 0;
 }
 
+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)
@@ -2034,6 +2042,12 @@ Item_func_if::fix_fields(THD *thd, Item 
 void
 Item_func_if::fix_length_and_dec()
 {
+  /* 
+     Do field type aggregation on THEN and ELSE branches of the if, 
+     i.e. arguments 2 and 3.
+  */
+  cached_field_type= agg_field_type(this->args + 1, this->arg_count - 1);
+
   maybe_null=args[1]->maybe_null || args[2]->maybe_null;
   decimals= max(args[1]->decimals, args[2]->decimals);
   unsigned_flag=args[1]->unsigned_flag && args[2]->unsigned_flag;

--- 1.152/sql/item_cmpfunc.h	2007-05-07 20:19:37 +02:00
+++ 1.153/sql/item_cmpfunc.h	2007-07-30 14:08:49 +02:00
@@ -675,6 +675,8 @@ public:
 class Item_func_if :public Item_func
 {
   enum Item_result cached_result_type;
+protected:
+  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)
@@ -688,6 +690,7 @@ public:
   void fix_length_and_dec();
   uint decimal_precision() const;
   const char *func_name() const { return "if"; }
+  enum_field_types field_type() const { return cached_field_type; };
 };
 
 

--- 1.237/sql/sql_insert.cc	2007-05-16 09:49:11 +02:00
+++ 1.238/sql/sql_insert.cc	2007-07-30 14:08:49 +02:00
@@ -3047,7 +3047,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,

--- 1.24/mysql-test/r/func_if.result	2007-02-12 21:59:26 +01:00
+++ 1.25/mysql-test/r/func_if.result	2007-07-30 14:08:48 +02:00
@@ -131,3 +131,13 @@ drop table t1;
 select if(0, 18446744073709551610, 18446744073709551610);
 if(0, 18446744073709551610, 18446744073709551610)
 18446744073709551610
+CREATE TABLE t1( a DATETIME );
+CREATE TABLE t2 SELECT IF( 1, a, a ) FROM t1;
+CREATE TABLE t3 SELECT IF( 0, a, a ) FROM t1;
+DESCRIBE t2;
+Field	Type	Null	Key	Default	Extra
+IF( 1, a, a )	datetime	YES		NULL	
+DESCRIBE t3;
+Field	Type	Null	Key	Default	Extra
+IF( 0, a, a )	datetime	YES		NULL	
+DROP TABLE t1, t2, t3;

--- 1.22/mysql-test/t/func_if.test	2007-02-12 21:59:26 +01:00
+++ 1.23/mysql-test/t/func_if.test	2007-07-30 14:08:48 +02:00
@@ -107,4 +107,13 @@ drop table t1;
 
 select if(0, 18446744073709551610, 18446744073709551610);
 
-
+#
+# Bug #27153: create table from subquery with IF statements is varbinary rather
+# than datetime
+#
+CREATE TABLE t1( a DATETIME );
+CREATE TABLE t2 SELECT IF( 1, a, a ) FROM t1;
+CREATE TABLE t3 SELECT IF( 0, a, a ) FROM t1;
+DESCRIBE t2;
+DESCRIBE t3;
+DROP TABLE t1, t2, t3;