summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/suite/compat/oracle/r/sp-package.result210
-rw-r--r--mysql-test/suite/compat/oracle/t/sp-package.test213
-rw-r--r--sql/item.cc20
-rw-r--r--sql/sql_class.h13
-rw-r--r--sql/sql_lex.cc50
-rw-r--r--sql/sql_lex.h5
-rw-r--r--sql/sql_yacc.yy5
-rw-r--r--sql/sql_yacc_ora.yy5
8 files changed, 519 insertions, 2 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result
index 598c766c808..a8be1a8eb16 100644
--- a/mysql-test/suite/compat/oracle/r/sp-package.result
+++ b/mysql-test/suite/compat/oracle/r/sp-package.result
@@ -2925,3 +2925,213 @@ END $$
CALL xyz.xyz123(17,18,@R);
DROP PACKAGE xyz;
DROP TABLE t1;
+#
+# MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
+#
+SELECT `db `.pkg.func();
+ERROR 42000: Incorrect database name 'db '
+SELECT db.`pkg `.func();
+ERROR 42000: Incorrect routine name 'pkg '
+SELECT db.pkg.`func `();
+ERROR 42000: Incorrect routine name 'func '
+CREATE DATABASE db1;
+USE db1;
+CREATE PACKAGE pkg1 AS
+FUNCTION f1 RETURN TEXT;
+FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
+FUNCTION f2_pkg1_f1 RETURN TEXT;
+FUNCTION f2_f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1
+AS
+FUNCTION f1 RETURN TEXT IS
+BEGIN
+RETURN 'This is db1.pkg1.f1';
+END;
+FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
+BEGIN
+RETURN db1.pkg1.f1();
+END;
+FUNCTION f2_pkg1_f1 RETURN TEXT IS
+BEGIN
+RETURN pkg1.f1();
+END;
+FUNCTION f2_f1 RETURN TEXT IS
+BEGIN
+RETURN f1();
+END;
+END;
+$$
+USE db1;
+SELECT pkg1.f2_db1_pkg1_f1();
+pkg1.f2_db1_pkg1_f1()
+This is db1.pkg1.f1
+SELECT pkg1.f2_pkg1_f1();
+pkg1.f2_pkg1_f1()
+This is db1.pkg1.f1
+SELECT pkg1.f2_f1();
+pkg1.f2_f1()
+This is db1.pkg1.f1
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+db1.pkg1.f2_db1_pkg1_f1()
+This is db1.pkg1.f1
+SELECT db1.pkg1.f2_pkg1_f1();
+db1.pkg1.f2_pkg1_f1()
+This is db1.pkg1.f1
+SELECT db1.pkg1.f2_f1();
+db1.pkg1.f2_f1()
+This is db1.pkg1.f1
+USE test;
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+db1.pkg1.f2_db1_pkg1_f1()
+This is db1.pkg1.f1
+SELECT db1.pkg1.f2_pkg1_f1();
+db1.pkg1.f2_pkg1_f1()
+This is db1.pkg1.f1
+SELECT db1.pkg1.f2_f1();
+db1.pkg1.f2_f1()
+This is db1.pkg1.f1
+DROP DATABASE db1;
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+CREATE PACKAGE db1.pkg1 AS
+FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db1.pkg1 AS
+FUNCTION f1 RETURN TEXT AS
+BEGIN
+RETURN 'This is db1.pkg1.f1';
+END;
+END;
+$$
+CREATE PACKAGE db2.pkg1 AS
+FUNCTION f1 RETURN TEXT;
+FUNCTION var1 RETURN TEXT;
+FUNCTION var2 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db2.pkg1 AS
+m_var1 TEXT;
+m_var2 TEXT;
+FUNCTION f1 RETURN TEXT AS
+BEGIN
+RETURN 'This is db2.pkg1.f1';
+END;
+FUNCTION var1 RETURN TEXT AS
+BEGIN
+RETURN m_var1;
+END;
+FUNCTION var2 RETURN TEXT AS
+BEGIN
+RETURN m_var2;
+END;
+BEGIN
+m_var1:= db1.pkg1.f1();
+m_var2:= db2.pkg1.f1();
+END;
+$$
+SELECT db2.pkg1.var1(), db2.pkg1.var2();
+db2.pkg1.var1() db2.pkg1.var2()
+This is db1.pkg1.f1 This is db2.pkg1.f1
+DROP DATABASE db1;
+DROP DATABASE db2;
+CREATE PACKAGE pkg1 AS
+FUNCTION f1(a TEXT) RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+FUNCTION f1(a TEXT) RETURN TEXT AS
+BEGIN
+RETURN a;
+END;
+END;
+$$
+SELECT test.pkg1.f1('xxx');
+test.pkg1.f1('xxx')
+xxx
+SELECT test.pkg1.f1('xxx' AS a);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS a)' at line 1
+DROP PACKAGE pkg1;
+#
+# MDEV-19328 sql_mode=ORACLE: Package function in VIEW
+#
+SET sql_mode=ORACLE;
+CREATE PACKAGE test1 AS
+FUNCTION f_test RETURN number;
+END test1;
+$$
+CREATE PACKAGE BODY test1
+AS
+FUNCTION f_test RETURN NUMBER IS
+BEGIN
+RETURN 1;
+END;
+END test1;
+$$
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+DROP VIEW v_test;
+SET sql_mode=DEFAULT;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+ERROR 42000: FUNCTION test1.f_test does not exist
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+DROP VIEW v_test;
+SET sql_mode=DEFAULT;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+SET sql_mode=ORACLE;
+SELECT * FROM v_test;
+c1
+1
+SHOW CREATE VIEW v_test;
+View v_test
+Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"()
+character_set_client latin1
+collation_connection latin1_swedish_ci
+DROP VIEW v_test;
+SET sql_mode=ORACLE;
+DROP PACKAGE test1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test
index 5eac3987982..8fcf72d3145 100644
--- a/mysql-test/suite/compat/oracle/t/sp-package.test
+++ b/mysql-test/suite/compat/oracle/t/sp-package.test
@@ -2682,3 +2682,216 @@ DELIMITER ;$$
CALL xyz.xyz123(17,18,@R);
DROP PACKAGE xyz;
DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
+--echo #
+
+--error ER_WRONG_DB_NAME
+SELECT `db `.pkg.func();
+--error ER_SP_WRONG_NAME
+SELECT db.`pkg `.func();
+--error ER_SP_WRONG_NAME
+SELECT db.pkg.`func `();
+
+
+CREATE DATABASE db1;
+USE db1;
+
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+ FUNCTION f1 RETURN TEXT;
+ FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
+ FUNCTION f2_pkg1_f1 RETURN TEXT;
+ FUNCTION f2_f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1
+AS
+ FUNCTION f1 RETURN TEXT IS
+ BEGIN
+ RETURN 'This is db1.pkg1.f1';
+ END;
+ FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
+ BEGIN
+ RETURN db1.pkg1.f1();
+ END;
+ FUNCTION f2_pkg1_f1 RETURN TEXT IS
+ BEGIN
+ RETURN pkg1.f1();
+ END;
+ FUNCTION f2_f1 RETURN TEXT IS
+ BEGIN
+ RETURN f1();
+ END;
+END;
+$$
+DELIMITER ;$$
+
+USE db1;
+SELECT pkg1.f2_db1_pkg1_f1();
+SELECT pkg1.f2_pkg1_f1();
+SELECT pkg1.f2_f1();
+
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+SELECT db1.pkg1.f2_pkg1_f1();
+SELECT db1.pkg1.f2_f1();
+
+USE test;
+SELECT db1.pkg1.f2_db1_pkg1_f1();
+SELECT db1.pkg1.f2_pkg1_f1();
+SELECT db1.pkg1.f2_f1();
+
+DROP DATABASE db1;
+
+
+#
+# Testing db.pkg.func() in the package initialization section
+#
+
+CREATE DATABASE db1;
+CREATE DATABASE db2;
+
+DELIMITER $$;
+CREATE PACKAGE db1.pkg1 AS
+ FUNCTION f1 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db1.pkg1 AS
+ FUNCTION f1 RETURN TEXT AS
+ BEGIN
+ RETURN 'This is db1.pkg1.f1';
+ END;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+CREATE PACKAGE db2.pkg1 AS
+ FUNCTION f1 RETURN TEXT;
+ FUNCTION var1 RETURN TEXT;
+ FUNCTION var2 RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY db2.pkg1 AS
+ m_var1 TEXT;
+ m_var2 TEXT;
+ FUNCTION f1 RETURN TEXT AS
+ BEGIN
+ RETURN 'This is db2.pkg1.f1';
+ END;
+ FUNCTION var1 RETURN TEXT AS
+ BEGIN
+ RETURN m_var1;
+ END;
+ FUNCTION var2 RETURN TEXT AS
+ BEGIN
+ RETURN m_var2;
+ END;
+BEGIN
+ m_var1:= db1.pkg1.f1();
+ m_var2:= db2.pkg1.f1();
+END;
+$$
+DELIMITER ;$$
+
+SELECT db2.pkg1.var1(), db2.pkg1.var2();
+
+DROP DATABASE db1;
+DROP DATABASE db2;
+
+#
+# Make sure fully qualified package function call does not support AS syntax:
+# SELECT db.pkg.func(10 AS a);
+#
+
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+ FUNCTION f1(a TEXT) RETURN TEXT;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+ FUNCTION f1(a TEXT) RETURN TEXT AS
+ BEGIN
+ RETURN a;
+ END;
+END;
+$$
+DELIMITER ;$$
+SELECT test.pkg1.f1('xxx');
+--error ER_PARSE_ERROR
+SELECT test.pkg1.f1('xxx' AS a);
+DROP PACKAGE pkg1;
+
+
+--echo #
+--echo # MDEV-19328 sql_mode=ORACLE: Package function in VIEW
+--echo #
+
+SET sql_mode=ORACLE;
+DELIMITER $$;
+CREATE PACKAGE test1 AS
+ FUNCTION f_test RETURN number;
+END test1;
+$$
+CREATE PACKAGE BODY test1
+AS
+ FUNCTION f_test RETURN NUMBER IS
+ BEGIN
+ RETURN 1;
+ END;
+END test1;
+$$
+DELIMITER ;$$
+
+
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+
+SET sql_mode=DEFAULT;
+--error ER_SP_DOES_NOT_EXIST
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
+
+
+SET sql_mode=ORACLE;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=DEFAULT;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+
+SET sql_mode=DEFAULT;
+CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+SET sql_mode=ORACLE;
+SELECT * FROM v_test;
+--vertical_results
+SHOW CREATE VIEW v_test;
+--horizontal_results
+DROP VIEW v_test;
+
+SET sql_mode=ORACLE;
+DROP PACKAGE test1;
diff --git a/sql/item.cc b/sql/item.cc
index 2fe5411f972..f06055f0a08 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2898,9 +2898,11 @@ Item_sp::func_name(THD *thd) const
/* Calculate length to avoid reallocation of string for sure */
size_t len= (((m_name->m_explicit_name ? m_name->m_db.length : 0) +
m_name->m_name.length)*2 + //characters*quoting
- 2 + // ` and `
+ 2 + // quotes for the function name
+ 2 + // quotes for the package name
(m_name->m_explicit_name ?
3 : 0) + // '`', '`' and '.' for the db
+ 1 + // '.' between package and function
1 + // end of string
ALIGN_SIZE(1)); // to avoid String reallocation
String qname((char *)alloc_root(thd->mem_root, len), len,
@@ -2912,7 +2914,21 @@ Item_sp::func_name(THD *thd) const
append_identifier(thd, &qname, &m_name->m_db);
qname.append('.');
}
- append_identifier(thd, &qname, &m_name->m_name);
+ if (m_sp && m_sp->m_handler == &sp_handler_package_function)
+ {
+ /*
+ In case of a package function split `pkg.func` and print
+ quoted `pkg` and `func` separately, so the entire result looks like:
+ `db`.`pkg`.`func`
+ */
+ Database_qualified_name tmp= Database_qualified_name::split(m_name->m_name);
+ DBUG_ASSERT(tmp.m_db.length);
+ append_identifier(thd, &qname, &tmp.m_db);
+ qname.append('.');
+ append_identifier(thd, &qname, &tmp.m_name);
+ }
+ else
+ append_identifier(thd, &qname, &m_name->m_name);
return qname.c_ptr_safe();
}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 311b47aea61..637e16c991b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -6680,6 +6680,19 @@ public:
}
void copy(MEM_ROOT *mem_root, const LEX_CSTRING &db,
const LEX_CSTRING &name);
+
+ static Database_qualified_name split(const LEX_CSTRING &txt)
+ {
+ DBUG_ASSERT(txt.str[txt.length] == '\0'); // Expect 0-terminated input
+ const char *dot= strchr(txt.str, '.');
+ if (!dot)
+ return Database_qualified_name(NULL, 0, txt.str, txt.length);
+ size_t dblen= dot - txt.str;
+ Lex_cstring db(txt.str, dblen);
+ Lex_cstring name(txt.str + dblen + 1, txt.length - dblen - 1);
+ return Database_qualified_name(db, name);
+ }
+
// Export db and name as a qualified name string: 'db.name'
size_t make_qname(char *dst, size_t dstlen) const
{
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d51b9bd5a26..cffc0eb25dd 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -8148,6 +8148,56 @@ Item *LEX::make_item_func_call_generic(THD *thd, Lex_ident_cli_st *cdb,
}
+/*
+ Create a 3-step qualified function call.
+ Currently it's possible for package routines only, e.g.:
+ SELECT db.pkg.func();
+*/
+Item *LEX::make_item_func_call_generic(THD *thd,
+ Lex_ident_cli_st *cdb,
+ Lex_ident_cli_st *cpkg,
+ Lex_ident_cli_st *cfunc,
+ List<Item> *args)
+{
+ static Lex_cstring dot(".", 1);
+ Lex_ident_sys db(thd, cdb), pkg(thd, cpkg), func(thd, cfunc);
+ Database_qualified_name q_db_pkg(db, pkg);
+ Database_qualified_name q_pkg_func(pkg, func);
+ sp_name *qname;
+
+ if (db.is_null() || pkg.is_null() || func.is_null())
+ return NULL; // EOM
+
+ if (check_db_name((LEX_STRING*) static_cast<LEX_CSTRING*>(&db)))
+ {
+ my_error(ER_WRONG_DB_NAME, MYF(0), db.str);
+ return NULL;
+ }
+ if (check_routine_name(&pkg) ||
+ check_routine_name(&func))
+ return NULL;
+
+ // Concat `pkg` and `name` to `pkg.name`
+ LEX_CSTRING pkg_dot_func;
+ if (q_pkg_func.make_qname(thd->mem_root, &pkg_dot_func) ||
+ check_ident_length(&pkg_dot_func) ||
+ !(qname= new (thd->mem_root) sp_name(&db, &pkg_dot_func, true)))
+ return NULL;
+
+ sp_handler_package_function.add_used_routine(thd->lex, thd, qname);
+ sp_handler_package_body.add_used_routine(thd->lex, thd, &q_db_pkg);
+
+ thd->lex->safe_to_cache_query= 0;
+
+ if (args && args->elements > 0)
+ return new (thd->mem_root) Item_func_sp(thd, thd->lex->current_context(),
+ qname, &sp_handler_package_function,
+ *args);
+ return new (thd->mem_root) Item_func_sp(thd, thd->lex->current_context(),
+ qname, &sp_handler_package_function);
+}
+
+
Item *LEX::create_item_qualified_asterisk(THD *thd,
const Lex_ident_sys_st *name)
{
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index e1f34afa350..a63ec7c9153 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3697,6 +3697,11 @@ public:
Item *make_item_func_substr(THD *thd, Item *a, Item *b);
Item *make_item_func_call_generic(THD *thd, Lex_ident_cli_st *db,
Lex_ident_cli_st *name, List<Item> *args);
+ Item *make_item_func_call_generic(THD *thd,
+ Lex_ident_cli_st *db,
+ Lex_ident_cli_st *pkg,
+ Lex_ident_cli_st *name,
+ List<Item> *args);
my_var *create_outvar(THD *thd, const LEX_CSTRING *name);
/*
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e0e4308ef5c..2852d2efc0c 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11210,6 +11210,11 @@ function_call_generic:
if (unlikely(!($$= Lex->make_item_func_call_generic(thd, &$1, &$3, $5))))
MYSQL_YYABORT;
}
+ | ident_cli '.' ident_cli '.' ident_cli '(' opt_expr_list ')'
+ {
+ if (unlikely(!($$= Lex->make_item_func_call_generic(thd, &$1, &$3, &$5, $7))))
+ MYSQL_YYABORT;
+ }
;
fulltext_options:
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index b45cee01502..8b96937c955 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -11149,6 +11149,11 @@ function_call_generic:
if (unlikely(!($$= Lex->make_item_func_call_generic(thd, &$1, &$3, $5))))
MYSQL_YYABORT;
}
+ | ident_cli '.' ident_cli '.' ident_cli '(' opt_expr_list ')'
+ {
+ if (unlikely(!($$= Lex->make_item_func_call_generic(thd, &$1, &$3, &$5, $7))))
+ MYSQL_YYABORT;
+ }
;
fulltext_options:
View Lorry Controller Status