MDEV-9217 Split Item::tmp_table_field_from_field_type() into virtual methods in Type_handler

- Adding Type_handler::make_table_field() and moving pieces of the code
  from Item::tmp_table_field_from_field_type() to virtual implementations
  for various type handlers.

- Adding a new Type_all_attributes, to access to Item's extended
  attributes, such as decimal_precision() and geometry_type().

- Adding a new class Record_addr, to pass record related information
  to Type_handler methods (ptr, null_ptr and null_bit) as a single structure.
  Note, later it will possibly be extended for BIT-alike field purposes,
  by adding new members (bit_ptr_arg, bit_ofs_arg).

- Moving the code from Field_new_decimal::create_from_item()
  to Type_handler_newdecimal::make_table_field().

- Removing Field_new_decimal() and Field_geom() helper constructor
  variants that were used for temporary field creation.

- Adding Item_field::type_handler(), Field::type_handler() and
  Field_blob::type_handler() to return correct type handlers for
  blob variants, according to Field_blob::packlength.

- Adding Type_handler_blob_common, as a common parent for
  Type_handler_tiny_blob, Type_handler_blob, Type_handler_medium_blob
  and Type_handler_long_blob.

- Implementing Type_handler_blob_common::Item_hybrid_func_fix_attributes().

  It's needed for cases when TEXT variants of different character sets are mixed
  in LEAST, GREATEST, CASE and its abreviations (IF, IFNULL, COALESCE), e.g.:
      CREATE TABLE t1 (
        a TINYTEXT CHARACTER SET latin1,
        b TINYTEXT CHARACTER SET utf8
      );
      CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1;
  Type handler aggregation returns TINYTEXT as a common data type
  for the two columns. But as conversion from latin1 to utf8
  happens for "a", the maximum possible length of "a" grows from 255 to 255*3.
  Type_handler_blob_common::Item_hybrid_func_fix_attributes() makes sure
  to update the blob type handler according to max_length.

- Adding Type_handler::blob_type_handler(uint max_octet_length).

- Adding a few m_type_aggregator_for_result.add() pairs, because
  now Item_xxx::type_handler() can return pointers to type_handler_tiny_blob,
  type_handler_blob, type_handler_medium_blob, type_handler_long_blob.
  Before the patch only type_handler_blob was possible result of type_handler().

- Making type_handler_tiny_blob, type_handler_blob, type_handler_medium_blob,
  type_handler_long_blob public.

- Removing the condition in Item_sum_avg::create_tmp_field()
  checking Item_sum_avg::result_type() against DECIMAL_RESULT.
  Now both REAL_RESULT and DECIMAL_RESULT are symmetrically handled
  by tmp_table_field_from_field_type().

- Removing Item_geometry_func::create_field_for_create_select(),
  as the inherited version perfectly works.

- Fixing Item_func_as_wkb::field_type() to return MYSQL_TYPE_LONG_BLOB
  rather than MYSQL_TYPE_BLOB. It's needed to make sure that
  tmp_table_field_from_field_type() creates a LONGBLOB field for AsWKB().

- Fixing Item_func_as_wkt::fix_length_and_dec() to set max_length to
  UINT32_MAX rather than MAX_BLOB_WIDTH, to make sure that
  tmp_table_field_from_field_type() creates a LONGTEXT field for AsWKT().

- Removing Item_func_set_user_var::create_field_for_create_select(),
  as the inherited version works fine.

- Adding Item_func_get_user_var::create_field_for_create_select() to
  make sure that "CREATE TABLE t1 AS SELECT @string_user variable"
  always creates a field of LONGTEXT/LONGBLOB type.

- Item_func_ifnull::create_field_for_create_select()
  behavior has changed. Before the patch it passed set_blob_packflag=false,
  which meant to create LONGBLOB for all blob variants.
  Now it takes into account max_length, which gives better column
  data types for:
    CREATE TABLE t2 AS SELECT IFNULL(blob_column1, blob_column2) FROM t1;

