MDEV-18092 Query with the table I_S.PARAMETERS stop working after a package is created

This patch was originally made by Anel Husakovic.

Skip `PACKAGE` and `PACKAGE BODY` records quickly.

These stored objects do not have any parameters or return values
(only procedures and functions have).
So no needs to build a `CREATE` statement
(in `Sp_handler::sp_load_for_information_schema()`) and parse it:
this won't give us any data useful for `INFORMATION_SCHEMA.PARAMETERS`.

3 files changed, 73 insertions, 1 deletions

diff --git a/mysql-test/suite/compat/oracle/r/information_schema_parameters.result b/mysql-test/suite/compat/oracle/r/information_schema_parameters.result
index e1ed53c39de..f7e9bfcafb9 100644
--- a/mysql-test/suite/compat/oracle/r/information_schema_parameters.result
+++ b/mysql-test/suite/compat/oracle/r/information_schema_parameters.result
@@ -814,3 +814,41 @@ DTD_IDENTIFIER	ROW
 ROUTINE_TYPE	FUNCTION
 --------	--------
 DROP FUNCTION f1;
+#
+# MDEV 18092 Query with the table I_S.PARAMETERS stop working
+# after a package is created
+#
+SET sql_mode=ORACLE;
+CREATE DATABASE db1_mdev18092;
+USE db1_mdev18092;
+CREATE PROCEDURE p1(a INT)
+AS BEGIN
+NULL;
+END;
+$$
+CREATE OR REPLACE PACKAGE employee_tools AS
+FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
+PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
+PROCEDURE raiseSalaryStd(eid INT);
+PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
+END;
+$$
+SELECT *, '---------------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='db1_mdev18092';
+SPECIFIC_CATALOG	def
+SPECIFIC_SCHEMA	db1_mdev18092
+SPECIFIC_NAME	p1
+ORDINAL_POSITION	1
+PARAMETER_MODE	IN
+PARAMETER_NAME	a
+DATA_TYPE	int
+CHARACTER_MAXIMUM_LENGTH	NULL
+CHARACTER_OCTET_LENGTH	NULL
+NUMERIC_PRECISION	10
+NUMERIC_SCALE	0
+DATETIME_PRECISION	NULL
+CHARACTER_SET_NAME	NULL
+COLLATION_NAME	NULL
+DTD_IDENTIFIER	int(11)
+ROUTINE_TYPE	PROCEDURE
+---------------	---------------
+DROP DATABASE db1_mdev18092;
diff --git a/mysql-test/suite/compat/oracle/t/information_schema_parameters.test b/mysql-test/suite/compat/oracle/t/information_schema_parameters.test
index af241661939..c13a59103dd 100644
--- a/mysql-test/suite/compat/oracle/t/information_schema_parameters.test
+++ b/mysql-test/suite/compat/oracle/t/information_schema_parameters.test
@@ -92,3 +92,36 @@ SET sql_mode=ORACLE;
 SELECT *, '--------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='f1';
 --horizontal_results
 DROP FUNCTION f1;
+
+--echo #
+--echo # MDEV 18092 Query with the table I_S.PARAMETERS stop working
+--echo # after a package is created
+--echo #
+
+SET sql_mode=ORACLE;
+
+CREATE DATABASE db1_mdev18092;
+USE db1_mdev18092;
+
+DELIMITER $$;
+
+CREATE PROCEDURE p1(a INT)
+AS BEGIN
+  NULL;
+END;
+$$
+
+CREATE OR REPLACE PACKAGE employee_tools AS
+  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
+  PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
+  PROCEDURE raiseSalaryStd(eid INT);
+  PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
+END;
+$$
+DELIMITER ;$$
+
+--vertical_results
+SELECT *, '---------------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='db1_mdev18092';
+--horizontal_results
+
+DROP DATABASE db1_mdev18092;
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index d9985406323..3f40384ab72 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -6268,7 +6268,8 @@ bool store_schema_params(THD *thd, TABLE *table, TABLE *proc_table,
   sph= Sp_handler::handler_mysql_proc((stored_procedure_type)
                                       proc_table->field[MYSQL_PROC_MYSQL_TYPE]->
                                       val_int());
-  if (!sph)
+  if (!sph || sph->type() == TYPE_ENUM_PACKAGE ||
+      sph->type() == TYPE_ENUM_PACKAGE_BODY)
     DBUG_RETURN(0);
 
   if (!full_access)