SET sql_mode=ORACLE;
#
# Creating a body of a non-existing package
#
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
ERROR 42000: PACKAGE test.test2 does not exist
#
# Dropping a non-existing package
#
DROP PACKAGE test2;
ERROR 42000: PACKAGE test.test2 does not exist
DROP PACKAGE IF EXISTS test2;
Warnings:
Note	1305	PACKAGE test.test2 does not exist
DROP PACKAGE BODY test2;
ERROR 42000: PACKAGE BODY test.test2 does not exist
#
# Bad combinations of OR REPLACE and IF EXISTS
#
CREATE OR REPLACE PACKAGE IF NOT EXISTS pkg AS
PROCEDURE p1;
END;
$$
ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
CREATE OR REPLACE PACKAGE BODY IF NOT EXISTS pkg AS
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
#
# PACKAGE and PS
#
PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2';
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
PREPARE stmt FROM 'CREATE PACKAGE BODY test2 AS'
                  '  FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;'
                  'END test2';
DROP PACKAGE test2;
#
# Package and READ ONLY transactions
#
SET SESSION TRANSACTION READ ONLY;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
$$
ERROR 25006: Cannot execute statement in a READ ONLY transaction
SET SESSION TRANSACTION READ WRITE;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
SET SESSION TRANSACTION READ ONLY
$$
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;
$$
ERROR 25006: Cannot execute statement in a READ ONLY transaction
SET SESSION TRANSACTION READ WRITE;
DROP PACKAGE test2;
SET SESSION TRANSACTION READ ONLY;
DROP PACKAGE test2;
ERROR 25006: Cannot execute statement in a READ ONLY transaction
DROP PACKAGE BODY test2;
ERROR 25006: Cannot execute statement in a READ ONLY transaction
SET SESSION TRANSACTION READ WRITE;
#
# Syntax error inside a CREATE PACKAGE, inside a routine definition
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
FUNCTION f3;
FUNCTION f4 RETURN INT;
END
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';
FUNCTION f4 RETURN INT;
END' at line 4
#
# Syntax error inside a CREATE PACKAGE, outside of a routine definition
#
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
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS BEGIN RETURN 10; END;
FUNCTION f4 RETURN INT;
END' at line 4
#
# Syntax error inside a CREATE PACKAGE BODY, inside a routine definition
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
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
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
END' at line 3
DROP PACKAGE test2;
#
# Syntax error inside a CREATE PACKAGE BODY, outside a routine definition
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
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
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SOME SYNTAX ERROR;
FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
END' at line 3
DROP PACKAGE test2;
#
# Syntax error inside a CREATE PACKAGE BODY executable section
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
BEGIN
SOME SYNTAX ERROR;
END
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SYNTAX ERROR;
END' at line 4
DROP PACKAGE test2;
#
# CREATE PROCEDURE inside a package PROCEDURE is not allowed
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
CREATE PROCEDURE p1 AS BEGIN NULL; END;
END;
END;
$$
ERROR 2F003: Can't create a PROCEDURE from within another stored routine
DROP PACKAGE test2;
#
# CREATE PACKAGE inside a package PROCEDURE is not allowed
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
CREATE PACKAGE p1 AS PROCEDURE p1; END;
END;
END;
$$
ERROR 2F003: Can't create a PACKAGE from within another stored routine
DROP PACKAGE test2;
#
# CREATE PROCEDURE inside a package executable section is not allowed
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
ERROR 2F003: Can't create a PROCEDURE from within another stored routine
DROP PACKAGE test2;
#
# CREATE FUNCTION inside a package executable section is not allowed
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
END;
$$
ERROR 2F003: Can't create a FUNCTION from within another stored routine
DROP PACKAGE test2;
#
# CREATE PACKAGE inside a package executable section is not allowed
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE PACKAGE p1 AS PROCEDURE p1; END;
END;
$$
ERROR 2F003: Can't create a PACKAGE from within another stored routine
DROP PACKAGE test2;
#
# Broken CREATE PACKAGE at CREATE PACKAGE BODY time
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
UPDATE mysql.proc SET `body`='garbage'
  WHERE db='test' AND name='test2' AND type='PACKAGE';
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT
AS BEGIN
RETURN f2();
END;
END;
$$
ERROR HY000: Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level	Code	Message
Error	1064	You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'garbage' at line 1
Error	1457	Failed to load routine test.test2 (internal code -6). For more details, run SHOW WARNINGS
DROP PACKAGE test2;
#
# Broken CREATE PACKAGE at a package function call time
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT
AS BEGIN
RETURN f2();
END;
END;
$$
SELECT test2.f1();
ERROR 42000: FUNCTION test.f2 does not exist
UPDATE mysql.proc SET `body`='garbage'
  WHERE db='test' AND name='test2' AND type='PACKAGE';
# sp-cache-invalidate
SELECT test2.f1();
ERROR 42000: FUNCTION test2.f1 does not exist
show warnings;
Level	Code	Message
Error	1305	FUNCTION test2.f1 does not exist
SELECT test2.f1();
ERROR 42000: FUNCTION test2.f1 does not exist
show warnings;
Level	Code	Message
Error	1305	FUNCTION test2.f1 does not exist
SELECT test2.f1();
ERROR 42000: FUNCTION test2.f1 does not exist
show warnings;
Level	Code	Message
Error	1305	FUNCTION test2.f1 does not exist
DROP PACKAGE test2;
#
# Broken CREATE PACKAGE at a package procedure call time
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1
AS BEGIN
CALL p2;
END;
END;
$$
CALL test2.f1();
ERROR 42000: PROCEDURE test2.f1 does not exist
UPDATE mysql.proc SET `body`='garbage'
  WHERE db='test' AND name='test2' AND type='PACKAGE';
