after review fixes

mysql-test/r/union.result:
  new tests, more correct results for old one
mysql-test/t/union.test:
  new tests, more correct results for old one
sql/field.cc:
  new way to make field types csting
sql/field.h:
  new way to make field types csting
sql/item.cc:
  new way to make field types csting
sql/sql_derived.cc:
  fixed typo
sql/sql_lex.h:
  comment added

5 files changed, 187 insertions, 81 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 70b8ce6c080..9b8e386fdc5 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -169,6 +169,128 @@ static inline uint field_length_without_space(const char *ptr, uint length)
   return (uint) (end-ptr);
 }
 
+/*
+ Tables of filed type compatibility.
+
+ There are tables for every type, table consist of list of types in which
+ given type can be converted without data lost, list should be ended with
+ FIELD_CAST_STOP
+*/
+static Field::field_cast_enum field_cast_decimal[]=
+{Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_tiny[]=
+{Field::FIELD_CAST_SHORT, Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG,
+ Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_short[]=
+{Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_medium[]=
+{Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_long[]=
+{Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_longlong[]=
+{Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_float[]=
+{Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_double[]=
+{Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_null[]=
+{Field::FIELD_CAST_DECIMAL, Field::FIELD_CAST_TINY, Field::FIELD_CAST_SHORT,
+ Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG, Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_TIMESTAMP, Field::FIELD_CAST_YEAR,
+ Field::FIELD_CAST_DATE, Field::FIELD_CAST_NEWDATE,
+ Field::FIELD_CAST_TIME, Field::FIELD_CAST_DATETIME,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB,
+ Field::FIELD_CAST_GEOM, Field::FIELD_CAST_ENUM, Field::FIELD_CAST_SET,
+ Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_timestamp[]=
+{Field::FIELD_CAST_DATETIME,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_year[]=
+{Field::FIELD_CAST_SHORT, Field::FIELD_CAST_MEDIUM, Field::FIELD_CAST_LONG,
+ Field::FIELD_CAST_LONGLONG,
+ Field::FIELD_CAST_FLOAT, Field::FIELD_CAST_DOUBLE,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_date[]=
+{Field::FIELD_CAST_DATETIME,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_newdate[]=
+{Field::FIELD_CAST_DATETIME,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_time[]=
+{Field::FIELD_CAST_DATETIME,
+ Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_datetime[]=
+{Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_string[]=
+{Field::FIELD_CAST_VARSTRING, Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_varstring[]=
+{Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_blob[]=
+{Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_geom[]=
+{Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_enum[]=
+{Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+static Field::field_cast_enum field_cast_set[]=
+{Field::FIELD_CAST_STRING, Field::FIELD_CAST_VARSTRING,
+ Field::FIELD_CAST_BLOB, Field::FIELD_CAST_STOP};
+// Array of pointers on conversion table for all fields types casting
+static Field::field_cast_enum *field_cast_array[]=
+{0, //FIELD_CAST_STOP
+ field_cast_decimal, field_cast_tiny, field_cast_short,
+ field_cast_medium, field_cast_long, field_cast_longlong,
+ field_cast_float, field_cast_double,
+ field_cast_null,
+ field_cast_timestamp, field_cast_year, field_cast_date, field_cast_newdate,
+ field_cast_time, field_cast_datetime,
+ field_cast_string, field_cast_varstring, field_cast_blob,
+ field_cast_geom, field_cast_enum, field_cast_set
+};
+
+
+bool Field::field_cast_compatible(Field::field_cast_enum type)
+{
+  DBUG_ASSERT(type != FIELD_CAST_STOP);
+  Field::field_cast_enum *array= field_cast_array[field_cast_type()];
+  uint i= 0;
+  Field::field_cast_enum tp;
+  do
+  {
+    tp=  array[i++];
+    if (tp == type)
+      return 1;
+  } while (tp != FIELD_CAST_STOP);
+  return 0;
+}
+
+
 /****************************************************************************
 ** Functions for the base classes
 ** This is an unpacked number.
diff --git a/sql/field.h b/sql/field.h
index ef6920f4d89..cb44ab76439 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -61,6 +61,17 @@ public:
     GEOM_GEOMETRYCOLLECTION = 7
   };
   enum imagetype { itRAW, itMBR};
+  enum field_cast_enum
+  {
+    FIELD_CAST_STOP, FIELD_CAST_DECIMAL, FIELD_CAST_TINY, FIELD_CAST_SHORT,
+    FIELD_CAST_MEDIUM, FIELD_CAST_LONG, FIELD_CAST_LONGLONG,
+    FIELD_CAST_FLOAT, FIELD_CAST_DOUBLE,
+    FIELD_CAST_NULL,
+    FIELD_CAST_TIMESTAMP, FIELD_CAST_YEAR, FIELD_CAST_DATE, FIELD_CAST_NEWDATE,
+    FIELD_CAST_TIME, FIELD_CAST_DATETIME,
+    FIELD_CAST_STRING, FIELD_CAST_VARSTRING, FIELD_CAST_BLOB,
+    FIELD_CAST_GEOM, FIELD_CAST_ENUM, FIELD_CAST_SET
+  };
 
   utype		unireg_check;
   uint32	field_length;		// Length of field
@@ -230,24 +241,8 @@ public:
   virtual bool has_charset(void) const { return FALSE; }
   virtual void set_charset(CHARSET_INFO *charset) { }
   void set_warning(const unsigned int level, const unsigned int code);
-  /*
-    number which describe preferences of field type converion,
-    for example, if we have int and float, float is prefered as more general
-    
-    ennumiration begins from:
-    100 for int types
-    300 for float point
-    500 time/date
-    700 string
-  */
-  virtual uint convert_order()= 0;
-  /*
-    Is this type is compatible with given 
-    (given can be stored in it)
-    Should take care only of types 'less' then current
-  */
-  virtual bool convert_order_compatible(uint order) { return 0; }
-
+  virtual field_cast_enum field_cast_type()= 0;
+  bool field_cast_compatible(field_cast_enum type);
   friend bool reopen_table(THD *,struct st_table *,bool);
   friend int cre_myisam(my_string name, register TABLE *form, uint options,
 			ulonglong auto_increment_value);
@@ -352,7 +347,7 @@ public:
   void overflow(bool negative);
   bool zero_pack() const { return 0; }
   void sql_type(String &str) const;
-  uint convert_order() { return 130; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_DECIMAL; }
 };
 
 
@@ -388,7 +383,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return 1; }
   void sql_type(String &str) const;
-  uint convert_order() { return 100; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_TINY; }
 };
 
 
@@ -424,7 +419,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return 2; }
   void sql_type(String &str) const;
-  uint convert_order() { return 101; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_SHORT; }
 };
 
 
@@ -455,7 +450,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return 3; }
   void sql_type(String &str) const;
-  uint convert_order() { return 102; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_MEDIUM; }
 };
 
 
@@ -491,7 +486,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return 4; }
   void sql_type(String &str) const;
-  uint convert_order() { return 103; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_LONG; }
 };
 
 
@@ -530,7 +525,7 @@ public:
   uint32 pack_length() const { return 8; }
   void sql_type(String &str) const;
   bool store_for_compare() { return 1; }
-  uint convert_order() { return 104; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_LONGLONG; }
 };
 #endif
 
@@ -564,7 +559,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return sizeof(float); }
   void sql_type(String &str) const;
-  uint convert_order() { return 300; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_FLOAT; }
 };
 
 
@@ -598,7 +593,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const { return sizeof(double); }
   void sql_type(String &str) const;
-  uint convert_order() { return 301; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_DOUBLE; }
 };
 
 
@@ -632,7 +627,7 @@ public:
   uint32 pack_length() const { return 0; }
   void sql_type(String &str) const;
   uint size_of() const { return sizeof(*this); }
-  uint convert_order() { return 0; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_NULL; }
 };
 
 
@@ -676,8 +671,7 @@ public:
   }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  uint convert_order() { return 520; }
-  bool convert_order_compatible(uint ord) { return ord<520; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_TIMESTAMP; }
 };
 
 
@@ -703,8 +697,7 @@ public:
   String *val_str(String*,String *);
   bool send_binary(Protocol *protocol);
   void sql_type(String &str) const;
-  uint convert_order() { return 501; }
-  bool convert_order_compatible(uint ord) { return ord<520; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_YEAR; }
 };
 
 
@@ -737,8 +730,7 @@ public:
   void sql_type(String &str) const;
   bool store_for_compare() { return 1; }
   bool zero_pack() const { return 1; }
-  uint convert_order() { return 502; }
-  bool convert_order_compatible(uint ord) { return ord<520; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_DATE; }
 };
 
 class Field_newdate :public Field_str {
@@ -770,8 +762,7 @@ public:
   bool zero_pack() const { return 1; }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  uint convert_order() { return 503; }
-  bool convert_order_compatible(uint ord) { return ord<520; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_NEWDATE; }
 };
 
 
@@ -805,8 +796,7 @@ public:
   void sql_type(String &str) const;
   bool store_for_compare() { return 1; }
   bool zero_pack() const { return 1; }
-  uint convert_order() { return 504; }
-  bool convert_order_compatible(uint ord) { return ord<520; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_TIME; }
 };
 
 
@@ -844,8 +834,7 @@ public:
   bool zero_pack() const { return 1; }
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
-  uint convert_order() { return 530; }
-  bool convert_order_compatible(uint ord) { return ord<=501; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_DATETIME; }
 };
 
 
