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

5 files changed, 505 insertions, 8 deletions

diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result
index 67be44c43f7..e08c5276ef4 100644
--- a/mysql-test/main/custom_aggregate_functions.result
+++ b/mysql-test/main/custom_aggregate_functions.result
@@ -947,3 +947,179 @@ select f1('2001-01-01'),cast(f1('2001-01-01') as time);
 f1('2001-01-01')	cast(f1('2001-01-01') as time)
 2001-01-01	00:00:00
 drop function f1;
+#
+# MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored functions
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+CREATE AGGREGATE FUNCTION f1(x INT) RETURNS INT(3)
+BEGIN
+DECLARE res INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN res-200;
+LOOP
+FETCH GROUP NEXT ROW;
+SET res= res + x;
+END LOOP;
+RETURN res;
+END;
+$$
+CREATE TABLE t2 AS SELECT CONCAT(f1(a)) FROM t1;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `CONCAT(f1(a))` varchar(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1,t2;
+DROP FUNCTION f1;
+CREATE AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET latin1
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS TEXT CHARACTER SET latin1
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET latin1
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET latin1
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET utf8
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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 AGGREGATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET utf8
+BEGIN
+DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+LOOP
+FETCH GROUP NEXT ROW;
+END LOOP;
+RETURN '';
+END;
+$$
+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;
diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test
index 13eb3bed2af..8ede846c90c 100644
--- a/mysql-test/main/custom_aggregate_functions.test
+++ b/mysql-test/main/custom_aggregate_functions.test
@@ -783,3 +783,159 @@ end|
 delimiter ;|
 select f1('2001-01-01'),cast(f1('2001-01-01') as time);
 drop function f1;
+
+
+--echo #
+--echo # MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored functions
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1(x INT) RETURNS INT(3)
+BEGIN
+  DECLARE res INT DEFAULT 0;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN res-200;
+  LOOP
+    FETCH GROUP NEXT ROW;
+    SET res= res + x;
+  END LOOP;
+  RETURN res;
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t2 AS SELECT CONCAT(f1(a)) FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t1,t2;
+DROP FUNCTION f1;
+
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET latin1
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS TEXT CHARACTER SET latin1
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET latin1
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET latin1
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET utf8
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE AGGREGATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET utf8
+BEGIN
+  DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
+  LOOP
+    FETCH GROUP NEXT ROW;
+  END LOOP;
+  RETURN '';
+END;
+$$
+DELIMITER ;$$
+CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP FUNCTION f1;
+
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;
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
index 7a6fc3e711d..80d541ac77c 100644
--- a/mysql-test/main/sp.test
+++ b/mysql-test/main/sp.test
@@ -10120,3 +10120,64 @@ CALL p2();
 DROP PROCEDURE p1;
 DROP PROCEDURE p2;
 drop table t1;
+
+--echo #
+--echo # MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored functions
+--echo #
+
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+DROP TABLE t1;
+DROP FUNCTION f1;
diff --git a/mysql-test/suite/compat/oracle/r/sp-param.result b/mysql-test/suite/compat/oracle/r/sp-param.result
index 68ecefa5077..aab1811ef67 100644
--- a/mysql-test/suite/compat/oracle/r/sp-param.result
+++ b/mysql-test/suite/compat/oracle/r/sp-param.result
@@ -14,7 +14,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text DEFAULT NULL
+  "a" varchar(2000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -30,7 +30,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text CHARACTER SET utf8 DEFAULT NULL
+  "a" varchar(2000) CHARACTER SET utf8 DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -46,7 +46,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',2000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob DEFAULT NULL
+  "a" varbinary(2000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -62,7 +62,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text DEFAULT NULL
+  "a" varchar(4000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -78,7 +78,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text DEFAULT NULL
+  "a" varchar(4000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -94,7 +94,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" text CHARACTER SET utf8 DEFAULT NULL
+  "a" varchar(4000) CHARACTER SET utf8 DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -110,7 +110,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob DEFAULT NULL
+  "a" varbinary(4000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;
@@ -126,7 +126,7 @@ CREATE TABLE t1 AS SELECT f1(REPEAT('a',4000)) AS a;;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE "t1" (
-  "a" blob DEFAULT NULL
+  "a" varbinary(4000) DEFAULT NULL
 )
 DROP TABLE t1;
 DROP FUNCTION f1;