summaryrefslogtreecommitdiff
path: root/mysql-test/main/sp.result
diff options
context:
space:
mode:
authorAlexander Barkov <bar@mariadb.com>2018-04-23 09:31:17 +0400
committerAlexander Barkov <bar@mariadb.com>2018-04-23 09:31:17 +0400
commit6426b52ed43876ccc0142fc31f98dd6d42292388 (patch)
treee3e4d8ec47326db603c746e4d4eb975d9256c6a5 /mysql-test/main/sp.result
parent9f84451d87ba1924539b7d2ab69619b71d0155b9 (diff)
downloadmariadb-git-6426b52ed43876ccc0142fc31f98dd6d42292388.tar.gz
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
Diffstat (limited to 'mysql-test/main/sp.result')
-rw-r--r--mysql-test/main/sp.result104
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;