- Fixing Item_func_nullif::fix_length_and_dec() to use
  set_handler(args[2]->type_handler()) instead of
  set_handler_by_field_type(args[2]->field_type()).
  This is needed to distinguish between BLOB variants.

- Implementing Item_blob::type_handler(), to make sure to create
  proper BLOB field variant, according to max_length, for queries like:
    CREATE TABLE t1 AS
      SELECT some_blob_field FROM INFORMATION_SCHEMA.SOME_TABLE;

- Fixing Item_field::real_type_handler() to make sure that
  the code aggregating fields for UNION gets a proper BLOB
  variant type handler from fields.

- Adding a special code into Item_type_holder::make_field_by_type(),
  to make sure that after aggregating field types it also properly
  takes into account max_length when mixing TEXT variants of different
  character sets and chooses a proper TEXT variant:
      CREATE TABLE t1 (
        a TINYTEXT CHARACTER SET latin1,
        b TINYTEXT CHARACTER SET utf8
      );
      CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;

- Adding tests, for better coverage of IFNULL, NULLIF, UNION.

- The fact that tmp_table_field_from_field_type() now takes
  into account BLOB variants (instead of always creating LONGBLOB),
  tests results for WEIGHT_STRING() and NULLIF() and UNION
  have become more precise.

10 files changed, 487 insertions, 38 deletions

diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index 1f032faaa40..1a3289b2618 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -488,13 +488,35 @@ d	date	YES		NULL
 e	varchar(1)	YES		NULL	
 f	datetime	YES		NULL	
 g	time	YES		NULL	
-h	longblob	YES		NULL	
+h	blob	YES		NULL	
 dd	time	YES		NULL	
 select * from t2;
 a	b	c	d	e	f	g	h	dd
 1	-7	7	2000-01-01	b	2000-01-01 00:00:00	05:04:03	yet another binary data	02:00:00
 2	-2	2	1825-12-14	a	2003-01-01 03:02:01	04:03:02	binary data	02:00:00
 drop table t1, t2;
+CREATE TABLE t1 (
+c_tinytext tinytext,
+c_text text,
+c_mediumtext mediumtext,
+c_longtext longtext
+);
+CREATE TABLE t2 AS SELECT
+ifnull(c_tinytext,   CAST('yet another binary data' AS BINARY)),
+ifnull(c_text,       CAST('yet another binary data' AS BINARY)),
+ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY)),
+ifnull(c_longtext,   CAST('yet another binary data' AS BINARY))
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `ifnull(c_tinytext,   CAST('yet another binary data' AS BINARY))` tinyblob DEFAULT NULL,
+  `ifnull(c_text,       CAST('yet another binary data' AS BINARY))` blob DEFAULT NULL,
+  `ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY))` mediumblob DEFAULT NULL,
+  `ifnull(c_longtext,   CAST('yet another binary data' AS BINARY))` longblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
 create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
 create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
 show create table t2;
diff --git a/mysql-test/r/func_weight_string.result b/mysql-test/r/func_weight_string.result
index 04fd9962218..5fa78c861a9 100644
--- a/mysql-test/r/func_weight_string.result
+++ b/mysql-test/r/func_weight_string.result
@@ -57,7 +57,7 @@ create table t1 select weight_string(repeat('t',66000)) as w;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `w` longblob DEFAULT NULL
+  `w` mediumblob DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 select weight_string(NULL);
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 77150ee3142..43de7d2e7d6 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -709,7 +709,7 @@ def	test	t1	t1	g	g	255	4294967295	0	Y	144	0	63
 g
 select asbinary(g) from t1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					asbinary(g)	252	4294967295	0	Y	128	0	63
+def					asbinary(g)	251	4294967295	0	Y	128	0	63
 asbinary(g)
 drop table t1;
 create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b));
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result
index 0ff5c3243d8..8bbdfb2f90b 100644
--- a/mysql-test/r/null.result
+++ b/mysql-test/r/null.result
@@ -575,8 +575,14 @@ c_float      FLOAT,
 c_double     DOUBLE,
 c_decimal103 DECIMAL(10,3),
 c_varchar10  VARCHAR(10),
