MDEV-8912 Wrong metadata or type for @c:=string_or_blob_field

11 files changed, 439 insertions, 113 deletions

diff --git a/mysql-test/r/func_hybrid_type.result b/mysql-test/r/func_hybrid_type.result
index 72e26c50274..95a8a8235d4 100644
--- a/mysql-test/r/func_hybrid_type.result
+++ b/mysql-test/r/func_hybrid_type.result
@@ -3231,5 +3231,170 @@ NULL
 DROP TABLE t2;
 DROP TABLE t1;
 #
+# MDEV-8912 Wrong metadata or type for @c:=string_or_blob_field
+#
+CREATE TABLE t1 (c1 TINYBLOB, c2 BLOB, c3 MEDIUMBLOB, c4 LONGBLOB);
+CREATE TABLE t2 AS
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2,
+@c3:=c3 AS c3,
+@c4:=c4 AS c4
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varbinary(255) DEFAULT NULL,
+  `c2` blob,
+  `c3` mediumblob,
+  `c4` longblob
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2,
+@c3:=c3 AS c3,
+@c4:=c4 AS c4
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	255	0	Y	128	31	63
+def					c2	252	65535	0	Y	128	31	63
+def					c3	250	16777215	0	Y	128	31	63
+def					c4	251	4294967295	0	Y	128	31	63
+c1	c2	c3	c4
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL,
+  `c2` varchar(255) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+c1	c2
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
+  `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c1:=c1 AS c1,
+@c2:=c2 AS c2
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+c1	c2
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+@c:=c1 AS c1,
+@c:=c2 AS c2,
+@c:=c3 AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL,
+  `c2` varchar(255) DEFAULT NULL,
+  `c3` text
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c:=c1 AS c1,
+@c:=c2 AS c2,
+@c:=c3 AS c3
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+def					c3	252	20000	0	Y	0	31	8
+c1	c2	c3
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+@c:=c1 AS c1,
+@c:=c2 AS c2,
+@c:=c3 AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
+  `c2` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
+  `c3` text CHARACTER SET utf8
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c:=c1 AS c1,
+@c:=c2 AS c2,
+@c:=c3 AS c3
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	31	8
+def					c2	253	255	0	Y	0	31	8
+def					c3	252	60000	0	Y	0	31	8
+c1	c2	c3
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+@c:=c1 AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c:=c1 AS c1
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	0	8
+c1
+DROP TABLE t2;
+DROP TABLE t1;
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+@c:=c1 AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT
+@c:=c1 AS c1
+FROM t1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def					c1	253	1	0	Y	0	0	8
+c1
+DROP TABLE t2;
+DROP TABLE t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/t/func_hybrid_type.test b/mysql-test/t/func_hybrid_type.test
index b4f995d7f76..047e5f7b72e 100644
--- a/mysql-test/t/func_hybrid_type.test
+++ b/mysql-test/t/func_hybrid_type.test
@@ -300,5 +300,139 @@ DROP TABLE t1;
 
 
 --echo #
+--echo # MDEV-8912 Wrong metadata or type for @c:=string_or_blob_field
+--echo #
+CREATE TABLE t1 (c1 TINYBLOB, c2 BLOB, c3 MEDIUMBLOB, c4 LONGBLOB);
+CREATE TABLE t2 AS
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2,
+  @c3:=c3 AS c3,
+  @c4:=c4 AS c4
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2,
+  @c3:=c3 AS c3,
+  @c4:=c4 AS c4
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 CHAR(1), c2 CHAR(255)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c1:=c1 AS c1,
+  @c2:=c2 AS c2
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+  @c:=c1 AS c1,
+  @c:=c2 AS c2,
+  @c:=c3 AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c:=c1 AS c1,
+  @c:=c2 AS c2,
+  @c:=c3 AS c3
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(1), c2 VARCHAR(255), c3 VARCHAR(20000)) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+  @c:=c1 AS c1,
+  @c:=c2 AS c2,
+  @c:=c3 AS c3
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c:=c1 AS c1,
+  @c:=c2 AS c2,
+  @c:=c3 AS c3
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET latin1;
+CREATE TABLE t2 AS
+SELECT
+  @c:=c1 AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c:=c1 AS c1
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 ENUM('a')) CHARACTER SET utf8;
+CREATE TABLE t2 AS
+SELECT
+  @c:=c1 AS c1
+FROM t1;
+SHOW CREATE TABLE t2;
+--disable_ps_protocol
+--enable_metadata
+SELECT
+  @c:=c1 AS c1
+FROM t1;
+--disable_metadata
+--enable_ps_protocol
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
diff --git a/sql/item.cc b/sql/item.cc
index 1e809d56340..4d07c3484f4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5565,7 +5565,9 @@ Field *Item::make_string_field(TABLE *table)
     \#    Created field
 */
 
-Field *Item::tmp_table_field_from_field_type(TABLE *table, bool fixed_length)
+Field *Item::tmp_table_field_from_field_type(TABLE *table,
+                                             bool fixed_length,
+                                             bool set_blob_packlength)
 {
   /*
     The field functions defines a field to be not null if null_ptr is not 0
@@ -5663,12 +5665,9 @@ Field *Item::tmp_table_field_from_field_type(TABLE *table, bool fixed_length)
   case MYSQL_TYPE_MEDIUM_BLOB:
   case MYSQL_TYPE_LONG_BLOB:
   case MYSQL_TYPE_BLOB:
-    if (this->type() == Item::TYPE_HOLDER)
-      field= new (mem_root)
-        Field_blob(max_length, maybe_null, name, collation.collation, 1);
-    else
-      field= new (mem_root)
-        Field_blob(max_length, maybe_null, name, collation.collation);
+    field= new (mem_root)
+           Field_blob(max_length, maybe_null, name,
+                      collation.collation, set_blob_packlength);
     break;					// Blob handled outside of case
 #ifdef HAVE_SPATIAL
   case MYSQL_TYPE_GEOMETRY:
@@ -9551,7 +9550,7 @@ Field *Item_type_holder::make_field_by_type(TABLE *table)
   default:
     break;
   }
-  return tmp_table_field_from_field_type(table, 0);
+  return tmp_table_field_from_field_type(table, false, true);
 }
 
 
diff --git a/sql/item.h b/sql/item.h
index b8cb0ce5fd6..4c1b23fc8d6 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1023,6 +1023,8 @@ public:
   virtual Field *get_tmp_table_field() { return 0; }
   /* This is also used to create fields in CREATE ... SELECT: */
   virtual Field *tmp_table_field(TABLE *t_arg) { return 0; }
+  virtual Field *create_field_for_create_select(THD *thd, TABLE *table);
+  virtual Field *create_field_for_schema(THD *thd, TABLE *table);
   virtual const char *full_name() const { return name ? name : "???"; }
   const char *field_name_or_null()
   { return real_item()->type() == Item::FIELD_ITEM ? name : NULL; }
@@ -1626,7 +1628,9 @@ public:
   // used in row subselects to get value of elements
   virtual void bring_value() {}
 
-  Field *tmp_table_field_from_field_type(TABLE *table, bool fixed_length);
+  Field *tmp_table_field_from_field_type(TABLE *table,
+                                         bool fixed_length,
+                                         bool set_blob_packlength);
   virtual Item_field *field_for_view_update() { return 0; }
 
   virtual Item *neg_transformer(THD *thd) { return NULL; }
@@ -3190,6 +3194,8 @@ public:
   { max_length= length; }
   enum Type type() const { return TYPE_HOLDER; }
   enum_field_types field_type() const { return MYSQL_TYPE_BLOB; }
+  Field *create_field_for_schema(THD *thd, TABLE *table)
+  { return tmp_table_field_from_field_type(table, false, true); }
 };
 
 
@@ -3384,7 +3390,7 @@ public:
   my_decimal *val_decimal(my_decimal *decimal_value)
   { return  val_decimal_from_date(decimal_value); }
   Field *tmp_table_field(TABLE *table)
-  { return tmp_table_field_from_field_type(table, 0); }
+  { return tmp_table_field_from_field_type(table, false, false); }
   int save_in_field(Field *field, bool no_conversions)
   { return save_date_in_field(field); }
 };
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index d26f0a4527e..764ca000e99 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2266,7 +2266,7 @@ uint Item_func_case_abbreviation2::decimal_precision2(Item **args) const
 
 Field *Item_func_ifnull::tmp_table_field(TABLE *table)
 {
-  return tmp_table_field_from_field_type(table, 0);
+  return tmp_table_field_from_field_type(table, false, false);
 }
 
 double
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 725f712546e..ed6722fac4a 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4729,15 +4729,15 @@ user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
 void Item_func_set_user_var::cleanup()
 {
   Item_func::cleanup();
-  entry= NULL;
+  m_var_entry= NULL;
 }
 
 
 bool Item_func_set_user_var::set_entry(THD *thd, bool create_if_not_exists)
 {
-  if (entry && thd->thread_id == entry_thread_id)
+  if (m_var_entry && thd->thread_id == entry_thread_id)
     goto end; // update entry->update_query_id for PS
-  if (!(entry= get_variable(&thd->user_vars, name, create_if_not_exists)))
+  if (!(m_var_entry= get_variable(&thd->user_vars, name, create_if_not_exists)))
   {
     entry_thread_id= 0;
     return TRUE;
@@ -4749,7 +4749,7 @@ bool Item_func_set_user_var::set_entry(THD *thd, bool create_if_not_exists)
      is different from query_id).
   */
 end:
-  entry->update_query_id= thd->query_id;
+  m_var_entry->update_query_id= thd->query_id;
   return FALSE;
 }
 
@@ -4781,11 +4781,12 @@ bool Item_func_set_user_var::fix_fields(THD *thd, Item **ref)
     and the variable has previously been initialized.
   */
   null_item= (args[0]->type() == NULL_ITEM);
-  if (!entry->charset() || !null_item)
-    entry->set_charset(args[0]->collation.derivation == DERIVATION_NUMERIC ?
-                       default_charset() : args[0]->collation.collation);
-  collation.set(entry->charset(), DERIVATION_IMPLICIT);
-  cached_result_type= args[0]->result_type();
+  if (!m_var_entry->charset() || !null_item)
+    m_var_entry->set_charset(args[0]->collation.derivation == DERIVATION_NUMERIC ?
+                             default_charset() : args[0]->collation.collation);
+  collation.set(m_var_entry->charset(), DERIVATION_IMPLICIT);
+  set_handler_by_result_type(args[0]->result_type(),
+                             max_length, collation.collation);
   if (thd->lex->current_select)
   {
     /*
@@ -4954,8 +4955,8 @@ Item_func_set_user_var::update_hash(void *ptr, uint length,
     result type of the variable
   */
   if ((null_value= args[0]->null_value) && null_item)
-    res_type= entry->type;                      // Don't change type of item
-  if (::update_hash(entry, (null_value= args[0]->null_value),
+    res_type= m_var_entry->type;                 // Don't change type of item
+  if (::update_hash(m_var_entry, (null_value= args[0]->null_value),
                     ptr, length, res_type, cs, unsigned_arg))
   {
     null_value= 1;
@@ -5108,7 +5109,7 @@ Item_func_set_user_var::check(bool use_result_field)
   if (use_result_field && !result_field)
     use_result_field= FALSE;
 
-  switch (cached_result_type) {
+  switch (Item_func_set_user_var::result_type()) {
   case REAL_RESULT:
   {
     save_result.vreal= use_result_field ? result_field->val_real() :
@@ -5201,7 +5202,7 @@ Item_func_set_user_var::update()
   bool res= 0;
   DBUG_ENTER("Item_func_set_user_var::update");
 
-  switch (cached_result_type) {
+  switch (Item_func_set_user_var::result_type()) {
   case REAL_RESULT:
   {
     res= update_hash((void*) &save_result.vreal,sizeof(save_result.vreal),
@@ -5248,7 +5249,7 @@ double Item_func_set_user_var::val_real()
   DBUG_ASSERT(fixed == 1);
   check(0);
   update();					// Store expression
-  return entry->val_real(&null_value);
+  return m_var_entry->val_real(&null_value);
 }
 
 longlong Item_func_set_user_var::val_int()
@@ -5256,7 +5257,7 @@ longlong Item_func_set_user_var::val_int()
   DBUG_ASSERT(fixed == 1);
   check(0);
   update();					// Store expression
-  return entry->val_int(&null_value);
+  return m_var_entry->val_int(&null_value);
 }
 
 String *Item_func_set_user_var::val_str(String *str)
@@ -5264,7 +5265,7 @@ String *Item_func_set_user_var::val_str(String *str)
   DBUG_ASSERT(fixed == 1);
   check(0);
   update();					// Store expression
-  return entry->val_str(&null_value, str, decimals);
+  return m_var_entry->val_str(&null_value, str, decimals);
 }
 
 
@@ -5273,7 +5274,7 @@ my_decimal *Item_func_set_user_var::val_decimal(my_decimal *val)
   DBUG_ASSERT(fixed == 1);
   check(0);
   update();					// Store expression
-  return entry->val_decimal(&null_value, val);
+  return m_var_entry->val_decimal(&null_value, val);
 }
 
 
@@ -5282,7 +5283,7 @@ double Item_func_set_user_var::val_result()
   DBUG_ASSERT(fixed == 1);
   check(TRUE);
   update();					// Store expression
-  return entry->val_real(&null_value);
+  return m_var_entry->val_real(&null_value);
 }
 
 longlong Item_func_set_user_var::val_int_result()
@@ -5290,7 +5291,7 @@ longlong Item_func_set_user_var::val_int_result()
   DBUG_ASSERT(fixed == 1);
   check(TRUE);
   update();					// Store expression
-  return entry->val_int(&null_value);
+  return m_var_entry->val_int(&null_value);
 }
 
 bool Item_func_set_user_var::val_bool_result()
@@ -5298,7 +5299,7 @@ bool Item_func_set_user_var::val_bool_result()
   DBUG_ASSERT(fixed == 1);
   check(TRUE);
   update();					// Store expression
-  return entry->val_int(&null_value) != 0;
+  return m_var_entry->val_int(&null_value) != 0;
 }
 
 String *Item_func_set_user_var::str_result(String *str)
@@ -5306,7 +5307,7 @@ String *Item_func_set_user_var::str_result(String *str)
   DBUG_ASSERT(fixed == 1);
   check(TRUE);
   update();					// Store expression
-  return entry->val_str(&null_value, str, decimals);
+  return m_var_entry->val_str(&null_value, str, decimals);
 }
 
 
@@ -5315,7 +5316,7 @@ my_decimal *Item_func_set_user_var::val_decimal_result(my_decimal *val)
   DBUG_ASSERT(fixed == 1);
   check(TRUE);
   update();					// Store expression
-  return entry->val_decimal(&null_value, val);
+  return m_var_entry->val_decimal(&null_value, val);
 }
 
 
@@ -5430,7 +5431,7 @@ int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions,
     CHARSET_INFO *cs= collation.collation;
     char buff[MAX_FIELD_WIDTH];		// Alloc buffer for small columns
     str_value.set_quick(buff, sizeof(buff), cs);
-    result= entry->val_str(&null_value, &str_value, decimals);
+    result= m_var_entry->val_str(&null_value, &str_value, decimals);
 
     if (null_value)
     {
@@ -5446,7 +5447,7 @@ int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions,
   }
   else if (result_type() == REAL_RESULT)
   {
-    double nr= entry->val_real(&null_value);
+    double nr= m_var_entry->val_real(&null_value);
     if (null_value)
       return set_field_to_null(field);
     field->set_notnull();
@@ -5455,7 +5456,7 @@ int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions,
   else if (result_type() == DECIMAL_RESULT)
   {
     my_decimal decimal_value;
-    my_decimal *val= entry->val_decimal(&null_value, &decimal_value);
+    my_decimal *val= m_var_entry->val_decimal(&null_value, &decimal_value);
     if (null_value)
       return set_field_to_null(field);
     field->set_notnull();
@@ -5463,7 +5464,7 @@ int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions,
   }
   else
   {
-    longlong nr= entry->val_int(&null_value);
+    longlong nr= m_var_entry->val_int(&null_value);
     if (null_value)
       return set_field_to_null_with_conversions(field, no_conversions);
     field->set_notnull();
@@ -5478,36 +5479,36 @@ Item_func_get_user_var::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
   DBUG_ENTER("Item_func_get_user_var::val_str");
-  if (!var_entry)
+  if (!m_var_entry)
     DBUG_RETURN((String*) 0);			// No such variable
-  DBUG_RETURN(var_entry->val_str(&null_value, str, decimals));
+  DBUG_RETURN(m_var_entry->val_str(&null_value, str, decimals));
 }
 
 
 double Item_func_get_user_var::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!var_entry)
+  if (!m_var_entry)
     return 0.0;					// No such variable
-  return (var_entry->val_real(&null_value));
+  return (m_var_entry->val_real(&null_value));
 }
 
 
 my_decimal *Item_func_get_user_var::val_decimal(my_decimal *dec)
 {
   DBUG_ASSERT(fixed == 1);
-  if (!var_entry)
+  if (!m_var_entry)
     return 0;
-  return var_entry->val_decimal(&null_value, dec);
+  return m_var_entry->val_decimal(&null_value, dec);
 }
 
 
 longlong Item_func_get_user_var::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  if (!var_entry)
+  if (!m_var_entry)
     return 0;				// No such variable
-  return (var_entry->val_int(&null_value));
+  return (m_var_entry->val_int(&null_value));
 }
 
 
@@ -5660,21 +5661,20 @@ void Item_func_get_user_var::fix_length_and_dec()
   decimals=NOT_FIXED_DEC;
   max_length=MAX_BLOB_WIDTH;
 
-  error= get_var_with_binlog(thd, thd->lex->sql_command, name, &var_entry);
+  error= get_var_with_binlog(thd, thd->lex->sql_command, name, &m_var_entry);
 
   /*
     If the variable didn't exist it has been created as a STRING-type.
-    'var_entry' is NULL only if there occured an error during the call to
+    'm_var_entry' is NULL only if there occured an error during the call to
     get_var_with_binlog.
   */
-  if (!error && var_entry)
+  if (!error && m_var_entry)
   {
-    m_cached_result_type= var_entry->type;
-    unsigned_flag= var_entry->unsigned_flag;
-    max_length= var_entry->length;
-
-    collation.set(var_entry->charset(), DERIVATION_IMPLICIT);
-    switch (m_cached_result_type) {
+    unsigned_flag= m_var_entry->unsigned_flag;
+    max_length= m_var_entry->length;
+    collation.set(m_var_entry->charset(), DERIVATION_IMPLICIT);
+    set_handler_by_result_type(m_var_entry->type);
+    switch (Item_func_get_user_var::result_type()) {
     case REAL_RESULT:
       fix_char_length(DBL_DIG + 8);
       break;
@@ -5684,6 +5684,7 @@ void Item_func_get_user_var::fix_length_and_dec()
       break;
     case STRING_RESULT:
       max_length= MAX_BLOB_WIDTH - 1;
+      set_handler_by_field_type(MYSQL_TYPE_MEDIUM_BLOB);
       break;
     case DECIMAL_RESULT:
       fix_char_length(DECIMAL_MAX_STR_LENGTH);
@@ -5699,7 +5700,7 @@ void Item_func_get_user_var::fix_length_and_dec()
   {
     collation.set(&my_charset_bin, DERIVATION_IMPLICIT);
     null_value= 1;
-    m_cached_result_type= STRING_RESULT;
+    set_handler_by_field_type(MYSQL_TYPE_LONG_BLOB);
     max_length= MAX_BLOB_WIDTH;
   }
 }
@@ -5707,13 +5708,8 @@ void Item_func_get_user_var::fix_length_and_dec()
 
 bool Item_func_get_user_var::const_item() const
 {
-  return (!var_entry || current_thd->query_id != var_entry->update_query_id);
-}
-
-
-enum Item_result Item_func_get_user_var::result_type() const
-{
-  return m_cached_result_type;
+  return (!m_var_entry ||
+          current_thd->query_id != m_var_entry->update_query_id);
 }
 
 
diff --git a/sql/item_func.h b/sql/item_func.h
index 917ae0ed02e..921063f8809 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -175,6 +175,12 @@ public:
   friend class udf_handler;
   Field *tmp_table_field() { return result_field; }
   Field *tmp_table_field(TABLE *t_arg);
+  Field *create_field_for_create_select(THD *thd, TABLE *table)
+  {
+    return result_type() != STRING_RESULT ?
+           tmp_table_field(table) :
+           tmp_table_field_from_field_type(table, false, false);
+  }
   Item *get_tmp_table_item(THD *thd);
 
   my_decimal *val_decimal(my_decimal *);
@@ -1618,10 +1624,39 @@ public:
 
 class user_var_entry;
 
-class Item_func_set_user_var :public Item_func
+
+/**
+  A class to set and get user variables
+*/
+class Item_func_user_var :public Item_func,
+                          public Type_handler_hybrid_field_type
+{
+protected:
+  user_var_entry *m_var_entry;
+public:
+  LEX_STRING name; // keep it public
+  Item_func_user_var(THD *thd, LEX_STRING a)
+    :Item_func(thd), m_var_entry(NULL), name(a) { }
+  Item_func_user_var(THD *thd, LEX_STRING a, Item *b)
+    :Item_func(thd, b), m_var_entry(NULL), name(a) { }
+  Item_func_user_var(THD *thd, Item_func_user_var *item)
+    :Item_func(thd, item),
+    m_var_entry(item->m_var_entry), name(item->name)
+  {
+    set_handler_by_result_type(item->result_type());
+  }
+  enum Item_result cmp_type() const
+  { return Type_handler_hybrid_field_type::cmp_type(); }
+  enum Item_result result_type() const
+  { return Type_handler_hybrid_field_type::result_type(); }
+  enum_field_types field_type() const
+  { return Type_handler_hybrid_field_type::field_type(); }
+  bool check_vcol_func_processor(uchar *int_arg) { return true; }
+};
+
+
+class Item_func_set_user_var :public Item_func_user_var
 {
-  enum Item_result cached_result_type;
-  user_var_entry *entry;
   /*
     The entry_thread_id variable is used:
     1) to skip unnecessary updates of the entry field (see above);
@@ -1646,17 +1681,15 @@ class Item_func_set_user_var :public Item_func
   } save_result;
 
 public:
-  LEX_STRING name; // keep it public
   Item_func_set_user_var(THD *thd, LEX_STRING a, Item *b):
-    Item_func(thd, b), cached_result_type(INT_RESULT),
-    entry(NULL), entry_thread_id(0), name(a)
+    Item_func_user_var(thd, a, b),
+    entry_thread_id(0)
   {}
   Item_func_set_user_var(THD *thd, Item_func_set_user_var *item)
-    :Item_func(thd, item), cached_result_type(item->cached_result_type),
-    entry(item->entry), entry_thread_id(item->entry_thread_id),
+    :Item_func_user_var(thd, item),
+    entry_thread_id(item->entry_thread_id),
     value(item->value), decimal_buff(item->decimal_buff),
-    null_item(item->null_item), save_result(item->save_result),
-    name(item->name)
+    null_item(item->null_item), save_result(item->save_result)
   {}
 
   enum Functype functype() const { return SUSERVAR_FUNC; }
@@ -1677,9 +1710,14 @@ public:
   bool check(bool use_result_field);
   void save_item_result(Item *item);
   bool update();
-  enum Item_result result_type () const { return cached_result_type; }
   bool fix_fields(THD *thd, Item **ref);
   void fix_length_and_dec();
+  Field *create_field_for_create_select(THD *thd, TABLE *table)
+  {
+    return result_type() != STRING_RESULT ?
+           tmp_table_field(table) :
+           tmp_table_field_from_field_type(table, false, true);
+  }
   table_map used_tables() const
   {
     return Item_func::used_tables() | RAND_TABLE_BIT;
@@ -1702,20 +1740,15 @@ public:
   bool register_field_in_bitmap(uchar *arg);
   bool set_entry(THD *thd, bool create_if_not_exists);
   void cleanup();
-  bool check_vcol_func_processor(uchar *int_arg) {return TRUE;}
 };
 
 
-class Item_func_get_user_var :public Item_func,
+class Item_func_get_user_var :public Item_func_user_var,
                               private Settable_routine_parameter
 {
-  user_var_entry *var_entry;
-  Item_result m_cached_result_type;
-
 public:
-  LEX_STRING name; // keep it public
   Item_func_get_user_var(THD *thd, LEX_STRING a):
-    Item_func(thd), m_cached_result_type(STRING_RESULT), name(a) {}
+    Item_func_user_var(thd, a) {}
   enum Functype functype() const { return GUSERVAR_FUNC; }
   LEX_STRING get_name() { return name; }
   double val_real();
@@ -1724,7 +1757,6 @@ public:
   String *val_str(String* str);
   void fix_length_and_dec();
   virtual void print(String *str, enum_query_type query_type);
-  enum Item_result result_type() const;
   /*
     We must always return variables as strings to guard against selects of type
     select @t1:=1,@t1,@t:="hello",@t from foo where (@t1:= t2.b)
@@ -1742,7 +1774,6 @@ public:
   {
     return this;
   }
-  bool check_vcol_func_processor(uchar *int_arg) { return TRUE;}
 };
 
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 2d02a55b16a..bb840987089 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -501,7 +501,7 @@ public:
   my_decimal *val_decimal(my_decimal *decimal_value)
   { return  val_decimal_from_date(decimal_value); }
   Field *tmp_table_field(TABLE *table)
-  { return tmp_table_field_from_field_type(table, 0); }
+  { return tmp_table_field_from_field_type(table, false, false); }
   int save_in_field(Field *field, bool no_conversions)
   { return save_date_in_field(field); }
   void fix_length_and_dec();
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 01f7bdb6b68..6aa6deaaa08 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -3866,6 +3866,14 @@ void select_insert::abort_result_set() {
   CREATE TABLE (SELECT) ...
 ***************************************************************************/
 
+Field *Item::create_field_for_create_select(THD *thd, TABLE *table)
+{
+  Field *def_field, *tmp_field;
+  return create_tmp_field(thd, table, this, type(),
+                          (Item ***) 0, &tmp_field, &def_field, 0, 0, 0, 0, 0);
+}
+
+
 /**
   Create table from lists of fields and items (or just return TABLE
   object for pre-opened existing table).
@@ -3920,7 +3928,6 @@ static TABLE *create_table_from_items(THD *thd,
   /* Add selected items to field list */
   List_iterator_fast<Item> it(*items);
   Item *item;
-  Field *tmp_field;
   DBUG_ENTER("create_table_from_items");
 
   tmp_table.alias= 0;
@@ -3938,18 +3945,7 @@ static TABLE *create_table_from_items(THD *thd,
   while ((item=it++))
   {
     Create_field *cr_field;
-    Field *field, *def_field;
-    if (item->type() == Item::FUNC_ITEM)
-    {
-      if (item->result_type() != STRING_RESULT)
-        field= item->tmp_table_field(&tmp_table);
-      else
-        field= item->tmp_table_field_from_field_type(&tmp_table, 0);
-    }
-    else
-      field= create_tmp_field(thd, &tmp_table, item, item->type(),
-                              (Item ***) 0, &tmp_field, &def_field, 0, 0, 0, 0,
-                              0);
+    Field *field= item->create_field_for_create_select(thd, &tmp_table);
     if (!field ||
         !(cr_field= (new (thd->mem_root)
                      Create_field(thd, field,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 00309878955..fff0dc162c6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15866,7 +15866,7 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
     */
     if (item->cmp_type() == TIME_RESULT ||
         item->field_type() == MYSQL_TYPE_GEOMETRY)
-      new_field= item->tmp_table_field_from_field_type(table, 1);
+      new_field= item->tmp_table_field_from_field_type(table, true, false);
     /* 
       Make sure that the blob fits into a Field_varstring which has 
       2-byte lenght. 
@@ -15918,23 +15918,21 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
     new_created field
 */
 
-Field *create_tmp_field_for_schema(THD *thd, Item *item, TABLE *table)
+Field *Item::create_field_for_schema(THD *thd, TABLE *table)
 {
-  if (item->field_type() == MYSQL_TYPE_VARCHAR)
+  if (field_type() == MYSQL_TYPE_VARCHAR)
   {
     Field *field;
-    if (item->max_length > MAX_FIELD_VARCHARLENGTH)
-      field= new Field_blob(item->max_length, item->maybe_null,
-                            item->name, item->collation.collation);
+    if (max_length > MAX_FIELD_VARCHARLENGTH)
+      field= new Field_blob(max_length, maybe_null, name, collation.collation);
     else
-      field= new Field_varstring(item->max_length, item->maybe_null,
-                                 item->name,
-                                 table->s, item->collation.collation);
+      field= new Field_varstring(max_length, maybe_null, name,
+                                 table->s, collation.collation);
     if (field)
       field->init(table);
     return field;
   }
-  return item->tmp_table_field_from_field_type(table, 0);
+  return tmp_table_field_from_field_type(table, false, false);
 }
 
 
@@ -16475,7 +16473,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
         key over a bit field as heap tables can't handle that.
       */
       Field *new_field= (param->schema_table) ?
-        create_tmp_field_for_schema(thd, item, table) :
+        item->create_field_for_schema(thd, table) :
         create_tmp_field(thd, table, item, type, &copy_func,
                          tmp_from_field, &default_field[fieldnr],
                          group != 0,
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 7eea689567c..7d52419ae18 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -44,10 +44,9 @@ static Type_handler_geometry    type_handler_geometry;
 
 /**
   This method is used by:
-  - Item_func_set_user_var
-  - Item_func_get_user_var
-  - Item_user_var_as_out_param
-  - Item_func_udf_str
+  - Item_user_var_as_out_param::field_type()
+  - Item_func_udf_str::field_type()
+  - Item_empty_string::make_field()
 
   TODO: type_handler_adjusted_to_max_octet_length() and string_type_handler()
   provide very similar functionality, to properly choose between
@@ -69,7 +68,9 @@ Type_handler::string_type_handler(uint max_octet_length) const
 
 
 /**
-  This method is used by Item_sum_hybrid, e.g. MAX(item), MIN(item).
+  This method is used by:
+  - Item_sum_hybrid, e.g. MAX(item), MIN(item).
+  - Item_func_set_user_var
 */
 const Type_handler *
 Type_handler_string_result::type_handler_adjusted_to_max_octet_length(
@@ -94,7 +95,7 @@ Type_handler_hybrid_field_type::get_handler_by_result_type(Item_result type)
   case REAL_RESULT:       return &type_handler_double;
   case INT_RESULT:        return &type_handler_longlong;
   case DECIMAL_RESULT:    return &type_handler_newdecimal;
-  case STRING_RESULT:     return &type_handler_string;
+  case STRING_RESULT:     return &type_handler_long_blob;
   case TIME_RESULT:
   case ROW_RESULT:
     DBUG_ASSERT(0);