Details
-
Technical task
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
None
Description
When running with the default sql_mode, MariaDB does not support qualifiers in stored function parameters. Qualifiers are only supported in stored procedure parameters.
When running with sql_mode=ORACLE, MariaDB should support IN, OUT, INOUT parameter qualifiers in CREATE FUNCTION, for Oracle compatibility.
Attachments
Issue Links
- blocks
-
MDEV-34323 Oracle compatibility project 3
-
- Open
-
- causes
-
MDEV-27331 FUNCTION declaration fails with ERROR 1064 (error in SQL) when using oracle sql_mode and OUT parameter
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- is part of
-
MDEV-27373 Q1 2022 release merge
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Open
-
Activity
PostgreSQL and Db2 support this feature as well. So I wouldn't consider it Oracle-specific.
Uploaded my own unit test cases (comparing Oracle vs MariaDB):
1. TestCase.xlsx ==> Test case summary
2. TestCases_Oracle.zip ==> Test case for Oracle
2. TestCases_MariaDB.zip ==> Test case for MariaDB
Currently working on the review points mentioned in the PR.
Please try to create test cases in MTR for this feature.
bar [suggested, mysql-test/main (for SQL/PSM) and into suite/compat/oracle/t. A reference document is https://mariadb.com/kb/en/mysql-test-overview/
As you can imagine a collection of tests outside the framework isn't easily consumable.
I pushed a commit to the end of your pull request (and is on your branch) that includes a sample test case.
This was tested with:
$ mysql-test/mtr mysql-test/suite/compat/oracle/t/func_inout.test
|
Logging: /home/dan/repos/mariadb-server-10.7/mysql-test/mariadb-test-run.pl mysql-test/suite/compat/oracle/t/func_inout.test
|
vardir: /home/dan/repos/build-mariadb-server-10.7/mysql-test/var
|
Checking leftover processes...
|
Removing old var directory...
|
Creating var directory '/home/dan/repos/build-mariadb-server-10.7/mysql-test/var'...
|
Checking supported features...
|
MariaDB Version 10.7.1-MariaDB
|
- SSL connections supported
|
- binaries built with wsrep patch
|
Collecting tests...
|
Installing system database...
|
|
|
==============================================================================
|
|
|
TEST RESULT TIME (ms) or COMMENT
|
--------------------------------------------------------------------------
|
|
|
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
|
SET sql_mode=ORACLE;
|
CREATE PACKAGE pkg2
|
AS
|
FUNCTION add_func4 (a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT;
|
END|
|
SHOW CREATE PACKAGE pkg2|
|
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
|
Create Package CREATE DEFINER="root"@"localhost" PACKAGE "pkg2" AS
|
FUNCTION add_func4 (a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT;
|
END
|
character_set_client latin1
|
collation_connection latin1_swedish_ci
|
Database Collation latin1_swedish_ci
|
CREATE PACKAGE BODY pkg2
|
AS
|
FUNCTION add_func4(a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT
|
AS
|
BEGIN
|
c := 100;
|
d := d + 1;
|
RETURN a + b;
|
END;
|
END|
|
SHOW CREATE PACKAGE BODY pkg2;
|
Package body 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
|
Create Package Body CREATE DEFINER="root"@"localhost" PACKAGE BODY "pkg2" AS
|
FUNCTION add_func4(a IN INT, b IN INT, c OUT INT, d INOUT INT) RETURN INT
|
AS
|
BEGIN
|
c := 100;
|
d := d + 1;
|
RETURN a + b;
|
END;
|
END
|
character_set_client latin1
|
collation_connection latin1_swedish_ci
|
Database Collation latin1_swedish_ci
|
DROP PACKAGE pkg2;
|
compat/oracle.func_inout [ pass ] 2
|
When I was happy the output matches the functionality:
$ mysql-test/mtr --record mysql-test/suite/compat/oracle/t/func_inout.test
|
Thank you for sample test case, that was helpful.
How do I record negative test case? For example, I want to include cases which returns error message like below.
$ mysql-test/mtr --record mysql-test/suite/compat/oracle/t/func_inout.test
|
...
|
...
|
CURRENT_TEST: compat/oracle.func_inout
|
mysqltest: At line 157: query 'select pkg2.add_func3(@a, @b, @c)' failed: ER_SF_OUT_INOUT_ARG_NOT_ALLOWED (4184): OUT or INOUT argument 3 for function pkg2.add_func3 is not allowed here |
...
|
...
|
Make the previous line to the select statement --error ER_SF_OUT_INOUT_ARG_NOT_ALLOWED
Thank you.
I have added test cases to and pushed the changes.
1. mysql-test/suite/compat/oracle/t/func_inout.test
2. mysql-test/suite/compat/oracle/r/func_inout.result
As the ticket description says, this fix only supports when sql_mode='ORACLE';
Preview notice: https://mariadb.org/?p=24584&preview=1&_ppp=08ff268aca
ManoharKB anything to add/change? If you want to credit an employer please let net me know.
Both myself and ramesh tested this feature and no major issues were observed. Please note this comment however (this could be made clear in the manual).
OK to push and with thanks to the implementer for the two high quality MTR testcases.
Logged MDEV-27574 MDEV-10654 (IN, OUT, INOUT parameters) documentation items
User contribution per PR 1931.
Test cases requested - https://lists.launchpad.net/maria-developers/msg12972.html
I didn't think there was a problem with the syntax in non-Oracle mode. sanja, bar, any other/alternate feedback?