+c_tinytext   TINYTEXT,
 c_text       TEXT,
+c_mediumtext MEDIUMTEXT,
+c_longtext   LONGTEXT,
+c_tinyblob   TINYBLOB,
 c_blob       BLOB,
+c_mediumblob MEDIUMBLOB,
+c_longblob   LONGBLOB,
 c_enum       ENUM('one','two','tree'),
 c_datetime3  DATETIME(3),
 c_timestamp3 TIMESTAMP(3),
@@ -899,6 +905,45 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
+NULLIF(c_tinytext,  1),
+NULLIF(c_tinytext,  c_smallint),
+NULLIF(c_tinytext,  c_tinyint),
+NULLIF(c_tinytext,  c_int),
+NULLIF(c_tinytext,  c_bigint),
+NULLIF(c_tinytext,  c_float),
+NULLIF(c_tinytext,  c_double),
+NULLIF(c_tinytext,  c_decimal103),
+NULLIF(c_tinytext,  c_varchar10),
+NULLIF(c_tinytext,  c_text),
+NULLIF(c_tinytext,  c_blob),
+NULLIF(c_tinytext,  c_enum),
+NULLIF(c_tinytext,  c_datetime3),
+NULLIF(c_tinytext,  c_timestamp3),
+NULLIF(c_tinytext,  c_date),
+NULLIF(c_tinytext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_tinytext,  1)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_smallint)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_tinyint)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_int)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_bigint)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_float)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_double)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_decimal103)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_varchar10)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_text)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_blob)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_enum)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_datetime3)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_timestamp3)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_date)` tinytext DEFAULT NULL,
+  `NULLIF(c_tinytext,  c_time)` tinytext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
 NULLIF(c_text,  1),
 NULLIF(c_text,  c_smallint),
 NULLIF(c_text,  c_tinyint),
@@ -919,22 +964,139 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `NULLIF(c_text,  1)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_smallint)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_tinyint)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_int)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_bigint)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_float)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_double)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_decimal103)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_varchar10)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_text)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_blob)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_enum)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_datetime3)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_timestamp3)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_date)` longtext DEFAULT NULL,
-  `NULLIF(c_text,  c_time)` longtext DEFAULT NULL
+  `NULLIF(c_text,  1)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_smallint)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_tinyint)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_int)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_bigint)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_float)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_double)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_decimal103)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_varchar10)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_text)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_blob)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_enum)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_datetime3)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_timestamp3)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_date)` text DEFAULT NULL,
+  `NULLIF(c_text,  c_time)` text DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+NULLIF(c_mediumtext,  1),
+NULLIF(c_mediumtext,  c_smallint),
+NULLIF(c_mediumtext,  c_tinyint),
+NULLIF(c_mediumtext,  c_int),
+NULLIF(c_mediumtext,  c_bigint),
+NULLIF(c_mediumtext,  c_float),
+NULLIF(c_mediumtext,  c_double),
+NULLIF(c_mediumtext,  c_decimal103),
+NULLIF(c_mediumtext,  c_varchar10),
+NULLIF(c_mediumtext,  c_text),
+NULLIF(c_mediumtext,  c_blob),
+NULLIF(c_mediumtext,  c_enum),
+NULLIF(c_mediumtext,  c_datetime3),
+NULLIF(c_mediumtext,  c_timestamp3),
+NULLIF(c_mediumtext,  c_date),
+NULLIF(c_mediumtext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_mediumtext,  1)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_smallint)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_tinyint)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_int)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_bigint)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_float)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_double)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_decimal103)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_varchar10)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_text)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_blob)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_enum)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_datetime3)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_timestamp3)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_date)` mediumtext DEFAULT NULL,
+  `NULLIF(c_mediumtext,  c_time)` mediumtext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+NULLIF(c_longtext,  1),
+NULLIF(c_longtext,  c_smallint),
+NULLIF(c_longtext,  c_tinyint),
+NULLIF(c_longtext,  c_int),
+NULLIF(c_longtext,  c_bigint),
+NULLIF(c_longtext,  c_float),
+NULLIF(c_longtext,  c_double),
+NULLIF(c_longtext,  c_decimal103),
+NULLIF(c_longtext,  c_varchar10),
+NULLIF(c_longtext,  c_text),
+NULLIF(c_longtext,  c_blob),
+NULLIF(c_longtext,  c_enum),
+NULLIF(c_longtext,  c_datetime3),
+NULLIF(c_longtext,  c_timestamp3),
+NULLIF(c_longtext,  c_date),
+NULLIF(c_longtext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_longtext,  1)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_smallint)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_tinyint)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_int)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_bigint)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_float)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_double)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_decimal103)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_varchar10)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_text)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_blob)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_enum)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_datetime3)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_timestamp3)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_date)` longtext DEFAULT NULL,
+  `NULLIF(c_longtext,  c_time)` longtext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+NULLIF(c_tinyblob,  1),
+NULLIF(c_tinyblob,  c_smallint),
+NULLIF(c_tinyblob,  c_tinyint),
+NULLIF(c_tinyblob,  c_int),
+NULLIF(c_tinyblob,  c_bigint),
+NULLIF(c_tinyblob,  c_float),
+NULLIF(c_tinyblob,  c_double),
+NULLIF(c_tinyblob,  c_decimal103),
+NULLIF(c_tinyblob,  c_varchar10),
+NULLIF(c_tinyblob,  c_text),
+NULLIF(c_tinyblob,  c_blob),
+NULLIF(c_tinyblob,  c_enum),
+NULLIF(c_tinyblob,  c_datetime3),
+NULLIF(c_tinyblob,  c_timestamp3),
+NULLIF(c_tinyblob,  c_date),
+NULLIF(c_tinyblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_tinyblob,  1)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_smallint)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_tinyint)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_int)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_bigint)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_float)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_double)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_decimal103)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_varchar10)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_text)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_blob)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_enum)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_datetime3)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_timestamp3)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_date)` tinyblob DEFAULT NULL,
+  `NULLIF(c_tinyblob,  c_time)` tinyblob DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
@@ -958,22 +1120,100 @@ FROM t1;
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `NULLIF(c_blob,  1)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_smallint)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_tinyint)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_int)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_bigint)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_float)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_double)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_decimal103)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_varchar10)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_text)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_blob)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_enum)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_datetime3)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_timestamp3)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_date)` longblob DEFAULT NULL,
-  `NULLIF(c_blob,  c_time)` longblob DEFAULT NULL
+  `NULLIF(c_blob,  1)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_smallint)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_tinyint)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_int)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_bigint)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_float)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_double)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_decimal103)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_varchar10)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_text)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_blob)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_enum)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_datetime3)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_timestamp3)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_date)` blob DEFAULT NULL,
+  `NULLIF(c_blob,  c_time)` blob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+NULLIF(c_mediumblob,  1),
+NULLIF(c_mediumblob,  c_smallint),
+NULLIF(c_mediumblob,  c_tinyint),
+NULLIF(c_mediumblob,  c_int),
+NULLIF(c_mediumblob,  c_bigint),
+NULLIF(c_mediumblob,  c_float),
+NULLIF(c_mediumblob,  c_double),
+NULLIF(c_mediumblob,  c_decimal103),
+NULLIF(c_mediumblob,  c_varchar10),
+NULLIF(c_mediumblob,  c_text),
+NULLIF(c_mediumblob,  c_blob),
+NULLIF(c_mediumblob,  c_enum),
+NULLIF(c_mediumblob,  c_datetime3),
+NULLIF(c_mediumblob,  c_timestamp3),
+NULLIF(c_mediumblob,  c_date),
+NULLIF(c_mediumblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_mediumblob,  1)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_smallint)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_tinyint)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_int)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_bigint)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_float)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_double)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_decimal103)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_varchar10)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_text)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_blob)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_enum)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_datetime3)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_timestamp3)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_date)` mediumblob DEFAULT NULL,
+  `NULLIF(c_mediumblob,  c_time)` mediumblob DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+NULLIF(c_longblob,  1),
+NULLIF(c_longblob,  c_smallint),
+NULLIF(c_longblob,  c_tinyint),
+NULLIF(c_longblob,  c_int),
+NULLIF(c_longblob,  c_bigint),
+NULLIF(c_longblob,  c_float),
+NULLIF(c_longblob,  c_double),
+NULLIF(c_longblob,  c_decimal103),
+NULLIF(c_longblob,  c_varchar10),
+NULLIF(c_longblob,  c_text),
+NULLIF(c_longblob,  c_blob),
+NULLIF(c_longblob,  c_enum),
+NULLIF(c_longblob,  c_datetime3),
+NULLIF(c_longblob,  c_timestamp3),
+NULLIF(c_longblob,  c_date),
+NULLIF(c_longblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `NULLIF(c_longblob,  1)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_smallint)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_tinyint)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_int)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_bigint)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_float)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_double)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_decimal103)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_varchar10)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_text)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_blob)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_enum)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_datetime3)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_timestamp3)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_date)` longblob DEFAULT NULL,
+  `NULLIF(c_longblob,  c_time)` longblob DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t2;
 CREATE TABLE t2 AS SELECT
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index fe456e2aa80..807a194e773 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -1449,6 +1449,28 @@ t2	CREATE TABLE `t2` (
   `f8` mediumtext CHARACTER SET utf8 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1, t2;
+CREATE TABLE t1
+(
+c_varchar varchar(1) character set utf8 collate utf8_general_ci,
+c_tinytext tinytext,
+c_text text,
+c_mediumtext mediumtext,
+c_longtext longtext
+);
+CREATE TABLE t2 AS
+SELECT c_tinytext, c_text, c_mediumtext, c_longtext FROM t1
+UNION
+SELECT c_varchar, c_varchar, c_varchar, c_varchar FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c_tinytext` text CHARACTER SET utf8 DEFAULT NULL,
+  `c_text` mediumtext CHARACTER SET utf8 DEFAULT NULL,
+  `c_mediumtext` longtext CHARACTER SET utf8 DEFAULT NULL,
+  `c_longtext` longtext CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
 (select avg(1)) union (select avg(1)) union (select avg(1)) union
diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result
index 31579a18ea0..3ab57babc43 100644
--- a/mysql-test/suite/innodb_gis/r/1.result
+++ b/mysql-test/suite/innodb_gis/r/1.result
@@ -681,7 +681,7 @@ def	test	t1	t1	g	g	255	4294967295	0	Y	144	0	63
 g
 select ST_asbinary(g) from t1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					ST_asbinary(g)	252	4294967295	0	Y	128	0	63
+def					ST_asbinary(g)	251	4294967295	0	Y	128	0	63
 ST_asbinary(g)
 drop table t1;
 create table t1 (a TEXT, b GEOMETRY NOT NULL, INDEX(b(5)));
diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result
index 4257413f816..beb411505f9 100644
--- a/mysql-test/suite/innodb_gis/r/gis.result
+++ b/mysql-test/suite/innodb_gis/r/gis.result
@@ -681,7 +681,7 @@ def	test	t1	t1	g	g	255	4294967295	0	Y	144	0	63
 g
 select ST_asbinary(g) from t1;
 Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
-def					ST_asbinary(g)	252	4294967295	0	Y	128	0	63
+def					ST_asbinary(g)	251	4294967295	0	Y	128	0	63
 ST_asbinary(g)
 drop table t1;
 create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b));
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 6461204f06e..bd89f220060 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -402,6 +402,22 @@ explain t2;
 select * from t2;
 drop table t1, t2;
 
