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`.

2 files changed, 71 insertions, 0 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;