@@ -890,7 +879,7 @@ public:
   uint size_of() const { return sizeof(*this); }
   enum_field_types real_type() const { return FIELD_TYPE_STRING; }
   bool has_charset(void) const { return TRUE; }
-  uint convert_order() { return 700; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_STRING; }
 };
 
 
@@ -934,7 +923,7 @@ public:
   uint size_of() const { return sizeof(*this); }
   enum_field_types real_type() const { return FIELD_TYPE_VAR_STRING; }
   bool has_charset(void) const { return TRUE; }
-  uint convert_order() { return 701; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_VARSTRING; }
 };
 
 
@@ -1024,7 +1013,7 @@ public:
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
-  uint convert_order() { return 701; }
+  field_cast_enum field_cast_type() { return FIELD_CAST_BLOB; }
 };
 
 
@@ -1053,8 +1042,7 @@ public:
 
   void get_key_image(char *buff,uint length, CHARSET_INFO *cs,imagetype type);
   void set_key_image(char *buff,uint length, CHARSET_INFO *cs);
-  uint convert_order() { return 750; }
-  bool convert_order_compatible(uint ord) { return ord < 750; };
+  field_cast_enum field_cast_type() { return FIELD_CAST_GEOM; }
 };
 
 
@@ -1096,8 +1084,7 @@ public:
   bool optimize_range(uint idx) { return 0; }
   bool eq_def(Field *field);
   bool has_charset(void) const { return TRUE; }
