← Back to team overview

maria-developers team mailing list archive

Re: Fwd: mixing of user-defined data types with other data types

 

Hello Vicențiu,


On 12/12/2016 11:57 PM, Vicențiu Ciorbaru wrote:
> Hi Alexander!
> 
> Comments inline. Most are stylistic comments. I think the patch is
> great. The Item_type_holder is really bugging me though. It feels
> poorly designed in the current place.

Thanks for great comments!
I've addressed most of your suggestions. A new version is attached.

There's a couple of things I'm not sure about though.
Please see details inline.

Thanks!
> 
>> diff --git a/sql/field.cc b/sql/field.cc
>> index 6f273e6..7876a6f 100644
>> --- a/sql/field.cc
>> +++ b/sql/field.cc
>> @@ -84,9 +84,13 @@ const char field_separator=',';
>>  */
>>  #define FIELDTYPE_TEAR_FROM (MYSQL_TYPE_BIT + 1)
>>  #define FIELDTYPE_TEAR_TO   (MYSQL_TYPE_NEWDECIMAL - 1)
>> -#define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (255 - FIELDTYPE_TEAR_TO))
>> +#define FIELDTYPE_LAST      254
>> +#define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (FIELDTYPE_LAST - FIELDTYPE_TEAR_TO))
>>
> These defines here look sketchy. I know it's not from your patch but can we not
> turn them to const ints instead?
> Also, the naming is horibly inconsistent, one uses 2 and others use to.
> I vote for _to_. We can have the full function definition as:
> 
> const int FIELDTYPE_TEAR_FROM= MYSQL_TYPE_BIT + 1;
> const int FIELDTYPE_TEAR_TO=   MYSQL_TYPE_NEWDECIMAL - 1;
> const int FIELDTYPE_LAST=      254;
> const int FIELDTYPE_NUM=       FIELDTYPE_TEAR_FROM + (FIELDTYPE_LAST -
>                                                      FIELDTYPE_TEAR_TO);
> static inline int field_type_to_index (enum_field_types field_type)
> {
>  DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM ||
>              real_type_to_type(field_type) > FIELDTYPE_TEAR_TO);
>  DBUG_ASSERT(field_type <= FIELDTYPE_LAST);
>  field_type= real_type_to_type(field_type);
>  if (field_type < FIELDTYPE_TEAR_FROM)
>    return field_type;
>  return FIELDTYPE_TEAR_FROM + (field_type - FIELDTYPE_TEAR_TO) - 1;
> }
> 
> The ternary operator provides no benefit in this case, let's replace it with
> regular if statement. Same number of lines but the code is cleaner I'd say.

Done.


>>
>>  static inline int field_type2index (enum_field_types field_type)
>>  {
>> +  DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM ||
>> +              real_type_to_type(field_type) > FIELDTYPE_TEAR_TO);
>> +  DBUG_ASSERT(field_type <= FIELDTYPE_LAST);
>>    field_type= real_type_to_type(field_type);
>>    return (field_type < FIELDTYPE_TEAR_FROM ?
>>            field_type :
>> @@ -94,6 +98,12 @@ static inline int field_type2index (enum_field_types field_type)
>>  }
>>
>>
>> +/**
>> +  Implements data type merge rules for the built-in traditional data types.
>> +  - UNION
>> +  - CASE and its abbreviations COALESCE, IF, IFNULL
>> +  - LEAST/GREATEST
>> +*/
>>
> I don't like this comment here as it's not complete. I'd much rather have it
> also explain how to interpret this table. I had to look up a calling spot
> to figure out exactly how it's done. I'd also have it say:
> Ex:
> Given Fields A and B of real_types a and b, we find the result type of
> COALESCE(A, B) by querying:
> field_types_merge_rules[field_type_to_index(a)][field_type_to_index(b)].

Done.


<cut>

>> +  enum_field_types ta= a->real_field_type();
>> +  enum_field_types tb= b->real_field_type();
>> +  return Type_handler::get_handler_by_real_type(Field::field_type_merge(ta, tb));
> Nit: this line is over 80 characters. Do we care? Most code tends to comply
> to it. It adds one extra line to split so it might not be worth it to adhere
> to a full 100% 80 character limit.

Done.

>> +}
>>
>>
>>  /*
>> diff --git a/sql/field.h b/sql/field.h
>> index 541da5a..fa84e7d 100644
>> --- a/sql/field.h
>> +++ b/sql/field.h
>> @@ -835,7 +835,6 @@ class Field: public Value_source
>>    virtual Item_result cmp_type () const { return result_type(); }
>>    static bool type_can_have_key_part(enum_field_types);
>>
> The field_type_merge function breaks all the other naming patterns. We
> have result_type, cmp_type real_type and now field_type_merge. Wouldn't it be
> better to name it field_merge_type, to be consistent? Or since this
> is a lookup kind of operation, perhaps name it (get|lookup)_merge_field_type?
> I know it's not _exactly_ like result_type and compare_type, but it generally
> is used in a simillar context.

Don't we make our life harder in respect of merging when renaming?

Usually I try not to rename existing functions:
- There is a chance that we'll merge something from earlier versions,
  and renaming can cause conflicts.
- Also, developers are used to this name and can do
  "grep field_type_merge" or similar when searching the code.


>>
>>    static enum_field_types field_type_merge(enum_field_types, enum_field_types);
>> -  static Item_result result_merge_type(enum_field_types);
>>    virtual bool eq(Field *field)
>>    {
>>      return (ptr == field->ptr && null_ptr == field->null_ptr &&
>> diff --git a/sql/item.cc b/sql/item.cc
>> index c97f41f..c9b6155 100644
>> --- a/sql/item.cc
>> +++ b/sql/item.cc
>> @@ -9657,20 +9657,8 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item)
>>    maybe_null= item->maybe_null;
>>    collation.set(item->collation);
>>    get_full_info(item);
>> -  /**
>> -    Field::result_merge_type(real_field_type()) should be equal to
>> -    result_type(), with one exception when "this" is a Item_field for
>> -    a BIT field:
>> -    - Field_bit::result_type() returns INT_RESULT, so does its Item_field.
>> -    - Field::result_merge_type(MYSQL_TYPE_BIT) returns STRING_RESULT.
>> -    Perhaps we need a new method in Type_handler to cover these type
>> -    merging rules for UNION.
>> -  */
>> -  DBUG_ASSERT(real_field_type() == MYSQL_TYPE_BIT ||
>> -              Item_type_holder::result_type()  ==
>> -              Field::result_merge_type(Item_type_holder::real_field_type()));
>>    /* fix variable decimals which always is NOT_FIXED_DEC */
>> -  if (Field::result_merge_type(real_field_type()) == INT_RESULT)
>>
> Alright so this seems to be fixed here, looking at Type_handler_bit,
> inheriting from Type_handler_int_result. Do we test this somewhere though?
> I couldn't find it in the test case, perhaps you can point it out to me.


In theory, decimals is always 0 if result_type() is INT_RESULT.
But I'm not fully sure that in reality non of the Items return
non-zero decimals in combination with INT_RESULT.
There's so many hacks in the code, so we combination can
be used somewhere.

I just tried to comment out these two lines:
> -  if (Item_type_holder::result_type() == INT_RESULT)
> -    decimals= 0;
> +//  if (Item_type_holder::result_type() == INT_RESULT)
> +//    decimals= 0;
both in the constructor and in the method join_types()
and run test. Nothing failed.