+CREATE TABLE t1 (
+  c_tinytext tinytext,
+  c_text text,
+  c_mediumtext mediumtext,
+  c_longtext longtext
+);
+CREATE TABLE t2 AS SELECT
+  ifnull(c_tinytext,   CAST('yet another binary data' AS BINARY)),
+  ifnull(c_text,       CAST('yet another binary data' AS BINARY)),
+  ifnull(c_mediumtext, CAST('yet another binary data' AS BINARY)),
+  ifnull(c_longtext,   CAST('yet another binary data' AS BINARY))
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
 create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10));
 create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
 show create table t2;
diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test
index 3de35a74a73..403790356ce 100644
--- a/mysql-test/t/null.test
+++ b/mysql-test/t/null.test
@@ -414,8 +414,14 @@ CREATE TABLE t1
   c_double     DOUBLE,
   c_decimal103 DECIMAL(10,3),
   c_varchar10  VARCHAR(10),
+  c_tinytext   TINYTEXT,
   c_text       TEXT,
+  c_mediumtext MEDIUMTEXT,
+  c_longtext   LONGTEXT,
+  c_tinyblob   TINYBLOB,
   c_blob       BLOB,
+  c_mediumblob MEDIUMBLOB,
+  c_longblob   LONGBLOB,
   c_enum       ENUM('one','two','tree'),
   c_datetime3  DATETIME(3),
   c_timestamp3 TIMESTAMP(3),