-  uint convert_order() { return 30; }
-  bool convert_order_compatible(uint ord) { return ord < 30; };
+  field_cast_enum field_cast_type() { return FIELD_CAST_ENUM; }
 };
 
 
@@ -1123,8 +1110,7 @@ public:
   void sql_type(String &str) const;
   enum_field_types real_type() const { return FIELD_TYPE_SET; }
   bool has_charset(void) const { return TRUE; }
-  uint convert_order() { return 40; }
-  bool convert_order_compatible(uint ord) { return ord < 40; };
+  field_cast_enum field_cast_type() { return FIELD_CAST_SET; }
 };
 
 
diff --git a/sql/item.cc b/sql/item.cc
index e5a9f9db740..c26e00456b7 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1996,12 +1996,13 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item)
   :Item(thd, *item), item_type(item->result_type())
 {
   DBUG_ASSERT(item->fixed);
+
+  /*
+    It is safe assign pointer on field, because it will be used just after
+    all JOIN::prepare calls and before any SELECT execution
+  */
   if (item->type() == Item::FIELD_ITEM)
-  {
-    Item_field *fitem= (Item_field*) item;
-    field_example= (Field*) thd->memdup((const char*)fitem->field,
-					fitem->field->size_of());
-  }
+    field_example= ((Item_field*) item)->field;
   else
     field_example= 0;
 }
@@ -2023,23 +2024,18 @@ void Item_type_holder::join_types(THD *thd, Item *item)
   if (field_example && item->type() == Item::FIELD_ITEM)
   {
     Field *field= ((Item_field *)item)->field;
-
-    // is new field better
-    if ((change_field=
-	 field_example->convert_order() < field->convert_order()))
+    if (field_example->field_cast_type() != field->field_cast_type())
     {
-      // is it compatible?
-      if (field->convert_order_compatible(field_example->convert_order()))
-	skip_store_field= 1;
-    }
-    else
-    {
-      /*
-	if old field can't store value of 'worse' new field we will make
-	decision about result field tipe based only on Item result type
-      */
-      if (field_example->convert_order_compatible(field->convert_order()))
-	skip_store_field= 1;
+      if (!(change_field=
+	    field_example->field_cast_compatible(field->field_cast_type())))
+      {
+	/*
+	  if old field can't store value of 'worse' new field we will make
+	  decision about result field type based only on Item result type
+	*/
+	if (!field->field_cast_compatible(field_example->field_cast_type()))
+	  skip_store_field= 1;
+      }
     }
   }
 
@@ -2057,19 +2053,15 @@ void Item_type_holder::join_types(THD *thd, Item *item)
 			((new_type == INT_RESULT) &&
 			 (decimals > item->decimals)) ||
 			(maybe_null && !item->maybe_null));
+    /*
+      It is safe assign pointer on field, because it will be used just after
+      all JOIN::prepare calls and before any SELECT execution
+    */
     if (skip_store_field || item->type() != Item::FIELD_ITEM)
       field_example= 0;
     else
-    {
-      /*
-	we do not need following, because we use mem_root
-	if (field_example)
-	  thd->free(field_example)
-      */
-      Item_field *fitem= (Item_field*) item;
-      field_example= (Field*) thd->memdup((const char*)fitem->field,
-					  fitem->field->size_of());
-    }
+      field_example= ((Item_field*) item)->field;
+
     max_length= max(max_length, item->max_length);
     decimals= max(decimals, item->decimals);
     maybe_null|= item->maybe_null;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 591c6579a46..20a1f7f0124 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -114,7 +114,7 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
     if(!(derived_result= new select_union(0)))
       DBUG_RETURN(1); // out of memory
 
-    // st_select_lex_unit::prepare coppectly work for single select
+    // st_select_lex_unit::prepare correctly work for single select
     if ((res= unit->prepare(thd, derived_result)))
       goto exit;
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 29301053c59..15da6ca57a3 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -309,7 +309,13 @@ protected:
 public:
   // list of fields which points to temporary table for union
   List<Item> item_list;
-  // list of types of items inside union (used for union & derived tables)
+  /*
+    list of types of items inside union (used for union & derived tables)
+    
+    Item_type_holders from which this list consist may have pointers to Field,
+    pointers is valid only after preparing SELECTS of this unit and before
+    any SELECT of this unit execution
+  */
   List<Item> types;
   /*
     Pointer to 'last' select or pointer to unit where stored