MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored func

Problems:

1. Unlike Item_field::fix_fields(),
  Item_sum_sp::fix_length_and_dec() and Item_func_sp::fix_length_and_dec()
  did not run the code which resided in adjust_max_effective_column_length(),
  therefore they did not extend max_length for the integer return data types
  from the user-specified length to the maximum length according to
  the data type capacity.

2. The code in adjust_max_effective_column_length() was not correct
   for TEXT data, because Field_blob::max_display_length()
   multiplies to mbmaxlen. So TEXT variants were unintentionally
   promoted to the next longer data type for multi-byte character
   sets: TINYTEXT->TEXT, TEXT->MEDIUMTEXT, MEDIUMTEXT->LONGTEXT.

3. Item_sum_sp::create_table_field_from_handler()
   Item_func_sp::create_table_field_from_handler()
   erroneously called tmp_table_field_from_field_type(),
   which converted VARCHAR(>512) to TEXT variants.
   So "CREATE..SELECT spfunc()" erroneously converted
   VARCHAR to TEXT. This was wrong, because stored
   functions have explicitly declared data types,
   which should be preserved.

Solution:

- Removing Type_std_attributes(const Field *)
  and using instead Type_std_attributes::set() in combination
  with field->type_str_attributes() all around the code, e.g.:
     Type_std_attributes::set(field->type_std_attributes())

  These two ways of copying attributes from a Field
  to an Item duplicated each other, and were slightly
  different in how to mix max_length and mbmaxlen.

- Removing adjust_max_effective_column_length() and
  fixing Field::type_std_attributes() to do all necessary
  type-specific calculations , so no further adjustments
  is needed.
  Field::type_std_attributes() is now called from all affected methods:
   Item_field::fix_fields()
   Item_sum_sp::fix_length_and_dec()
   Item_func_sp::fix_length_and_dec()
  This fixes the problem N1.

- Making Field::type_std_attributes() virtual, to make
  sure that type-specific adjustments a properly done
  by individual Field_xxx classes. Implementing
  Field_blob::type_std_attributes() in the way that
  no TEXT promotion is done.
  This fixes the problem N2.

- Fixing Item_sum_sp::create_table_field_from_handler()
  Item_func_sp::create_table_field_from_handler() to
  call create_table_field_from_handler() instead of
  tmp_table_field_from_field_type() to avoid
  VARCHAR->TEXT conversion on "CREATE..SELECT spfunc()".

- Recording mysql-test/suite/compat/oracle/r/sp-param.result
  as "CREATE..SELECT spfunc()" now correctly
  preserve the data type as specified in the RETURNS clause.

- Adding new tests

1 files changed, 104 insertions, 0 deletions

diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result
index 582ea33d903..f6f34318584 100644
--- a/mysql-test/main/sp.result
+++ b/mysql-test/main/sp.result
@@ -8571,3 +8571,107 @@ affected rows: 5
 DROP PROCEDURE p1;
 DROP PROCEDURE p2;
 drop table t1;
+#
+# MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored functions
+#
+CREATE TABLE t1 (a INT(3));
+INSERT INTO t1 VALUES (-999);
+CREATE FUNCTION f1(a INT(3)) RETURNS INT(3) RETURN a;
+CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(COALESCE(a)) AS c2, CONCAT(f1(a)) AS c3 FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c1` varchar(11) DEFAULT NULL,
+  `c2` varchar(11) DEFAULT NULL,
+  `c3` varchar(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET latin1 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` tinytext DEFAULT NULL,
+  `c2` tinytext DEFAULT NULL,
+  `c3` varchar(255) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET latin1 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` text DEFAULT NULL,
+  `c2` text DEFAULT NULL,
+  `c3` text DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET latin1 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` mediumtext DEFAULT NULL,
+  `c2` mediumtext DEFAULT NULL,
+  `c3` mediumtext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET latin1 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` longtext DEFAULT NULL,
+  `c2` longtext DEFAULT NULL,
+  `c3` longtext DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` tinytext CHARACTER SET utf8 DEFAULT NULL,
+  `c2` text CHARACTER SET utf8 DEFAULT NULL,
+  `c3` varchar(255) CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` text CHARACTER SET utf8 DEFAULT NULL,
+  `c2` mediumtext CHARACTER SET utf8 DEFAULT NULL,
+  `c3` mediumtext CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET utf8 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` mediumtext CHARACTER SET utf8 DEFAULT NULL,
+  `c2` longtext CHARACTER SET utf8 DEFAULT NULL,
+  `c3` longtext CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET utf8 RETURN '';
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` longtext CHARACTER SET utf8 DEFAULT NULL,
+  `c2` longtext CHARACTER SET utf8 DEFAULT NULL,
+  `c3` longtext CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP FUNCTION f1;