From 6426b52ed43876ccc0142fc31f98dd6d42292388 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 23 Apr 2018 09:31:17 +0400 Subject: 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 --- mysql-test/main/sp.test | 61 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 61 insertions(+) (limited to 'mysql-test/main/sp.test') 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; -- cgit v1.2.1