MySQL Bugs: #78635: Non-MySQL data types are not translated in procedure/function params

Bug #78635	Non-MySQL data types are not translated in procedure/function params
Submitted:	30 Sep 2015 6:56	Modified:	30 Sep 2015 7:56
Reporter:	Alexander Soklakov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.7.8, 5.7.10, 8.0.10, 5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There are several data types from other database engines which MySQL understands. According to http://dev.mysql.com/doc/refman/5.7/en/other-vendor-data-types.html "Data type mapping occurs at table creation time, after which the original type specifications are discarded. If you create a table with types used by other vendors and then issue a DESCRIBE tbl_name statement, MySQL reports the table structure using the equivalent MySQL types."

It works as documented for tables, but behaves inconsistently with procedures and functions. For procedures parameter type left untouched, for functions parameter type left untouched but return type is changed like it's described for tables.

How to repeat:
1. Create procedure with non-MySQL type of parameter, eg  LONG VARCHAR, and look at 'show create procedure' result:

mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 LONG VARCHAR)
    -> BEGIN
    ->   SET @x = p1;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> show create procedure dorepeat;
+-----------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                          | Create Procedure                                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| dorepeat  | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 LONG VARCHAR)
BEGIN
  SET @x = p1;
END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----------+-----------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

Param type isn't translated to MEDIUMTEXT.

2. Create function with non-MySQL type of parameter, eg  LONG VARCHAR, and look at 'show create function' result:

mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    ->     DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!')$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> show create function hello;
+----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                                                          | Create Function                                                                                                                                       | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| hello    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!') | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+-----------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

Type isn't translated to MEDIUMTEXT for parameter, but is translated for RETURNS.

Suggested fix:
Types of procedure/function parameters should be translated to MySQL ones.

Hello Alex,

Thank you for the report and test case.

Thanks,
Umesh

// 5.7.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);
Query OK, 0 rows affected (0.00 sec)

mysql> desc t;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | tinyint(1)    | YES  |     | NULL    |       |
| b     | double        | YES  |     | NULL    |       |
| c     | mediumtext    | YES  |     | NULL    |       |
| d     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>  DELIMITER $$
mysql>  CREATE PROCEDURE dorepeat(p1 LONG VARCHAR)
    ->     BEGIN
    ->       SET @x = p1;
    ->     END$$
Query OK, 0 rows affected (0.01 sec)

mysql>  DELIMITER ;
mysql> show create procedure dorepeat\G
*************************** 1. row ***************************
           Procedure: dorepeat
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 LONG VARCHAR)
BEGIN
      SET @x = p1;
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>  DELIMITER $$
mysql>  CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    ->          DETERMINISTIC
    ->      RETURN CONCAT('Hello, ',s,'!')$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  DELIMITER ;
mysql> show create function hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

// 5.8.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.8.0: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.8.0-m17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql>  DELIMITER $$
mysql>  CREATE PROCEDURE dorepeat(p1 LONG VARCHAR)
    ->     BEGIN
    ->       SET @x = p1;
    ->     END$$
Query OK, 0 rows affected (0.00 sec)

mysql>  DELIMITER ;
mysql>  show create procedure dorepeat\G
*************************** 1. row ***************************
           Procedure: dorepeat
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 LONG VARCHAR)
BEGIN
      SET @x = p1;
    END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>  DELIMITER $$
mysql>  CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    ->          DETERMINISTIC
    ->      RETURN CONCAT('Hello, ',s,'!')$$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  DELIMITER ;
mysql> show create function hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

Older version (like 5.6.24) are also affected. Please, check.

// 5.6.27 - affected

[root@cluster-repo ~]#  mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 LONG VARCHAR)
    ->  BEGIN
    ->    SET @x = p1;
    ->  END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> how create procedure dorepeat\G
mysql> show create procedure dorepeat\G
*************************** 1. row ***************************
           Procedure: dorepeat
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `dorepeat`(p1 LONG VARCHAR)
BEGIN
   SET @x = p1;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>  DELIMITER $$
mysql>  CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    ->          DETERMINISTIC
    ->      RETURN CONCAT('Hello, ',s,'!')$$
Query OK, 0 rows affected (0.00 sec)

mysql>  DELIMITER ;
mysql> show create function hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s LONG VARCHAR) RETURNS mediumtext CHARSET latin1
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0