So perhaps these two lines can be just replaced to:

DBUG_ASSERT(decimals == 0 ||
            Item_type_holder::result_type() != INT_RESULT);

push, and see.

Any suggestions?


>>
>> +  if (Item_type_holder::result_type() == INT_RESULT)
>>      decimals= 0;
>>    prev_decimal_int_part= item->decimal_int_part();
>>  #ifdef HAVE_SPATIAL
>> @@ -9785,12 +9773,21 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
>>    DBUG_PRINT("info:", ("in type %d len %d, dec %d",
>>                         get_real_type(item),
>>                         item->max_length, item->decimals));
>> -  set_handler_by_real_type(Field::field_type_merge(real_field_type(),
>> -                                                   get_real_type(item)));
>> +  const Type_handler *item_type_handler=
>> +    Type_handler::get_handler_by_real_type(get_real_type(item));
>> +  if (aggregate_for_result(item_type_handler))
>> +  {
>> +    my_error(ER_CANT_AGGREGATE_2TYPES, MYF(0),
>> +             Item_type_holder::type_handler()->name().ptr(),
>> +             item_type_handler->name().ptr(),
>> +             "UNION");
>> +    DBUG_RETURN(true);
>> +  }
>> +
>>    {
>>      uint item_decimals= item->decimals;
>>      /* fix variable decimals which always is NOT_FIXED_DEC */
>> -    if (Field::result_merge_type(real_field_type()) == INT_RESULT)
>> +    if (Item_type_holder::result_type() == INT_RESULT)
>>
> Ok, we're avoiding a virtual function call here. What I don't like is that
> this Item_type_holder class basically "abuses" the Item interface to such a
> degree that it even has to directly declare functions such as:
> double Item_type_holder::val_real()
> {
>   DBUG_ASSERT(0); // should never be called
>   return 0.0;
> }
> 
> Let's discuss about cleaning this up later. To me it feels like this Item does
> not really belong in the Item class and should be factored out. Probably
> a whole project on its own :)

I made attempts to move Item_type_holder out of the Item hierarchy in
the past, but failed. It caused too much refactoring, because
Item_type_holder is used with List<Item> all around the UNION
and table creation code.

Perhaps we should make another attempt eventually.
I suggest to postpone this at least after the main Type_handler related
changes are done.

>>
>>        item_decimals= 0;
>>      decimals= MY_MAX(decimals, item_decimals);
>>    }
>> diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
>> index 98b179b..e5e366e 100644
>> --- a/sql/item_cmpfunc.cc
>> +++ b/sql/item_cmpfunc.cc
>> @@ -180,32 +179,40 @@ static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
>>    @return aggregated field type.
>>  */
>>
>> -enum_field_types agg_field_type(Item **items, uint nitems,
>> -                                bool treat_bit_as_number)
>>
> Why is this function in item_cmpfunc.cc and not in sql_type.cc?

Moving this to sql_type.cc can cause additional merge conflicts.

But perhaps it's Ok to move it, as it gets changed significantly anyway
(not logically, but textually).


>> +bool
>> +Type_handler_hybrid_field_type::aggregate_for_result(const char *funcname,
>> +                                                     Item **items, uint nitems,
>> +                                                     bool treat_bit_as_number)
>>  {
> I would move uint i to be a local for variable. This is a C-style loop.
> Is there a compiler that doesn't support this in one of our builders?

Done.

> Also, how about size_t instead of uint? (Probably not necessary but
> wlad made a point of prefering to use that for iterators and such).

size_t is fine. But this should be done together with changing
Item_args::arg_count, which is passed to this method.
I suggest not to do this change under terms of this patch.

<cut>

>> diff --git a/sql/sql_type.cc b/sql/sql_type.cc
>> index 8746595..397b5cf 100644
>> --- a/sql/sql_type.cc
>> +++ b/sql/sql_type.cc
>> @@ -54,6 +51,41 @@ static Type_handler_set         type_handler_set;
>>  Type_handler_null        type_handler_null;
>>  Type_handler_row         type_handler_row;
>>  Type_handler_varchar     type_handler_varchar;
>> +Type_handler_newdecimal  type_handler_newdecimal;
>> +Type_handler_longlong    type_handler_longlong;
>> +Type_handler_bit         type_handler_bit;
>> +
>> +
> I'm sure there's a better way to write this so that it gets initialized at
> compile time instead of at runtime (before main).
> Perhaps we can define the Type_aggregator differently. I need to look this
> Up. For now it will work.
> Standard offers no guarantees regarding the order, but it shouldn't matter
> for us as the address shouldn't change for global objects during
> initialization.

The part from Static_data_initializer should eventually be gone.
We need to extend the Type_handler API first, so a data type handler
(plugin) can provide an array of its aggregation rules.
So in the future the server will do the calls like
type_aggregator_for_result.add() when loading a new data
type plugin, either on startup, or during INSTALL PLUGIN.

For now, type handlers reside statically in the server anyway,
so this should work fine, and I think it's 100% safe.
As you noted, the addresses should not change even if
the get initialized in some non-reliable order.


I chose this approach because I didn't want to expose this code to
mysqld.cc now. Exposing it would be too early at this point.


> "It is implementation-defined whether or not the dynamic initialization
> (8.5, 9.4, 12.1, 12.6.1) of an object of namespace scope is done before the
> first statement of main. If the initialization is deferred to some point in
> time after the first statement of main, it shall occur before the first use
> of any function or object defined in the same translation unit as the object
> to be initialized."
>> +Type_aggregator type_aggregator_for_result;
>> +
>> +
>> +class Static_data_initializer
>> +{
>> +public:
>> +  static Static_data_initializer m_singleton;
>> +  Static_data_initializer()
>> +  {
>> +#ifdef HAVE_SPATIAL
>> +    type_aggregator_for_result.add(&type_handler_geometry,
>> +                                   &type_handler_null,
>> +                                   &type_handler_geometry);
>> +    type_aggregator_for_result.add(&type_handler_geometry,
>> +                                   &type_handler_geometry,
>> +                                   &type_handler_geometry);
>> +    type_aggregator_for_result.add(&type_handler_geometry,
>> +                                   &type_handler_blob,
>> +                                   &type_handler_long_blob);
>> +    type_aggregator_for_result.add(&type_handler_geometry,
>> +                                   &type_handler_varchar,
>> +                                   &type_handler_long_blob);
>> +    type_aggregator_for_result.add(&type_handler_geometry,
>> +                                   &type_handler_string,
>> +                                   &type_handler_long_blob);
>> +#endif
>> +  }
>> +};
>> +
>> +Static_data_initializer Static_data_initializer::m_singleton;
>>
>>
>>  /**
>> diff --git a/sql/sql_type.h b/sql/sql_type.h
>> index 92dee61..4bc5542 100644
>> --- a/sql/sql_type.h
>> +++ b/sql/sql_type.h
>> @@ -214,6 +214,31 @@ class Type_std_attributes
>>  };
>>
>>
> I don't like this class too much. One can easily break it by either changing
> LEX_CSTRING::str or LEX_CSTRING::length without changing the other one.
> I suggest we make the inheritance private so that the only way to access the
> members is through the methods available.

Done: I changed it to derive privately.

> A suggestion I have is to create a generic "String" class that has this same
> behaviour, without calling it Name. Afterwards typedefing it to Name.

Earlier I proposed to add similar classes to struct.h,
something like this:

struct Lex_cstring_st: public LEX_CSTRING; // without initialization

class Lex_cstring: protected Lex_cstring_st; // with initialization

and to move all global functions operating
on LEX_CSTRING as methods into these new struct and class.


But Monty disliked it. Monty thinks that having more globally visible
classes makes the code harder to read.
I think it makes the code easier to read, to use, and to reuse.
We never could agree :)

So if we're adding Lex_cstring_st at this point we should be ready:
- either to convince Monty that this is good
- or to revert our changes in struct.h and move the class locally
  to sql_type.h again.

Let's go with Lex_cstring_st in struct.h ?

:)


>>
>> +class Name: public LEX_CSTRING
>> +{
>> +public:
>> +  Name(const char *str_arg, uint length_arg)
>> +  {
>> +    LEX_CSTRING::str= str_arg;
>> +    LEX_CSTRING::length= length_arg;
>> +  }
> Also, these ought to be removed if they are commented out.

Done.

>> +  /*
>> +  Name()
>> +  {
>> +    LEX_CSTRING::str= NULL;
>> +    LEX_CSTRING::length= 0;
>> +  }
>> +  Name(const LEX_STRING &name)
>> +  {
>> +    LEX_CSTRING::str= name.str;
>> +    LEX_CSTRING::length= name.length;
>> +  }
>> +  */
>> +  const char *ptr() const { return LEX_CSTRING::str; }
>> +  uint length() const { return LEX_CSTRING::length; }
>> +};
>> +
>> +
>>  class Type_handler
>>  {
>>  protected:
>> @@ -235,6 +260,11 @@ class Type_handler
>>      DBUG_ASSERT(type != TIME_RESULT);
>>      return get_handler_by_cmp_type(type);
>>    }
>> +  static const
>> +  Type_handler *aggregate_for_result_traditional(const Type_handler *h1,
>> +                                                 const Type_handler *h2);
>> +
> This function can return a const reference from a const static object
> within the class's namespace. Why create an object every time this is called?
> Compiler might optimize it, but why risk it?
> This goes for all the implementations.