@@ -597,6 +603,27 @@ SHOW CREATE TABLE t2;
 DROP TABLE t2;
 
 CREATE TABLE t2 AS SELECT
+  NULLIF(c_tinytext,  1),
+  NULLIF(c_tinytext,  c_smallint),
+  NULLIF(c_tinytext,  c_tinyint),
+  NULLIF(c_tinytext,  c_int),
+  NULLIF(c_tinytext,  c_bigint),
+  NULLIF(c_tinytext,  c_float),
+  NULLIF(c_tinytext,  c_double),
+  NULLIF(c_tinytext,  c_decimal103),
+  NULLIF(c_tinytext,  c_varchar10),
+  NULLIF(c_tinytext,  c_text),
+  NULLIF(c_tinytext,  c_blob),
+  NULLIF(c_tinytext,  c_enum),
+  NULLIF(c_tinytext,  c_datetime3),
+  NULLIF(c_tinytext,  c_timestamp3),
+  NULLIF(c_tinytext,  c_date),
+  NULLIF(c_tinytext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
   NULLIF(c_text,  1),
   NULLIF(c_text,  c_smallint),
   NULLIF(c_text,  c_tinyint),
@@ -617,7 +644,70 @@ FROM t1;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
 
-# QQ: this should probably create BLOB instead of LONGBLOB
+CREATE TABLE t2 AS SELECT
+  NULLIF(c_mediumtext,  1),
+  NULLIF(c_mediumtext,  c_smallint),
+  NULLIF(c_mediumtext,  c_tinyint),
+  NULLIF(c_mediumtext,  c_int),
+  NULLIF(c_mediumtext,  c_bigint),
+  NULLIF(c_mediumtext,  c_float),
+  NULLIF(c_mediumtext,  c_double),
+  NULLIF(c_mediumtext,  c_decimal103),
+  NULLIF(c_mediumtext,  c_varchar10),
+  NULLIF(c_mediumtext,  c_text),
+  NULLIF(c_mediumtext,  c_blob),
+  NULLIF(c_mediumtext,  c_enum),
+  NULLIF(c_mediumtext,  c_datetime3),
+  NULLIF(c_mediumtext,  c_timestamp3),
+  NULLIF(c_mediumtext,  c_date),
+  NULLIF(c_mediumtext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+  NULLIF(c_longtext,  1),
+  NULLIF(c_longtext,  c_smallint),
+  NULLIF(c_longtext,  c_tinyint),
+  NULLIF(c_longtext,  c_int),
+  NULLIF(c_longtext,  c_bigint),
+  NULLIF(c_longtext,  c_float),
+  NULLIF(c_longtext,  c_double),
+  NULLIF(c_longtext,  c_decimal103),
+  NULLIF(c_longtext,  c_varchar10),
+  NULLIF(c_longtext,  c_text),
+  NULLIF(c_longtext,  c_blob),
+  NULLIF(c_longtext,  c_enum),
+  NULLIF(c_longtext,  c_datetime3),
+  NULLIF(c_longtext,  c_timestamp3),
+  NULLIF(c_longtext,  c_date),
+  NULLIF(c_longtext,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+
+CREATE TABLE t2 AS SELECT
+  NULLIF(c_tinyblob,  1),
+  NULLIF(c_tinyblob,  c_smallint),
+  NULLIF(c_tinyblob,  c_tinyint),
+  NULLIF(c_tinyblob,  c_int),
+  NULLIF(c_tinyblob,  c_bigint),
+  NULLIF(c_tinyblob,  c_float),
+  NULLIF(c_tinyblob,  c_double),
+  NULLIF(c_tinyblob,  c_decimal103),
+  NULLIF(c_tinyblob,  c_varchar10),
+  NULLIF(c_tinyblob,  c_text),
+  NULLIF(c_tinyblob,  c_blob),
+  NULLIF(c_tinyblob,  c_enum),
+  NULLIF(c_tinyblob,  c_datetime3),
+  NULLIF(c_tinyblob,  c_timestamp3),
+  NULLIF(c_tinyblob,  c_date),
+  NULLIF(c_tinyblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
 CREATE TABLE t2 AS SELECT
   NULLIF(c_blob,  1),
   NULLIF(c_blob,  c_smallint),
@@ -639,6 +729,49 @@ FROM t1;
 SHOW CREATE TABLE t2;
 DROP TABLE t2;
 
+CREATE TABLE t2 AS SELECT
+  NULLIF(c_mediumblob,  1),
+  NULLIF(c_mediumblob,  c_smallint),
+  NULLIF(c_mediumblob,  c_tinyint),
+  NULLIF(c_mediumblob,  c_int),
+  NULLIF(c_mediumblob,  c_bigint),
+  NULLIF(c_mediumblob,  c_float),
+  NULLIF(c_mediumblob,  c_double),
+  NULLIF(c_mediumblob,  c_decimal103),
+  NULLIF(c_mediumblob,  c_varchar10),
+  NULLIF(c_mediumblob,  c_text),
+  NULLIF(c_mediumblob,  c_blob),
+  NULLIF(c_mediumblob,  c_enum),
+  NULLIF(c_mediumblob,  c_datetime3),
+  NULLIF(c_mediumblob,  c_timestamp3),
+  NULLIF(c_mediumblob,  c_date),
+  NULLIF(c_mediumblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+  NULLIF(c_longblob,  1),
+  NULLIF(c_longblob,  c_smallint),
+  NULLIF(c_longblob,  c_tinyint),
+  NULLIF(c_longblob,  c_int),
+  NULLIF(c_longblob,  c_bigint),
+  NULLIF(c_longblob,  c_float),
+  NULLIF(c_longblob,  c_double),
+  NULLIF(c_longblob,  c_decimal103),
+  NULLIF(c_longblob,  c_varchar10),
+  NULLIF(c_longblob,  c_text),
+  NULLIF(c_longblob,  c_blob),
+  NULLIF(c_longblob,  c_enum),
+  NULLIF(c_longblob,  c_datetime3),
+  NULLIF(c_longblob,  c_timestamp3),
+  NULLIF(c_longblob,  c_date),
+  NULLIF(c_longblob,  c_time)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+
 # QQ: this should probably create a ENUM column instead of VARCHAR(4)
 CREATE TABLE t2 AS SELECT
   NULLIF(c_enum,  1),
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index e0c011e3d20..fa07dc22dbb 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -889,6 +889,22 @@ create table t2 as select *, f6 as f8 from t1 union select *, f7 from t1;
 show create table t2;
 drop table t1, t2;
 
+CREATE TABLE t1
+(
+  c_varchar varchar(1) character set utf8 collate utf8_general_ci,
+  c_tinytext tinytext,
+  c_text text,
+  c_mediumtext mediumtext,
+  c_longtext longtext
+);
+CREATE TABLE t2 AS
+SELECT c_tinytext, c_text, c_mediumtext, c_longtext FROM t1
+UNION
+SELECT c_varchar, c_varchar, c_varchar, c_varchar FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+
 #
 # Bug#18175: Union select over 129 tables with a sum function fails.
 #