# sp-cache-invalidate
CALL test2.p1();
ERROR 42000: PROCEDURE test2.p1 does not exist
show warnings;
Level	Code	Message
Error	1305	PROCEDURE test2.p1 does not exist
CALL test2.p1();
ERROR 42000: PROCEDURE test2.p1 does not exist
show warnings;
Level	Code	Message
Error	1305	PROCEDURE test2.p1 does not exist
CALL test2.p1();
ERROR 42000: PROCEDURE test2.p1 does not exist
show warnings;
Level	Code	Message
Error	1305	PROCEDURE test2.p1 does not exist
DROP PACKAGE test2;
#
# Bad routine names
#
CREATE PACKAGE p1 AS
PROCEDURE pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1;
END;
$$
ERROR 42000: Identifier name 'pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1' is too long
CREATE PACKAGE p1 AS
FUNCTION fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1
RETURN INT;
END;
$$
ERROR 42000: Identifier name 'fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1' is too long
CREATE PACKAGE p1 AS
PROCEDURE "p1 ";
END;
$$
ERROR 42000: Incorrect routine name 'p1 '
CREATE PACKAGE p1 AS
FUNCTION "f1 " RETURN INT;
END;
$$
ERROR 42000: Incorrect routine name 'f1 '
CREATE PACKAGE p1 AS
PROCEDURE "p1.p1";
END;
$$
ERROR 42000: Incorrect routine name 'p1.p1'
CREATE PACKAGE p1 AS
FUNCTION "f1.f1" RETURN INT;
END;
$$
ERROR 42000: Incorrect routine name 'f1.f1'
#
# Duplicate PROCEDURE in CREATE PACKAGE
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
PROCEDURE p1;
END;
$$
ERROR 42000: PROCEDURE test2.p1 already exists
CREATE PACKAGE test2 AS
PROCEDURE p1;
PROCEDURE P1;
END;
$$
ERROR 42000: PROCEDURE test2.P1 already exists
#
# Duplicate FUNCTION in CREATE PACKAGE
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f1 RETURN INT;
END;
$$
ERROR 42000: FUNCTION test2.f1 already exists
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION F1 RETURN INT;
END;
$$
ERROR 42000: FUNCTION test2.F1 already exists
#
# Duplicate PROCEDURE in CREATE PACKAGE BODY
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
ERROR 42000: PROCEDURE test2.p1 already exists
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE P1 AS BEGIN NULL; END;
END;
$$
ERROR 42000: PROCEDURE test2.P1 already exists
DROP PACKAGE test2;
#
# Duplicate FUNCTION in CREATE PACKAGE BODY
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
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 42000: FUNCTION test2.f1 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 42000: FUNCTION test2.F1 already exists
DROP PACKAGE test2;
#
# Routines declared in CREATE PACKAGE missing in CREATE PACKAGE BODY
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p2 AS BEGIN NULL; END;
END;
$$
ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
DROP PACKAGE test2;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY test2 AS
FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
ERROR HY000: FUNCTION `test.test2.f1` is declared in the package specification but is not defined in the package body
DROP PACKAGE test2;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
DROP PACKAGE test2;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
END;
$$
ERROR HY000: PROCEDURE `test.test2.p1` is declared in the package specification but is not defined in the package body
DROP PACKAGE test2;
#
# Forward declarations in CREATE PACKAGE BODY with missing implementations
#
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE p2;
END;
$$
ERROR HY000: PROCEDURE `test.test2.p2` has a forward declaration but is not defined
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
ERROR HY000: FUNCTION `test.test2.f1` has a forward declaration but is not defined
DROP PACKAGE test2;
#
# Creating a new package
#
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
$$
Warnings:
Note	1585	This function 'concat' has the same name as a native function
SELECT * FROM mysql.proc WHERE db='test' AND name='test2';
db	test
name	test2
type	PACKAGE
specific_name	test2
language	SQL
sql_data_access	CONTAINS_SQL
is_deterministic	NO
security_type	DEFINER
param_list	
returns	
body	AS
FUNCTION f1 RETURN INT DETERMINISTIC;
FUNCTION f2(a INT) RETURN INT;
FUNCTION concat RETURN INT;
PROCEDURE p1;
PROCEDURE p2(a INT);
END
definer	root@localhost
created	#
modified	#
sql_mode	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
comment	package-test2-comment
character_set_client	latin1
collation_connection	latin1_swedish_ci
db_collation	utf8mb4_uca1400_ai_ci
body_utf8	AS
FUNCTION f1 RETURN INT DETERMINISTIC;
FUNCTION f2(a INT) RETURN INT;
FUNCTION concat RETURN INT;
PROCEDURE p1;
PROCEDURE p2(a INT);
END
aggregate	NONE
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
SPECIFIC_NAME	test2
ROUTINE_CATALOG	def
ROUTINE_SCHEMA	test
ROUTINE_NAME	test2
ROUTINE_TYPE	PACKAGE
DATA_TYPE	
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	AS
FUNCTION f1 RETURN INT DETERMINISTIC;
FUNCTION f2(a INT) RETURN INT;
FUNCTION concat RETURN INT;
PROCEDURE p1;
PROCEDURE p2(a INT);
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	#
LAST_ALTERED	#
SQL_MODE	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
ROUTINE_COMMENT	package-test2-comment
DEFINER	root@localhost
CHARACTER_SET_CLIENT	latin1
COLLATION_CONNECTION	latin1_swedish_ci
DATABASE_COLLATION	utf8mb4_uca1400_ai_ci
CREATE PACKAGE IF NOT EXISTS test2 AS
FUNCTION f1 RETURN INT;
END test2
$$
Warnings:
Note	1304	PACKAGE test2 already exists
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;
$$
Warnings:
Note	1585	This function 'concat' has the same name as a native function
Note	1585	This function 'concat' has the same name as a native function
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;
$$
Warnings:
Note	1585	This function 'concat' has the same name as a native function
Note	1585	This function 'concat' has the same name as a native function
Note	1304	PACKAGE BODY test2 already exists
SELECT test2.f1();
test2.f1()
10
SELECT test2.f2(1);
test2.f2(1)
11
CALL test2.p1();
f2(0)
10
CALL test2.p2(1);
f2(a)
11
SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'test2.%';
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
SPECIFIC_NAME	test2
ROUTINE_CATALOG	def
ROUTINE_SCHEMA	test
ROUTINE_NAME	test2
ROUTINE_TYPE	PACKAGE
DATA_TYPE	
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	AS
FUNCTION f1 RETURN INT DETERMINISTIC;
FUNCTION f2(a INT) RETURN INT;
FUNCTION concat RETURN INT;
PROCEDURE p1;
PROCEDURE p2(a INT);
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	#
LAST_ALTERED	#
SQL_MODE	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
ROUTINE_COMMENT	package-test2-comment
DEFINER	root@localhost
CHARACTER_SET_CLIENT	latin1
COLLATION_CONNECTION	latin1_swedish_ci
DATABASE_COLLATION	utf8mb4_uca1400_ai_ci
SPECIFIC_NAME	test2
ROUTINE_CATALOG	def
ROUTINE_SCHEMA	test
ROUTINE_NAME	test2
ROUTINE_TYPE	PACKAGE BODY
DATA_TYPE	
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	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
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	#
LAST_ALTERED	#
SQL_MODE	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
ROUTINE_COMMENT	package-body-test2-comment
DEFINER	root@localhost
CHARACTER_SET_CLIENT	latin1
COLLATION_CONNECTION	latin1_swedish_ci
DATABASE_COLLATION	utf8mb4_uca1400_ai_ci
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME LIKE 'test2.%';
SHOW PACKAGE STATUS;
Db	test
Name	test2
Type	PACKAGE
Definer	root@localhost
Modified	0000-00-00 00:00:00
Created	0000-00-00 00:00:00
Security_type	DEFINER
Comment	package-test2-comment
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
SHOW PACKAGE BODY STATUS;
Db	test
Name	test2
Type	PACKAGE BODY
Definer	root@localhost
Modified	0000-00-00 00:00:00
Created	0000-00-00 00:00:00
Security_type	DEFINER
Comment	package-body-test2-comment
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
SHOW CREATE PACKAGE test2;
Package	test2
sql_mode	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
Create Package	CREATE DEFINER="root"@"localhost" 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
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
Warnings:
Level	Note
Code	1585
Message	This function 'concat' has the same name as a native function
SHOW CREATE PACKAGE BODY test2;
Package body	test2
sql_mode	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
Create Package Body	CREATE DEFINER="root"@"localhost" 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
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
Warnings:
Level	Note
Code	1585
Message	This function 'concat' has the same name as a native function
DROP PACKAGE BODY test2;
SELECT test2.f1();
ERROR 42000: FUNCTION test.test2.f1 does not exist
SELECT test2.f2();
ERROR 42000: FUNCTION test.test2.f2 does not exist
CALL test2.p1();
ERROR 42000: PROCEDURE test.test2.p1 does not exist
DROP PACKAGE BODY IF EXISTS test2;
Warnings:
Note	1305	PACKAGE BODY test.test2 does not exist
DROP PACKAGE BODY test2;
ERROR 42000: PACKAGE BODY test.test2 does not exist
DROP PACKAGE test2;
#
# Creating a new package in a remote database
#
CREATE DATABASE test2;
CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
$$
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;
$$
SHOW PACKAGE STATUS;
Db	test2
Name	test2
Type	PACKAGE
Definer	root@localhost
Modified	0000-00-00 00:00:00
Created	0000-00-00 00:00:00
Security_type	DEFINER
Comment	package-test2-comment
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
SHOW PACKAGE BODY STATUS;
Db	test2
Name	test2
Type	PACKAGE BODY
Definer	root@localhost
Modified	0000-00-00 00:00:00
Created	0000-00-00 00:00:00
Security_type	DEFINER
Comment	package-body-test2-comment
character_set_client	latin1
collation_connection	latin1_swedish_ci
Database Collation	utf8mb4_uca1400_ai_ci
USE test2;
SELECT test2.f1();
test2.f1()
10
CALL test2.p1();
f1()
10
USE test;
DROP PACKAGE BODY test2.test2;
DROP PACKAGE test2.test2;
DROP DATABASE test2;
#
# Only public routines are available outside
#
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;
$$
SELECT test2.f1();
test2.f1()
This is test2.f1
CALL test2.p1();
This is test2.p1
This is test2.p1
SELECT test2.f2();
ERROR 42000: FUNCTION test2.f2 does not exist
CALL test2.p2();
ERROR 42000: PROCEDURE test2.p2 does not exist
DROP PACKAGE test2;
#
# PACKAGE BODY with forward declarations
#
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;
$$
SELECT test2.f1();
test2.f1()
This is f2private
CALL test2.p1();
This is p2private
This is p2private
DROP PACKAGE test2;
#
# Calling private routines with forward declarations,
# using qualified notation, e.g. "CALL pkg.proc"
#
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;
$$
SELECT test2.f1();
test2.f1()
This is f2private
CALL test2.p1();
msg
This is p2private
DROP PACKAGE test2;
#
# Calling private routines, using qualified notation, e.g. "pkg.proc"
#
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;
$$
SELECT test2.f1();
test2.f1()
This is f2private
CALL test2.p1();
msg
This is p2private
DROP PACKAGE test2;
#
# Calling private routines from the package initialization section,
# using qualified notation, e.g. "pkg.proc"
#
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;
$$
CALL test2.p1();
test2.f2private()
This is f2private
msg
This is p2private
msg
This is p1
DROP PACKAGE test2;
#
# Testing OR REPLACE
#
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f0 RETURN INT;
END;
$$
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f1 RETURN INT;
END;
$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
name	type	body
pkg	PACKAGE	AS
FUNCTION f1 RETURN INT;
END
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
name	type	body
pkg	PACKAGE	AS
FUNCTION f1 RETURN INT;
END
pkg	PACKAGE BODY	AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
END
SELECT pkg.f1();
pkg.f1()
10
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
END;
$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
name	type	body
pkg	PACKAGE	AS
FUNCTION f1 RETURN INT;
END
pkg	PACKAGE BODY	AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
END
SELECT pkg.f1();
pkg.f1()
20
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f1 RETURN BIGINT;
END;
$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
name	type	body
pkg	PACKAGE	AS
FUNCTION f1 RETURN BIGINT;
END
SELECT pkg.f1();
ERROR 42000: FUNCTION test.pkg.f1 does not exist
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
END;
$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
name	type	body
pkg	PACKAGE	AS
FUNCTION f1 RETURN BIGINT;
END
pkg	PACKAGE BODY	AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
END
SELECT pkg.f1();
pkg.f1()
30
DROP PACKAGE pkg;
#
# Package routines accessing tables
#
CREATE TABLE t1 (a INT);
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;
$$
CALL test2.p1(10);
SELECT * FROM t1;
a
10
DROP PACKAGE test2;
DROP TABLE t1;
#
# CREATE PACKAGE: Optional package name after the "END" keyword
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2.test2
$$
ERROR HY000: END identifier 'test2.test2' does not match 'test.test2'
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test3
$$
ERROR HY000: END identifier 'test3' does not match 'test2'
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2
$$
DROP PACKAGE test2;
#
# MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2;
$$
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f1.f1;
END test2;
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.f1;
END test2' at line 5
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f2;
END test2;
$$
ERROR HY000: END identifier 'f2' does not match 'f1'
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
NULL;
END p1.p1;
END test2;
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.p1;
END test2' at line 5
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
NULL;
END p2;
END test2;
$$
ERROR HY000: END identifier 'p2' does not match 'p1'
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f1;
PROCEDURE p1 AS
BEGIN
NULL;
END p1;
END test2;
$$
DROP PACKAGE test2;
#
# Package and package routine name and end name are case insensitive
#
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN TEXT;
PROCEDURE p1;
END TEST2;
$$
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;
$$
SELECT TEST2.F1();
TEST2.F1()
This is f1
SELECT test2.f1();
test2.f1()
This is f1
CALL TEST2.p1();
msg
This is p1
CALL test2.P1();
msg
This is p1
DROP PACKAGE BODY TEST2;
DROP PACKAGE TEST2;
#
# Testing various qualified/non-qualified db/package SP call chains
#
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;
$$
SET max_sp_recursion_depth=10;
# pack.routine -> *
CALL pack.p1('p2');
@track
test.pack.p1() test.pack.p2()
CALL pack.p1('f2');
@track
test.pack.p1() test.pack.f2()
CALL pack.p1('px');
ERROR 42000: PROCEDURE test.px does not exist
CALL pack.p1('fx');
ERROR 42000: FUNCTION test.fx does not exist
CALL pack.p1('pp2');
@track
test.pack.p1() test.pp2()
CALL pack.p1('ff2');
@track
test.pack.p1() test.ff2()
CALL pack.p1('pack.p2');
@track
test.pack.p1() test.pack.p2()
CALL pack.p1('pack.f2');
@track
test.pack.p1() test.pack.f2()
CALL pack.p1('pack.px');
ERROR 42000: PROCEDURE pack.px does not exist
CALL pack.p1('pack.fx');
ERROR 42000: FUNCTION pack.fx does not exist
CALL pack.p1('test.pp2');
@track
test.pack.p1() test.pp2()
CALL pack.p1('test.ff2');
@track
test.pack.p1() test.ff2()
DO pack.f1('p2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2()
DO pack.f1('f2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2()
DO pack.p1('px');
ERROR 42000: FUNCTION pack.p1 does not exist
DO pack.p1('fx');
ERROR 42000: FUNCTION pack.p1 does not exist
DO pack.f1('pp2');
Warnings:
Warning	1642	test.pack.f1() test.pp2()
DO pack.f1('ff2');
Warnings:
Warning	1642	test.pack.f1() test.ff2()
DO pack.f1('pack.p2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2()
DO pack.f1('pack.f2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2()
SELECT pack.f1('pack.px');
ERROR 42000: PROCEDURE pack.px does not exist
SELECT pack.f1('pack.fx');
ERROR 42000: FUNCTION pack.fx does not exist
DO pack.f1('test.pp2');
Warnings:
Warning	1642	test.pack.f1() test.pp2()
DO pack.f1('test.ff2');
Warnings:
Warning	1642	test.pack.f1() test.ff2()
#
# Qualified_package_routine -> Non_qualified_package_routine
#
# pack.routine -> [pack.]routine -> pack.routine
CALL pack.p1('p2 pack.p3');
@track
test.pack.p1() test.pack.p2() test.pack.p3()
CALL pack.p1('p2 pack.f3');
@track
test.pack.p1() test.pack.p2() test.pack.f3()
CALL pack.p1('f2 pack.p3');
@track
test.pack.p1() test.pack.f2() test.pack.p3()
CALL pack.p1('f2 pack.f3');
@track
test.pack.p1() test.pack.f2() test.pack.f3()
DO pack.f1('p2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.p3()
DO pack.f1('p2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.f3()
DO pack.f1('f2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.p3()
DO pack.f1('f2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.f3()
# pack.routine -> [pack.]routine -> [pack]routine
CALL pack.p1('p2 p3');
@track
test.pack.p1() test.pack.p2() test.pack.p3()
CALL pack.p1('p2 f3');
@track
test.pack.p1() test.pack.p2() test.pack.f3()
CALL pack.p1('f2 p3');
@track
test.pack.p1() test.pack.f2() test.pack.p3()
CALL pack.p1('f2 f3');
@track
test.pack.p1() test.pack.f2() test.pack.f3()
DO pack.f1('p2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.p3()
DO pack.f1('p2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.f3()
DO pack.f1('f2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.p3()
DO pack.f1('f2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.f3()
# pack.routine -> [pack.]routine -> test.routine
CALL pack.p1('p2 test.p3');
@track
test.pack.p1() test.pack.p2() test.p3()
CALL pack.p1('p2 test.f3');
@track
test.pack.p1() test.pack.p2() test.f3()
CALL pack.p1('f2 test.p3');
@track
test.pack.p1() test.pack.f2() test.p3()
CALL pack.p1('f2 test.f3');
@track
test.pack.p1() test.pack.f2() test.f3()
DO pack.f1('p2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.p3()
DO pack.f1('p2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.f3()
DO pack.f1('f2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.p3()
DO pack.f1('f2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.f3()
# pack.routine -> [pack.]routine -> [test.]routine
CALL pack.p1('p2 pp2');
@track
test.pack.p1() test.pack.p2() test.pp2()
CALL pack.p1('p2 ff2');
@track
test.pack.p1() test.pack.p2() test.ff2()
CALL pack.p1('f2 pp2');
@track
test.pack.p1() test.pack.f2() test.pp2()
CALL pack.p1('f2 ff2');
@track
test.pack.p1() test.pack.f2() test.ff2()
DO pack.f1('p2 pp2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pp2()
DO pack.f1('p2 ff2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.ff2()
DO pack.f1('f2 pp2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pp2()
DO pack.f1('f2 ff2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.ff2()
#
# Qualified_package_routine -> Non_qualified_database_routine
#
# pack.routine -> [test.]routine -> pack.routine
CALL pack.p1('pp2 pack.p3');
@track
test.pack.p1() test.pp2() test.pack.p3()
CALL pack.p1('pp2 pack.f3');
@track
test.pack.p1() test.pp2() test.pack.f3()
CALL pack.p1('ff2 pack.p3');
@track
test.pack.p1() test.ff2() test.pack.p3()
CALL pack.p1('ff2 pack.f3');
@track
test.pack.p1() test.ff2() test.pack.f3()
DO pack.f1('pp2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.pack.p3()
DO pack.f1('pp2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.pack.f3()
DO pack.f1('ff2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.pack.p3()
DO pack.f1('ff2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.pack.f3()
# pack.routine -> [test.]routine -> test.routine
CALL pack.p1('pp2 test.p3');
@track
test.pack.p1() test.pp2() test.p3()
CALL pack.p1('pp2 test.f3');
@track
test.pack.p1() test.pp2() test.f3()
CALL pack.p1('ff2 test.p3');
@track
test.pack.p1() test.ff2() test.p3()
CALL pack.p1('ff2 test.f3');
@track
test.pack.p1() test.ff2() test.f3()
DO pack.f1('pp2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.p3()
DO pack.f1('pp2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.f3()
DO pack.f1('ff2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.p3()
DO pack.f1('ff2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.f3()
# pack.routine -> [test.]routine -> [test.]routine
CALL pack.p1('pp2 p3');
@track
test.pack.p1() test.pp2() test.p3()
CALL pack.p1('pp2 f3');
@track
test.pack.p1() test.pp2() test.f3()
CALL pack.p1('ff2 p3');
@track
test.pack.p1() test.ff2() test.p3()
CALL pack.p1('ff2 f3');
@track
test.pack.p1() test.ff2() test.f3()
DO pack.f1('pp2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.p3()
DO pack.f1('pp2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.f3()
DO pack.f1('ff2 p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.p3()
DO pack.f1('ff2 f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.f3()
#
# Qualified_package_routine -> Qualified_package_routine
#
# pack.routine -> pack.routine -> pack.routine
CALL pack.p1('pack.p2 pack.p3');
@track
test.pack.p1() test.pack.p2() test.pack.p3()
CALL pack.p1('pack.p2 pack.f3');
@track
test.pack.p1() test.pack.p2() test.pack.f3()
CALL pack.p1('pack.f2 pack.p3');
@track
test.pack.p1() test.pack.f2() test.pack.p3()
CALL pack.p1('pack.f2 pack.f3');
@track
test.pack.p1() test.pack.f2() test.pack.f3()
DO pack.f1('pack.p2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.p3()
DO pack.f1('pack.p2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.f3()
DO pack.f1('pack.f2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.p3()
DO pack.f1('pack.f2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.f3()
# pack.routine -> pack.routine -> [pack.]routine
CALL pack.p1('pack.p2 p3');
@track
test.pack.p1() test.pack.p2() test.pack.p3()
CALL pack.p1('pack.p2 f3');
@track
test.pack.p1() test.pack.p2() test.pack.f3()
CALL pack.p1('pack.f2 p3');
@track
test.pack.p1() test.pack.f2() test.pack.p3()
CALL pack.p1('pack.f2 f3');
@track
test.pack.p1() test.pack.f2() test.pack.f3()
DO pack.f1('pack.p2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.p3()
DO pack.f1('pack.p2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pack.f3()
DO pack.f1('pack.f2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.p3()
DO pack.f1('pack.f2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pack.f3()
# pack.routine -> pack.routine -> test.routine
CALL pack.p1('pack.p2 test.p3');
@track
test.pack.p1() test.pack.p2() test.p3()
CALL pack.p1('pack.p2 test.f3');
@track
test.pack.p1() test.pack.p2() test.f3()
CALL pack.p1('pack.f2 test.p3');
@track
test.pack.p1() test.pack.f2() test.p3()
CALL pack.p1('pack.f2 test.f3');
@track
test.pack.p1() test.pack.f2() test.f3()
DO pack.f1('pack.p2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.p3()
DO pack.f1('pack.p2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.f3()
DO pack.f1('pack.f2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.p3()
DO pack.f1('pack.f2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.f3()
# pack.routine -> pack.routine -> [test.]routine
CALL pack.p1('pack.p2 pp2');
@track
test.pack.p1() test.pack.p2() test.pp2()
CALL pack.p1('pack.p2 ff2');
@track
test.pack.p1() test.pack.p2() test.ff2()
CALL pack.p1('pack.f2 pp2');
@track
test.pack.p1() test.pack.f2() test.pp2()
CALL pack.p1('pack.f2 ff2');
@track
test.pack.p1() test.pack.f2() test.ff2()
DO pack.f1('pack.p2 pp2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.pp2()
DO pack.f1('pack.p2 ff2');
Warnings:
Warning	1642	test.pack.f1() test.pack.p2() test.ff2()
DO pack.f1('pack.f2 pp2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.pp2()
DO pack.f1('pack.f2 ff2');
Warnings:
Warning	1642	test.pack.f1() test.pack.f2() test.ff2()
#
# Qualified_package_routine -> Qualified_database_routine
#
pack.routine -> test.routine -> pack.routine
CALL pack.p1('test.pp2 pack.p3');
@track
test.pack.p1() test.pp2() test.pack.p3()
CALL pack.p1('test.pp2 pack.f3');
@track
test.pack.p1() test.pp2() test.pack.f3()
CALL pack.p1('test.ff2 pack.p3');
@track
test.pack.p1() test.ff2() test.pack.p3()
CALL pack.p1('test.ff2 pack.f3');
@track
test.pack.p1() test.ff2() test.pack.f3()
DO pack.f1('test.pp2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.pack.p3()
DO pack.f1('test.pp2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.pack.f3()
DO pack.f1('test.ff2 pack.p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.pack.p3()
DO pack.f1('test.ff2 pack.f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.pack.f3()
pack.routine -> test.routine -> test.routine
CALL pack.p1('test.pp2 test.p3');
@track
test.pack.p1() test.pp2() test.p3()
CALL pack.p1('test.pp2 test.f3');
@track
test.pack.p1() test.pp2() test.f3()
CALL pack.p1('test.ff2 test.p3');
@track
test.pack.p1() test.ff2() test.p3()
CALL pack.p1('test.ff2 test.f3');
@track
test.pack.p1() test.ff2() test.f3()
DO pack.f1('test.pp2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.p3()
DO pack.f1('test.pp2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.f3()
DO pack.f1('test.ff2 test.p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.p3()
DO pack.f1('test.ff2 test.f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.f3()
pack.routine -> test.routine -> [test.]routine
CALL pack.p1('test.pp2 p3');
@track
test.pack.p1() test.pp2() test.p3()
CALL pack.p1('test.pp2 f3');
@track
test.pack.p1() test.pp2() test.f3()
CALL pack.p1('test.ff2 p3');
@track
test.pack.p1() test.ff2() test.p3()
CALL pack.p1('test.ff2 f3');
@track
test.pack.p1() test.ff2() test.f3()
DO pack.f1('test.pp2 p3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.p3()
DO pack.f1('test.pp2 f3');
Warnings:
Warning	1642	test.pack.f1() test.pp2() test.f3()
DO pack.f1('test.ff2 p3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.p3()
DO pack.f1('test.ff2 f3');
Warnings:
Warning	1642	test.pack.f1() test.ff2() test.f3()
# Longer chains
CALL pack.p1('p2 f2 p2 test.pp2 test.ff2 pack.p3');
@track
test.pack.p1() test.pack.p2() test.pack.f2() test.pack.p2() test.pp2() test.ff2() test.pack.p3()
CALL pack.p1('p2 test.pp2 pack.p2 pack.f2 test.ff2 pack.p3');
@track
test.pack.p1() test.pack.p2() test.pp2() test.pack.p2() test.pack.f2() test.ff2() test.pack.p3()
DROP PACKAGE pack;
DROP FUNCTION f3;
DROP PROCEDURE p3;
DROP FUNCTION ff2;
DROP PROCEDURE pp2;
#
# Calling a standalone function from a non-current database,
# which calls a package routine from the same non-current database.
#
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;
$$
CALL p1;
database()
test
CREATE DATABASE test2;
USE test2;
CALL test.p1;
database()
test
DROP DATABASE test2;
CALL test.p1;
database()
test
USE test;
DROP PACKAGE pkg1;
DROP PROCEDURE p1;
#
# Creating a package with a different DEFINER
#
CREATE USER xxx@localhost;
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;
$$
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer	name	security_type	type
xxx@localhost	p1	DEFINER	PACKAGE
xxx@localhost	p1	DEFINER	PACKAGE BODY
DROP PACKAGE p1;
DROP USER xxx@localhost;
#
# Creating a package with a different DEFINER, with SQL SECURITY INVOKER
#
CREATE USER xxx@localhost;
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;
$$
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
definer	name	security_type	type
xxx@localhost	p1	INVOKER	PACKAGE
xxx@localhost	p1	INVOKER	PACKAGE BODY
DROP PACKAGE p1;
DROP USER xxx@localhost;
#
# A package with an initialization section
#
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;
$$
CALL p1.p1();
@a
11
CALL p1.p1();
@a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
@a
12
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
@a
14
DROP PACKAGE p1;
#
# A package with an initialization section calling
# routines from the same package, and standalone routines.
#
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;
$$
CALL p1.p1();
@msg
[p1.init1][p1.init2][p1.init3][init20][p1.p1]
CALL p1.p1();
@msg
[p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1]
SELECT p1.f1();
p1.f1()
[p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1][p1.f1]
SELECT p1.f1();
p1.f1()
[p1.init1][p1.init2][p1.init3][init20][p1.p1][p1.p1][p1.f1][p1.f1]
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
[p1.init1][p1.init2][p1.init3][init20][p1.f1]
CALL p1.p1();
@msg
[p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1]
SELECT p1.f1();
p1.f1()
[p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1][p1.f1]
CALL p1.p1();
@msg
[p1.init1][p1.init2][p1.init3][init20][p1.f1][p1.p1][p1.f1][p1.p1]
DROP PACKAGE p1;
DROP PROCEDURE init20;
#
# EXECUTE IMMEDIATE in the package initialization section
#
SET @a=1000;
CREATE TABLE t1 AS SELECT 10 AS a;
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;
$$
CALL p1.p1();
@a
11
CALL p1.p1();
@a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger
DROP PACKAGE p1;
DROP TABLE t1;
#
# A package with an initialization section, loading table data into a user variable
#
SET @a=1000;
CREATE TABLE t1 AS SELECT 10 AS a;
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;
$$
CALL p1.p1();
@a
11
CALL p1.p1();
@a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
DROP PACKAGE p1;
DROP TABLE t1;
#
# A package with an initialization section producing an error
#
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;
$$
CALL p1.p1();
ERROR 42S02: Table 'test.t1' doesn't exist
SELECT p1.f1();
ERROR 42S02: Table 'test.t1' doesn't exist
# sp-cache-invalidate
SELECT p1.f1();
ERROR 42S02: Table 'test.t1' doesn't exist
CALL p1.p1();
ERROR 42S02: Table 'test.t1' doesn't exist
SELECT p1.f1();
ERROR 42S02: Table 'test.t1' doesn't exist
CREATE TABLE t1 (a INT) AS SELECT 1;
CALL p1.p1();
msg
This is p1
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
This is f1
# sp-cache-invalidate
CALL p1.p1();
msg
This is p1
DROP TABLE t1;
DROP PACKAGE p1;
#
# A package with SF-unsafe statements in the initialization section
#
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;
$$
CALL p1.p1();
msg
This is p1
SELECT p1.f1();
p1.f1()
This is f1
# sp-cache-invalidate
SELECT p1.f1();
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
CALL p1.p1();
msg
This is p1
SELECT p1.f1();
p1.f1()
This is f1
DROP PACKAGE p1;
#
# MDEV-13139 Package-wide variables in CREATE PACKAGE
#
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT;
a INT;
PROCEDURE p1 AS
BEGIN
CREATE VIEW v1 AS SELECT a;
END;
END;
$$
ERROR 42000: Duplicate variable: a
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS
BEGIN
NULL;
END;
b INT; -- Variables cannot go after routine definitions
END;
$$
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'b INT; -- Variables cannot go after routine definitions
END' at line 7
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS
BEGIN
CREATE VIEW v1 AS SELECT a;
END;
END;
$$
ERROR HY000: View's SELECT contains a variable or parameter
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;
$$
CALL p1.p1;
a
NULL
CALL p1.p1;
a
101
CALL p1.p1;
a
202
SELECT p1.f1();
p1.f1()
303
DROP PACKAGE p1;
#
# One package variable with a default value
#
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;
$$
CALL p1.p1();
a
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
a
14
DROP PACKAGE p1;
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;
$$
CALL p1.p1();
a.a	a.b
11	bbbB
CALL p1.p1();
a.a	a.b
12	bbbBB
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
a.a	a.b
12	bbbB
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
a.a	a.b
14	bbbBB
DROP PACKAGE p1;
CREATE TABLE t1 (a INT);
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;
$$
CALL p1.p1();
a
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
a
14
DROP PACKAGE p1;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
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;
$$
CALL p1.p1();
a.a	a.b
11	bbbB
CALL p1.p1();
a.a	a.b
12	bbbBB
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
a.a	a.b
12	bbbB
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
a.a	a.b
14	bbbBB
DROP PACKAGE p1;
DROP TABLE t1;
#
# One package variable, set in the package initialization section
#
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;
$$
CALL p1.p1();
a
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
CALL p1.p1();
a
14
DROP PACKAGE p1;
#
# A package with an initialization section,
# loading table data into a package variable
#
CREATE TABLE t1 AS SELECT 10 AS a;
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;
$$
CALL p1.p1();
a
11
CALL p1.p1();
a
12
SELECT p1.f1();
p1.f1()
13
SELECT p1.f1();
p1.f1()
14
# sp-cache-invalidate
SELECT p1.f1();
p1.f1()
11
DROP PACKAGE p1;
DROP TABLE t1;
#
# Package variables and XPath
#
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;
$$
SELECT p1.f1();
p1.f1()
b1
SELECT p1.f1();
p1.f1()
b2
SELECT p1.f1();
p1.f1()
b3
DROP PACKAGE p1;
#
# Package variables as OUT routine parameter
#
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;
$$
CALL p1.p1;
a	b
10	11
DROP PACKAGE p1;
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;
$$
CALL p1.p1;
a.a	a.b	b.a	b.b
101	bX	102	bBX
DROP PACKAGE p1;
CREATE TABLE t1 (a INT,b TEXT);
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;
$$
CALL p1.p1;
a.a	a.b	b.a	b.b
101	bX	102	bBX
DROP PACKAGE p1;
DROP TABLE t1;
#
# Package variable fields as OUT routine parameters
#
CREATE TABLE t1 (a INT,b TEXT);
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;
$$
CALL p1.p1;
a.a	a.b
10	b
a.a	a.b
11	bB
DROP PACKAGE p1;
DROP TABLE t1;
#
# Package variables as SELECT INTO targets
#
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;
$$
CALL p1.p1;
a	b
1	2
DROP PACKAGE p1;
CREATE TABLE t1 (a INT, b TEXT);
INSERT INTO t1 VALUES (10,'b');
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;
$$
CALL p1.p1;
b.a	b.b
10	b
a.a	a.b
10	b
DROP PACKAGE p1;
DROP TABLE t1;
#
# Package variable fields as SELECT INTO targets
#
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;
$$
CALL p1.p1;
a.a	a.b	b.a	b.b
10	x1	20	x2
DROP PACKAGE p1;
#
# Recursive package procedure calls
# Makes sure that the non-top sp_head instances created by
# sp_clone_and_link_routine() correctly reproduce the package context:
# package variables, package routines.
#
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;
$$
SET max_sp_recursion_depth=5;
CALL p1.p1(5);
c	pv1	f1()
5	10	110
c	pv1	f1()
4	11	111
c	pv1	f1()
3	12	112
c	pv1	f1()
2	13	113
c	pv1	f1()
1	14	114
c	pv1	f1()
0	15	115
SET max_sp_recursion_depth=0;
CALL p1.p1(0);
c	pv1	f1()
0	15	115
CALL p1.p1(1);
c	pv1	f1()
1	15	115
ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine p1.p1
DROP PACKAGE p1;
#
# Non-reserved keywords as package body variable names
#
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;
$$
CALL p1.p1;
ascii	action
11	21
DROP PACKAGE p1;
#
# Package routines calling routines of another package
#
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;
$$
CALL p1.p1;
msg
This is p1.p1
CALL p2.p1;
msg
This is p1.p1
SELECT p1.f1(), p2.f1();
p1.f1()	p2.f1()
This is p1.f1	This is p1.f1
DROP PACKAGE p2;
DROP PACKAGE p1;
#
# Package names with dot characters
#
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;
$$
CALL "p1.p1"."p1";
msg
This is p1
SELECT "p1.p1"."f1"();
"p1.p1"."f1"()
This is f1
DROP PACKAGE "p1.p1";
#
# MDEV-15070 Crash when doing a CREATE VIEW inside a package routine
#
SET sql_mode=ORACLE;
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;
$$
CALL pkg1.p00;
msg
This is p01
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;
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;
$$
CALL pkg1.p00;
msg
This is p01
DROP PACKAGE pkg1;
DROP TABLE t1;
#
# MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
#
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)
);
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 $$
CALL xyz.xyz123(17,18,@R);
DROP PACKAGE xyz;
DROP TABLE t1;
#
# MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
#
SELECT `db `.pkg.func();
ERROR 42000: Incorrect database name 'db '
SELECT db.`pkg `.func();
ERROR 42000: Incorrect routine name 'pkg '
SELECT db.pkg.`func `();
ERROR 42000: Incorrect routine name 'func '
CREATE DATABASE db1;
USE db1;
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;
$$
USE db1;
SELECT pkg1.f2_db1_pkg1_f1();
pkg1.f2_db1_pkg1_f1()
This is db1.pkg1.f1
SELECT pkg1.f2_pkg1_f1();
pkg1.f2_pkg1_f1()
This is db1.pkg1.f1
SELECT pkg1.f2_f1();
pkg1.f2_f1()
This is db1.pkg1.f1
SELECT db1.pkg1.f2_db1_pkg1_f1();
db1.pkg1.f2_db1_pkg1_f1()
This is db1.pkg1.f1
SELECT db1.pkg1.f2_pkg1_f1();
db1.pkg1.f2_pkg1_f1()
This is db1.pkg1.f1
SELECT db1.pkg1.f2_f1();
db1.pkg1.f2_f1()
This is db1.pkg1.f1
USE test;
SELECT db1.pkg1.f2_db1_pkg1_f1();
db1.pkg1.f2_db1_pkg1_f1()
This is db1.pkg1.f1
SELECT db1.pkg1.f2_pkg1_f1();
db1.pkg1.f2_pkg1_f1()
This is db1.pkg1.f1
SELECT db1.pkg1.f2_f1();
db1.pkg1.f2_f1()
This is db1.pkg1.f1
DROP DATABASE db1;
CREATE DATABASE db1;
CREATE DATABASE db2;
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;
$$
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;
$$
SELECT db2.pkg1.var1(), db2.pkg1.var2();
db2.pkg1.var1()	db2.pkg1.var2()
This is db1.pkg1.f1	This is db2.pkg1.f1
DROP DATABASE db1;
DROP DATABASE db2;
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;
$$
SELECT test.pkg1.f1('xxx');
test.pkg1.f1('xxx')
xxx
SELECT test.pkg1.f1('xxx' AS a);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS a)' at line 1
DROP PACKAGE pkg1;
#
# MDEV-19328 sql_mode=ORACLE: Package function in VIEW
#
SET sql_mode=ORACLE;
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;
$$
SET sql_mode=ORACLE;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL  where 1 = "test"."test1"."f_test"()
character_set_client	latin1
collation_connection	latin1_swedish_ci
SET sql_mode=DEFAULT;
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL  where 1 = `test`.`test1`.`f_test`()
character_set_client	latin1
collation_connection	latin1_swedish_ci
DROP VIEW v_test;
SET sql_mode=DEFAULT;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL  where 1 = `test`.`test1`.`f_test`()
character_set_client	latin1
collation_connection	latin1_swedish_ci
SET sql_mode=ORACLE;
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL  where 1 = "test"."test1"."f_test"()
character_set_client	latin1
collation_connection	latin1_swedish_ci
DROP VIEW v_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;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL  where 1 = "test"."test1"."f_test"()
character_set_client	latin1
collation_connection	latin1_swedish_ci
SET sql_mode=DEFAULT;
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL  where 1 = `test`.`test1`.`f_test`()
character_set_client	latin1
collation_connection	latin1_swedish_ci
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;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL  where 1 = `test`.`test1`.`f_test`()
character_set_client	latin1
collation_connection	latin1_swedish_ci
SET sql_mode=ORACLE;
SELECT * FROM v_test;
c1
1
SHOW CREATE VIEW v_test;
View	v_test
Create View	CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL  where 1 = "test"."test1"."f_test"()
character_set_client	latin1
collation_connection	latin1_swedish_ci
DROP VIEW v_test;
SET sql_mode=ORACLE;
DROP PACKAGE test1;
#
# MDEV-19804 sql_mode=ORACLE: call procedure in packages
#
CALL `db1 `.pkg.p;
ERROR 42000: Incorrect database name 'db1 '
CALL db1.`pkg `.p;
ERROR 42000: Incorrect routine name 'pkg '
CALL db1.pkg.`p `;
ERROR 42000: Incorrect routine name 'p '
SET sql_mode=ORACLE;
CREATE PACKAGE pkg1 as
PROCEDURE p1();
END;
$$
CREATE PACKAGE BODY pkg1 as
PROCEDURE p1() as
BEGIN
SELECT 'test-function' AS c1;
END;
END;
$$
CALL pkg1.p1;
c1
test-function
CALL test.pkg1.p1;
c1
test-function
SET sql_mode=DEFAULT;
CALL test.pkg1.p1;
c1
test-function
SET sql_mode=ORACLE;
BEGIN
CALL pkg1.p1;
CALL test.pkg1.p1;
END
$$
c1
test-function
c1
test-function
BEGIN
pkg1.p1;
test.pkg1.p1;
END
$$
c1
test-function
c1
test-function
DROP PACKAGE pkg1;
CREATE DATABASE db1;
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;
$$
CREATE DATABASE db2;
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;
$$
SELECT db2.pkg1.var1();
db2.pkg1.var1()
This is db1.pkg1.p1
CALL db2.pkg1.p2_db1_pkg1_p1;
a
This is db1.pkg1.p1
DROP DATABASE db1;
DROP DATABASE db2;
#
# MDEV-29370 Functions in packages are slow and seems to ignore deterministic
#
SET SQL_MODE=ORACLE;
CREATE TABLE t1 (c1 CHAR(1));
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;
//
CALL pkg1.t1_populate(3);
EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM t1 WHERE c1 = f1_deterministic();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select 'Deterministic function' AS "Deterministic function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = <cache>("f1_deterministic"())
EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM t1 WHERE c1 = f2_not_deterministic();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select 'Non-deterministic function' AS "Non-deterministic function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = "f2_not_deterministic"()
EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f3_deterministic();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select 'Deterministic package function' AS "Deterministic package function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = <cache>("test"."pkg1"."f3_deterministic"())
EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f4_not_deterministic();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
Warnings:
Note	1003	select 'Non-deterministic package function' AS "Non-deterministic package function",count(0) AS "COUNT(*)" from "test"."t1" where "test"."t1"."c1" = "test"."pkg1"."f4_not_deterministic"()
DROP TABLE t1;
DROP FUNCTION f1_deterministic;
DROP FUNCTION f2_not_deterministic;
DROP PACKAGE pkg1;
#
# Start of 11.7 tests
#
#
# MDEV-12252 ROW data type for stored function return values
#
#
# Testing fixed ROW type with package routines
#
CREATE PACKAGE pkg
AS
FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32));
PROCEDURE p1(r ROW(a INT, b VARCHAR(32)));
PROCEDURE p2();
END;
$$
CREATE PACKAGE BODY pkg
AS
FUNCTION f1() RETURN ROW(a INT, b VARCHAR(32)) AS
BEGIN
RETURN ROW(1,'b1');
END;
PROCEDURE p1(r ROW(a INT, b VARCHAR(32))) AS
BEGIN
SELECT r.a, r.b;
END;
PROCEDURE p2() AS
BEGIN
CALL p1(f1());
END; 
END;
$$
CALL pkg.p1(pkg.f1());
r.a	r.b
1	b1
CALL pkg.p2;
r.a	r.b
1	b1
DROP PACKAGE pkg;
#
# Testing table%ROWTYPE with package routines
#
CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (1,'b1');
CREATE PACKAGE pkg
AS
FUNCTION f1 RETURN t1%ROWTYPE;
PROCEDURE p1(r t1%ROWTYPE);
PROCEDURE p2;
END;
/
CREATE PACKAGE BODY pkg
AS
FUNCTION f1 RETURN t1%ROWTYPE AS
r t1%ROWTYPE;
BEGIN
SELECT * INTO r FROM t1;
RETURN r;
END;
PROCEDURE p1(r t1%ROWTYPE) AS
BEGIN
SELECT r.a || ' ' || r.b;
END;
PROCEDURE p2 AS
BEGIN
p1(f1());
END; 
END;
/
CALL pkg.p1(pkg.f1());
r.a || ' ' || r.b
1 b1
CALL pkg.p2;
r.a || ' ' || r.b
1 b1
DROP PACKAGE pkg;
DROP TABLE t1;
#
# End of 11.7 tests
#