summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/compat/oracle/t/sp-package.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/compat/oracle/t/sp-package.test')
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-package.test3092
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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-25 00:50:27 +0000