MDEV-11952 Oracle-style packages: stage#5mariadb-10.3.5bb-10.3-compatibility

- CREATE PACKAGE [BODY] statements are now
  entirely written to mysql.proc with type='PACKAGE' and type='PACKAGE BODY'.
- CREATE PACKAGE BODY now supports IF NOT EXISTS
- DROP PACKAGE BODY now supports IF EXISTS
- CREATE OR REPLACE PACKAGE [BODY] is now supported
- CREATE PACKAGE [BODY] now support the DEFINER clause:

    CREATE DEFINER user@host PACKAGE pkg ... END;
    CREATE DEFINER user@host PACKAGE BODY pkg ... END;

- CREATE PACKAGE [BODY] now supports SQL SECURITY and COMMENT clauses, e.g.:

    CREATE PACKAGE p1 SQL SECURITY INVOKER COMMENT "comment" AS ... END;

- Package routines are now created from the package CREATE PACKAGE BODY
  statement and don't produce individual records in mysql.proc.

- CREATE PACKAGE BODY now supports package-wide variables.
  Package variables can be read and set inside package routines.
  Package variables are stored in a separate sp_rcontext,
  which is cached in THD on the first packate routine call.

- CREATE PACKAGE BODY now supports the initialization section.

- All public routines (i.e. declared in CREATE PACKAGE)
  must have implementations in CREATE PACKAGE BODY

- Only public package routines are available outside of the package

- {CREATE|DROP} PACKAGE [BODY] now respects CREATE ROUTINE and ALTER ROUTINE
  privileges

- "GRANT EXECUTE ON PACKAGE BODY pkg" is now supported

- SHOW CREATE PACKAGE [BODY] is now supported

- SHOW PACKAGE [BODY] STATUS is now supported

- CREATE and DROP for PACKAGE [BODY] now works for non-current databases

- mysqldump now supports packages

- "SHOW {PROCEDURE|FUNCTION) CODE pkg.routine" now works for package routines

- "SHOW PACKAGE BODY CODE pkg" now works (the package initialization section)

- A new package body level MDL was added

- Recursive calls for package procedures are now possible

- Routine forward declarations in CREATE PACKATE BODY are now supported.

- Package body variables now work as SP OUT parameters

- Package body variables now work as SELECT INTO targets

- Package body variables now support ROW, %ROWTYPE, %TYPE

1 files changed, 31 insertions, 2 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 902933ffed8..e7be6ed4e90 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3169,6 +3169,17 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
 
   switch (mdl_type)
   {
+  case MDL_key::PACKAGE_BODY:
+    DBUG_ASSERT(rt != (Sroutine_hash_entry*)prelocking_ctx->sroutines_list.first);
+    /*
+      No need to cache the package body itself.
+      It gets cached during open_and_process_routine()
+      for the first used package routine. See the package related code
+      in the "case" below.
+    */
+    if (sp_acquire_mdl(thd, rt, ot_ctx))
+      DBUG_RETURN(TRUE);
+    break;
   case MDL_key::FUNCTION:
   case MDL_key::PROCEDURE:
     {
@@ -3183,11 +3194,13 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
       if (rt != (Sroutine_hash_entry*)prelocking_ctx->sroutines_list.first ||
           mdl_type != MDL_key::PROCEDURE)
       {
+        /*
+          TODO: If this is a package routine, we should not put MDL
+          TODO: on the routine itself. We should put only the package MDL.
+        */
         if (sp_acquire_mdl(thd, rt, ot_ctx))
           DBUG_RETURN(TRUE);
 
-        DEBUG_SYNC(thd, "after_shared_lock_pname");
-
         /* Ensures the routine is up-to-date and cached, if exists. */
         if (rt->sp_cache_routine(thd, has_prelocking_list, &sp))
           DBUG_RETURN(TRUE);
@@ -3202,8 +3215,24 @@ open_and_process_routine(THD *thd, Query_tables_list *prelocking_ctx,
           *routine_modifies_data= sp->modifies_data();
 
           if (!has_prelocking_list)
+          {
             prelocking_strategy->handle_routine(thd, prelocking_ctx, rt, sp,
                                                 need_prelocking);
+            if (sp->m_parent)
+            {
+              /*
+                If it's a package routine, we need also to handle the
+                package body, as its initialization section can use
+                some tables and routine calls.
+                TODO: Only package public routines actually need this.
+                TODO: Skip package body handling for private routines.
+              */
+              *routine_modifies_data|= sp->m_parent->modifies_data();
+              prelocking_strategy->handle_routine(thd, prelocking_ctx, rt,
+                                                  sp->m_parent,
+                                                  need_prelocking);
+            }
+          }
         }
       }
       else