Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 3092 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test
new file mode 100644
index 00000000..b6310eed
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-package.test
@@ -0,0 +1,3092 @@
+--source include/default_charset.inc
+
+SET sql_mode=ORACLE;
+
+--enable_prepare_warnings
+--disable_ps2_protocol
+
+--echo #
+--echo # Creating a body of a non-existing package
+--echo #
+DELIMITER $$;
+--error ER_SP_DOES_NOT_EXIST
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+END;
+$$
+DELIMITER ;$$
+
+--echo #
+--echo # Dropping a non-existing package
+--echo #
+--error ER_SP_DOES_NOT_EXIST
+DROP PACKAGE test2;
+DROP PACKAGE IF EXISTS test2;
+--error ER_SP_DOES_NOT_EXIST
+DROP PACKAGE BODY test2;
+
+
+--echo #
+--echo # Bad combinations of OR REPLACE and IF EXISTS
+--echo #
+
+DELIMITER $$;
+--error ER_WRONG_USAGE
+CREATE OR REPLACE PACKAGE IF NOT EXISTS pkg AS
+  PROCEDURE p1;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_WRONG_USAGE
+CREATE OR REPLACE PACKAGE BODY IF NOT EXISTS pkg AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # PACKAGE and PS
+--echo #
+
+PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2';
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+PREPARE stmt FROM 'CREATE PACKAGE BODY test2 AS'
+                  '  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;'
+                  'END test2';
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Package and READ ONLY transactions
+--echo #
+
+SET SESSION TRANSACTION READ ONLY;
+
+DELIMITER $$;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END
+$$
+DELIMITER ;$$
+
+SET SESSION TRANSACTION READ WRITE;
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f2 RETURN INT;
+END;
+$$
+SET SESSION TRANSACTION READ ONLY
+$$
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+  FUNCTION f2 RETURN INT AS BEGIN RETURN f1(); END;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT f2();
+  END;
+END;
+$$
+DELIMITER ;$$
+SET SESSION TRANSACTION READ WRITE;
+DROP PACKAGE test2;
+
+SET SESSION TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP PACKAGE test2;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP PACKAGE BODY test2;
+
+SET SESSION TRANSACTION READ WRITE;
+
+
+--echo #
+--echo # Syntax error inside a CREATE PACKAGE, inside a routine definition
+--echo #
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f2 RETURN INT;
+  FUNCTION f3;
+  FUNCTION f4 RETURN INT;
+END
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Syntax error inside a CREATE PACKAGE, outside of a routine definition
+--echo #
+
+# The definition "FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;"
+# is valid in CREATE PACKAGE BODY, but not in CREATE PACKAGE.
+# Syntax error happens after parsing "FUNCTION f3 RETURN INT".
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f2 RETURN INT;
+  FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;
+  FUNCTION f4 RETURN INT;
+END
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Syntax error inside a CREATE PACKAGE BODY, inside a routine definition
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f2 RETURN INT;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+  FUNCTION f2 RETURN INT SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
+END
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+--echo #
+--echo # Syntax error inside a CREATE PACKAGE BODY, outside a routine definition
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f2 RETURN INT;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+  SOME SYNTAX ERROR;
+  FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
+END
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Syntax error inside a CREATE PACKAGE BODY executable section
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+BEGIN
+  SOME SYNTAX ERROR;
+END
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # CREATE PROCEDURE inside a package PROCEDURE is not allowed
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_NO_RECURSIVE_CREATE
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS
+  BEGIN
+    CREATE PROCEDURE p1 AS BEGIN NULL; END;
+  END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # CREATE PACKAGE inside a package PROCEDURE is not allowed
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_NO_RECURSIVE_CREATE
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS
+  BEGIN
+    CREATE PACKAGE p1 AS PROCEDURE p1; END;
+  END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # CREATE PROCEDURE inside a package executable section is not allowed
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_NO_RECURSIVE_CREATE
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+BEGIN
+  CREATE PROCEDURE p1 AS BEGIN NULL; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # CREATE FUNCTION inside a package executable section is not allowed
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_NO_RECURSIVE_CREATE
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+BEGIN
+  CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # CREATE PACKAGE inside a package executable section is not allowed
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_NO_RECURSIVE_CREATE
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+BEGIN
+  CREATE PACKAGE p1 AS PROCEDURE p1; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Broken CREATE PACKAGE at CREATE PACKAGE BODY time
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+UPDATE mysql.proc SET `body`='garbage'
+  WHERE db='test' AND name='test2' AND type='PACKAGE';
+
+DELIMITER $$;
+--error ER_SP_PROC_TABLE_CORRUPT
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT
+  AS BEGIN
+    RETURN f2();
+  END;
+END;
+$$
+DELIMITER ;$$
+show warnings;
+
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Broken CREATE PACKAGE at a package function call time
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT
+  AS BEGIN
+    RETURN f2();
+  END;
+END;
+$$
+DELIMITER ;$$
+
+--error ER_SP_DOES_NOT_EXIST
+SELECT test2.f1();
+UPDATE mysql.proc SET `body`='garbage'
+  WHERE db='test' AND name='test2' AND type='PACKAGE';
+--source sp-cache-invalidate.inc
+--error ER_SP_PROC_TABLE_CORRUPT
+SELECT test2.f1();
+show warnings;
+--error ER_SP_PROC_TABLE_CORRUPT
+SELECT test2.f1();
+show warnings;
+--error ER_SP_PROC_TABLE_CORRUPT
+SELECT test2.f1();
+show warnings;
+
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Broken CREATE PACKAGE at a package procedure call time
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1
+  AS BEGIN
+    CALL p2;
+  END;
+END;
+$$
+DELIMITER ;$$
+
+--error ER_SP_DOES_NOT_EXIST
+CALL test2.f1();
+UPDATE mysql.proc SET `body`='garbage'
+  WHERE db='test' AND name='test2' AND type='PACKAGE';
+--source sp-cache-invalidate.inc
+--error ER_SP_PROC_TABLE_CORRUPT
+CALL test2.p1();
+show warnings;
+--error ER_SP_PROC_TABLE_CORRUPT
+CALL test2.p1();
+show warnings;
+--error ER_SP_PROC_TABLE_CORRUPT
+CALL test2.p1();
+show warnings;
+
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Bad routine names
+--echo #
+
+DELIMITER $$;
+--error ER_TOO_LONG_IDENT
+CREATE PACKAGE p1 AS
+  PROCEDURE pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_TOO_LONG_IDENT
+CREATE PACKAGE p1 AS
+  FUNCTION fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1
+    RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_WRONG_NAME
+CREATE PACKAGE p1 AS
+  PROCEDURE "p1 ";
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_WRONG_NAME
+CREATE PACKAGE p1 AS
+  FUNCTION "f1 " RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_WRONG_NAME
+CREATE PACKAGE p1 AS
+  PROCEDURE "p1.p1";
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_WRONG_NAME
+CREATE PACKAGE p1 AS
+  FUNCTION "f1.f1" RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Duplicate PROCEDURE in CREATE PACKAGE
+--echo #
+
+DELIMITER $$;
+--error ER_SP_ALREADY_EXISTS,
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+  PROCEDURE p1;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_SP_ALREADY_EXISTS,
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+  PROCEDURE P1;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Duplicate FUNCTION in CREATE PACKAGE
+--echo #
+
+DELIMITER $$;
+--error ER_SP_ALREADY_EXISTS,
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_SP_ALREADY_EXISTS,
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  FUNCTION F1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo #
+--echo # Duplicate PROCEDURE in CREATE PACKAGE BODY
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_SP_ALREADY_EXISTS
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+  PROCEDURE p1 AS BEGIN NULL; END;
+END;
+$$
+--error ER_SP_ALREADY_EXISTS
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+  PROCEDURE P1 AS BEGIN NULL; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Duplicate FUNCTION in CREATE PACKAGE BODY
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+--error ER_SP_ALREADY_EXISTS
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
+END;
+$$
+--error ER_SP_ALREADY_EXISTS
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
+  FUNCTION F1 RETURN INT AS BEGIN RETURN 0; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Routines declared in CREATE PACKAGE missing in CREATE PACKAGE BODY
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p2 AS BEGIN NULL; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
+CREATE PACKAGE BODY test2 AS
+  FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+--echo #
+--echo # Forward declarations in CREATE PACKAGE BODY with missing implementations
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS BEGIN NULL; END;
+  PROCEDURE p2;
+END;
+$$
+--error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1 AS BEGIN NULL; END;
+END;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Creating a new package
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 COMMENT 'package-test2-comment' AS
+  FUNCTION f1 RETURN INT DETERMINISTIC;
+  FUNCTION f2(a INT) RETURN INT;
+  FUNCTION concat RETURN INT;
+  PROCEDURE p1;
+  PROCEDURE p2(a INT);
+END
+$$
+DELIMITER ;$$
+
+--vertical_results
+--replace_column 13 # 14 #
+SELECT * FROM mysql.proc WHERE db='test' AND name='test2';
+--replace_column 24 # 25 #
+SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
+--horizontal_results
+
+DELIMITER $$;
+CREATE PACKAGE IF NOT EXISTS test2 AS
+  FUNCTION f1 RETURN INT;
+END test2
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2 COMMENT 'package-body-test2-comment' AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+  FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
+  FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT f2(0);
+  END;
+  PROCEDURE p2(a INT) AS
+  BEGIN
+    SELECT f2(a);
+  END;
+END;
+$$
+DELIMITER ;$$
+
+# This should do nothing and return a warning
+DELIMITER $$;
+CREATE PACKAGE BODY IF NOT EXISTS test2 AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
+  FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
+  FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT f2(0);
+  END;
+  PROCEDURE p2(a INT) AS
+  BEGIN
+    SELECT f2(a);
+  END;
+END;
+$$
+DELIMITER ;$$
+
+#
+# The next query issues a warning about "concat" name collision,
+# raised during compilation of the package body.
+# However, "mtr --ps" does not produce the warning.
+# It's not a package specific issue. The same difference exists for
+# standalone functions. So just suppress warning for now.
+#
+--disable_warnings
+SELECT test2.f1();
+--enable_warnings
+SELECT test2.f2(1);
+CALL test2.p1();
+CALL test2.p2(1);
+
+--vertical_results
+--replace_column 13 # 14 #
+SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'test2.%';
+--replace_column 24 # 25 #
+SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
+--replace_column 24 # 25 #
+SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME LIKE 'test2.%';
+--replace_column 4 'root@localhost'  5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+SHOW PACKAGE STATUS;
+--replace_column 4 'root@localhost'  5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+SHOW PACKAGE BODY STATUS;
+SHOW CREATE PACKAGE test2;
+SHOW CREATE PACKAGE BODY test2;
+--horizontal_results
+
+
+
+DROP PACKAGE BODY test2;
+--error ER_SP_DOES_NOT_EXIST
+SELECT test2.f1();
+--error ER_SP_DOES_NOT_EXIST
+SELECT test2.f2();
+--error ER_SP_DOES_NOT_EXIST
+CALL test2.p1();
+
+DROP PACKAGE BODY IF EXISTS test2;
+
+--error ER_SP_DOES_NOT_EXIST
+DROP PACKAGE BODY test2;
+
+
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Creating a new package in a remote database
+--echo #
+
+CREATE DATABASE test2;
+
+DELIMITER $$;
+CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+  PROCEDURE p1 AS BEGIN SELECT f1(); END;
+END;
+$$
+DELIMITER ;$$
+
+--vertical_results
+--replace_column 4 'root@localhost'  5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+SHOW PACKAGE STATUS;
+--replace_column 4 'root@localhost'  5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+SHOW PACKAGE BODY STATUS;
+--horizontal_results
+
+USE test2;
+SELECT test2.f1();
+CALL test2.p1();
+USE test;
+DROP PACKAGE BODY test2.test2;
+DROP PACKAGE test2.test2;
+DROP DATABASE test2;
+
+
+--echo #
+--echo # Only public routines are available outside
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  -- Public routines
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is test2.f1';
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 'This is test2.p1';
+  END;
+  -- Private routines
+  FUNCTION f2 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is test2.f2';
+  END;
+  PROCEDURE p2 AS
+  BEGIN
+    SELECT 'This is test2.p2';
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT test2.f1();
+CALL test2.p1();
+--error ER_SP_DOES_NOT_EXIST
+SELECT test2.f2();
+--error ER_SP_DOES_NOT_EXIST
+CALL test2.p2();
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # PACKAGE BODY with forward declarations
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  -- Forward declarations
+  FUNCTION f2private RETURN TEXT;
+  PROCEDURE p2private;
+  -- Public routines
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN f2private();
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p2private;
+  END;
+  -- Definitions for the forward declarations
+  FUNCTION f2private RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f2private';
+  END;
+  PROCEDURE p2private AS
+  BEGIN
+   SELECT 'This is p2private';
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT test2.f1();
+CALL test2.p1();
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Calling private routines with forward declarations,
+--echo # using qualified notation, e.g. "CALL pkg.proc"
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  -- Forward declarations
+  FUNCTION f2private RETURN TEXT;
+  PROCEDURE p2private;
+  -- Public routines
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN test2.f2private();
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL test2.p2private;
+  END;
+  -- Definitions for the forward declarations
+  FUNCTION f2private RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f2private';
+  END;
+  PROCEDURE p2private AS
+  BEGIN
+   SELECT 'This is p2private' AS msg;
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT test2.f1();
+CALL test2.p1();
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Calling private routines, using qualified notation, e.g. "pkg.proc"
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  -- Private routines
+  FUNCTION f2private RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f2private';
+  END;
+  PROCEDURE p2private AS
+  BEGIN
+   SELECT 'This is p2private' AS msg;
+  END;
+  -- Public routines
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN test2.f2private();
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL test2.p2private;
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT test2.f1();
+CALL test2.p1();
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Calling private routines from the package initialization section,
+--echo # using qualified notation, e.g. "pkg.proc"
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  -- Private routines
+  FUNCTION f2private RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f2private';
+  END;
+  PROCEDURE p2private AS
+  BEGIN
+   SELECT 'This is p2private' AS msg;
+  END;
+  -- Public routines
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 'This is p1' AS msg;
+  END;
+BEGIN
+  SELECT test2.f2private();
+  CALL test2.p2private();
+END;
+$$
+DELIMITER ;$$
+CALL test2.p1();
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # Testing OR REPLACE
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE pkg AS
+  FUNCTION f0 RETURN INT;
+END;
+$$
+CREATE OR REPLACE PACKAGE pkg AS
+  FUNCTION f1 RETURN INT;
+END;
+$$
+DELIMITER ;$$
+SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
+
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE BODY pkg AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
+END;
+$$
+DELIMITER ;$$
+SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
+SELECT pkg.f1();
+
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE BODY pkg AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
+END;
+$$
+DELIMITER ;$$
+SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
+SELECT pkg.f1();
+
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE pkg AS
+  FUNCTION f1 RETURN BIGINT;
+END;
+$$
+DELIMITER ;$$
+SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
+--error ER_SP_DOES_NOT_EXIST
+SELECT pkg.f1();
+
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE BODY pkg AS
+  FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
+END;
+$$
+DELIMITER ;$$
+SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
+SELECT pkg.f1();
+
+DROP PACKAGE pkg;
+
+
+--echo #
+--echo # Package routines accessing tables
+--echo #
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  PROCEDURE p1(a INT);
+END;
+$$
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1(a INT) AS
+  BEGIN
+    INSERT INTO t1 VALUES (10);
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL test2.p1(10);
+SELECT * FROM t1;
+DROP PACKAGE test2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # CREATE PACKAGE: Optional package name after the "END" keyword
+--echo #
+
+DELIMITER $$;
+--error ER_END_IDENTIFIER_DOES_NOT_MATCH
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END test2.test2
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_END_IDENTIFIER_DOES_NOT_MATCH
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END test3
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END test2
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+
+--echo #
+--echo # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
+--echo #
+
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN INT;
+  PROCEDURE p1;
+END test2;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    RETURN 10;
+  END f1.f1;
+END test2;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_END_IDENTIFIER_DOES_NOT_MATCH
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    RETURN 10;
+  END f2;
+END test2;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END p1.p1;
+END test2;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_END_IDENTIFIER_DOES_NOT_MATCH
+CREATE PACKAGE BODY test2 AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END p2;
+END test2;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    RETURN 10;
+  END f1;
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END p1;
+END test2;
+$$
+DELIMITER ;$$
+DROP PACKAGE test2;
+
+--echo #
+--echo # Package and package routine name and end name are case insensitive
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE test2 AS
+  FUNCTION f1 RETURN TEXT;
+  PROCEDURE p1;
+END TEST2;
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+CREATE PACKAGE BODY test2 AS
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f1';
+  END F1;
+  PROCEDURE P1 AS
+  BEGIN
+    SELECT 'This is p1' AS msg;
+  END p1;
+END TEST2;
+$$
+DELIMITER ;$$
+SELECT TEST2.F1();
+SELECT test2.f1();
+CALL TEST2.p1();
+CALL test2.P1();
+DROP PACKAGE BODY TEST2;
+DROP PACKAGE TEST2;
+
+
+--echo #
+--echo # Testing various qualified/non-qualified db/package SP call chains
+--echo #
+
+DELIMITER $$;
+CREATE FUNCTION f3() RETURN TEXT AS
+BEGIN
+  SET @track= @track || ' ' || 'test.f3()';
+  RETURN '';
+END;
+$$
+CREATE PROCEDURE p3() AS
+BEGIN
+  SET @track= @track || ' ' || 'test.p3()';
+END;
+$$
+CREATE FUNCTION ff2(task TEXT) RETURN TEXT AS
+  step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+  tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+  rc TEXT;
+BEGIN
+  SET @track= @track || ' ' || 'test.ff2()';
+  CASE step
+    WHEN ''         THEN NULL;
+    WHEN 'p3'       THEN CALL p3();
+    WHEN 'f3'       THEN rc:= f3();
+    WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+    WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+    WHEN 'pack.p3'  THEN CALL pack.p3();
+    WHEN 'pack.f3'  THEN rc:= pack.f3();
+    WHEN 'test.p3'  THEN CALL test.p3();
+    WHEN 'test.f3'  THEN rc:= test.f3();
+    WHEN 'test.pp2' THEN CALL test.pp2(tail);
+    WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+    ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+  END CASE;
+  RETURN '';
+END;
+$$
+CREATE PROCEDURE pp2(task TEXT) AS
+  step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+  tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+  rc TEXT;
+BEGIN
+  SET @track= @track || ' ' || 'test.pp2()';
+  CASE step
+    WHEN ''         THEN NULL;
+    WHEN 'p3'       THEN CALL p3();
+    WHEN 'f3'       THEN rc:= f3();
+    WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+    WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+    WHEN 'pack.p3'  THEN CALL pack.p3();
+    WHEN 'pack.f3'  THEN rc:= pack.f3();
+    WHEN 'test.p3'  THEN CALL test.p3();
+    WHEN 'test.f3'  THEN rc:= test.f3();
+    WHEN 'test.pp2' THEN CALL test.pp2(tail);
+    WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+    ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+  END CASE;
+END;
+$$
+CREATE PACKAGE pack AS
+  PROCEDURE p1(task TEXT);
+  PROCEDURE p2(task TEXT);
+  FUNCTION f1(task TEXT) RETURN TEXT;
+  FUNCTION f2(step2 TEXT) RETURN TEXT;
+  FUNCTION f3 RETURN TEXT;
+  PROCEDURE p3;
+END;
+$$
+CREATE PACKAGE BODY pack AS
+  PROCEDURE p1(task TEXT) AS
+    step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+    tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+    rc TEXT;
+  BEGIN
+    SET @track= 'test.pack.p1()';
+    CASE step
+      WHEN ''         THEN NULL;
+      WHEN 'p2'       THEN CALL p2(tail);
+      WHEN 'f2'       THEN rc:= f2(tail);
+      WHEN 'p3'       THEN CALL p3();
+      WHEN 'f3'       THEN rc:= f3();
+      WHEN 'px'       THEN CALL px();
+      WHEN 'fx'       THEN rc:= fx();
+      WHEN 'pp2'      THEN CALL pp2(tail);
+      WHEN 'ff2'      THEN rc:= ff2(tail);
+      WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+      WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+      WHEN 'pack.p3'  THEN CALL pack.p3();
+      WHEN 'pack.f3'  THEN rc:= pack.f3();
+      WHEN 'pack.px'  THEN CALL pack.px();
+      WHEN 'pack.fx'  THEN rc:= pack.fx();
+      WHEN 'test.p3'  THEN CALL test.p3();
+      WHEN 'test.f3'  THEN rc:= test.f3();
+      WHEN 'test.pp2' THEN CALL test.pp2(tail);
+      WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+      ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+    END CASE;
+    SELECT @track;
+  END;
+
+  FUNCTION f1(task TEXT) RETURN TEXT AS
+    step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+    tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+    rc TEXT;
+  BEGIN
+    SET @track= 'test.pack.f1()';
+    CASE step
+      WHEN ''         THEN NULL;
+      WHEN 'p2'       THEN CALL p2(tail);
+      WHEN 'f2'       THEN rc:= f2(tail);
+      WHEN 'p3'       THEN CALL p3();
+      WHEN 'f3'       THEN rc:= f3();
+      WHEN 'px'       THEN CALL px();
+      WHEN 'fx'       THEN rc:= fx();
+      WHEN 'pp2'      THEN CALL pp2(tail);
+      WHEN 'ff2'      THEN rc:= ff2(tail);
+      WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+      WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+      WHEN 'pack.p3'  THEN CALL pack.p3();
+      WHEN 'pack.f3'  THEN rc:= pack.f3();
+      WHEN 'pack.px'  THEN CALL pack.px();
+      WHEN 'pack.fx'  THEN rc:= pack.fx();
+      WHEN 'test.p3'  THEN CALL test.p3();
+      WHEN 'test.f3'  THEN rc:= test.f3();
+      WHEN 'test.pp2' THEN CALL test.pp2(tail);
+      WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+      ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+    END CASE;
+    SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT=@track;
+    RETURN '';
+  END;
+
+  PROCEDURE p2(task TEXT) AS
+    step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+    tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+    rc TEXT;
+  BEGIN
+    SET @track= @track || ' ' || 'test.pack.p2()';
+    CASE step
+      WHEN ''         THEN NULL;
+      WHEN 'p2'       THEN CALL p2(tail);
+      WHEN 'f2'       THEN rc:= f2(tail);
+      WHEN 'p3'       THEN CALL p3();
+      WHEN 'f3'       THEN rc:= f3();
+      WHEN 'px'       THEN CALL px();
+      WHEN 'fx'       THEN rc:= fx();
+      WHEN 'pp2'      THEN CALL pp2(tail);
+      WHEN 'ff2'      THEN rc:= ff2(tail);
+      WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+      WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+      WHEN 'pack.p3'  THEN CALL pack.p3();
+      WHEN 'pack.f3'  THEN rc:= pack.f3();
+      WHEN 'pack.px'  THEN CALL pack.px();
+      WHEN 'pack.fx'  THEN rc:= pack.fx();
+      WHEN 'test.p3'  THEN CALL test.p3();
+      WHEN 'test.f3'  THEN rc:= test.f3();
+      WHEN 'test.pp2' THEN CALL test.pp2(tail);
+      WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+      ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+    END CASE;
+  END;
+
+  FUNCTION f2(task TEXT) RETURN TEXT AS
+    step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
+    tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
+    rc TEXT;
+  BEGIN
+    SET @track= @track || ' ' || 'test.pack.f2()';
+    CASE step
+      WHEN ''         THEN NULL;
+      WHEN 'p2'       THEN CALL p2(tail);
+      WHEN 'f2'       THEN rc:= f2(tail);
+      WHEN 'p3'       THEN CALL p3();
+      WHEN 'f3'       THEN rc:= f3();
+      WHEN 'px'       THEN CALL px();
+      WHEN 'fx'       THEN rc:= fx();
+      WHEN 'pp2'      THEN CALL pp2(tail);
+      WHEN 'ff2'      THEN rc:= ff2(tail);
+      WHEN 'pack.p2'  THEN CALL pack.p2(tail);
+      WHEN 'pack.f2'  THEN rc:= pack.f2(tail);
+      WHEN 'pack.p3'  THEN CALL pack.p3();
+      WHEN 'pack.f3'  THEN rc:= pack.f3();
+      WHEN 'pack.px'  THEN CALL pack.px();
+      WHEN 'pack.fx'  THEN rc:= pack.fx();
+      WHEN 'test.p3'  THEN CALL test.p3();
+      WHEN 'test.f3'  THEN rc:= test.f3();
+      WHEN 'test.pp2' THEN CALL test.pp2(tail);
+      WHEN 'test.ff2' THEN rc:= test.ff2(tail);
+      ELSE SET @track= @track || ' ' || step || ' [unknown step]';
+    END CASE;
+    RETURN '';
+  END;
+  PROCEDURE p3 AS
+  BEGIN
+    SET @track= @track || ' ' || 'test.pack.p3()';
+  END;
+  FUNCTION f3 RETURN TEXT AS
+  BEGIN
+    SET @track= @track || ' ' || 'test.pack.f3()';
+    RETURN '';
+  END;
+
+END pack;
+$$
+DELIMITER ;$$
+
+SET max_sp_recursion_depth=10;
+
+--echo # pack.routine -> *
+
+CALL pack.p1('p2');
+CALL pack.p1('f2');
+--error ER_SP_DOES_NOT_EXIST
+CALL pack.p1('px');
+--error ER_SP_DOES_NOT_EXIST
+CALL pack.p1('fx');
+
+CALL pack.p1('pp2');
+CALL pack.p1('ff2');
+
+CALL pack.p1('pack.p2');
+CALL pack.p1('pack.f2');
+--error ER_SP_DOES_NOT_EXIST
+CALL pack.p1('pack.px');
+--error ER_SP_DOES_NOT_EXIST
+CALL pack.p1('pack.fx');
+
+CALL pack.p1('test.pp2');
+CALL pack.p1('test.ff2');
+
+DO pack.f1('p2');
+DO pack.f1('f2');
+--error ER_SP_DOES_NOT_EXIST
+DO pack.p1('px');
+--error ER_SP_DOES_NOT_EXIST
+DO pack.p1('fx');
+
+DO pack.f1('pp2');
+DO pack.f1('ff2');
+
+DO pack.f1('pack.p2');
+DO pack.f1('pack.f2');
+--error ER_SP_DOES_NOT_EXIST
+SELECT pack.f1('pack.px');
+--error ER_SP_DOES_NOT_EXIST
+SELECT pack.f1('pack.fx');
+
+DO pack.f1('test.pp2');
+DO pack.f1('test.ff2');
+
+--echo #
+--echo # Qualified_package_routine -> Non_qualified_package_routine
+--echo #
+
+--echo # pack.routine -> [pack.]routine -> pack.routine
+
+CALL pack.p1('p2 pack.p3');
+CALL pack.p1('p2 pack.f3');
+CALL pack.p1('f2 pack.p3');
+CALL pack.p1('f2 pack.f3');
+
+DO pack.f1('p2 pack.p3');
+DO pack.f1('p2 pack.f3');
+DO pack.f1('f2 pack.p3');
+DO pack.f1('f2 pack.f3');
+
+--echo # pack.routine -> [pack.]routine -> [pack]routine
+
+CALL pack.p1('p2 p3');
+CALL pack.p1('p2 f3');
+CALL pack.p1('f2 p3');
+CALL pack.p1('f2 f3');
+
+DO pack.f1('p2 p3');
+DO pack.f1('p2 f3');
+DO pack.f1('f2 p3');
+DO pack.f1('f2 f3');
+
+--echo # pack.routine -> [pack.]routine -> test.routine
+
+CALL pack.p1('p2 test.p3');
+CALL pack.p1('p2 test.f3');
+CALL pack.p1('f2 test.p3');
+CALL pack.p1('f2 test.f3');
+
+DO pack.f1('p2 test.p3');
+DO pack.f1('p2 test.f3');
+DO pack.f1('f2 test.p3');
+DO pack.f1('f2 test.f3');
+
+--echo # pack.routine -> [pack.]routine -> [test.]routine
+
+CALL pack.p1('p2 pp2');
+CALL pack.p1('p2 ff2');
+CALL pack.p1('f2 pp2');
+CALL pack.p1('f2 ff2');
+
+DO pack.f1('p2 pp2');
+DO pack.f1('p2 ff2');
+DO pack.f1('f2 pp2');
+DO pack.f1('f2 ff2');
+
+
+--echo #
+--echo # Qualified_package_routine -> Non_qualified_database_routine
+--echo #
+
+--echo # pack.routine -> [test.]routine -> pack.routine
+
+CALL pack.p1('pp2 pack.p3');
+CALL pack.p1('pp2 pack.f3');
+CALL pack.p1('ff2 pack.p3');
+CALL pack.p1('ff2 pack.f3');
+
+DO pack.f1('pp2 pack.p3');
+DO pack.f1('pp2 pack.f3');
+DO pack.f1('ff2 pack.p3');
+DO pack.f1('ff2 pack.f3');
+
+--echo # pack.routine -> [test.]routine -> test.routine
+
+CALL pack.p1('pp2 test.p3');
+CALL pack.p1('pp2 test.f3');
+CALL pack.p1('ff2 test.p3');
+CALL pack.p1('ff2 test.f3');
+
+DO pack.f1('pp2 test.p3');
+DO pack.f1('pp2 test.f3');
+DO pack.f1('ff2 test.p3');
+DO pack.f1('ff2 test.f3');
+
+--echo # pack.routine -> [test.]routine -> [test.]routine
+
+CALL pack.p1('pp2 p3');
+CALL pack.p1('pp2 f3');
+CALL pack.p1('ff2 p3');
+CALL pack.p1('ff2 f3');
+
+DO pack.f1('pp2 p3');
+DO pack.f1('pp2 f3');
+DO pack.f1('ff2 p3');
+DO pack.f1('ff2 f3');
+
+
+--echo #
+--echo # Qualified_package_routine -> Qualified_package_routine
+--echo #
+
+--echo # pack.routine -> pack.routine -> pack.routine
+
+CALL pack.p1('pack.p2 pack.p3');
+CALL pack.p1('pack.p2 pack.f3');
+CALL pack.p1('pack.f2 pack.p3');
+CALL pack.p1('pack.f2 pack.f3');
+
+DO pack.f1('pack.p2 pack.p3');
+DO pack.f1('pack.p2 pack.f3');
+DO pack.f1('pack.f2 pack.p3');
+DO pack.f1('pack.f2 pack.f3');
+
+--echo # pack.routine -> pack.routine -> [pack.]routine
+
+CALL pack.p1('pack.p2 p3');
+CALL pack.p1('pack.p2 f3');
+CALL pack.p1('pack.f2 p3');
+CALL pack.p1('pack.f2 f3');
+
+DO pack.f1('pack.p2 p3');
+DO pack.f1('pack.p2 f3');
+DO pack.f1('pack.f2 p3');
+DO pack.f1('pack.f2 f3');
+
+--echo # pack.routine -> pack.routine -> test.routine
+
+CALL pack.p1('pack.p2 test.p3');
+CALL pack.p1('pack.p2 test.f3');
+CALL pack.p1('pack.f2 test.p3');
+CALL pack.p1('pack.f2 test.f3');
+
+DO pack.f1('pack.p2 test.p3');
+DO pack.f1('pack.p2 test.f3');
+DO pack.f1('pack.f2 test.p3');
+DO pack.f1('pack.f2 test.f3');
+
+--echo # pack.routine -> pack.routine -> [test.]routine
+
+CALL pack.p1('pack.p2 pp2');
+CALL pack.p1('pack.p2 ff2');
+CALL pack.p1('pack.f2 pp2');
+CALL pack.p1('pack.f2 ff2');
+
+DO pack.f1('pack.p2 pp2');
+DO pack.f1('pack.p2 ff2');
+DO pack.f1('pack.f2 pp2');
+DO pack.f1('pack.f2 ff2');
+
+
+--echo #
+--echo # Qualified_package_routine -> Qualified_database_routine
+--echo #
+
+--echo pack.routine -> test.routine -> pack.routine
+
+CALL pack.p1('test.pp2 pack.p3');
+CALL pack.p1('test.pp2 pack.f3');
+CALL pack.p1('test.ff2 pack.p3');
+CALL pack.p1('test.ff2 pack.f3');
+
+DO pack.f1('test.pp2 pack.p3');
+DO pack.f1('test.pp2 pack.f3');
+DO pack.f1('test.ff2 pack.p3');
+DO pack.f1('test.ff2 pack.f3');
+
+--echo pack.routine -> test.routine -> test.routine
+
+CALL pack.p1('test.pp2 test.p3');
+CALL pack.p1('test.pp2 test.f3');
+CALL pack.p1('test.ff2 test.p3');
+CALL pack.p1('test.ff2 test.f3');
+
+DO pack.f1('test.pp2 test.p3');
+DO pack.f1('test.pp2 test.f3');
+DO pack.f1('test.ff2 test.p3');
+DO pack.f1('test.ff2 test.f3');
+
+--echo pack.routine -> test.routine -> [test.]routine
+
+CALL pack.p1('test.pp2 p3');
+CALL pack.p1('test.pp2 f3');
+CALL pack.p1('test.ff2 p3');
+CALL pack.p1('test.ff2 f3');
+
+DO pack.f1('test.pp2 p3');
+DO pack.f1('test.pp2 f3');
+DO pack.f1('test.ff2 p3');
+DO pack.f1('test.ff2 f3');
+
+
+--echo # Longer chains
+
+CALL pack.p1('p2 f2 p2 test.pp2 test.ff2 pack.p3');
+CALL pack.p1('p2 test.pp2 pack.p2 pack.f2 test.ff2 pack.p3');
+
+
+DROP PACKAGE pack;
+DROP FUNCTION f3;
+DROP PROCEDURE p3;
+DROP FUNCTION ff2;
+DROP PROCEDURE pp2;
+
+
+--echo #
+--echo # Calling a standalone function from a non-current database,
+--echo # which calls a package routine from the same non-current database.
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1 AS
+BEGIN
+  CALL pkg1.p1;
+END;
+$$
+CREATE PACKAGE pkg1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT database();
+  END;
+END;
+$$
+DELIMITER ;$$
+# Current database
+CALL p1;
+CREATE DATABASE test2;
+USE test2;
+# Non-current database
+CALL test.p1;
+DROP DATABASE test2;
+# No current database at all
+CALL test.p1;
+USE test;
+DROP PACKAGE pkg1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Creating a package with a different DEFINER
+--echo #
+
+CREATE USER xxx@localhost;
+DELIMITER $$;
+CREATE DEFINER=xxx@localhost PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
+DROP PACKAGE p1;
+DROP USER xxx@localhost;
+
+--echo #
+--echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER
+--echo #
+
+CREATE USER xxx@localhost;
+DELIMITER $$;
+CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
+  PROCEDURE p1;
+END;
+$$
+CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
+DROP PACKAGE p1;
+DROP USER xxx@localhost;
+
+--echo #
+--echo # A package with an initialization section
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
+  FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
+BEGIN
+  SET @a:=10;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # A package with an initialization section calling
+--echo # routines from the same package, and standalone routines.
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE init20 AS
+BEGIN
+  SET @msg= @msg || '[init20]';
+END;
+$$
+CREATE PACKAGE p1 AS
+  PROCEDURE init1;
+  PROCEDURE init2;
+  FUNCTION init3 RETURN INT;
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE init1 AS
+  BEGIN
+    SET @msg= @msg || '[p1.init1]';
+  END;
+  PROCEDURE init2 AS
+  BEGIN
+    SET @msg= @msg || '[p1.init2]';
+  END;
+  FUNCTION init3 RETURN INT AS
+  BEGIN
+    SET @msg= @msg || '[p1.init3]';
+    RETURN 0;
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    SET @msg= @msg || '[p1.p1]';
+    SELECT @msg;
+  END;
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    SET @msg= @msg || '[p1.f1]';
+    RETURN @msg;
+  END;
+BEGIN
+  SET @msg= '';
+  init1();
+  init2();
+  DO init3();
+  init20();
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+DROP PROCEDURE init20;
+
+
+--echo #
+--echo # EXECUTE IMMEDIATE in the package initialization section
+--echo #
+
+SET @a=1000;
+CREATE TABLE t1 AS SELECT 10 AS a;
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
+  FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
+BEGIN
+  EXECUTE IMMEDIATE 'SELECT MAX(a) FROM t1 INTO @a';
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
+SELECT p1.f1();
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # A package with an initialization section, loading table data into a user variable
+--echo #
+
+SET @a=1000;
+CREATE TABLE t1 AS SELECT 10 AS a;
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
+  FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
+BEGIN
+  SELECT MAX(a) FROM t1 INTO @a;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # A package with an initialization section producing an error
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
+  FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
+BEGIN
+  SELECT 1 FROM t1 INTO @a;
+END;
+$$
+DELIMITER ;$$
+--error ER_NO_SUCH_TABLE
+CALL p1.p1();
+--error ER_NO_SUCH_TABLE
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+--error ER_NO_SUCH_TABLE
+SELECT p1.f1();
+--error ER_NO_SUCH_TABLE
+CALL p1.p1();
+--error ER_NO_SUCH_TABLE
+SELECT p1.f1();
+CREATE TABLE t1 (a INT) AS SELECT 1;
+CALL p1.p1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+CALL p1.p1();
+DROP TABLE t1;
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # A package with SF-unsafe statements in the initialization section
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
+  FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
+BEGIN
+  CREATE TABLE IF NOT EXISTS t1 (a INT);
+  DROP TABLE IF EXISTS t1;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+--error ER_SP_DUP_VAR
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  a INT;
+  PROCEDURE p1 AS
+  BEGIN
+    CREATE VIEW v1 AS SELECT a;
+  END;
+END;
+$$
+--error ER_PARSE_ERROR
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END;
+  b INT; -- Variables cannot go after routine definitions
+END;
+$$
+--error ER_VIEW_SELECT_VARIABLE
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  PROCEDURE p1 AS
+  BEGIN
+    CREATE VIEW v1 AS SELECT a;
+  END;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT:=NULL;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT a;
+    a:=COALESCE(a,0)+100;
+    SET a=a+1;
+  END;
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    RETURN a;
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+CALL p1.p1;
+CALL p1.p1;
+SELECT p1.f1();
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # One package variable with a default value
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT:=10;
+  PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
+  FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a ROW (a INT, b TEXT):=ROW(10,'bbb');
+  PROCEDURE p1 AS
+  BEGIN
+    a.a:= a.a+1;
+    a.b:= a.b || 'B';
+    SELECT a.a, a.b;
+  END;
+  FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+
+
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a t1.a%TYPE:=10;
+  PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
+  FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (a INT, b TEXT);
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a t1%ROWTYPE:=ROW(10,'bbb');
+  PROCEDURE p1 AS
+  BEGIN
+    a.a:= a.a+1;
+    a.b:= a.b || 'B';
+    SELECT a.a, a.b;
+  END;
+  FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # One package variable, set in the package initialization section
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
+  FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
+BEGIN
+  a:=10;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+CALL p1.p1();
+SELECT p1.f1();
+CALL p1.p1();
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # A package with an initialization section,
+--echo # loading table data into a package variable
+--echo #
+
+CREATE TABLE t1 AS SELECT 10 AS a;
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  PROCEDURE p1 AS BEGIN SET a=a+1; SELECT a; END;
+  FUNCTION f1 RETURN INT AS BEGIN SET a=a+1; RETURN a; END;
+BEGIN
+  a:=(SELECT MAX(t1.a) FROM t1);
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1();
+CALL p1.p1();
+SELECT p1.f1();
+SELECT p1.f1();
+--source sp-cache-invalidate.inc
+SELECT p1.f1();
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Package variables and XPath
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  i INT:=0;
+  xml TEXT:= '<a><b>b1</b><b>b2</b><b>b3</b></a>';
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    SET i=i+1;
+    RETURN ExtractValue(xml, '/a/b[$i]');
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT p1.f1();
+SELECT p1.f1();
+SELECT p1.f1();
+DROP PACKAGE p1;
+
+--echo #
+--echo # Package variables as OUT routine parameter
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  b INT;
+  c INT:=10;
+  PROCEDURE p2(a OUT INT) AS
+  BEGIN
+    a:=c;
+    c:=c+1;
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p2(b);
+    SELECT a,b;
+  END;
+BEGIN
+  CALL p2(a);
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a ROW(a INT, b TEXT);
+  b ROW(a INT, b TEXT);
+  c ROW(a INT, b TEXT):=ROW(1,'b');
+  PROCEDURE p2(x OUT ROW(a INT,b TEXT)) AS
+  BEGIN
+    x:=c;
+    x.a:=c.a+100;
+    x.b:=c.b||'X';
+    c.a:=c.a+1;
+    c.b:=c.b||'B';
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p2(b);
+    SELECT a.a,a.b,b.a,b.b;
+  END;
+BEGIN
+  CALL p2(a);
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+
+
+CREATE TABLE t1 (a INT,b TEXT);
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a t1%ROWTYPE;
+  b t1%ROWTYPE;
+  c t1%ROWTYPE:=ROW(1,'b');
+  PROCEDURE p2(x OUT t1%ROWTYPE) AS
+  BEGIN
+    x:=c;
+    x.a:=c.a+100;
+    x.b:=c.b||'X';
+    c.a:=c.a+1;
+    c.b:=c.b||'B';
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p2(b);
+    SELECT a.a,a.b,b.a,b.b;
+  END;
+BEGIN
+  CALL p2(a);
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Package variable fields as OUT routine parameters
+--echo #
+
+CREATE TABLE t1 (a INT,b TEXT);
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a t1%ROWTYPE;
+  x t1%ROWTYPE:=ROW(10,'b');
+  PROCEDURE p2(a OUT INT,b OUT TEXT) AS
+  BEGIN
+    a:=x.a;
+    b:=x.b;
+    x.a:=x.a+1;
+    x.b:=x.b||'B';
+  END;
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p2(a.a, a.b);
+    SELECT a.a,a.b;
+  END;
+BEGIN
+  CALL p2(a.a, a.b);
+  SELECT a.a, a.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Package variables as SELECT INTO targets
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a INT;
+  b INT;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 2 INTO b;
+    SELECT a,b;
+  END;
+BEGIN
+  SELECT 1 INTO a;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+
+
+CREATE TABLE t1 (a INT, b TEXT);
+INSERT INTO t1 VALUES (10,'b');
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a t1%ROWTYPE;
+  b t1%ROWTYPE;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT * FROM t1 INTO a;
+    SELECT a.a,a.b;
+  END;
+BEGIN
+  SELECT * FROM t1 INTO b;
+  SELECT b.a, b.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Package variable fields as SELECT INTO targets
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  a ROW(a INT, b TEXT);
+  b ROW(a INT, b TEXT);
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 20,'x2' INTO b.a,b.b;
+    SELECT a.a,a.b,b.a,b.b;
+  END;
+BEGIN
+  SELECT 10,'x1' INTO a.a,a.b;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # Recursive package procedure calls
+--echo # Makes sure that the non-top sp_head instances created by
+--echo # sp_clone_and_link_routine() correctly reproduce the package context:
+--echo # package variables, package routines.
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1(c INT);
+END p1;
+$$
+CREATE PACKAGE BODY p1 AS
+  pv1 INT:=10;
+  FUNCTION f1 RETURN INT AS BEGIN RETURN pv1+100; END;
+  PROCEDURE p1(c INT) AS
+  BEGIN
+    SELECT c, pv1, f1();
+    IF c>0 THEN
+       pv1:=pv1+1;
+       CALL p1(c-1);
+    END IF;
+  END;
+END;
+$$
+DELIMITER ;$$
+SET max_sp_recursion_depth=5;
+CALL p1.p1(5);
+SET max_sp_recursion_depth=0;
+CALL p1.p1(0);
+--error ER_SP_RECURSION_LIMIT
+CALL p1.p1(1);
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # Non-reserved keywords as package body variable names
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+END p1;
+$$
+CREATE PACKAGE BODY p1 AS
+  ascii INT:=10;
+  action INT:=20;
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT ascii, action;
+  END;
+BEGIN
+  ascii := ascii + 1;
+  action := action + 1;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+DROP PACKAGE p1;
+
+
+--echo #
+--echo # Package routines calling routines of another package
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE p1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE p2 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY p1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 'This is p1.p1' AS msg;
+  END;
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is p1.f1';
+  END;
+END;
+$$
+CREATE PACKAGE BODY p2 AS
+  PROCEDURE p1 AS
+  BEGIN
+    CALL p1.p1;
+  END;
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN p1.f1();
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL p1.p1;
+CALL p2.p1;
+SELECT p1.f1(), p2.f1();
+DROP PACKAGE p2;
+DROP PACKAGE p1;
+
+--echo #
+--echo # Package names with dot characters
+--echo #
+
+DELIMITER $$;
+CREATE PACKAGE "p1.p1" AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY "p1.p1" AS
+  PROCEDURE p1 AS
+  BEGIN
+    SELECT 'This is p1' AS msg;
+  END;
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is f1';
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL "p1.p1"."p1";
+SELECT "p1.p1"."f1"();
+DROP PACKAGE "p1.p1";
+
+
+--echo #
+--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine
+--echo #
+
+SET sql_mode=ORACLE;
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE pkg1 AS
+  PROCEDURE p00();
+END;
+$$
+CREATE OR REPLACE PACKAGE BODY pkg1 AS
+  PROCEDURE p01() AS
+  BEGIN
+    SELECT 'This is p01' AS msg;
+  END;
+  PROCEDURE p00() AS
+  BEGIN
+    CREATE OR REPLACE VIEW v1 AS SELECT 1;
+    DROP VIEW v1;
+    CALL p01();
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL pkg1.p00;
+DROP PACKAGE pkg1;
+
+
+CREATE OR REPLACE TABLE t1 (a INT);
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
+DELIMITER $$;
+CREATE OR REPLACE PACKAGE pkg1 AS
+  PROCEDURE p00();
+END;
+$$
+CREATE OR REPLACE PACKAGE BODY pkg1 AS
+  PROCEDURE p01() AS
+  BEGIN
+    SELECT 'This is p01' AS msg;
+  END;
+  PROCEDURE p00() AS
+  BEGIN
+    DROP TRIGGER tr1;
+    CALL p01();
+  END;
+END;
+$$
+DELIMITER ;$$
+CALL pkg1.p00;
+DROP PACKAGE pkg1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
+--echo #
+
+CREATE TABLE t1 (
+  CTR varchar(2) NOT NULL,
+  COR varchar(3) NOT NULL,
+  DATE datetime NOT NULL,
+  CHAN varchar(4) NOT NULL,
+  CNO varchar(20) NOT NULL,
+  JOBN varchar(18) NOT NULL,
+  C1 varchar(30) DEFAULT NULL,
+  C2 varchar(30) DEFAULT NULL,
+  TIME datetime DEFAULT NULL,
+  AMT decimal(12,2) DEFAULT NULL,
+  DT datetime NOT NULL,
+  pk int(11) NOT NULL,
+  PRIMARY KEY (pk),
+   KEY Indx1 (JOBN)
+);
+
+DELIMITER $$;
+
+CREATE PACKAGE xyz IS
+  PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
+END;
+$$
+
+CREATE OR REPLACE PACKAGE BODY xyz IS
+  PROCEDURE xyz123(
+    ctr IN VARCHAR2,
+    Jn IN VARCHAR2,
+    R OUT VARCHAR2)
+  AS
+    lS NUMBER(10) :=0;
+    CURSOR cBPD IS
+      SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
+      FROM t1 WHERE JOBN=Jn;
+  BEGIN
+    FOR lbpd IN cBPD
+    LOOP
+      lS:=lS+1;
+    END LOOP;
+  EXCEPTION
+    WHEN OTHERS THEN
+    BEGIN
+      SELECT SQLERRM;
+    END;
+  END;
+END $$
+DELIMITER ;$$
+
+CALL xyz.xyz123(17,18,@R);
+DROP PACKAGE xyz;
+DROP TABLE t1;
+--disable_prepare_warnings
+
+
+--echo #
+--echo # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
+--echo #
+
+--error ER_WRONG_DB_NAME
+SELECT `db `.pkg.func();
+--error ER_SP_WRONG_NAME
+SELECT db.`pkg `.func();
+--error ER_SP_WRONG_NAME
+SELECT db.pkg.`func `();
+
+
+CREATE DATABASE db1;
+USE db1;
+
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+  FUNCTION f1 RETURN TEXT;
+  FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
+  FUNCTION f2_pkg1_f1 RETURN TEXT;
+  FUNCTION f2_f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1
+AS
+  FUNCTION f1 RETURN TEXT IS
+  BEGIN
+    RETURN 'This is db1.pkg1.f1';
+  END;
+  FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
+  BEGIN
+    RETURN db1.pkg1.f1();
+  END;
+  FUNCTION f2_pkg1_f1 RETURN TEXT IS
+  BEGIN
+    RETURN pkg1.f1();
+  END;
+  FUNCTION f2_f1 RETURN TEXT IS
+  BEGIN
+    RETURN f1();
+  END;
+END;
+$$
+DELIMITER ;$$
+
+USE db1;
+SELECT pkg1.f2_db1_pkg1_f1();
+SELECT pkg1.f2_pkg1_f1();
+SELECT pkg1.f2_f1();
+
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+SELECT db1.pkg1.f2_pkg1_f1();
+SELECT db1.pkg1.f2_f1();
+
+USE test;
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+SELECT db1.pkg1.f2_pkg1_f1();
+SELECT db1.pkg1.f2_f1();
+
+DROP DATABASE db1;
+
+
+#
+# Testing db.pkg.func() in the package initialization section
+#
+
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+
+DELIMITER $$;
+CREATE PACKAGE db1.pkg1 AS
+  FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db1.pkg1 AS
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is db1.pkg1.f1';
+  END;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PACKAGE db2.pkg1 AS
+  FUNCTION f1 RETURN TEXT;
+  FUNCTION var1 RETURN TEXT;
+  FUNCTION var2 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db2.pkg1 AS
+  m_var1 TEXT;
+  m_var2 TEXT;
+  FUNCTION f1 RETURN TEXT AS
+  BEGIN
+    RETURN 'This is db2.pkg1.f1';
+  END;
+  FUNCTION var1 RETURN TEXT AS
+  BEGIN
+    RETURN m_var1;
+  END;
+  FUNCTION var2 RETURN TEXT AS
+  BEGIN
+    RETURN m_var2;
+  END;
+BEGIN
+  m_var1:= db1.pkg1.f1();
+  m_var2:= db2.pkg1.f1();
+END;
+$$
+DELIMITER ;$$
+
+SELECT db2.pkg1.var1(), db2.pkg1.var2();
+
+DROP DATABASE db1;
+DROP DATABASE db2;
+
+#
+# Make sure fully qualified package function call does not support AS syntax:
+#   SELECT db.pkg.func(10 AS a);
+#
+
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+  FUNCTION f1(a TEXT) RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+  FUNCTION f1(a TEXT) RETURN TEXT AS
+  BEGIN
+    RETURN a;
+  END;
+END;
+$$
+DELIMITER ;$$
+SELECT test.pkg1.f1('xxx');
+--error ER_PARSE_ERROR
+SELECT test.pkg1.f1('xxx' AS a);
+DROP PACKAGE pkg1;
+
+
+--echo #
+--echo # MDEV-19328 sql_mode=ORACLE: Package function in VIEW
+--echo #
+
+SET sql_mode=ORACLE;
+DELIMITER $$;
+CREATE PACKAGE test1 AS
+  FUNCTION f_test RETURN number;
+END test1;
+$$
+CREATE PACKAGE BODY test1
+AS
+  FUNCTION f_test RETURN NUMBER IS
+  BEGIN
+    RETURN 1;
+  END;
+END test1;
+$$
+DELIMITER ;$$
+
+
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+
+SET sql_mode=DEFAULT;
+--error ER_SP_DOES_NOT_EXIST
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+
+
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+
+SET sql_mode=DEFAULT;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=ORACLE;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+SET sql_mode=ORACLE;
+DROP PACKAGE test1;
+
+
+--echo #
+--echo # MDEV-19804 sql_mode=ORACLE: call procedure in packages
+--echo #
+
+--error ER_WRONG_DB_NAME
+CALL `db1 `.pkg.p;
+--error ER_SP_WRONG_NAME
+CALL db1.`pkg `.p;
+--error ER_SP_WRONG_NAME
+CALL db1.pkg.`p `;
+
+
+SET sql_mode=ORACLE;
+DELIMITER $$;
+CREATE PACKAGE pkg1 as
+  PROCEDURE p1();
+END;
+$$
+CREATE PACKAGE BODY pkg1 as
+  PROCEDURE p1() as
+  BEGIN
+    SELECT 'test-function' AS c1;
+  END;
+END;
+$$
+DELIMITER ;$$
+
+CALL pkg1.p1;
+CALL test.pkg1.p1;
+
+# In sql_mode=DEFAULT we support fully qualified package function names
+# (this is needed for VIEWs). Let's make sure we also support fully
+# qualified package procedure names, for symmetry
+
+SET sql_mode=DEFAULT;
+CALL test.pkg1.p1;
+SET sql_mode=ORACLE;
+
+DELIMITER $$;
+BEGIN
+  CALL pkg1.p1;
+  CALL test.pkg1.p1;
+END
+$$
+DELIMITER ;$$
+
+DELIMITER $$;
+BEGIN
+  pkg1.p1;
+  test.pkg1.p1;
+END
+$$
+DELIMITER ;$$
+
+DROP PACKAGE pkg1;
+
+
+#
+# Testing packages in different databases calling each other
+# in routines and in the initialization section.
+#
+
+CREATE DATABASE db1;
+DELIMITER $$;
+CREATE PACKAGE db1.pkg1 AS
+  PROCEDURE p1(a OUT TEXT);
+END;
+$$
+CREATE PACKAGE BODY db1.pkg1 AS
+  PROCEDURE p1(a OUT TEXT) AS
+  BEGIN
+    a:= 'This is db1.pkg1.p1';
+  END;
+END;
+$$
+DELIMITER ;$$
+
+CREATE DATABASE db2;
+DELIMITER $$;
+CREATE PACKAGE db2.pkg1 AS
+  FUNCTION var1 RETURN TEXT;
+  PROCEDURE p1(a OUT TEXT);
+  PROCEDURE p2_db1_pkg1_p1;
+END;
+$$
+CREATE PACKAGE BODY db2.pkg1 AS
+  m_var1 TEXT;
+  FUNCTION var1 RETURN TEXT AS
+  BEGIN
+    RETURN m_var1;
+  END;
+  PROCEDURE p1(a OUT TEXT) AS
+  BEGIN
+    a:= 'This is db2.pkg1.p1';
+  END;
+  PROCEDURE p2_db1_pkg1_p1 AS
+    a TEXT;
+  BEGIN
+    db1.pkg1.p1(a);
+    SELECT a;
+  END;
+BEGIN
+  db1.pkg1.p1(m_var1);
+END;
+$$
+DELIMITER ;$$
+
+SELECT db2.pkg1.var1();
+CALL db2.pkg1.p2_db1_pkg1_p1;
+--enable_ps2_protocol
+
+DROP DATABASE db1;
+DROP DATABASE db2;
+
+
+--echo #
+--echo # MDEV-29370 Functions in packages are slow and seems to ignore deterministic
+--echo #
+
+SET SQL_MODE=ORACLE;
+
+CREATE TABLE t1 (c1 CHAR(1));
+
+DELIMITER //;
+CREATE FUNCTION f1_deterministic()
+RETURN CHAR(1)
+DETERMINISTIC
+IS
+BEGIN
+   RETURN 'X';
+END;
+//
+
+CREATE FUNCTION f2_not_deterministic()
+RETURN CHAR(1)
+IS
+BEGIN
+   RETURN 'X';
+END;
+//
+
+CREATE PACKAGE pkg1
+IS
+  PROCEDURE t1_populate(numrows INTEGER);
+  FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC;
+  FUNCTION f4_not_deterministic() RETURN CHAR(1);
+END;
+//
+
+CREATE PACKAGE BODY pkg1
+IS
+  PROCEDURE t1_populate(numrounds INTEGER)
+  IS
+    i INTEGER;
+  BEGIN
+    INSERT INTO t1 VALUES('Y');
+    FOR i IN 1..numrounds LOOP
+      INSERT INTO t1 SELECT * FROM t1;
+    END LOOP;
+  END;
+  FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC COMMENT 'xxx'
+  IS
+  BEGIN
+     RETURN 'X';
+  END;
+  FUNCTION f4_not_deterministic() RETURN CHAR(1)
+  IS
+  BEGIN
+     RETURN 'X';
+  END;
+END;
+//
+DELIMITER ;//
+
+CALL pkg1.t1_populate(3);
+
+EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM t1 WHERE c1 = f1_deterministic();
+EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM t1 WHERE c1 = f2_not_deterministic();
+EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f3_deterministic();
+EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f4_not_deterministic();
+
+DROP TABLE t1;
+DROP FUNCTION f1_deterministic;
+DROP FUNCTION f2_not_deterministic;
+DROP PACKAGE pkg1;