MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters

Item_param::set_value() did not set Item::collation and
Item_param::str_value_ptr.str_charset properly. So both
metadata and data for OUT parameters were sent in a wrong
way to the client.

This patch removes the old implementation of Item_param::set_value()
and rewrites it using Type_handler::Item_param_set_from_value(),
so now setting IN and OUT parameters share the a lot of code.

1. Item_param::set_str() now:
  - accepts two additional parameters fromcs, tocs
  - sets str_value_ptr, to make sure it's always in sync with str_value,
    even without Item_param::convert_str_value()
  - does collation.set(tocs, DERIVATION_COERCIBLE),
    to make sure that DTCollation is valid even without
    Item_param::convert_str_value()

2. Item_param::set_value(), which is used to set OUT parameters,
   now reuses Type_handler::Item_param_set_from_value().

3. Cleanup: moving Item_param::str_value_ptr to private,
   as it's not needed outside.

4. Cleanup: adding a new virtual method
   Settable_routine_parameter::get_item_param()
   and using it a few new DBUG_ASSERTs, where
   Item_param cannot appear.

After this change:
1. Assigning of IN parameters works as before:
a. Item_param::set_str() is called and sets the value as a binary string
b. The original value is sent to the query used for binary/general logging
c. Item_param::convert_str_value() converts the value from the client
   character set to the connection character set

2. Assigning of OUT parameters works in the new way:
a. Item_param::set_str() and sets the value
   using the source Item's collation, so both Item::collation
   and Item_param::str_value_ptr.str_charset are properly set.
b. Protocol_binary::send_out_parameters() sends the
   value to the client correctly:
   - Protocol::send_result_set_metadata() uses Item::collation.collation
     (which is now properly set), to detect if conversion is needed,
     and sends a correct collation ID.
   - Protocol::send_result_set_row() calls Type_handler::Item_send_str(),
     which uses Item_param::str_value_ptr.str_charset
     (which is now properly set) to actually perform the conversion.

1 files changed, 22 insertions, 8 deletions

diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index f1a62d88111..2174293b45b 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -3550,7 +3550,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('a', 16);
 @a	@a = REPEAT('a', 16)
@@ -3568,7 +3568,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('b', 16);
 @a	@a = REPEAT('b', 16)
@@ -3586,7 +3586,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('c', 16);
 @a	@a = REPEAT('c', 16)
@@ -3604,7 +3604,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('d', 16);
 @a	@a = REPEAT('d', 16)
@@ -3622,7 +3622,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('e', 16);
 @a	@a = REPEAT('e', 16)
@@ -3640,7 +3640,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = REPEAT('f', 16);
 @a	@a = REPEAT('f', 16)
@@ -3766,7 +3766,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = 'aaa';
 @a	@a = 'aaa'
@@ -3784,7 +3784,7 @@ CREATE TEMPORARY TABLE tmp1 AS SELECT @a AS c1;
 SHOW CREATE TABLE tmp1;
 Table	Create Table
 tmp1	CREATE TEMPORARY TABLE `tmp1` (
-  `c1` longblob DEFAULT NULL
+  `c1` longtext DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 SELECT @a, @a = 'aaa';
 @a	@a = 'aaa'
@@ -5074,3 +5074,17 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
 DROP PROCEDURE p1;
+#
+# MDEV-14454 Binary protocol returns wrong collation ID for SP OUT parameters
+#
+CREATE PROCEDURE p1(OUT v CHAR(32) CHARACTER SET utf8) SET v='aaa';
+PREPARE stmt1 FROM 'CALL p1(?)';
+EXECUTE stmt1 USING @a;
+CREATE TABLE t1 AS SELECT @a AS c1;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` longtext CHARACTER SET utf8 DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP PROCEDURE p1;