For my opinion it's 100% safe. There is no any risk here.
It should be the same safe with doing "return 10" from a "int" function.

It just reserves 16 bytes for a LEX_CSTRING on the stack and populates
it, and then the caller uses this populated LEX_CSTRING to access
its members though the methods ptr() or length().
Some compilers should probably be able even to use registers instead
of stack for this. But my intent was not to rely on using registers.
I just found this style as the shortest possible and the most readable.


As for performance, it requires the same amount of resources with for
example passing LEX_CSTRING by value to some function, or just
to create a local LEX_CSTRING/LEX_STRING variable.

Here are some examples in the existing code:

sp_sql->append(C_STRING_WITH_LEN("CREATE "));
sp_sql->append(C_STRING_WITH_LEN("PROCEDURE "));
LEX_STRING pw= { C_STRING_WITH_LEN("password") };

Notice, we don't create static LEX_STRING or LEX_CSTRING for all
possible strings we need in the server.
The proposed code should be exactly the same cheap with these examples.

Another approach would be to:
- have a static variable for every Type_handler name
- return this variable from the method name().

This could give slight benefits when we need ptr() without length(),
or the other way around. And the caller in item.cc actually uses
name().ptr() without name().length().

But as name() will be used for errors and for DBUG_PRINT mostly,
so I thought it would be more useful to save the number of lines.
And it's easier to read this way.
You can see the name right inside the class definition,
you don't have to go to sql_class.cc to see it.



>> +  virtual const Name name() const= 0;
>>    virtual enum_field_types field_type() const= 0;
>>    virtual enum_field_types real_field_type() const { return field_type(); }
>>    virtual Item_result result_type() const= 0;
>> @@ -884,5 +958,63 @@ class Type_handler_hybrid_real_field_type:
>>  extern Type_handler_row   type_handler_row;
>>  extern Type_handler_null  type_handler_null;
>>  extern Type_handler_varchar type_handler_varchar;
>> +extern Type_handler_newdecimal type_handler_newdecimal;
>> +extern Type_handler_longlong type_handler_longlong;
>> +extern Type_handler_bit type_handler_bit;
>> +
>> +class Type_aggregator
>> +{
> 
> I'm not sure if Element is a good name for this. Aggregate_result_pair maybe?

Renamed to Pair. It's defined privately inside the class
Type_aggregator, so no needs for a long name such as Aggregate_result_pair.

> 
>> +  class Element
>> +  {
>> +  public:
>> +    const Type_handler *m_handler1;
>> +    const Type_handler *m_handler2;
>> +    const Type_handler *m_result;
>> +    Element() { }
>> +    Element(const Type_handler *handler1,
>> +            const Type_handler *handler2,
>> +            const Type_handler *result)
>> +     :m_handler1(handler1), m_handler2(handler2), m_result(result)
>> +    { }
>> +    bool eq(const Type_handler *handler1, const Type_handler *handler2) const
>> +    {
>> +      return m_handler1 == handler1 && m_handler2 == handler2;
>> +    }
>> +  };
> So, we've discussed this before to change to a dynamic array. I prefer the
> template Dynamic_array over DYNAMIC_ARRAY.

The new version uses Dynamic_array.


>> +  Element m_element_array[256];
>> +  uint m_element_count;
>> +  const Element *element(uint i) const { return &m_element_array[i]; }
<cut>

diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index d4c17e6..53231bd 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -2938,13 +2938,7 @@ sha1('P'),
 )
 )
 ) AS r;
-r
-0
-Warnings:
-Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
-Warning	1292	Truncated incorrect DECIMAL value: '[.DC2.]'
-Warning	1292	Truncated incorrect INTEGER value: '511993d3c99719e38a6779073019dacd7178ddb9'
-Warning	1292	Truncated incorrect DOUBLE value: '0.000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'
+ERROR HY000: Illegal parameter data types geometry and decimal for operation 'coalesce'
 connection conn1;
 SET @@global.max_allowed_packet:= @tmp_max;
 disconnect newconn;
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index d10cfec..4293e7c 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -2191,5 +2191,617 @@ c
 1
 DROP TABLE t1;
 #
