diff options
Diffstat (limited to 'mysql-test/suite')
40 files changed, 8491 insertions, 29 deletions
diff --git a/mysql-test/suite/compat/oracle/r/binlog_stm_sp_package.result b/mysql-test/suite/compat/oracle/r/binlog_stm_sp_package.result new file mode 100644 index 00000000000..8c1ee05686c --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/binlog_stm_sp_package.result @@ -0,0 +1,268 @@ +SET sql_mode=ORACLE; +CREATE PACKAGE p1 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE IF NOT EXISTS p1 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END; +$$ +Warnings: +Note 1304 PACKAGE p1 already exists +CREATE PACKAGE BODY p1 AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END; +$$ +DROP PACKAGE BODY p1; +DROP PACKAGE p1; +DROP PACKAGE IF EXISTS p1; +Warnings: +Note 1305 PACKAGE test.p1 does not exist +# +# Creating a package with a COMMENT clause +# +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +DROP PACKAGE p1; +# +# Creating a package with a different DEFINER +# +CREATE DEFINER=xxx@localhost PACKAGE p1 AS +PROCEDURE p1; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +DROP PACKAGE p1; +# +# Creating a package with a different DEFINER, with SQL SECURITY INVOKER +# +CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS +PROCEDURE p1; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +DROP PACKAGE p1; +# +# 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; +$$ +DROP PACKAGE BODY test2.test2; +DROP PACKAGE test2.test2; +DROP DATABASE test2; +# +# MDEV-13139 Package-wide variables in CREATE PACKAGE +# +CREATE TABLE t1 (a INT); +CREATE PACKAGE p1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS +a INT:=0; +PROCEDURE p1 AS +BEGIN +INSERT INTO t1 VALUES (a); +a:=a+1; +END; +BEGIN +a:=10; +END; +$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +a +10 +11 +# sp-cache-invalidate +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +a +10 +11 +10 +11 +DROP PACKAGE p1; +DROP TABLE t1; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE "p1" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE IF NOT EXISTS "p1" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE BODY "p1" AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE BODY p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE IF EXISTS p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE "p1" COMMENT 'package-p1-comment' + AS +PROCEDURE p1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE BODY "p1" COMMENT 'package-body-p1-comment' + AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="xxx"@"localhost" PACKAGE "p1" AS +PROCEDURE p1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="xxx"@"localhost" PACKAGE BODY "p1" AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="xxx"@"localhost" PACKAGE "p1" SQL SECURITY INVOKER + AS +PROCEDURE p1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="xxx"@"localhost" PACKAGE BODY "p1" SQL SECURITY INVOKER + AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # CREATE DATABASE test2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE "test2"."test2" COMMENT 'package-test2-comment' + AS +FUNCTION f1 RETURN INT; +PROCEDURE p1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" 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 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE BODY test2.test2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE test2.test2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # DROP DATABASE test2 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE "p1" AS +PROCEDURE p1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" PACKAGE BODY "p1" AS +a INT:=0; +PROCEDURE p1 AS +BEGIN +INSERT INTO t1 VALUES (a); +a:=a+1; +END; +BEGIN +a:=10; +END +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a',10)) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a',11)) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE DEFINER="root"@"localhost" FUNCTION "dummy"() RETURN int(11) +AS +BEGIN +RETURN 1; +END +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP FUNCTION dummy +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a',10)) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES ( NAME_CONST('a',11)) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP PACKAGE p1 +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP TABLE "t1" /* generated by server */ diff --git a/mysql-test/suite/compat/oracle/r/rpl_sp_package.result b/mysql-test/suite/compat/oracle/r/rpl_sp_package.result new file mode 100644 index 00000000000..2f10ec8ccd9 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/rpl_sp_package.result @@ -0,0 +1,183 @@ +include/master-slave.inc +[connection master] +connection master; +SET sql_mode=ORACLE; +CREATE PACKAGE pack AS +FUNCTION f1 RETURN INT; +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pack AS +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +PROCEDURE p1 AS +BEGIN +SELECT f1(); +END; +END pack; +$$ +connection slave; +connection slave; +SELECT * FROM mysql.proc WHERE db='test' AND name='pack'; +db test +name pack +type PACKAGE +specific_name pack +language SQL +sql_data_access CONTAINS_SQL +is_deterministic NO +security_type DEFINER +param_list +returns +body AS +FUNCTION f1 RETURN INT; +PROCEDURE p1; +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 +character_set_client latin1 +collation_connection latin1_swedish_ci +db_collation latin1_swedish_ci +body_utf8 +aggregate NONE +db test +name pack +type PACKAGE BODY +specific_name pack +language SQL +sql_data_access CONTAINS_SQL +is_deterministic NO +security_type DEFINER +param_list +returns +body AS +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +PROCEDURE p1 AS +BEGIN +SELECT f1(); +END; +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 +character_set_client latin1 +collation_connection latin1_swedish_ci +db_collation latin1_swedish_ci +body_utf8 +aggregate NONE +SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'pack.%'; +SET @@sql_mode=ORACLE; +SELECT pack.f1(); +pack.f1() +10 +CALL pack.p1(); +f1() +10 +SET @@sql_mode=DEFAULT; +connection master; +DROP PACKAGE pack; +connection slave; +connection slave; +SELECT COUNT(*) FROM mysql.proc WHERE db='test' AND name='pack'; +COUNT(*) +0 +# +# Creating a package with a COMMENT +# +connection master; +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +definer name security_type type comment +root@localhost p1 DEFINER PACKAGE package-p1-comment +root@localhost p1 DEFINER PACKAGE BODY package-body-p1-comment +connection slave; +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +definer name security_type type comment +root@localhost p1 DEFINER PACKAGE package-p1-comment +root@localhost p1 DEFINER PACKAGE BODY package-body-p1-comment +connection master; +DROP PACKAGE p1; +connection slave; +# +# Creating a package with a different DEFINER +# +connection master; +CREATE DEFINER=xxx@localhost PACKAGE p1 AS +PROCEDURE p1; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +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 +connection slave; +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 +connection master; +DROP PACKAGE p1; +connection slave; +# +# Creating a package with a different DEFINER + SQL SECURITY INVOKER +# +connection master; +CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS +PROCEDURE p1; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS +PROCEDURE p1 AS +BEGIN +NULL; +END; +END; +$$ +Warnings: +Note 1449 The user specified as a definer ('xxx'@'localhost') does not exist +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 +connection slave; +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 +connection master; +DROP PACKAGE p1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/r/rpl_sp_package_variables.result b/mysql-test/suite/compat/oracle/r/rpl_sp_package_variables.result new file mode 100644 index 00000000000..764686e4118 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/rpl_sp_package_variables.result @@ -0,0 +1,38 @@ +include/master-slave.inc +[connection master] +connection master; +SET sql_mode=ORACLE; +# +# MDEV-13139 Package-wide variables in CREATE PACKAGE +# +connection master; +CREATE PACKAGE p1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS +va INT:=10; +PROCEDURE p1 AS +BEGIN +INSERT INTO t1 VALUES (va); +END; +BEGIN +CREATE OR REPLACE TABLE t1 (a INT); +END; +$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +a +10 +10 +connection slave; +SELECT * FROM t1; +a +10 +10 +connection master; +DROP PACKAGE p1; +DROP TABLE t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/r/sp-package-code.result b/mysql-test/suite/compat/oracle/r/sp-package-code.result new file mode 100644 index 00000000000..0dea72dba89 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-code.result @@ -0,0 +1,245 @@ +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +PROCEDURE p2show; +PROCEDURE p2public; +FUNCTION f2public RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +a INT:=10; +PROCEDURE p1 AS +b INT:=20; +BEGIN +b:=a; +b:=a+1; +a:=b; +a:=b+1; +a:=a+1; +SET @a:=@a+2; +SELECT f1() FROM DUAL; +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN a; +END; +PROCEDURE p2private AS +BEGIN +SELECT 'This is p2private'; +END; +PROCEDURE p2public AS +BEGIN +SELECT 'This is p2public'; +END; +FUNCTION f2private RETURN TEXT AS +BEGIN +RETURN 'This is f2private'; +END; +FUNCTION f2public RETURN TEXT AS +BEGIN +RETURN 'This is f2public'; +END; +PROCEDURE p2show AS +BEGIN +SHOW FUNCTION CODE f2public; +SHOW FUNCTION CODE f2private; +SHOW PROCEDURE CODE p2public; +SHOW PROCEDURE CODE p2private; +SHOW PROCEDURE CODE p2show; +END; +BEGIN +a:=a+1; +DECLARE +b INT; +BEGIN +b:=a; +b:=a+1; +a:=b; +a:=b+1; +END; +END; +$$ +SHOW PROCEDURE CODE pkg1.p1; +Pos Instruction +0 set b@0 20 +1 set b@0 PACKAGE_BODY.a@0 +2 set b@0 PACKAGE_BODY.a@0 + 1 +3 set PACKAGE_BODY.a@0 b@0 +4 set PACKAGE_BODY.a@0 b@0 + 1 +5 set PACKAGE_BODY.a@0 PACKAGE_BODY.a@0 + 1 +6 stmt 31 "SET @a:=@a+2" +7 stmt 0 "SELECT f1() FROM DUAL" +SHOW FUNCTION CODE pkg1.f1; +Pos Instruction +0 freturn int PACKAGE_BODY.a@0 +SHOW PACKAGE BODY CODE pkg1; +Pos Instruction +0 set a@0 10 +1 set a@0 a@0 + 1 +2 set b@1 NULL +3 set b@1 a@0 +4 set b@1 a@0 + 1 +5 set a@0 b@1 +6 set a@0 b@1 + 1 +7 jump 11 +CALL pkg1.p2show; +Pos Instruction +0 freturn blob 'This is f2public' +Pos Instruction +0 freturn blob 'This is f2private' +Pos Instruction +0 stmt 0 "SELECT 'This is p2public'" +Pos Instruction +0 stmt 0 "SELECT 'This is p2private'" +Pos Instruction +0 stmt 110 "SHOW FUNCTION CODE f2public" +1 stmt 110 "SHOW FUNCTION CODE f2private" +2 stmt 109 "SHOW PROCEDURE CODE p2public" +3 stmt 109 "SHOW PROCEDURE CODE p2private" +4 stmt 109 "SHOW PROCEDURE CODE p2show" +DROP PACKAGE pkg1; +CREATE TABLE t1 (a INT); +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +a t1.a%TYPE:=10; +PROCEDURE p1 AS +b t1.a%TYPE:=20; +BEGIN +b:=a; +b:=a+1; +b:=b+1; +a:=b; +a:=b+1; +a:=a+1; +END; +BEGIN +a:=a+1; +DECLARE +b t1.a%TYPE; +BEGIN +b:=a; +b:=a+1; +a:=b; +a:=b+1; +END; +END; +$$ +SHOW PROCEDURE CODE pkg1.p1; +Pos Instruction +0 set b@0 20 +1 set b@0 PACKAGE_BODY.a@0 +2 set b@0 PACKAGE_BODY.a@0 + 1 +3 set b@0 b@0 + 1 +4 set PACKAGE_BODY.a@0 b@0 +5 set PACKAGE_BODY.a@0 b@0 + 1 +6 set PACKAGE_BODY.a@0 PACKAGE_BODY.a@0 + 1 +SHOW PACKAGE BODY CODE pkg1; +Pos Instruction +0 set a@0 10 +1 set a@0 a@0 + 1 +2 set b@1 NULL +3 set b@1 a@0 +4 set b@1 a@0 + 1 +5 set a@0 b@1 +6 set a@0 b@1 + 1 +7 jump 11 +DROP PACKAGE pkg1; +DROP TABLE t1; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +a ROW(a INT,b TEXT):=ROW(10,'x10'); +PROCEDURE p1 AS +b ROW(a INT,b TEXT):=ROW(20,'x20'); +BEGIN +b:=a; +a:=b; +b.a:=a.a+1; +a.a:=b.a+1; +a.a:=a.a+1; +END; +BEGIN +a.a:=a.a+1; +DECLARE +b ROW(a INT,b TEXT):=ROW(30,'x30'); +BEGIN +b:=a; +b.a:=a.a+1; +a:=b; +a.a:=b.a+1; +END; +END; +$$ +SHOW PROCEDURE CODE pkg1.p1; +Pos Instruction +0 set b@0 (20,'x20') +1 set b@0 PACKAGE_BODY.a@0 +2 set PACKAGE_BODY.a@0 b@0 +3 set b.a@0[0] PACKAGE_BODY.a.a@0[0] + 1 +4 set PACKAGE_BODY.a.a@0[0] b.a@0[0] + 1 +5 set PACKAGE_BODY.a.a@0[0] PACKAGE_BODY.a.a@0[0] + 1 +SHOW PACKAGE BODY CODE pkg1; +Pos Instruction +0 set a@0 (10,'x10') +1 set a.a@0[0] a.a@0[0] + 1 +2 set b@1 (30,'x30') +3 set b@1 a@0 +4 set b.a@1[0] a.a@0[0] + 1 +5 set a@0 b@1 +6 set a.a@0[0] b.a@1[0] + 1 +7 jump 11 +DROP PACKAGE pkg1; +CREATE TABLE t1 (a INT, b TEXT); +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +a t1%ROWTYPE:=ROW(10,'x10'); +PROCEDURE p1 AS +b t1%ROWTYPE:=ROW(20,'x20'); +BEGIN +b:=a; +a:=b; +b.a:=a.a+1; +a.a:=b.a+1; +a.a:=a.a+1; +END; +BEGIN +a.a:=a.a+1; +DECLARE +b t1%ROWTYPE:=ROW(30,'x30'); +BEGIN +b:=a; +b.a:=a.a+1; +a:=b; +a.a:=b.a+1; +END; +END; +$$ +SHOW PROCEDURE CODE pkg1.p1; +Pos Instruction +0 set b@0 (20,'x20') +1 set b@0 PACKAGE_BODY.a@0 +2 set PACKAGE_BODY.a@0 b@0 +3 set b.a@0["a"] PACKAGE_BODY.a.a@0["a"] + 1 +4 set PACKAGE_BODY.a.a@0["a"] b.a@0["a"] + 1 +5 set PACKAGE_BODY.a.a@0["a"] PACKAGE_BODY.a.a@0["a"] + 1 +SHOW PACKAGE BODY CODE pkg1; +Pos Instruction +0 set a@0 (10,'x10') +1 set a.a@0["a"] a.a@0["a"] + 1 +2 set b@1 (30,'x30') +3 set b@1 a@0 +4 set b.a@1["a"] a.a@0["a"] + 1 +5 set a@0 b@1 +6 set a.a@0["a"] b.a@1["a"] + 1 +7 jump 11 +DROP PACKAGE pkg1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-db.result b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-db.result new file mode 100644 index 00000000000..95f45c25314 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-db.result @@ -0,0 +1,43 @@ +# +# MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +# +SET @object_type='db'; +# +# Start of sp-package-concurrent-dml.inc +# +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p2 AS +BEGIN +SELECT 'This is p2' AS msg; +END; +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1' AS msg; +DO GET_LOCK('mdev15070',120); +CALL p2(); +DO RELEASE_LOCK('mdev15070'); +END; +END; +$$ +connect con2,localhost,root; +connection con2; +DO GET_LOCK('mdev15070', 120); +connection default; +CALL pkg1.p1; +connection con2; +CREATE DATABASE test1; +CREATE FUNCTION test1.f1() RETURNS INT RETURN 10; +DROP DATABASE test1; +DO RELEASE_LOCK('mdev15070'); +disconnect con2; +connection default; +msg +This is p1 +msg +This is p2 +DROP PACKAGE IF EXISTS pkg1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-package.result b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-package.result new file mode 100644 index 00000000000..eb7d38a8f67 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-package.result @@ -0,0 +1,96 @@ +# +# MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +# +SET @object_type='package_replace_pkg1'; +# +# Start of sp-package-concurrent-dml.inc +# +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p2 AS +BEGIN +SELECT 'This is p2' AS msg; +END; +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1' AS msg; +DO GET_LOCK('mdev15070',120); +CALL p2(); +DO RELEASE_LOCK('mdev15070'); +END; +END; +$$ +connect con2,localhost,root; +connection con2; +DO GET_LOCK('mdev15070', 120); +connection default; +CALL pkg1.p1; +connection con2; +SET sql_mode=ORACLE; +CREATE OR REPLACE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +DROP PACKAGE pkg1; +DO RELEASE_LOCK('mdev15070'); +disconnect con2; +connection default; +msg +This is p1 +msg +This is p2 +DROP PACKAGE IF EXISTS pkg1; +Warnings: +Note 1305 PACKAGE test.pkg1 does not exist +SET @object_type='package_body_replace_pkg1'; +# +# Start of sp-package-concurrent-dml.inc +# +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p2 AS +BEGIN +SELECT 'This is p2' AS msg; +END; +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1' AS msg; +DO GET_LOCK('mdev15070',120); +CALL p2(); +DO RELEASE_LOCK('mdev15070'); +END; +END; +$$ +connect con2,localhost,root; +connection con2; +DO GET_LOCK('mdev15070', 120); +connection default; +CALL pkg1.p1; +connection con2; +SET sql_mode=ORACLE; +CREATE OR REPLACE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1 version 2' AS msg; +END; +END; +$$ +DROP PACKAGE pkg1; +DO RELEASE_LOCK('mdev15070'); +disconnect con2; +connection default; +msg +This is p1 +msg +This is p2 +DROP PACKAGE IF EXISTS pkg1; +Warnings: +Note 1305 PACKAGE test.pkg1 does not exist diff --git a/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-trigger.result b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-trigger.result new file mode 100644 index 00000000000..8181714f59c --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-trigger.result @@ -0,0 +1,44 @@ +# +# MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +# +SET @object_type='trigger'; +# +# Start of sp-package-concurrent-dml.inc +# +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p2 AS +BEGIN +SELECT 'This is p2' AS msg; +END; +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1' AS msg; +DO GET_LOCK('mdev15070',120); +CALL p2(); +DO RELEASE_LOCK('mdev15070'); +END; +END; +$$ +connect con2,localhost,root; +connection con2; +DO GET_LOCK('mdev15070', 120); +connection default; +CALL pkg1.p1; +connection con2; +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1; +DROP TRIGGER tr1; +DROP TABLE t1; +DO RELEASE_LOCK('mdev15070'); +disconnect con2; +connection default; +msg +This is p1 +msg +This is p2 +DROP PACKAGE IF EXISTS pkg1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-view.result b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-view.result new file mode 100644 index 00000000000..b0ceec608a6 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-concurrent-dml-view.result @@ -0,0 +1,42 @@ +# +# MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +# +SET @object_type='view'; +# +# Start of sp-package-concurrent-dml.inc +# +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p2 AS +BEGIN +SELECT 'This is p2' AS msg; +END; +PROCEDURE p1 AS +BEGIN +SELECT 'This is p1' AS msg; +DO GET_LOCK('mdev15070',120); +CALL p2(); +DO RELEASE_LOCK('mdev15070'); +END; +END; +$$ +connect con2,localhost,root; +connection con2; +DO GET_LOCK('mdev15070', 120); +connection default; +CALL pkg1.p1; +connection con2; +CREATE VIEW v1 AS SELECT 1 AS c; +DROP VIEW v1; +DO RELEASE_LOCK('mdev15070'); +disconnect con2; +connection default; +msg +This is p1 +msg +This is p2 +DROP PACKAGE IF EXISTS pkg1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-innodb.result b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result new file mode 100644 index 00000000000..50eb2dc6cd0 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-innodb.result @@ -0,0 +1,75 @@ +SET default_storage_engine=InnoDB; +SET sql_mode=ORACLE; +CREATE TABLE t1 (a INT, routine TEXT); +SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +ENGINE +InnoDB +INSERT INTO t1 VALUES (10,'none'); +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +a INT; +PROCEDURE p1 AS +BEGIN +a:=a+1; +INSERT INTO t1 VALUES (a,'p1'); +END; +BEGIN +SELECT MAX(t1.a) FROM t1 INTO a; +a:=a+1; +INSERT INTO t1 VALUES (a,'pkg1 initialization'); +END; +$$ +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +a routine +10 none +11 pkg1 initialization +12 p1 +DELETE FROM t1; +# sp-cache-invalidate +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +a routine +NULL pkg1 initialization +NULL p1 +ROLLBACK; +SELECT * FROM t1 ORDER BY a; +a routine +DELETE FROM t1; +# sp-cache-invalidate +INSERT INTO t1 VALUES (20,'none'); +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +a routine +20 none +21 pkg1 initialization +22 p1 +COMMIT; +SELECT * FROM t1 ORDER BY a; +a routine +20 none +21 pkg1 initialization +22 p1 +DELETE FROM t1; +# sp-cache-invalidate +INSERT INTO t1 VALUES (20,'none'); +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +a routine +20 none +21 pkg1 initialization +22 p1 +ROLLBACK; +SELECT * FROM t1 ORDER BY a; +a routine +20 none +DELETE FROM t1; +DROP PACKAGE pkg1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-mdl.result b/mysql-test/suite/compat/oracle/r/sp-package-mdl.result new file mode 100644 index 00000000000..18cc834461c --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-mdl.result @@ -0,0 +1,80 @@ +SET sql_mode=ORACLE; +DO GET_LOCK('lock',300); +connect conn1,localhost,root,,; +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS +BEGIN +DO GET_LOCK('lock',300); +END; +FUNCTION f1 RETURN INT AS +BEGIN +CALL p1; +RETURN 1; +END; +END; +$$ +SELECT pkg1.f1(); +connection default; +connect conn2,localhost,root,,; +SET sql_mode=ORACLE; +DROP PACKAGE pkg1; +connection default; +SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME +FROM INFORMATION_SCHEMA.PROCESSLIST +LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO +ON (ID=THREAD_ID) +ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE; +CONN 0 +INFO SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME +FROM INFORMATION_SCHEMA.PROCESSLIST +LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO +ON (ID=THREAD_ID) +ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE +STATE Filling schema table +LOCK_MODE MDL_SHARED_NO_WRITE +LOCK_TYPE User lock +TABLE_NAME +CONN 1 +INFO DO GET_LOCK('lock',300) +STATE User lock +LOCK_MODE MDL_SHARED +LOCK_TYPE Stored package body metadata lock +TABLE_NAME pkg1 +CONN 1 +INFO DO GET_LOCK('lock',300) +STATE User lock +LOCK_MODE MDL_SHARED +LOCK_TYPE Stored function metadata lock +TABLE_NAME pkg1.f1 +CONN 1 +INFO DO GET_LOCK('lock',300) +STATE User lock +LOCK_MODE MDL_SHARED +LOCK_TYPE Stored procedure metadata lock +TABLE_NAME pkg1.p1 +CONN 2 +INFO DROP PACKAGE pkg1 +STATE Waiting for stored package body metadata lock +LOCK_MODE MDL_INTENTION_EXCLUSIVE +LOCK_TYPE Global read lock +TABLE_NAME +CONN 2 +INFO DROP PACKAGE pkg1 +STATE Waiting for stored package body metadata lock +LOCK_MODE MDL_INTENTION_EXCLUSIVE +LOCK_TYPE Schema metadata lock +TABLE_NAME +DO RELEASE_LOCK('lock'); +connection conn1; +pkg1.f1() +1 +disconnect conn1; +connection conn2; +disconnect conn2; +connection default; diff --git a/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result b/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result new file mode 100644 index 00000000000..1a8e01a992f --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-mysqldump.result @@ -0,0 +1,261 @@ +SET sql_mode=ORACLE; +CREATE PROCEDURE p1 AS +BEGIN +SELECT pkg1.f1(); -- a standalone routine calls a package routine +END; +$$ +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS +BEGIN +CALL test.p1; -- a package routine calls a standalone routine +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END; +$$ +CALL p1; +pkg1.f1() +10 +CALL pkg1.p1; +pkg1.f1() +10 +SELECT pkg1.f1(); +pkg1.f1() +10 +CREATE PACKAGE pkg2 AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END; +$$ + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +/*!50003 DROP PROCEDURE IF EXISTS `p1` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET 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' */ ; +DELIMITER ;; +CREATE DEFINER="root"@"localhost" PROCEDURE "p1"() +AS +BEGIN +SELECT pkg1.f1(); -- a standalone routine calls a package routine +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PACKAGE IF EXISTS `pkg1` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET 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' */ ; +DELIMITER ;; +CREATE DEFINER="root"@"localhost" PACKAGE "pkg1" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PACKAGE IF EXISTS `pkg2` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET 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' */ ; +DELIMITER ;; +CREATE DEFINER="root"@"localhost" PACKAGE "pkg2" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PACKAGE BODY IF EXISTS `pkg1` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET 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' */ ; +DELIMITER ;; +CREATE DEFINER="root"@"localhost" PACKAGE BODY "pkg1" AS +PROCEDURE p1 AS +BEGIN +CALL test.p1; -- a package routine calls a standalone routine +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="test"> + <routines> + <routine Procedure="p1" 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" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER="root"@"localhost" PROCEDURE "p1"() +AS +BEGIN +SELECT pkg1.f1(); -- a standalone routine calls a package routine +END +]]> + </routine> + <routine Package="pkg1" 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" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER="root"@"localhost" PACKAGE "pkg1" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END +]]> + </routine> + <routine Package="pkg2" 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" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER="root"@"localhost" PACKAGE "pkg2" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END +]]> + </routine> + <routine Package_body="pkg1" 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" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> +<![CDATA[ +CREATE DEFINER="root"@"localhost" PACKAGE BODY "pkg1" AS +PROCEDURE p1 AS +BEGIN +CALL test.p1; -- a package routine calls a standalone routine +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END +]]> + </routine> + </routines> +</database> +</mysqldump> +DROP PACKAGE pkg1; +DROP PACKAGE pkg2; +DROP PROCEDURE p1; +SHOW PACKAGE STATUS; +Db test +Name pkg1 +Type PACKAGE +Definer root@localhost +Modified 0000-00-00 00:00:00 +Created 0000-00-00 00:00:00 +Security_type DEFINER +Comment +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +Db test +Name pkg2 +Type PACKAGE +Definer root@localhost +Modified 0000-00-00 00:00:00 +Created 0000-00-00 00:00:00 +Security_type DEFINER +Comment +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +SHOW PACKAGE BODY STATUS; +Db test +Name pkg1 +Type PACKAGE BODY +Definer root@localhost +Modified 0000-00-00 00:00:00 +Created 0000-00-00 00:00:00 +Security_type DEFINER +Comment +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PACKAGE "pkg1" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE pkg2; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg2 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PACKAGE "pkg2" AS +PROCEDURE p1; +FUNCTION f1 RETURN INT; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg1; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PACKAGE BODY "pkg1" AS +PROCEDURE p1 AS +BEGIN +CALL test.p1; +END; +FUNCTION f1 RETURN INT AS +BEGIN +RETURN 10; +END; +END latin1 latin1_swedish_ci latin1_swedish_ci +CALL p1; +pkg1.f1() +10 +CALL pkg1.p1; +pkg1.f1() +10 +SELECT pkg1.f1(); +pkg1.f1() +10 +DROP PACKAGE pkg1; +DROP PACKAGE pkg2; +DROP PROCEDURE p1; +# removing the dump file diff --git a/mysql-test/suite/compat/oracle/r/sp-package-security.result b/mysql-test/suite/compat/oracle/r/sp-package-security.result new file mode 100644 index 00000000000..6cb580e0c46 --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package-security.result @@ -0,0 +1,322 @@ +SET sql_mode=ORACLE; +CREATE DATABASE db1; +CREATE USER u1@localhost IDENTIFIED BY ''; +GRANT SELECT ON db1.* TO u1@localhost; +connect conn1,localhost,u1,,db1; +SELECT CURRENT_USER; +CURRENT_USER +u1@localhost +SET sql_mode=ORACLE; +# +# User u1 cannot drop PROCEDURE, PACKAGE, PACKAGE BODY by default +# +DROP PROCEDURE p1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.p1' +DROP PACKAGE pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +DROP PACKAGE BODY pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# User u1 cannot create PROCEDURE, PACKAGE, PACKAGE BODY by default +# +CREATE PROCEDURE p1 AS +BEGIN +NULL; +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS BEGIN NULL; END; +END; +$$ +ERROR 42000: PACKAGE db1.pkg1 does not exist +# +# Now create a PACKAGE by root +# +connection default; +USE db1; +CREATE PROCEDURE p1root AS +BEGIN +SELECT 1; +END; +$$ +CREATE PACKAGE pkg1 AS +PROCEDURE p1; +FUNCTION f1 RETURN TEXT; +END; +$$ +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PACKAGE "pkg1" AS +PROCEDURE p1; +FUNCTION f1 RETURN TEXT; +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# u1 cannot SHOW yet: +# - the standalone procedure earlier created by root +# - the package specifications earlier create by root +# +connection conn1; +SHOW CREATE PROCEDURE p1root; +ERROR 42000: PROCEDURE p1root does not exist +SHOW CREATE PACKAGE pkg1; +ERROR 42000: PACKAGE pkg1 does not exist +# +# User u1 still cannot create a PACKAGE BODY +# +connection conn1; +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS BEGIN NULL; END; +FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END; +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +# +# Now grant EXECUTE: +# - on the standalone procedure earlier created by root +# - on the package specification earlier created by root +# +connection default; +GRANT EXECUTE ON PROCEDURE db1.p1root TO u1@localhost; +GRANT EXECUTE ON PACKAGE db1.pkg1 TO u1@localhost; +# +# Now u1 can do SHOW for: +# - the standalone procedure earlier created by root +# - the package specification earlier created by root +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +SET sql_mode=ORACLE; +SHOW CREATE PROCEDURE db1.p1root; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1root PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE db1.pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +# +# Now revoke EXECUTE and grant CREATE ROUTINE instead +# +connection default; +REVOKE EXECUTE ON PROCEDURE db1.p1root FROM u1@localhost; +REVOKE EXECUTE ON PACKAGE db1.pkg1 FROM u1@localhost; +GRANT CREATE ROUTINE ON db1.* TO u1@localhost; +# +# Reconnect u1 to make new grants have effect +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +SET sql_mode=ORACLE; +# +# Now u1 can SHOW: +# - standalone routines earlier created by root +# - package specifications earlier created by root +# +SHOW CREATE PROCEDURE p1root; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1root PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +# +# Now u1 can CREATE, DROP and EXECUTE its own standalone procedures +# +CREATE PROCEDURE p1 AS +BEGIN +NULL; +END; +$$ +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE "db1"."p1" TO 'u1'@'localhost' +CALL p1; +DROP PROCEDURE p1; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +# +# Now u1 can also CREATE, DROP its own package specifications +# +CREATE PACKAGE pkg2 AS +PROCEDURE p1; +FUNCTION f1 RETURN TEXT; +END; +$$ +SHOW CREATE PACKAGE pkg2; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg2 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="u1"@"localhost" PACKAGE "pkg2" AS +PROCEDURE p1; +FUNCTION f1 RETURN TEXT; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PACKAGE "db1"."pkg2" TO 'u1'@'localhost' +DROP PACKAGE pkg2; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +# +# Now u1 can also CREATE, DROP package bodies and EXECUTE package body routines +# +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END; +END; +$$ +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg1; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="u1"@"localhost" PACKAGE BODY "pkg1" AS +PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PACKAGE BODY "db1"."pkg1" TO 'u1'@'localhost' +CALL pkg1.p1; +comment +This is pkg1.p1 +SELECT pkg1.f1(); +pkg1.f1() +This is pkg1.f1 +DROP PACKAGE BODY pkg1; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +# +# Now create a PACKAGE BODY by root. +# u1 does not have EXECUTE access by default. +# +connection default; +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END; +END; +$$ +connection conn1; +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg1; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +pkg1 PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT NULL latin1 latin1_swedish_ci latin1_swedish_ci +CALL pkg1.p1; +ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +SELECT pkg1.f1(); +ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# Now grant EXECUTE to u1 on the PACKAGE BODY created by root +# +connection default; +GRANT EXECUTE ON PACKAGE BODY db1.pkg1 TO u1@localhost; +disconnect conn1; +connect conn1,localhost,u1,,db1; +SELECT CURRENT_USER; +CURRENT_USER +u1@localhost +SET sql_mode=ORACLE; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO 'u1'@'localhost' +GRANT SELECT, CREATE ROUTINE ON "db1".* TO 'u1'@'localhost' +GRANT EXECUTE ON PACKAGE BODY "db1"."pkg1" TO 'u1'@'localhost' +CALL pkg1.p1; +comment +This is pkg1.p1 +SELECT pkg1.f1(); +pkg1.f1() +This is pkg1.f1 +connection default; +DROP PACKAGE BODY pkg1; +# +# u1 still cannot DROP the package specification earlier created by root. +# +connection conn1; +DROP PACKAGE pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# Grant ALTER ROUTINE to u1 +# +connection default; +GRANT ALTER ROUTINE ON db1.* TO u1@localhost; +# +# Now u1 can DROP: +# - the standalone procedure earlier created by root +# - the package specification earlier created by root +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +SET sql_mode=ORACLE; +DROP PACKAGE pkg1; +DROP PROCEDURE p1root; +disconnect conn1; +connection default; +DROP USER u1@localhost; +DROP DATABASE db1; +USE test; +# +# Creator=root, definer=xxx +# +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 +SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; +END; +BEGIN +SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +CALL p1.p1; +ERROR 42000: execute command denied to user 'xxx'@'localhost' for routine 'test.p1' +GRANT EXECUTE ON PACKAGE BODY test.p1 TO xxx@localhost; +CALL p1.p1; +SESSION_USER() CURRENT_USER() msg +root@localhost xxx@localhost package body p1 +SESSION_USER() CURRENT_USER() msg +root@localhost xxx@localhost p1.p1 +DROP PACKAGE p1; +DROP USER xxx@localhost; +# +# Creator=root, definer=xxx, SQL SECURITY INVOKER +# +CREATE USER xxx@localhost; +CREATE DEFINER=xxx@localhost PACKAGE p1 AS +PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS +PROCEDURE p1 AS +BEGIN +SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; +END; +BEGIN +SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +CALL p1.p1; +SESSION_USER() CURRENT_USER() msg +root@localhost root@localhost package body p1 +SESSION_USER() CURRENT_USER() msg +root@localhost root@localhost p1.p1 +DROP PACKAGE p1; +DROP USER xxx@localhost; diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result new file mode 100644 index 00000000000..4c8fac26d2d --- /dev/null +++ b/mysql-test/suite/compat/oracle/r/sp-package.result @@ -0,0 +1,2871 @@ +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'; +ERROR HY000: This command is not supported in the prepared statement protocol yet +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'; +ERROR HY000: This command is not supported in the prepared statement protocol yet +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. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +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 HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +SELECT test2.f1(); +ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +SELECT test2.f1(); +ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +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 HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +CALL test2.p1(); +ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +CALL test2.p1(); +ERROR HY000: Failed to load routine test.test2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +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: Subroutine '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: Subroutine '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: Subroutine '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: Subroutine '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: Subroutine '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: Subroutine '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 latin1_swedish_ci +body_utf8 +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 +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 latin1_swedish_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 +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 latin1_swedish_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 +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 latin1_swedish_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 latin1_swedish_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 latin1_swedish_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 latin1_swedish_ci +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 latin1_swedish_ci +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 latin1_swedish_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 latin1_swedish_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; diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result index f0a7d2808f1..7f042825385 100644 --- a/mysql-test/suite/compat/oracle/r/sp.result +++ b/mysql-test/suite/compat/oracle/r/sp.result @@ -166,6 +166,13 @@ IF a=10 THEN NULL; ELSE NULL; END IF; END; / DROP PROCEDURE p1; +# Keywords that are OK for table names, but not for SP variables +CREATE TABLE function (function int); +INSERT INTO function SET function=10; +SELECT function.function FROM function; +function +10 +DROP TABLE function; # Testing that (some) keyword_sp are allowed in Oracle-style assignments CREATE PROCEDURE p1 (action OUT INT) AS BEGIN action:=10; END;/ DROP PROCEDURE p1/ diff --git a/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test b/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test new file mode 100644 index 00000000000..577ff58d5ae --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/binlog_stm_sp_package.test @@ -0,0 +1,158 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +--disable_query_log +reset master; # get rid of previous tests binlog +--enable_query_log + +SET sql_mode=ORACLE; + +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE IF NOT EXISTS p1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE BODY p1 AS + PROCEDURE p1 AS + BEGIN + NULL; + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 10; + END; +END; +$$ +DELIMITER ;$$ + +DROP PACKAGE BODY p1; +DROP PACKAGE p1; +DROP PACKAGE IF EXISTS p1; + +--echo # +--echo # Creating a package with a COMMENT clause +--echo # + +DELIMITER $$; +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ +DROP PACKAGE p1; + +--echo # +--echo # Creating a package with a different DEFINER +--echo # + +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 ;$$ +DROP PACKAGE p1; + + +--echo # +--echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER +--echo # + +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 ;$$ +DROP PACKAGE p1; + + +--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 ;$$ + +DROP PACKAGE BODY test2.test2; +DROP PACKAGE test2.test2; +DROP DATABASE test2; + + +--echo # +--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS + a INT:=0; + PROCEDURE p1 AS + BEGIN + INSERT INTO t1 VALUES (a); + a:=a+1; + END; +BEGIN + a:=10; +END; +$$ +DELIMITER ;$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +--source sp-cache-invalidate.inc +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +DROP PACKAGE p1; +DROP TABLE t1; + + +--let $binlog_file = LAST +source include/show_binlog_events.inc; diff --git a/mysql-test/suite/compat/oracle/t/rpl_sp_package.test b/mysql-test/suite/compat/oracle/t/rpl_sp_package.test new file mode 100644 index 00000000000..40bb0b0d9cd --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_sp_package.test @@ -0,0 +1,134 @@ +--source include/master-slave.inc + +connection master; + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE pack AS + FUNCTION f1 RETURN INT; + PROCEDURE p1; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +CREATE PACKAGE BODY pack AS + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 10; + END; + PROCEDURE p1 AS + BEGIN + SELECT f1(); + END; +END pack; +$$ +DELIMITER ;$$ + +sync_slave_with_master; +connection slave; +--vertical_results +--replace_column 13 # 14 # +SELECT * FROM mysql.proc WHERE db='test' AND name='pack'; +--replace_column 13 # 14 # +SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'pack.%'; +--horizontal_results + +SET @@sql_mode=ORACLE; +SELECT pack.f1(); +CALL pack.p1(); +SET @@sql_mode=DEFAULT; + +connection master; +DROP PACKAGE pack; + +sync_slave_with_master; +connection slave; +SELECT COUNT(*) FROM mysql.proc WHERE db='test' AND name='pack'; + +--echo # +--echo # Creating a package with a COMMENT +--echo # + +connection master; + +DELIMITER $$; +CREATE PACKAGE p1 COMMENT 'package-p1-comment' AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 COMMENT 'package-body-p1-comment' AS + PROCEDURE p1 AS + BEGIN + NULL; + END; +END; +$$ +DELIMITER ;$$ + +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; +sync_slave_with_master; +SELECT definer, name, security_type, type, `comment` FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + + +--echo # +--echo # Creating a package with a different DEFINER +--echo # + +connection master; + +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; +sync_slave_with_master; +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + +--echo # +--echo # Creating a package with a different DEFINER + SQL SECURITY INVOKER +--echo # + +connection master; + +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; +sync_slave_with_master; +SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type; + +connection master; +DROP PACKAGE p1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test b/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test new file mode 100644 index 00000000000..fca611243ad --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/rpl_sp_package_variables.test @@ -0,0 +1,36 @@ +--source include/master-slave.inc + +connection master; +SET sql_mode=ORACLE; + +--echo # +--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE +--echo # +connection master; +DELIMITER $$; +CREATE PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY p1 AS + va INT:=10; + PROCEDURE p1 AS + BEGIN + INSERT INTO t1 VALUES (va); + END; +BEGIN + CREATE OR REPLACE TABLE t1 (a INT); +END; +$$ +DELIMITER ;$$ +CALL p1.p1(); +CALL p1.p1(); +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +DROP PACKAGE p1; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/compat/oracle/t/sp-cache-invalidate.inc b/mysql-test/suite/compat/oracle/t/sp-cache-invalidate.inc new file mode 100644 index 00000000000..945af6e7129 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-cache-invalidate.inc @@ -0,0 +1,11 @@ +--echo # sp-cache-invalidate +--disable_query_log +DELIMITER $$; +CREATE FUNCTION dummy RETURN INT AS +BEGIN + RETURN 1; +END; +$$ +DELIMITER ;$$ +DROP FUNCTION dummy; +--enable_query_log diff --git a/mysql-test/suite/compat/oracle/t/sp-package-code.test b/mysql-test/suite/compat/oracle/t/sp-package-code.test new file mode 100644 index 00000000000..9cca53964ec --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-code.test @@ -0,0 +1,182 @@ +-- source include/have_debug.inc + +SET sql_mode=ORACLE; + + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; + PROCEDURE p2show; + PROCEDURE p2public; + FUNCTION f2public RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + a INT:=10; + PROCEDURE p1 AS + b INT:=20; + BEGIN + b:=a; + b:=a+1; + a:=b; + a:=b+1; + a:=a+1; + SET @a:=@a+2; + SELECT f1() FROM DUAL; + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN a; + END; + PROCEDURE p2private AS + BEGIN + SELECT 'This is p2private'; + END; + PROCEDURE p2public AS + BEGIN + SELECT 'This is p2public'; + END; + FUNCTION f2private RETURN TEXT AS + BEGIN + RETURN 'This is f2private'; + END; + FUNCTION f2public RETURN TEXT AS + BEGIN + RETURN 'This is f2public'; + END; + PROCEDURE p2show AS + BEGIN + SHOW FUNCTION CODE f2public; + SHOW FUNCTION CODE f2private; + SHOW PROCEDURE CODE p2public; + SHOW PROCEDURE CODE p2private; + SHOW PROCEDURE CODE p2show; + END; +BEGIN + a:=a+1; + DECLARE + b INT; + BEGIN + b:=a; + b:=a+1; + a:=b; + a:=b+1; + END; +END; +$$ +DELIMITER ;$$ + +SHOW PROCEDURE CODE pkg1.p1; +SHOW FUNCTION CODE pkg1.f1; +SHOW PACKAGE BODY CODE pkg1; +CALL pkg1.p2show; + +DROP PACKAGE pkg1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + a t1.a%TYPE:=10; + PROCEDURE p1 AS + b t1.a%TYPE:=20; + BEGIN + b:=a; + b:=a+1; + b:=b+1; + a:=b; + a:=b+1; + a:=a+1; + END; +BEGIN + a:=a+1; + DECLARE + b t1.a%TYPE; + BEGIN + b:=a; + b:=a+1; + a:=b; + a:=b+1; + END; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE pkg1.p1; +SHOW PACKAGE BODY CODE pkg1; +DROP PACKAGE pkg1; +DROP TABLE t1; + + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + a ROW(a INT,b TEXT):=ROW(10,'x10'); + PROCEDURE p1 AS + b ROW(a INT,b TEXT):=ROW(20,'x20'); + BEGIN + b:=a; + a:=b; + b.a:=a.a+1; + a.a:=b.a+1; + a.a:=a.a+1; + END; +BEGIN + a.a:=a.a+1; + DECLARE + b ROW(a INT,b TEXT):=ROW(30,'x30'); + BEGIN + b:=a; + b.a:=a.a+1; + a:=b; + a.a:=b.a+1; + END; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE pkg1.p1; +SHOW PACKAGE BODY CODE pkg1; +DROP PACKAGE pkg1; + + +CREATE TABLE t1 (a INT, b TEXT); +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + a t1%ROWTYPE:=ROW(10,'x10'); + PROCEDURE p1 AS + b t1%ROWTYPE:=ROW(20,'x20'); + BEGIN + b:=a; + a:=b; + b.a:=a.a+1; + a.a:=b.a+1; + a.a:=a.a+1; + END; +BEGIN + a.a:=a.a+1; + DECLARE + b t1%ROWTYPE:=ROW(30,'x30'); + BEGIN + b:=a; + b.a:=a.a+1; + a:=b; + a.a:=b.a+1; + END; +END; +$$ +DELIMITER ;$$ +SHOW PROCEDURE CODE pkg1.p1; +SHOW PACKAGE BODY CODE pkg1; +DROP PACKAGE pkg1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-db.test b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-db.test new file mode 100644 index 00000000000..0528e6cbfcd --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-db.test @@ -0,0 +1,6 @@ +--echo # +--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +--echo # + +SET @object_type='db'; +--source sp-package-concurrent-dml.inc diff --git a/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-package.test b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-package.test new file mode 100644 index 00000000000..0f1a0ef3975 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-package.test @@ -0,0 +1,10 @@ +--echo # +--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +--echo # + +SET @object_type='package_replace_pkg1'; +--source sp-package-concurrent-dml.inc + +SET @object_type='package_body_replace_pkg1'; +--source sp-package-concurrent-dml.inc + diff --git a/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-trigger.test b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-trigger.test new file mode 100644 index 00000000000..09ba1d70142 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-trigger.test @@ -0,0 +1,6 @@ +--echo # +--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +--echo # + +SET @object_type='trigger'; +--source sp-package-concurrent-dml.inc diff --git a/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-view.test b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-view.test new file mode 100644 index 00000000000..d2c2041a353 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml-view.test @@ -0,0 +1,6 @@ +--echo # +--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine +--echo # + +SET @object_type='view'; +--source sp-package-concurrent-dml.inc diff --git a/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml.inc b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml.inc new file mode 100644 index 00000000000..8ee96d1ee6a --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-concurrent-dml.inc @@ -0,0 +1,107 @@ +--echo # +--echo # Start of sp-package-concurrent-dml.inc +--echo # + +--source include/count_sessions.inc + +let $object_type= `SELECT @object_type`; + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p2 AS + BEGIN + SELECT 'This is p2' AS msg; + END; + PROCEDURE p1 AS + BEGIN + SELECT 'This is p1' AS msg; + DO GET_LOCK('mdev15070',120); + CALL p2(); + DO RELEASE_LOCK('mdev15070'); + END; +END; +$$ +DELIMITER ;$$ + +connect (con2,localhost,root); +connection con2; +DO GET_LOCK('mdev15070', 120); + +connection default; +send CALL pkg1.p1; + +connection con2; +let $wait_condition= + SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST + WHERE state = "User lock" AND info LIKE "%GET_LOCK%mdev15070%"; +--source include/wait_condition.inc + + +if ($object_type==view) +{ + CREATE VIEW v1 AS SELECT 1 AS c; + DROP VIEW v1; +} + + +if ($object_type==package_replace_pkg1) +{ + SET sql_mode=ORACLE; + DELIMITER $$; + CREATE OR REPLACE PACKAGE pkg1 AS + PROCEDURE p1; + END; + $$ + DELIMITER ;$$ + DROP PACKAGE pkg1; +} + + +if ($object_type==package_body_replace_pkg1) +{ + SET sql_mode=ORACLE; + DELIMITER $$; + CREATE OR REPLACE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + SELECT 'This is p1 version 2' AS msg; + END; + END; + $$ + DELIMITER ;$$ + DROP PACKAGE pkg1; +} + + +if ($object_type==trigger) +{ + CREATE TABLE t1 (a INT); + CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1; + DROP TRIGGER tr1; + DROP TABLE t1; +} + + +if ($object_type=='db') +{ + CREATE DATABASE test1; + CREATE FUNCTION test1.f1() RETURNS INT RETURN 10; + DROP DATABASE test1; +} + + +DO RELEASE_LOCK('mdev15070'); + +disconnect con2; + +connection default; +reap; + +DROP PACKAGE IF EXISTS pkg1; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/compat/oracle/t/sp-package-innodb.test b/mysql-test/suite/compat/oracle/t/sp-package-innodb.test new file mode 100644 index 00000000000..f4cd05b7112 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-innodb.test @@ -0,0 +1,62 @@ +-- source include/have_innodb.inc + +SET default_storage_engine=InnoDB; + +SET sql_mode=ORACLE; + +CREATE TABLE t1 (a INT, routine TEXT); +SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +INSERT INTO t1 VALUES (10,'none'); + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + a INT; + PROCEDURE p1 AS + BEGIN + a:=a+1; + INSERT INTO t1 VALUES (a,'p1'); + END; +BEGIN + SELECT MAX(t1.a) FROM t1 INTO a; + a:=a+1; + INSERT INTO t1 VALUES (a,'pkg1 initialization'); +END; +$$ +DELIMITER ;$$ +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +DELETE FROM t1; + +--source sp-cache-invalidate.inc +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +ROLLBACK; +SELECT * FROM t1 ORDER BY a; +DELETE FROM t1; + +--source sp-cache-invalidate.inc +INSERT INTO t1 VALUES (20,'none'); +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +COMMIT; +SELECT * FROM t1 ORDER BY a; +DELETE FROM t1; + +--source sp-cache-invalidate.inc +INSERT INTO t1 VALUES (20,'none'); +START TRANSACTION; +CALL pkg1.p1; +SELECT * FROM t1 ORDER BY a; +ROLLBACK; +SELECT * FROM t1 ORDER BY a; +DELETE FROM t1; + +DROP PACKAGE pkg1; +DROP TABLE t1; diff --git a/mysql-test/suite/compat/oracle/t/sp-package-mdl.test b/mysql-test/suite/compat/oracle/t/sp-package-mdl.test new file mode 100644 index 00000000000..de4f7aaabad --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-mdl.test @@ -0,0 +1,110 @@ +--source include/have_metadata_lock_info.inc + +# +# This test demonstrates that: +# - A call to a package routine acquires a shared MDL lock on the entire package +# - "DROP PACKAGE" waits until the currently running package routines end +# + +SET sql_mode=ORACLE; +DO GET_LOCK('lock',300); + + +# +# conn1 will execute package pkg1 routines and +# and therefore acquire a shared MDL on "package body pkg1" +# + +connect (conn1,localhost,root,,); +SET sql_mode=ORACLE; +let $conn1_id= `SELECT CONNECTION_ID()`; +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + DO GET_LOCK('lock',300); + END; + FUNCTION f1 RETURN INT AS + BEGIN + CALL p1; + RETURN 1; + END; +END; +$$ +DELIMITER ;$$ +send SELECT pkg1.f1(); + +# +# wait for conn1 to actually start execution of pkg1.p1 +# + +connection default; +let $wait_timeout= 60; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=$conn1_id AND INFO LIKE '%GET_LOCK%' AND STATE='User lock'; +--source include/wait_condition.inc + + +# +# conn2 will do "DROP PACKAGE pkg1". +# It will acquire an exclusive MDL on "package body pkg1", and therefore +# it should wait until conn1 ends the package routine execution +# + +connect (conn2,localhost,root,,); +let $conn2_id= `SELECT CONNECTION_ID()`; +SET sql_mode=ORACLE; +send DROP PACKAGE pkg1; + +# +# wait for conn2 to actually enter the "DROP" statement and get locked by conn1 +# +connection default; +let $wait_timeout= 60; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID=$conn2_id + AND INFO LIKE '%DROP PACKAGE%' + AND STATE='Waiting for stored package body metadata lock'; +--source include/wait_condition.inc + +# +# Now we have three threads involved. +# The following I_S query will check that the threads are in these states: +# +# default (0) - is holding a user lock 'lock' +# conn1 (1) - is executing the package procedure test.pkg1.p1, +# is holding a shared MDL on 'package body pkg1', +# is waiting for the user lock 'lock' to be released +# conn2 (2) - is waiting for 'conn1' to end execution of test.pkg1.* routines, +# to acquire an exclusive MDL on 'package body pkg1', +# to DROP the package pkg1 +# +--vertical_results +SELECT ID-CONNECTION_ID() AS CONN,INFO,STATE,LOCK_MODE,LOCK_TYPE,TABLE_NAME + FROM INFORMATION_SCHEMA.PROCESSLIST + LEFT JOIN INFORMATION_SCHEMA.METADATA_LOCK_INFO + ON (ID=THREAD_ID) + ORDER BY ID,TABLE_NAME,LOCK_MODE,LOCK_TYPE; +--horizontal_results + +# +# Now let conn1 finish the package routine execution +# +DO RELEASE_LOCK('lock'); +connection conn1; +reap; +disconnect conn1; + +# +# Now conn2 should actually DROP the package +# +connection conn2; +reap; +disconnect conn2; + +connection default; diff --git a/mysql-test/suite/compat/oracle/t/sp-package-mysqldump.test b/mysql-test/suite/compat/oracle/t/sp-package-mysqldump.test new file mode 100644 index 00000000000..8f50c1fc4b0 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-mysqldump.test @@ -0,0 +1,93 @@ +--source include/not_embedded.inc + +SET sql_mode=ORACLE; + +# +# Create a standalone procedure test.p1 and a package pkg1. +# The standalone routine test.p1 and the package routines call each other. +# + +DELIMITER $$; +CREATE PROCEDURE p1 AS +BEGIN + SELECT pkg1.f1(); -- a standalone routine calls a package routine +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS + BEGIN + CALL test.p1; -- a package routine calls a standalone routine + END; + FUNCTION f1 RETURN INT AS + BEGIN + RETURN 10; + END; +END; +$$ +DELIMITER ;$$ + +CALL p1; +CALL pkg1.p1; +SELECT pkg1.f1(); + + +# +# Create specifications for one more package, without a BODY +# +DELIMITER $$; +CREATE PACKAGE pkg2 AS + PROCEDURE p1; + FUNCTION f1 RETURN INT; +END; +$$ +DELIMITER ;$$ + + +--exec $MYSQL_DUMP --skip-comments --routines test +--exec $MYSQL_DUMP --skip-comments --routines --xml test + +let $dump = $MYSQLTEST_VARDIR/tmp/sp-package-mysqldump.sql; + +--exec $MYSQL_DUMP --compact --routines test > $dump + +DROP PACKAGE pkg1; +DROP PACKAGE pkg2; +DROP PROCEDURE p1; + +--exec $MYSQL test < $dump + +--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 + +SHOW CREATE PACKAGE pkg1; +SHOW CREATE PACKAGE pkg2; +SHOW CREATE PACKAGE BODY pkg1; + +CALL p1; +CALL pkg1.p1; +SELECT pkg1.f1(); + +DROP PACKAGE pkg1; +DROP PACKAGE pkg2; +DROP PROCEDURE p1; + +--echo # removing the dump file +--error 0,1 +--remove_file $dump diff --git a/mysql-test/suite/compat/oracle/t/sp-package-security.test b/mysql-test/suite/compat/oracle/t/sp-package-security.test new file mode 100644 index 00000000000..e8c852ef452 --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package-security.test @@ -0,0 +1,331 @@ +--source include/not_embedded.inc + +SET sql_mode=ORACLE; + +CREATE DATABASE db1; +CREATE USER u1@localhost IDENTIFIED BY ''; +GRANT SELECT ON db1.* TO u1@localhost; + +connect (conn1,localhost,u1,,db1); +SELECT CURRENT_USER; +SET sql_mode=ORACLE; + +--echo # +--echo # User u1 cannot drop PROCEDURE, PACKAGE, PACKAGE BODY by default +--echo # + +--error ER_PROCACCESS_DENIED_ERROR +DROP PROCEDURE p1; +--error ER_PROCACCESS_DENIED_ERROR +DROP PACKAGE pkg1; +--error ER_PROCACCESS_DENIED_ERROR +DROP PACKAGE BODY pkg1; + +--echo # +--echo # User u1 cannot create PROCEDURE, PACKAGE, PACKAGE BODY by default +--echo # + +DELIMITER $$; +--error ER_DBACCESS_DENIED_ERROR +CREATE PROCEDURE p1 AS +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_DBACCESS_DENIED_ERROR +CREATE PACKAGE pkg1 AS + PROCEDURE p1; +END; +$$ +DELIMITER ;$$ + +# TODO: this should probably return ER_DBACCESS_DENIED_ERROR +DELIMITER $$; +--error ER_SP_DOES_NOT_EXIST +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS BEGIN NULL; END; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Now create a PACKAGE by root +--echo # + +connection default; +USE db1; + +DELIMITER $$; +CREATE PROCEDURE p1root AS +BEGIN + SELECT 1; +END; +$$ +DELIMITER ;$$ + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + PROCEDURE p1; + FUNCTION f1 RETURN TEXT; +END; +$$ +DELIMITER ;$$ +SHOW CREATE PACKAGE pkg1; + +--echo # +--echo # u1 cannot SHOW yet: +--echo # - the standalone procedure earlier created by root +--echo # - the package specifications earlier create by root +--echo # + +connection conn1; +--error ER_SP_DOES_NOT_EXIST +SHOW CREATE PROCEDURE p1root; +--error ER_SP_DOES_NOT_EXIST +SHOW CREATE PACKAGE pkg1; + + +--echo # +--echo # User u1 still cannot create a PACKAGE BODY +--echo # + +connection conn1; +DELIMITER $$; +--error ER_DBACCESS_DENIED_ERROR +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS BEGIN NULL; END; + FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # Now grant EXECUTE: +--echo # - on the standalone procedure earlier created by root +--echo # - on the package specification earlier created by root +--echo # +connection default; +GRANT EXECUTE ON PROCEDURE db1.p1root TO u1@localhost; +GRANT EXECUTE ON PACKAGE db1.pkg1 TO u1@localhost; + +--echo # +--echo # Now u1 can do SHOW for: +--echo # - the standalone procedure earlier created by root +--echo # - the package specification earlier created by root +--echo # + +disconnect conn1; +connect (conn1,localhost,u1,,db1); +SET sql_mode=ORACLE; +SHOW CREATE PROCEDURE db1.p1root; +SHOW CREATE PACKAGE db1.pkg1; + + +--echo # +--echo # Now revoke EXECUTE and grant CREATE ROUTINE instead +--echo # + +connection default; +REVOKE EXECUTE ON PROCEDURE db1.p1root FROM u1@localhost; +REVOKE EXECUTE ON PACKAGE db1.pkg1 FROM u1@localhost; +GRANT CREATE ROUTINE ON db1.* TO u1@localhost; + +--echo # +--echo # Reconnect u1 to make new grants have effect +--echo # + +disconnect conn1; +connect (conn1,localhost,u1,,db1); +SET sql_mode=ORACLE; + +--echo # +--echo # Now u1 can SHOW: +--echo # - standalone routines earlier created by root +--echo # - package specifications earlier created by root +--echo # +SHOW CREATE PROCEDURE p1root; +SHOW CREATE PACKAGE pkg1; + +--echo # +--echo # Now u1 can CREATE, DROP and EXECUTE its own standalone procedures +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 AS +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ +SHOW GRANTS; +CALL p1; +DROP PROCEDURE p1; +SHOW GRANTS; + +--echo # +--echo # Now u1 can also CREATE, DROP its own package specifications +--echo # + +DELIMITER $$; +CREATE PACKAGE pkg2 AS + PROCEDURE p1; + FUNCTION f1 RETURN TEXT; +END; +$$ +DELIMITER ;$$ +SHOW CREATE PACKAGE pkg2; +SHOW GRANTS; +DROP PACKAGE pkg2; +SHOW GRANTS; + + +--echo # +--echo # Now u1 can also CREATE, DROP package bodies and EXECUTE package body routines +--echo # + +DELIMITER $$; +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; + FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END; +END; +$$ +DELIMITER ;$$ +SHOW CREATE PACKAGE pkg1; +SHOW CREATE PACKAGE BODY pkg1; +SHOW GRANTS; +CALL pkg1.p1; +SELECT pkg1.f1(); +DROP PACKAGE BODY pkg1; +SHOW GRANTS; + +--echo # +--echo # Now create a PACKAGE BODY by root. +--echo # u1 does not have EXECUTE access by default. +--echo # + +connection default; +DELIMITER $$; +CREATE PACKAGE BODY pkg1 AS + PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; + FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END; +END; +$$ +DELIMITER ;$$ + +connection conn1; +SHOW CREATE PACKAGE pkg1; +SHOW CREATE PACKAGE BODY pkg1; +--error ER_PROCACCESS_DENIED_ERROR +CALL pkg1.p1; +--error ER_PROCACCESS_DENIED_ERROR +SELECT pkg1.f1(); + +--echo # +--echo # Now grant EXECUTE to u1 on the PACKAGE BODY created by root +--echo # + +connection default; +GRANT EXECUTE ON PACKAGE BODY db1.pkg1 TO u1@localhost; +disconnect conn1; +connect (conn1,localhost,u1,,db1); +SELECT CURRENT_USER; +SET sql_mode=ORACLE; +SHOW GRANTS; +CALL pkg1.p1; +SELECT pkg1.f1(); + +connection default; +DROP PACKAGE BODY pkg1; + + +--echo # +--echo # u1 still cannot DROP the package specification earlier created by root. +--echo # + +connection conn1; +--error ER_PROCACCESS_DENIED_ERROR +DROP PACKAGE pkg1; + +--echo # +--echo # Grant ALTER ROUTINE to u1 +--echo # + +connection default; +GRANT ALTER ROUTINE ON db1.* TO u1@localhost; + +--echo # +--echo # Now u1 can DROP: +--echo # - the standalone procedure earlier created by root +--echo # - the package specification earlier created by root +--echo # + +disconnect conn1; +connect (conn1,localhost,u1,,db1); +SET sql_mode=ORACLE; +DROP PACKAGE pkg1; +DROP PROCEDURE p1root; + +disconnect conn1; +connection default; + +DROP USER u1@localhost; +DROP DATABASE db1; +USE test; + + +--echo # +--echo # Creator=root, definer=xxx +--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 + SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; + END; +BEGIN + SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +DELIMITER ;$$ +--error ER_PROCACCESS_DENIED_ERROR +CALL p1.p1; +GRANT EXECUTE ON PACKAGE BODY test.p1 TO xxx@localhost; +CALL p1.p1; +DROP PACKAGE p1; +DROP USER xxx@localhost; + + +--echo # +--echo # Creator=root, definer=xxx, SQL SECURITY INVOKER +--echo # + +CREATE USER xxx@localhost; +DELIMITER $$; +CREATE DEFINER=xxx@localhost PACKAGE p1 AS + PROCEDURE p1; +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS + PROCEDURE p1 AS + BEGIN + SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; + END; +BEGIN + SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +DELIMITER ;$$ +CALL p1.p1; +DROP PACKAGE p1; +DROP USER xxx@localhost; 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 00000000000..e61dd37467c --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp-package.test @@ -0,0 +1,2626 @@ + +SET sql_mode=ORACLE; + + +--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 # + +--error ER_UNSUPPORTED_PS +PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2'; + +DELIMITER $$; +CREATE PACKAGE test2 AS + FUNCTION f1 RETURN INT; +END; +$$ +DELIMITER ;$$ +--error ER_UNSUPPORTED_PS +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 ;$$ + +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(); +--error ER_SP_PROC_TABLE_CORRUPT +SELECT test2.f1(); +--error ER_SP_PROC_TABLE_CORRUPT +SELECT test2.f1(); + +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(); +--error ER_SP_PROC_TABLE_CORRUPT +CALL test2.p1(); +--error ER_SP_PROC_TABLE_CORRUPT +CALL test2.p1(); + +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; diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test index 693569d184c..b88271ad3e2 100644 --- a/mysql-test/suite/compat/oracle/t/sp.test +++ b/mysql-test/suite/compat/oracle/t/sp.test @@ -170,6 +170,11 @@ END; DELIMITER ;/ DROP PROCEDURE p1; +--echo # Keywords that are OK for table names, but not for SP variables +CREATE TABLE function (function int); +INSERT INTO function SET function=10; +SELECT function.function FROM function; +DROP TABLE function; --echo # Testing that (some) keyword_sp are allowed in Oracle-style assignments DELIMITER /; diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index bf6511e80bf..d76efef3e08 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -308,7 +308,7 @@ def information_schema ROUTINES ROUTINE_COMMENT 27 '' NO longtext 4294967295 429 def information_schema ROUTINES ROUTINE_DEFINITION 16 NULL YES longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select NEVER NULL def information_schema ROUTINES ROUTINE_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema ROUTINES ROUTINE_SCHEMA 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL -def information_schema ROUTINES ROUTINE_TYPE 5 '' NO varchar 9 27 NULL NULL NULL utf8 utf8_general_ci varchar(9) select NEVER NULL +def information_schema ROUTINES ROUTINE_TYPE 5 '' NO varchar 13 39 NULL NULL NULL utf8 utf8_general_ci varchar(13) select NEVER NULL def information_schema ROUTINES SECURITY_TYPE 23 '' NO varchar 7 21 NULL NULL NULL utf8 utf8_general_ci varchar(7) select NEVER NULL def information_schema ROUTINES SPECIFIC_NAME 1 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL def information_schema ROUTINES SQL_DATA_ACCESS 21 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select NEVER NULL @@ -820,7 +820,7 @@ NULL information_schema PROCESSLIST TID bigint NULL NULL NULL NULL bigint(4) 3.0000 information_schema ROUTINES ROUTINE_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) 3.0000 information_schema ROUTINES ROUTINE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema ROUTINES ROUTINE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) -3.0000 information_schema ROUTINES ROUTINE_TYPE varchar 9 27 utf8 utf8_general_ci varchar(9) +3.0000 information_schema ROUTINES ROUTINE_TYPE varchar 13 39 utf8 utf8_general_ci varchar(13) 3.0000 information_schema ROUTINES DATA_TYPE varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema ROUTINES CHARACTER_MAXIMUM_LENGTH int NULL NULL NULL NULL int(21) NULL information_schema ROUTINES CHARACTER_OCTET_LENGTH int NULL NULL NULL NULL int(21) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index edf11d25f7e..b0d3c177580 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -308,7 +308,7 @@ def information_schema ROUTINES ROUTINE_COMMENT 27 '' NO longtext 4294967295 429 def information_schema ROUTINES ROUTINE_DEFINITION 16 NULL YES longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext NEVER NULL def information_schema ROUTINES ROUTINE_NAME 4 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema ROUTINES ROUTINE_SCHEMA 3 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL -def information_schema ROUTINES ROUTINE_TYPE 5 '' NO varchar 9 27 NULL NULL NULL utf8 utf8_general_ci varchar(9) NEVER NULL +def information_schema ROUTINES ROUTINE_TYPE 5 '' NO varchar 13 39 NULL NULL NULL utf8 utf8_general_ci varchar(13) NEVER NULL def information_schema ROUTINES SECURITY_TYPE 23 '' NO varchar 7 21 NULL NULL NULL utf8 utf8_general_ci varchar(7) NEVER NULL def information_schema ROUTINES SPECIFIC_NAME 1 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL def information_schema ROUTINES SQL_DATA_ACCESS 21 '' NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) NEVER NULL @@ -820,7 +820,7 @@ NULL information_schema PROCESSLIST TID bigint NULL NULL NULL NULL bigint(4) 3.0000 information_schema ROUTINES ROUTINE_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512) 3.0000 information_schema ROUTINES ROUTINE_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64) 3.0000 information_schema ROUTINES ROUTINE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64) -3.0000 information_schema ROUTINES ROUTINE_TYPE varchar 9 27 utf8 utf8_general_ci varchar(9) +3.0000 information_schema ROUTINES ROUTINE_TYPE varchar 13 39 utf8 utf8_general_ci varchar(13) 3.0000 information_schema ROUTINES DATA_TYPE varchar 64 192 utf8 utf8_general_ci varchar(64) NULL information_schema ROUTINES CHARACTER_MAXIMUM_LENGTH int NULL NULL NULL NULL int(21) NULL information_schema ROUTINES CHARACTER_OCTET_LENGTH int NULL NULL NULL NULL int(21) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index 74dff5a46ad..5d4e8fdf19d 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -154,13 +154,13 @@ def mysql proc security_type 8 'DEFINER' NO enum 7 21 NULL NULL NULL utf8 utf8_g def mysql proc specific_name 4 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) select,insert,update,references NEVER NULL def mysql proc sql_data_access 6 'CONTAINS_SQL' NO enum 17 51 NULL NULL NULL utf8 utf8_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') select,insert,update,references NEVER NULL def mysql proc sql_mode 15 '' NO set 539 1617 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') select,insert,update,references NEVER NULL -def mysql proc type 3 NULL NO enum 9 27 NULL NULL NULL utf8 utf8_general_ci enum('FUNCTION','PROCEDURE') PRI select,insert,update,references NEVER NULL +def mysql proc type 3 NULL NO enum 12 36 NULL NULL NULL utf8 utf8_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI select,insert,update,references NEVER NULL def mysql procs_priv Db 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI select,insert,update,references NEVER NULL def mysql procs_priv Grantor 6 '' NO char 141 423 NULL NULL NULL utf8 utf8_bin char(141) MUL select,insert,update,references NEVER NULL def mysql procs_priv Host 1 '' NO char 60 180 NULL NULL NULL utf8 utf8_bin char(60) PRI select,insert,update,references NEVER NULL def mysql procs_priv Proc_priv 7 '' NO set 27 81 NULL NULL NULL utf8 utf8_general_ci set('Execute','Alter Routine','Grant') select,insert,update,references NEVER NULL def mysql procs_priv Routine_name 4 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) PRI select,insert,update,references NEVER NULL -def mysql procs_priv Routine_type 5 NULL NO enum 9 27 NULL NULL NULL utf8 utf8_bin enum('FUNCTION','PROCEDURE') PRI select,insert,update,references NEVER NULL +def mysql procs_priv Routine_type 5 NULL NO enum 12 36 NULL NULL NULL utf8 utf8_bin enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI select,insert,update,references NEVER NULL def mysql procs_priv Timestamp 8 current_timestamp() NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update current_timestamp() select,insert,update,references NEVER NULL def mysql procs_priv User 3 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI select,insert,update,references NEVER NULL def mysql proxies_priv Grantor 6 '' NO char 141 423 NULL NULL NULL utf8 utf8_bin char(141) MUL select,insert,update,references NEVER NULL @@ -479,7 +479,7 @@ NULL mysql innodb_table_stats sum_of_other_index_sizes bigint NULL NULL NULL NUL 3.0000 mysql plugin dl varchar 128 384 utf8 utf8_general_ci varchar(128) 3.0000 mysql proc db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql proc name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql proc type enum 9 27 utf8 utf8_general_ci enum('FUNCTION','PROCEDURE') +3.0000 mysql proc type enum 12 36 utf8 utf8_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') 3.0000 mysql proc specific_name char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql proc language enum 3 9 utf8 utf8_general_ci enum('SQL') 3.0000 mysql proc sql_data_access enum 17 51 utf8 utf8_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') @@ -502,7 +502,7 @@ NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp 3.0000 mysql procs_priv Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql procs_priv User char 80 240 utf8 utf8_bin char(80) 3.0000 mysql procs_priv Routine_name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql procs_priv Routine_type enum 9 27 utf8 utf8_bin enum('FUNCTION','PROCEDURE') +3.0000 mysql procs_priv Routine_type enum 12 36 utf8 utf8_bin enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') 3.0000 mysql procs_priv Grantor char 141 423 utf8 utf8_bin char(141) 3.0000 mysql procs_priv Proc_priv set 27 81 utf8 utf8_general_ci set('Execute','Alter Routine','Grant') NULL mysql procs_priv Timestamp timestamp NULL NULL NULL NULL timestamp diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index 435a28728fe..9f17724b356 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -140,13 +140,13 @@ def mysql proc security_type 8 'DEFINER' NO enum 7 21 NULL NULL NULL utf8 utf8_g def mysql proc specific_name 4 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) NEVER NULL def mysql proc sql_data_access 6 'CONTAINS_SQL' NO enum 17 51 NULL NULL NULL utf8 utf8_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NEVER NULL def mysql proc sql_mode 15 '' NO set 539 1617 NULL NULL NULL utf8 utf8_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') NEVER NULL -def mysql proc type 3 NULL NO enum 9 27 NULL NULL NULL utf8 utf8_general_ci enum('FUNCTION','PROCEDURE') PRI NEVER NULL +def mysql proc type 3 NULL NO enum 12 36 NULL NULL NULL utf8 utf8_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI NEVER NULL def mysql procs_priv Db 2 '' NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI NEVER NULL def mysql procs_priv Grantor 6 '' NO char 141 423 NULL NULL NULL utf8 utf8_bin char(141) MUL NEVER NULL def mysql procs_priv Host 1 '' NO char 60 180 NULL NULL NULL utf8 utf8_bin char(60) PRI NEVER NULL def mysql procs_priv Proc_priv 7 '' NO set 27 81 NULL NULL NULL utf8 utf8_general_ci set('Execute','Alter Routine','Grant') NEVER NULL def mysql procs_priv Routine_name 4 '' NO char 64 192 NULL NULL NULL utf8 utf8_general_ci char(64) PRI NEVER NULL -def mysql procs_priv Routine_type 5 NULL NO enum 9 27 NULL NULL NULL utf8 utf8_bin enum('FUNCTION','PROCEDURE') PRI NEVER NULL +def mysql procs_priv Routine_type 5 NULL NO enum 12 36 NULL NULL NULL utf8 utf8_bin enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI NEVER NULL def mysql procs_priv Timestamp 8 current_timestamp() NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update current_timestamp() NEVER NULL def mysql procs_priv User 3 '' NO char 80 240 NULL NULL NULL utf8 utf8_bin char(80) PRI NEVER NULL def mysql proxies_priv Grantor 6 '' NO char 141 423 NULL NULL NULL utf8 utf8_bin char(141) MUL NEVER NULL @@ -462,7 +462,7 @@ NULL mysql index_stats avg_frequency decimal NULL NULL NULL NULL decimal(12,4) 3.0000 mysql plugin dl varchar 128 384 utf8 utf8_general_ci varchar(128) 3.0000 mysql proc db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql proc name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql proc type enum 9 27 utf8 utf8_general_ci enum('FUNCTION','PROCEDURE') +3.0000 mysql proc type enum 12 36 utf8 utf8_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') 3.0000 mysql proc specific_name char 64 192 utf8 utf8_general_ci char(64) 3.0000 mysql proc language enum 3 9 utf8 utf8_general_ci enum('SQL') 3.0000 mysql proc sql_data_access enum 17 51 utf8 utf8_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') @@ -485,7 +485,7 @@ NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp 3.0000 mysql procs_priv Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql procs_priv User char 80 240 utf8 utf8_bin char(80) 3.0000 mysql procs_priv Routine_name char 64 192 utf8 utf8_general_ci char(64) -3.0000 mysql procs_priv Routine_type enum 9 27 utf8 utf8_bin enum('FUNCTION','PROCEDURE') +3.0000 mysql procs_priv Routine_type enum 12 36 utf8 utf8_bin enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') 3.0000 mysql procs_priv Grantor char 141 423 utf8 utf8_bin char(141) 3.0000 mysql procs_priv Proc_priv set 27 81 utf8 utf8_general_ci set('Execute','Alter Routine','Grant') NULL mysql procs_priv Timestamp timestamp NULL NULL NULL NULL timestamp diff --git a/mysql-test/suite/funcs_1/r/is_routines.result b/mysql-test/suite/funcs_1/r/is_routines.result index aadf89d89f5..5f8e965de84 100644 --- a/mysql-test/suite/funcs_1/r/is_routines.result +++ b/mysql-test/suite/funcs_1/r/is_routines.result @@ -33,7 +33,7 @@ SPECIFIC_NAME varchar(64) NO ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO -ROUTINE_TYPE varchar(9) NO +ROUTINE_TYPE varchar(13) NO DATA_TYPE varchar(64) NO CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL @@ -67,7 +67,7 @@ ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '', `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '', + `ROUTINE_TYPE` varchar(13) NOT NULL DEFAULT '', `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, @@ -101,7 +101,7 @@ SPECIFIC_NAME varchar(64) NO ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO -ROUTINE_TYPE varchar(9) NO +ROUTINE_TYPE varchar(13) NO DATA_TYPE varchar(64) NO CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL diff --git a/mysql-test/suite/funcs_1/r/is_routines_embedded.result b/mysql-test/suite/funcs_1/r/is_routines_embedded.result index 8879efb21a5..a2acd83af49 100644 --- a/mysql-test/suite/funcs_1/r/is_routines_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_routines_embedded.result @@ -33,7 +33,7 @@ SPECIFIC_NAME varchar(64) NO ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO -ROUTINE_TYPE varchar(9) NO +ROUTINE_TYPE varchar(13) NO DATA_TYPE varchar(64) NO CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL @@ -67,7 +67,7 @@ ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '', `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '', + `ROUTINE_TYPE` varchar(13) NOT NULL DEFAULT '', `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, @@ -101,7 +101,7 @@ SPECIFIC_NAME varchar(64) NO ROUTINE_CATALOG varchar(512) NO ROUTINE_SCHEMA varchar(64) NO ROUTINE_NAME varchar(64) NO -ROUTINE_TYPE varchar(9) NO +ROUTINE_TYPE varchar(13) NO DATA_TYPE varchar(64) NO CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL diff --git a/mysql-test/suite/roles/acl_statistics.result b/mysql-test/suite/roles/acl_statistics.result index bf74cbf2e85..46e48b86936 100644 --- a/mysql-test/suite/roles/acl_statistics.result +++ b/mysql-test/suite/roles/acl_statistics.result @@ -4,6 +4,8 @@ Acl_column_grants 0 Acl_database_grants 2 Acl_function_grants 0 Acl_procedure_grants 0 +Acl_package_spec_grants 0 +Acl_package_body_grants 0 Acl_proxy_users 2 Acl_role_grants 0 Acl_roles 0 @@ -67,6 +69,8 @@ Acl_column_grants 2 Acl_database_grants 4 Acl_function_grants 3 Acl_procedure_grants 2 +Acl_package_spec_grants 0 +Acl_package_body_grants 0 Acl_proxy_users 3 Acl_role_grants 4 Acl_roles 2 diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 09ac37198f0..c1dcc26e262 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -3024,9 +3024,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 191 +GLOBAL_VALUE 200 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 191 +DEFAULT_VALUE 200 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 63f50e34ed3..e991e0c72b4 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3234,9 +3234,9 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES SESSION_VALUE NULL -GLOBAL_VALUE 191 +GLOBAL_VALUE 200 GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE 191 +DEFAULT_VALUE 200 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT Maximum number of statement instruments. diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index decf22d4118..d44ded30218 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -90,7 +90,7 @@ ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exact alter table t1 add partition ( partition p1 history); Warnings: -Warning 4113 Maybe missing parameters: no rotation condition for multiple HISTORY partitions. +Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions. show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -244,11 +244,11 @@ x 6 insert into t1 values (7), (8); Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions ### warn about full partition delete from t1; Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p1) order by x; x 4 @@ -303,7 +303,7 @@ x delete from t1 where x < 3; delete from t1; Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p0) order by x; x 1 @@ -323,7 +323,7 @@ insert into t1 values (1); update t1 set x= 2; update t1 set x= 3; Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions select * from t1 partition (p0); x 1 @@ -498,10 +498,10 @@ insert t1 values (1); delete from t1; insert t1 values (2); Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions delete from t1; Warnings: -Warning 4112 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions +Warning 4114 Versioned table `test`.`t1`: partition `p1` is full, add more HISTORY partitions alter table t1 add partition (partition p0 history, partition p2 history); select subpartition_name,table_rows from information_schema.partitions where table_schema='test' and table_name='t1'; subpartition_name table_rows |