Do empty job for package/package body - refactor store_schema_params()bb-anel-10.3-refactor-store_schema_params

4 files changed, 155 insertions, 3 deletions

diff --git a/mysql-test/suite/anel/mdev_18092.result b/mysql-test/suite/anel/mdev_18092.result
new file mode 100644
index 00000000000..a217674be36
--- /dev/null
+++ b/mysql-test/suite/anel/mdev_18092.result
@@ -0,0 +1,68 @@
+DROP DATABASE IF EXISTS db1;
+Warnings:
+Note	1008	Can't drop database 'db1'; database doesn't exist
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE employee (
+id int(11) NOT NULL AUTO_INCREMENT,
+salary decimal(10, 2) DEFAULT NULL,
+PRIMARY KEY (id)
+)
+ENGINE = INNODB,
+CHARACTER SET latin1,
+COLLATE latin1_swedish_ci;
+CREATE TABLE employee_log (
+id int(11) NOT NULL AUTO_INCREMENT,
+cmnt text DEFAULT NULL,
+PRIMARY KEY (id)
+)
+ENGINE = INNODB;
+SET sql_mode=ORACLE;
+CREATE PROCEDURE p1(a INT)
+AS BEGIN
+NULL;
+END;
+CREATE OR REPLACE PACKAGE employee_tools AS
+FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
+END;
+$$
+CREATE PACKAGE BODY employee_tools AS
+-- package body variables
+stdRaiseAmount DECIMAL(10,2):=500;
+-- private routines
+PROCEDURE log (eid INT, ecmnt TEXT) AS
+BEGIN
+INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
+END;
+FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
+nSalary DECIMAL(10,2);
+BEGIN
+SELECT salary INTO nSalary FROM employee WHERE id=eid;
+log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
+RETURN nSalary;
+END;
+BEGIN
+-- This code is executed when the current session
+-- accesses any of the package routines for the first time
+log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
+END;
+$$
+SELECT *, '---------------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='db1';
+SPECIFIC_CATALOG	def
+SPECIFIC_SCHEMA	db1
+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;
diff --git a/mysql-test/suite/anel/mdev_18092.test b/mysql-test/suite/anel/mdev_18092.test
new file mode 100644
index 00000000000..9614f163bbb
--- /dev/null
+++ b/mysql-test/suite/anel/mdev_18092.test
@@ -0,0 +1,67 @@
+--source include/have_innodb.inc
+
+DROP DATABASE IF EXISTS db1;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE employee (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  salary decimal(10, 2) DEFAULT NULL,
+  PRIMARY KEY (id)
+)
+ENGINE = INNODB,
+CHARACTER SET latin1,
+COLLATE latin1_swedish_ci;
+
+CREATE TABLE employee_log (
+  id int(11) NOT NULL AUTO_INCREMENT,
+  cmnt text DEFAULT NULL,
+  PRIMARY KEY (id)
+)
+ENGINE = INNODB;
+
+SET sql_mode=ORACLE;
+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);
+
+END;
+$$
+CREATE PACKAGE BODY employee_tools AS
+  -- package body variables
+  stdRaiseAmount DECIMAL(10,2):=500;
+
+  -- private routines
+  PROCEDURE log (eid INT, ecmnt TEXT) AS
+  BEGIN
+    INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
+  END;
+
+
+  FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
+    nSalary DECIMAL(10,2);
+  BEGIN
+    SELECT salary INTO nSalary FROM employee WHERE id=eid;
+    log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
+    RETURN nSalary;
+  END;
+
+BEGIN
+  -- This code is executed when the current session
+  -- accesses any of the package routines for the first time
+  log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
+END;
+$$
+
+DELIMITER ;$$
+
+--vertical_results
+SELECT *, '---------------' FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='db1';
+--horizontal_results
+
+
+drop database db1;
diff --git a/sql/sp.h b/sql/sp.h
index 852fbccafdf..91f16ccd00c 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -243,6 +243,12 @@ public:
                               const DDL_options_st ddl_options,
                               sql_mode_t sql_mode) const;
 
+  virtual bool skip_object() const
+  {
+    if(!this)
+      return 1;
+    return 0;
+  }
 };
 
 
@@ -394,7 +400,13 @@ public:
   sp_cache **get_cache(THD *) const;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   HASH *get_priv_hash() const;
-#endif
+#endif  
+  bool skip_object() const
+  {
+    if(this->type() == TYPE_ENUM_PACKAGE)
+      return 1;
+    return 0;
+  }
 };
 
 
@@ -428,6 +440,12 @@ public:
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   HASH *get_priv_hash() const;
 #endif
+  bool skip_object() const
+  {
+    if(this->type() == TYPE_ENUM_PACKAGE_BODY)
+      return 1;
+    return 0;
+  }
 };
 
 
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 64770595348..b0a0bc5b416 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -6274,8 +6274,7 @@ 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 || sph->type() == TYPE_ENUM_PACKAGE ||
-      sph->type() == TYPE_ENUM_PACKAGE_BODY)
+  if(sph->skip_object())
     DBUG_RETURN(0);
 
   if (!full_access)