+#
+#
+CREATE PROCEDURE p2(query TEXT)
+BEGIN
+DECLARE errcount INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
+BEGIN
+SET errcount = errcount+1;
+#SHOW WARNINGS;
+GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT;
+SELECT @p AS `ERROR: `;
+END;
+SELECT query AS ``;
+EXECUTE IMMEDIATE query;
+IF errcount = 0
+THEN
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END IF;
+END;
+$$
+CREATE PROCEDURE p1(query TEXT)
+BEGIN
+SELECT query AS `-------------------------------------`;
+EXECUTE IMMEDIATE query;
+CALL p2('CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1');
+CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1');
+CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1');
+CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1');
+CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1');
+DROP TABLE t1;
+END;
+$$
+-------------------------------------
+CREATE TABLE t1 (a CHAR(10), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a VARCHAR(10), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a TINYTEXT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a TEXT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a MEDIUMTEXT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a LONGTEXT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+-------------------------------------
+CREATE TABLE t1 (a TINYINT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types tinyint and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types tinyint and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types tinyint and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types tinyint and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types tinyint and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a SMALLINT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types shortint and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types shortint and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types shortint and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types shortint and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types shortint and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a MEDIUMINT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types mediumint and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types mediumint and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types mediumint and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types mediumint and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types mediumint and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a INT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types int and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types int and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types int and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types int and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types int and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a BIGINT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a FLOAT, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types float and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types float and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types float and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types float and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types float and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a DOUBLE, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types double and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types double and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types double and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types double and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types double and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a DECIMAL(10,2), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types decimal and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types decimal and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types decimal and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types decimal and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types decimal and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a BIT(8), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types bigint and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types bit and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a TIME, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types time and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types time and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types time and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types time and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types time and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a DATE, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types date and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types date and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types date and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types date and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types date and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a DATETIME, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types datetime and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types datetime and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types datetime and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types datetime and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types datetime and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a TIMESTAMP, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types timestamp and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types timestamp and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types timestamp and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types timestamp and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types timestamp and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a YEAR, b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+ERROR: 
+Illegal parameter data types year and geometry for operation 'case'
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+ERROR: 
+Illegal parameter data types year and geometry for operation 'coalesce'
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+ERROR: 
+Illegal parameter data types year and geometry for operation 'if'
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+ERROR: 
+Illegal parameter data types year and geometry for operation 'ifnull'
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types year and geometry for operation 'UNION'
+# This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458)
+-------------------------------------
+CREATE TABLE t1 (a ENUM(0x61), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types enum and geometry for operation 'UNION'
+-------------------------------------
+CREATE TABLE t1 (a SET(0x61), b Point)
+
+CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `COALESCE(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IF(TRUE,a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `IFNULL(a,b)` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
+ERROR: 
+Illegal parameter data types set and geometry for operation 'UNION'
+# This does not preserve geometry type (MDEV-9405)
+CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `COALESCE(NULL, Point(1,1))` geometry DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `NULL` geometry DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 2645417..cfaae1a 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1554,6 +1554,8 @@ format(rpad('111111111.1',
 # But in mysqltest --disable_prepare_warnings affects SELECT queries only
 # and can't suppress prepare time warnings for DO.
 #
+
+--error ER_CANT_AGGREGATE_2TYPES
 SELECT
 round(
   concat( (
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 12b3e60..ade0b9f 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -1709,5 +1709,84 @@ SELECT c FROM t1;
 DROP TABLE t1;
 
 --echo #
+--echo #
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p2(query TEXT)
+BEGIN
+  DECLARE errcount INT DEFAULT 0;
+  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
+  BEGIN
+    SET errcount = errcount+1;
+    #SHOW WARNINGS;
+    GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT;
+    SELECT @p AS `ERROR: `;
+  END;
+  SELECT query AS ``;
+  EXECUTE IMMEDIATE query;
+  IF errcount = 0
+  THEN
+    SHOW CREATE TABLE t2;
+    DROP TABLE t2;
+  END IF;
+END;
+$$
+CREATE PROCEDURE p1(query TEXT)
+BEGIN
+  SELECT query AS `-------------------------------------`;
+  EXECUTE IMMEDIATE query;
+  CALL p2('CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1');
+  CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1');
+  DROP TABLE t1;
+END;
+$$
+DELIMITER ;$$
+
+--disable_query_log
+CALL p1('CREATE TABLE t1 (a CHAR(10), b Point)');
+CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point)');
+CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a TEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point)');
+CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point)');
+
+CALL p1('CREATE TABLE t1 (a TINYINT, b Point)');
+CALL p1('CREATE TABLE t1 (a SMALLINT, b Point)');
+CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point)');
+CALL p1('CREATE TABLE t1 (a INT, b Point)');
+CALL p1('CREATE TABLE t1 (a BIGINT, b Point)');
+CALL p1('CREATE TABLE t1 (a FLOAT, b Point)');
+CALL p1('CREATE TABLE t1 (a DOUBLE, b Point)');
+CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point)');
+CALL p1('CREATE TABLE t1 (a BIT(8), b Point)');
+
+CALL p1('CREATE TABLE t1 (a TIME, b Point)');
+CALL p1('CREATE TABLE t1 (a DATE, b Point)');
+CALL p1('CREATE TABLE t1 (a DATETIME, b Point)');
+CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point)');
+CALL p1('CREATE TABLE t1 (a YEAR, b Point)');
+
+--echo # This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458)
+CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
+CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
+--enable_query_log
+
+--echo # This does not preserve geometry type (MDEV-9405)
+CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/sql/field.cc b/sql/field.cc
index 6fc500e..2b90d6f 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -82,18 +82,35 @@ const char field_separator=',';
   following #defines describe that gap and how to canculate number of fields
   and index of field in thia array.
 */
-#define FIELDTYPE_TEAR_FROM (MYSQL_TYPE_BIT + 1)
-#define FIELDTYPE_TEAR_TO   (MYSQL_TYPE_NEWDECIMAL - 1)
-#define FIELDTYPE_NUM (FIELDTYPE_TEAR_FROM + (255 - FIELDTYPE_TEAR_TO))
+const int FIELDTYPE_TEAR_FROM= (MYSQL_TYPE_BIT + 1);
+const int FIELDTYPE_TEAR_TO=   (MYSQL_TYPE_NEWDECIMAL - 1);
+const int FIELDTYPE_LAST=      254;
+const int FIELDTYPE_NUM=       FIELDTYPE_TEAR_FROM + (FIELDTYPE_LAST -
+                                                      FIELDTYPE_TEAR_TO);
+
 static inline int field_type2index (enum_field_types field_type)
 {
+  DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM ||
+              real_type_to_type(field_type) > FIELDTYPE_TEAR_TO);
+  DBUG_ASSERT(field_type <= FIELDTYPE_LAST);
   field_type= real_type_to_type(field_type);
-  return (field_type < FIELDTYPE_TEAR_FROM ?
-          field_type :
-          ((int)FIELDTYPE_TEAR_FROM) + (field_type - FIELDTYPE_TEAR_TO) - 1);
+  if (field_type < FIELDTYPE_TEAR_FROM)
+    return field_type;
+  return FIELDTYPE_TEAR_FROM + (field_type - FIELDTYPE_TEAR_TO) - 1;
 }
 
 
+/**
+  Implements data type merge rules for the built-in traditional data types.
+  Used for operations such as:
+  - UNION
+  - CASE and its abbreviations COALESCE, IF, IFNULL
+  - LEAST/GREATEST
+
+  Given Fields A and B of real_types a and b, we find the result type of
+  COALESCE(A, B) by querying:
+    field_types_merge_rules[field_type_to_index(a)][field_type_to_index(b)].
+*/
 static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
 {
   /* MYSQL_TYPE_DECIMAL -> */
@@ -124,8 +141,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_TINY -> */
   {
@@ -155,8 +172,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_SHORT -> */
   {
@@ -186,8 +203,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_LONG -> */
   {
@@ -217,8 +234,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_FLOAT -> */
   {
@@ -248,8 +265,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_DOUBLE -> */
   {
@@ -279,8 +296,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_NULL -> */
   {
@@ -310,8 +327,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_GEOMETRY
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_TIMESTAMP -> */
   {
@@ -341,8 +358,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_LONGLONG -> */
   {
@@ -372,8 +389,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_INT24 -> */
   {
@@ -403,8 +420,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_DATE -> */
   {
@@ -434,8 +451,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_TIME -> */
   {
@@ -465,8 +482,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_DATETIME -> */
   {
@@ -496,8 +513,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_YEAR -> */
   {
@@ -527,8 +544,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_NEWDATE -> */
   {
@@ -558,8 +575,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_VARCHAR -> */
   {
@@ -589,8 +606,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_VARCHAR
   },
   /* MYSQL_TYPE_BIT -> */
   {
@@ -620,8 +637,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_NEWDECIMAL -> */
   {
@@ -651,8 +668,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_ENUM -> */
   {
@@ -682,8 +699,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_SET -> */
   {
@@ -713,8 +730,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   },
   /* MYSQL_TYPE_TINY_BLOB -> */
   {
@@ -744,8 +761,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_TINY_BLOB,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_TINY_BLOB,   MYSQL_TYPE_TINY_BLOB
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_TINY_BLOB
   },
   /* MYSQL_TYPE_MEDIUM_BLOB -> */
   {
@@ -775,8 +792,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_MEDIUM_BLOB
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_MEDIUM_BLOB
   },
   /* MYSQL_TYPE_LONG_BLOB -> */
   {
@@ -806,8 +823,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_LONG_BLOB,   MYSQL_TYPE_LONG_BLOB
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_LONG_BLOB
   },
   /* MYSQL_TYPE_BLOB -> */
   {
@@ -837,8 +854,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_BLOB,        MYSQL_TYPE_BLOB
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_BLOB
   },
   /* MYSQL_TYPE_VAR_STRING -> */
   {
@@ -868,8 +885,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_VARCHAR
   },
   /* MYSQL_TYPE_STRING -> */
   {
@@ -899,39 +916,8 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
     MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
   //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
     MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_STRING
-  },
-  /* MYSQL_TYPE_GEOMETRY -> */
-  {
-  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
-    MYSQL_TYPE_GEOMETRY,    MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_NEWDATE      MYSQL_TYPE_VARCHAR
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_BIT          <16>-<245>
-    MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_NEWDECIMAL   MYSQL_TYPE_ENUM
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
-    MYSQL_TYPE_VARCHAR,     MYSQL_TYPE_TINY_BLOB,
-  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
-    MYSQL_TYPE_MEDIUM_BLOB, MYSQL_TYPE_LONG_BLOB,
-  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
-    MYSQL_TYPE_BLOB,        MYSQL_TYPE_VARCHAR,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-    MYSQL_TYPE_STRING,      MYSQL_TYPE_GEOMETRY
+  //MYSQL_TYPE_STRING
+    MYSQL_TYPE_STRING
   }
 };
 
@@ -948,46 +934,19 @@ static enum_field_types field_types_merge_rules [FIELDTYPE_NUM][FIELDTYPE_NUM]=
 enum_field_types Field::field_type_merge(enum_field_types a,
                                          enum_field_types b)
 {
-  DBUG_ASSERT(real_type_to_type(a) < FIELDTYPE_TEAR_FROM ||
-              real_type_to_type(a) > FIELDTYPE_TEAR_TO);
-  DBUG_ASSERT(real_type_to_type(b) < FIELDTYPE_TEAR_FROM ||
-              real_type_to_type(b) > FIELDTYPE_TEAR_TO);
   return field_types_merge_rules[field_type2index(a)]
                                 [field_type2index(b)];
 }
 
-
-static Item_result field_types_result_type [FIELDTYPE_NUM]=
+const Type_handler *
+Type_handler::aggregate_for_result_traditional(const Type_handler *a,
+                                               const Type_handler *b)
 {
-  //MYSQL_TYPE_DECIMAL      MYSQL_TYPE_TINY
-  DECIMAL_RESULT,           INT_RESULT,
-  //MYSQL_TYPE_SHORT        MYSQL_TYPE_LONG
-  INT_RESULT,               INT_RESULT,
-  //MYSQL_TYPE_FLOAT        MYSQL_TYPE_DOUBLE
-  REAL_RESULT,              REAL_RESULT,
-  //MYSQL_TYPE_NULL         MYSQL_TYPE_TIMESTAMP
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_LONGLONG     MYSQL_TYPE_INT24
-  INT_RESULT,               INT_RESULT,
-  //MYSQL_TYPE_DATE         MYSQL_TYPE_TIME
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_DATETIME     MYSQL_TYPE_YEAR
-  STRING_RESULT,            INT_RESULT,
-  //MYSQL_TYPE_NEWDATE      MYSQL_TYPE_VARCHAR
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_BIT          <16>-<245>
-  STRING_RESULT,
-  //MYSQL_TYPE_NEWDECIMAL   MYSQL_TYPE_ENUM
-  DECIMAL_RESULT,           STRING_RESULT,
-  //MYSQL_TYPE_SET          MYSQL_TYPE_TINY_BLOB
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_MEDIUM_BLOB  MYSQL_TYPE_LONG_BLOB
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_BLOB         MYSQL_TYPE_VAR_STRING
-  STRING_RESULT,            STRING_RESULT,
-  //MYSQL_TYPE_STRING       MYSQL_TYPE_GEOMETRY
-  STRING_RESULT,            STRING_RESULT
-};
+  enum_field_types ta= a->real_field_type();
+  enum_field_types tb= b->real_field_type();
+  return
+    Type_handler::get_handler_by_real_type(Field::field_type_merge(ta, tb));
+}
 
 
 /*
@@ -1034,21 +993,6 @@ int compare(unsigned int a, unsigned int b)
 
 CPP_UNNAMED_NS_END
 
-/**
-  Detect Item_result by given field type of UNION merge result.
-
-  @param field_type  given field type
-
-  @return
-    Item_result (type of internal MySQL expression result)
-*/
-
-Item_result Field::result_merge_type(enum_field_types field_type)
-{
-  DBUG_ASSERT(real_type_to_type(field_type) < FIELDTYPE_TEAR_FROM ||
-              real_type_to_type(field_type) > FIELDTYPE_TEAR_TO);
-  return field_types_result_type[field_type2index(field_type)];
-}
 
 /*****************************************************************************
   Static help functions
diff --git a/sql/field.h b/sql/field.h
index 54617da..9b7b84c 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -835,7 +835,6 @@ class Field: public Value_source
   virtual Item_result cmp_type () const { return result_type(); }
   static bool type_can_have_key_part(enum_field_types);
   static enum_field_types field_type_merge(enum_field_types, enum_field_types);
-  static Item_result result_merge_type(enum_field_types);
   virtual bool eq(Field *field)
   {
     return (ptr == field->ptr && null_ptr == field->null_ptr &&
diff --git a/sql/item.cc b/sql/item.cc
index a1c3f13..40649c0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9779,20 +9779,8 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item)
   maybe_null= item->maybe_null;
   collation.set(item->collation);
   get_full_info(item);
-  /**
-    Field::result_merge_type(real_field_type()) should be equal to
-    result_type(), with one exception when "this" is a Item_field for
-    a BIT field:
-    - Field_bit::result_type() returns INT_RESULT, so does its Item_field.
-    - Field::result_merge_type(MYSQL_TYPE_BIT) returns STRING_RESULT.
-    Perhaps we need a new method in Type_handler to cover these type
-    merging rules for UNION.
-  */
-  DBUG_ASSERT(real_field_type() == MYSQL_TYPE_BIT ||
-              Item_type_holder::result_type()  ==
-              Field::result_merge_type(Item_type_holder::real_field_type()));
   /* fix variable decimals which always is NOT_FIXED_DEC */
-  if (Field::result_merge_type(real_field_type()) == INT_RESULT)
+  if (Item_type_holder::result_type() == INT_RESULT)
     decimals= 0;
   prev_decimal_int_part= item->decimal_int_part();
 #ifdef HAVE_SPATIAL
@@ -9907,12 +9895,21 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
   DBUG_PRINT("info:", ("in type %d len %d, dec %d",
                        get_real_type(item),
                        item->max_length, item->decimals));
-  set_handler_by_real_type(Field::field_type_merge(real_field_type(),
-                                                   get_real_type(item)));
+  const Type_handler *item_type_handler=
+    Type_handler::get_handler_by_real_type(get_real_type(item));
+  if (aggregate_for_result(item_type_handler))
+  {
+    my_error(ER_CANT_AGGREGATE_2TYPES, MYF(0),
+             Item_type_holder::type_handler()->name().ptr(),
+             item_type_handler->name().ptr(),
+             "UNION");
+    DBUG_RETURN(true);
+  }
+
   {
     uint item_decimals= item->decimals;
     /* fix variable decimals which always is NOT_FIXED_DEC */
-    if (Field::result_merge_type(real_field_type()) == INT_RESULT)
+    if (Item_type_holder::result_type() == INT_RESULT)
       item_decimals= 0;
     decimals= MY_MAX(decimals, item_decimals);
   }
@@ -9921,7 +9918,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
     geometry_type=
       Field_geom::geometry_type_merge(geometry_type, item->get_geometry_type());
 
-  if (Field::result_merge_type(real_field_type()) == DECIMAL_RESULT)
+  if (Item_type_holder::result_type() == DECIMAL_RESULT)
   {
     decimals= MY_MIN(MY_MAX(decimals, item->decimals), DECIMAL_MAX_SCALE);
     int item_int_part= item->decimal_int_part();
@@ -9933,7 +9930,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
                                                              unsigned_flag);
   }
 
-  switch (Field::result_merge_type(real_field_type()))
+  switch (Item_type_holder::result_type())
   {
   case STRING_RESULT:
   {
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 61c5f5b..339468e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -173,8 +173,7 @@ bool Type_handler_hybrid_field_type::aggregate_for_comparison(Item **items,
   @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.
+    Aggregation itself is performed by Type_handler::aggregate_for_result().
 
   @note The term "aggregation" is used here in the sense of inferring the
     result type of a function from its argument types.
@@ -182,32 +181,39 @@ bool Type_handler_hybrid_field_type::aggregate_for_comparison(Item **items,
   @return aggregated field type.
 */
 
-enum_field_types agg_field_type(Item **items, uint nitems,
-                                bool treat_bit_as_number)
+bool
+Type_handler_hybrid_field_type::aggregate_for_result(const char *funcname,
+                                                     Item **items, uint nitems,
+                                                     bool treat_bit_as_number)
 {
-  uint i;
   if (!nitems || items[0]->result_type() == ROW_RESULT)
   {
     DBUG_ASSERT(0);
-    return MYSQL_TYPE_NULL;
+    set_handler(&type_handler_null);
+    return true;
   }
-  enum_field_types res= items[0]->field_type();
+  set_handler(items[0]->type_handler());
   uint unsigned_count= items[0]->unsigned_flag;
-  for (i= 1 ; i < nitems ; i++)
+  for (uint i= 1 ; i < nitems ; i++)
   {
-    enum_field_types cur= items[i]->field_type();
+    const Type_handler *cur= items[i]->type_handler();
     if (treat_bit_as_number &&
-        ((res == MYSQL_TYPE_BIT) ^ (cur == MYSQL_TYPE_BIT)))
+        ((type_handler() == &type_handler_bit) ^ (cur == &type_handler_bit)))
     {
-      if (res == MYSQL_TYPE_BIT)
-        res= MYSQL_TYPE_LONGLONG; // BIT + non-BIT
+      if (type_handler() == &type_handler_bit)
+        set_handler(&type_handler_longlong); // BIT + non-BIT
       else
-        cur= MYSQL_TYPE_LONGLONG; // non-BIT + BIT
+        cur= &type_handler_longlong; // non-BIT + BIT
+    }
+    if (aggregate_for_result(cur))
+    {
+      my_error(ER_CANT_AGGREGATE_2TYPES, MYF(0),
+               type_handler()->name().ptr(), cur->name().ptr(), funcname);
+      return true;
     }
-    res= Field::field_type_merge(res, cur);
     unsigned_count+= items[i]->unsigned_flag;
   }
-  switch (res) {
+  switch (field_type()) {
   case MYSQL_TYPE_TINY:
   case MYSQL_TYPE_SHORT:
   case MYSQL_TYPE_LONG:
@@ -221,12 +227,12 @@ enum_field_types agg_field_type(Item **items, uint nitems,
         If all arguments are of INT-alike type but have different
         unsigned_flag, then convert to DECIMAL.
       */
-      return MYSQL_TYPE_NEWDECIMAL;
+      set_handler(&type_handler_newdecimal);
     }
   default:
     break;
   }
-  return res;
+  return false;
 }
 
 /*
@@ -3108,8 +3114,9 @@ void Item_func_case::fix_length_and_dec()
   
   if (else_expr_num != -1)
     agg[nagg++]= args[else_expr_num];
-  
-  set_handler_by_field_type(agg_field_type(agg, nagg, true));
+
+  if (aggregate_for_result(func_name(), agg, nagg, true))
+    return;
 
   if (Item_func_case::result_type() == STRING_RESULT)
   {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 6a59754..45d1f07 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -973,8 +973,8 @@ class Item_func_coalesce :public Item_func_hybrid_field_type
   bool date_op(MYSQL_TIME *ltime,uint fuzzydate);
   void fix_length_and_dec()
   {
-    set_handler_by_field_type(agg_field_type(args, arg_count, true));
-    fix_attributes(args, arg_count);
+    if (!aggregate_for_result(func_name(), args, arg_count, true))
+      fix_attributes(args, arg_count);
   }
   const char *func_name() const { return "coalesce"; }
   table_map not_null_tables() const { return 0; }
@@ -993,8 +993,8 @@ class Item_func_case_abbreviation2 :public Item_func_hybrid_field_type
 protected:
   void fix_length_and_dec2(Item **items)
   {
-    set_handler_by_field_type(agg_field_type(items, 2, true));
-    fix_attributes(items, 2);
+    if (!aggregate_for_result(func_name(), items, 2, true))
+      fix_attributes(items, 2);
   }
   uint decimal_precision2(Item **args) const;
 public:
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 90bec7b..a3ec502 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -2726,6 +2726,8 @@ void Item_func_min_max::fix_length_and_dec()
     break;
 
   case STRING_RESULT:
+    if (aggregate_for_result(func_name(), args, arg_count, false))
+      return;
     /*
       All arguments are of string-alike types:
         CHAR, VARCHAR, TEXT, BINARY, VARBINARY, BLOB, SET, ENUM
@@ -2733,7 +2735,6 @@ void Item_func_min_max::fix_length_and_dec()
     */
     agg_arg_charsets_for_string_result_with_comparison(collation,
                                                        args, arg_count);
-    set_handler_by_field_type(agg_field_type(args, arg_count, false));
     break;
 
   case INT_RESULT:
@@ -2762,7 +2763,8 @@ void Item_func_min_max::fix_length_and_dec()
         Treat BIT as LONGLONG when aggregating to non-BIT types.
         Possible final type: TINY, SHORT, LONG, LONGLONG, INT24, YEAR, BIT.
       */
-      set_handler_by_field_type(agg_field_type(args, arg_count, true));
+      if (aggregate_for_result(func_name(), args, arg_count, true))
+        return;
     }
     break;
 
@@ -2785,7 +2787,7 @@ void Item_func_min_max::fix_length_and_dec()
     /*
       Set type to DOUBLE, as Item_func::create_tmp_field() does not
       distinguish between DOUBLE and FLOAT and always creates Field_double.
-      Perhaps we should eventually change this to use agg_field_type() here,
+      Perhaps we should eventually change this to use aggregate_for_result()
       and fix Item_func::create_tmp_field() to create Field_float when possible.
     */
     set_handler_by_field_type(MYSQL_TYPE_DOUBLE);
diff --git a/sql/item_func.h b/sql/item_func.h
index 2ec3297..456d1c8 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -2531,8 +2531,6 @@ class Item_func_last_value :public Item_func
 Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name,
                      LEX_STRING component);
 extern bool check_reserved_words(LEX_STRING *name);
-extern enum_field_types agg_field_type(Item **items, uint nitems,
-                                       bool treat_bit_as_number);
 Item *find_date_time_item(Item **args, uint nargs, uint col);
 double my_double_round(double value, longlong dec, bool dec_unsigned,
                        bool truncate);
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 8f81d23..1028e8e 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7414,3 +7414,5 @@ ER_BINLOG_NON_SUPPORTED_BULK
         eng "Only row based replication supported for bulk operations"
 ER_BINLOG_UNCOMPRESS_ERROR
         eng "Uncompress the compressed binlog failed"
+ER_CANT_AGGREGATE_2TYPES
+        eng "Illegal parameter data types %s and %s for operation '%s'"
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index fc34c7c..de72830 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -25,7 +25,6 @@ static Type_handler_short       type_handler_short;
 static Type_handler_long        type_handler_long;
 static Type_handler_int24       type_handler_int24;
 static Type_handler_year        type_handler_year;
-static Type_handler_bit         type_handler_bit;
 static Type_handler_float       type_handler_float;
 static Type_handler_double      type_handler_double;
 static Type_handler_time        type_handler_time;
@@ -54,6 +53,39 @@ Type_handler_varchar     type_handler_varchar;
 Type_handler_longlong    type_handler_longlong;
 Type_handler_newdecimal  type_handler_newdecimal;
 Type_handler_datetime    type_handler_datetime;
+Type_handler_bit         type_handler_bit;
+
+
+Type_aggregator type_aggregator_for_result;
+
+
+class Static_data_initializer
+{
+public:
+  static Static_data_initializer m_singleton;
+  Static_data_initializer()
+  {
+#ifdef HAVE_SPATIAL
+    type_aggregator_for_result.add(&type_handler_geometry,
+                                   &type_handler_null,
+                                   &type_handler_geometry);
+    type_aggregator_for_result.add(&type_handler_geometry,
+                                   &type_handler_geometry,
+                                   &type_handler_geometry);
+    type_aggregator_for_result.add(&type_handler_geometry,
+                                   &type_handler_blob,
+                                   &type_handler_long_blob);
+    type_aggregator_for_result.add(&type_handler_geometry,
+                                   &type_handler_varchar,
+                                   &type_handler_long_blob);
+    type_aggregator_for_result.add(&type_handler_geometry,
+                                   &type_handler_string,
+                                   &type_handler_long_blob);
+#endif
+  }
+};
+
+Static_data_initializer Static_data_initializer::m_singleton;
 
 
 /**
@@ -123,6 +155,23 @@ Type_handler_hybrid_field_type::Type_handler_hybrid_field_type()
 }
 
 
+bool
+Type_handler_hybrid_field_type::aggregate_for_result(const Type_handler *other)
+{
+  if (m_type_handler->is_traditional_type() && other->is_traditional_type())
+  {
+    m_type_handler=
+      Type_handler::aggregate_for_result_traditional(m_type_handler, other);
+    return false;
+  }
+  other= type_aggregator_for_result.find_handler(m_type_handler, other);
+  if (!other)
+    return true;
+  m_type_handler= other;
+  return false;
+}
+
+
 /**
   Collect built-in data type handlers for comparison.
   This method is very similar to item_cmp_type() defined in item.cc.
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 6a694df..bbb75a8 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -22,6 +22,7 @@
 
 
 #include "mysqld.h"
+#include "sql_array.h"
 
 class Field;
 class Item;
@@ -218,6 +219,19 @@ class Type_std_attributes
 };
 
 
+class Name: private LEX_CSTRING
+{
+public:
+  Name(const char *str_arg, uint length_arg)
+  {
+    LEX_CSTRING::str= str_arg;
+    LEX_CSTRING::length= length_arg;
+  }
+  const char *ptr() const { return LEX_CSTRING::str; }
+  uint length() const { return LEX_CSTRING::length; }
+};
+
+
 class Type_handler
 {
 protected:
@@ -239,6 +253,11 @@ class Type_handler
     DBUG_ASSERT(type != TIME_RESULT);
     return get_handler_by_cmp_type(type);
   }
+  static const
+  Type_handler *aggregate_for_result_traditional(const Type_handler *h1,
+                                                 const Type_handler *h2);
+
+  virtual const Name name() const= 0;
   virtual enum_field_types field_type() const= 0;
   virtual enum_field_types real_field_type() const { return field_type(); }
   virtual Item_result result_type() const= 0;
@@ -249,6 +268,14 @@ class Type_handler
   { return this; }
   virtual ~Type_handler() {}
   /**
+    Determines MariaDB traditional data types that always present
+    in the server.
+  */
+  virtual bool is_traditional_type() const
+  {
+    return true;
+  }
+  /**
     Makes a temporary table Field to handle numeric aggregate functions,
     e.g. SUM(DISTINCT expr), AVG(DISTINCT expr), etc.
   */
@@ -336,6 +363,7 @@ class Type_handler_row: public Type_handler
 {
 public:
   virtual ~Type_handler_row() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("row")); }
   enum_field_types field_type() const
   {
     DBUG_ASSERT(0);
@@ -648,6 +676,7 @@ class Type_handler_tiny: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_tiny() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("tinyint")); }
   enum_field_types field_type() const { return MYSQL_TYPE_TINY; }
   Field *make_conversion_table_field(TABLE *TABLE, uint metadata,
                                      const Field *target) const;
@@ -658,6 +687,7 @@ class Type_handler_short: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_short() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("shortint")); }
   enum_field_types field_type() const { return MYSQL_TYPE_SHORT; }
   Field *make_conversion_table_field(TABLE *TABLE, uint metadata,
                                      const Field *target) const;
@@ -668,6 +698,7 @@ class Type_handler_long: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_long() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("int")); }
   enum_field_types field_type() const { return MYSQL_TYPE_LONG; }
   Field *make_conversion_table_field(TABLE *TABLE, uint metadata,
                                      const Field *target) const;
@@ -678,6 +709,7 @@ class Type_handler_longlong: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_longlong() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("bigint")); }
   enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; }
   Field *make_conversion_table_field(TABLE *TABLE, uint metadata,
                                      const Field *target) const;
@@ -688,6 +720,7 @@ class Type_handler_int24: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_int24() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("mediumint")); }
   enum_field_types field_type() const { return MYSQL_TYPE_INT24; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -698,6 +731,7 @@ class Type_handler_year: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_year() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("year")); }
   enum_field_types field_type() const { return MYSQL_TYPE_YEAR; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -708,6 +742,7 @@ class Type_handler_bit: public Type_handler_int_result
 {
 public:
   virtual ~Type_handler_bit() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("bit")); }
   enum_field_types field_type() const { return MYSQL_TYPE_BIT; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -718,6 +753,7 @@ class Type_handler_float: public Type_handler_real_result
 {
 public:
   virtual ~Type_handler_float() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("float")); }
   enum_field_types field_type() const { return MYSQL_TYPE_FLOAT; }
   Field *make_num_distinct_aggregator_field(MEM_ROOT *, const Item *) const;
   Field *make_conversion_table_field(TABLE *, uint metadata,
@@ -729,6 +765,7 @@ class Type_handler_double: public Type_handler_real_result
 {
 public:
   virtual ~Type_handler_double() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("double")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DOUBLE; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -739,6 +776,7 @@ class Type_handler_time_common: public Type_handler_temporal_result
 {
 public:
   virtual ~Type_handler_time_common() { }
+  const Name name() const { return Name(C_STRING_WITH_LEN("time")); }
   enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
   int Item_save_in_field(Item *item, Field *field, bool no_conversions) const;
   in_vector *make_in_vector(THD *, const Item_func_in *, uint nargs) const;
@@ -777,6 +815,7 @@ class Type_handler_date: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_date() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("date")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -787,6 +826,7 @@ class Type_handler_newdate: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_newdate() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("date")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -797,6 +837,7 @@ class Type_handler_datetime: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_datetime() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("datetime")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -807,6 +848,7 @@ class Type_handler_datetime2: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_datetime2() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("datetime")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
   enum_field_types real_field_type() const { return MYSQL_TYPE_DATETIME2; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
@@ -818,6 +860,7 @@ class Type_handler_timestamp: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_timestamp() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("timestamp")); }
   enum_field_types field_type() const { return MYSQL_TYPE_TIMESTAMP; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -828,6 +871,7 @@ class Type_handler_timestamp2: public Type_handler_temporal_with_date
 {
 public:
   virtual ~Type_handler_timestamp2() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("timestamp")); }
   enum_field_types field_type() const { return MYSQL_TYPE_TIMESTAMP; }
   enum_field_types real_field_type() const { return MYSQL_TYPE_TIMESTAMP2; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
@@ -839,6 +883,7 @@ class Type_handler_olddecimal: public Type_handler_decimal_result
 {
 public:
   virtual ~Type_handler_olddecimal() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("decimal")); }
   enum_field_types field_type() const { return MYSQL_TYPE_DECIMAL; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -849,6 +894,7 @@ class Type_handler_newdecimal: public Type_handler_decimal_result
 {
 public:
   virtual ~Type_handler_newdecimal() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("decimal")); }
   enum_field_types field_type() const { return MYSQL_TYPE_NEWDECIMAL; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -859,6 +905,7 @@ class Type_handler_null: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_null() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("null")); }
   enum_field_types field_type() const { return MYSQL_TYPE_NULL; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -869,6 +916,7 @@ class Type_handler_string: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_string() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("char")); }
   enum_field_types field_type() const { return MYSQL_TYPE_STRING; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -879,6 +927,7 @@ class Type_handler_varchar: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_varchar() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("varchar")); }
   enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -889,6 +938,7 @@ class Type_handler_tiny_blob: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_tiny_blob() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("tinyblob")); }
   enum_field_types field_type() const { return MYSQL_TYPE_TINY_BLOB; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -899,6 +949,7 @@ class Type_handler_medium_blob: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_medium_blob() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("mediumblob")); }
   enum_field_types field_type() const { return MYSQL_TYPE_MEDIUM_BLOB; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -909,6 +960,7 @@ class Type_handler_long_blob: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_long_blob() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("longblob")); }
   enum_field_types field_type() const { return MYSQL_TYPE_LONG_BLOB; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -919,6 +971,7 @@ class Type_handler_blob: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_blob() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("blob")); }
   enum_field_types field_type() const { return MYSQL_TYPE_BLOB; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
@@ -930,9 +983,14 @@ class Type_handler_geometry: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_geometry() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("geometry")); }
   enum_field_types field_type() const { return MYSQL_TYPE_GEOMETRY; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
                                      const Field *target) const;
+  bool is_traditional_type() const
+  {
+    return false;
+  }
 };
 #endif
 
@@ -941,6 +999,7 @@ class Type_handler_enum: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_enum() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("enum")); }
   enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
   virtual enum_field_types real_field_type() const { return MYSQL_TYPE_ENUM; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
@@ -952,6 +1011,7 @@ class Type_handler_set: public Type_handler_string_result
 {
 public:
   virtual ~Type_handler_set() {}
+  const Name name() const { return Name(C_STRING_WITH_LEN("set")); }
   enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
   virtual enum_field_types real_field_type() const { return MYSQL_TYPE_SET; }
   Field *make_conversion_table_field(TABLE *, uint metadata,
@@ -1021,6 +1081,9 @@ class Type_handler_hybrid_field_type
   }
   void aggregate_for_comparison(const Type_handler *other);
   bool aggregate_for_comparison(Item **items, uint nitems);
+  bool aggregate_for_result(const Type_handler *other);
+  bool aggregate_for_result(const char *funcname,
+                            Item **item, uint nitems, bool treat_bit_as_number);
 };
 
 
@@ -1044,5 +1107,57 @@ extern Type_handler_varchar type_handler_varchar;
 extern Type_handler_longlong type_handler_longlong;
 extern Type_handler_newdecimal type_handler_newdecimal;
 extern Type_handler_datetime type_handler_datetime;
+extern Type_handler_longlong type_handler_longlong;
+extern Type_handler_bit type_handler_bit;
+
+class Type_aggregator
+{
+  class Pair
+  {
+  public:
+    const Type_handler *m_handler1;
+    const Type_handler *m_handler2;
+    const Type_handler *m_result;
+    Pair() { }
+    Pair(const Type_handler *handler1,
+         const Type_handler *handler2,
+         const Type_handler *result)
+     :m_handler1(handler1), m_handler2(handler2), m_result(result)
+    { }
+    bool eq(const Type_handler *handler1, const Type_handler *handler2) const
+    {
+      return m_handler1 == handler1 && m_handler2 == handler2;
+    }
+  };
+  Dynamic_array<Pair> m_array;
+  const Pair* find_pair(const Type_handler *handler1,
+                        const Type_handler *handler2) const
+  {
+    for (uint i= 0; i < m_array.elements(); i++)
+    {
+      const Pair& el= m_array.at(i);
+      if (el.eq(handler1, handler2) || el.eq(handler2, handler1))
+        return &el;
+    }
+    return NULL;
+  }
+public:
+  Type_aggregator()
+  { }
+  bool add(const Type_handler *handler1,
+           const Type_handler *handler2,
+           const Type_handler *result)
+  {
+    return m_array.append(Pair(handler1, handler2, result));
+  }
+  const Type_handler *find_handler(const Type_handler *handler1,
+                                   const Type_handler *handler2) const
+  {
+    const Pair* el= find_pair(handler1, handler2);
+    return el ? el->m_result : NULL;
+  }
+};
+
+extern Type_aggregator type_aggregator_for_result;
 
 #endif /* SQL_TYPE_H_INCLUDED */

Follow ups

References