MDEV-10654 add support IN, OUT, INOUT parameter qualifiers for stored functions

Problem: Currently stored function does not support IN/OUT/INOUT parameter qualifiers.
This is needed for Oracle compatibility (sql_mode = ORACLE).

Solution: Implemented parameter qualifier support to CREATE FUNCTION (reference: CREATE PROCEDURE)
Implemented return by reference for OUT/INOUT parameters in execute_function() (reference: execute_procedure())

Files changed:
sql/sql_yacc.yy: Added IN, OUT, INOUT parameter qualifiers for CREATE FUNCTION.
sql/sp_head.cc: Added input and output parameter binding for IN/OUT/INOUT parameters in execute_function() so that OUT/INOUT can return by reference.
sql/share/errmsg-utf8.txt: Added error message to restrict OUT/INOUT parameters while function being called from SQL query.
mysql-test/suite/compat/oracle/t/sp-inout.test: Added test cases
mysql-test/suite/compat/oracle/r/sp-inout.result: Added test results

Reviewed-by: iqbal@hasprime.com

1 files changed, 42 insertions, 22 deletions

diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 315eba18703..2991f235357 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3063,20 +3063,6 @@ sp_param_name:
           }
         ;
 
-sp_param_name_and_type:
-          sp_param_name field_type
-          {
-            if (unlikely(Lex->sp_param_fill_definition($$= $1, $2)))
-              MYSQL_YYABORT;
-          }
-        | sp_param_name ROW_SYM row_type_body
-          {
-            if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $3)))
-              MYSQL_YYABORT;
-          }
-        | sp_param_name_and_type_anchored
-        ;
-
 /* Stored PROCEDURE parameter declaration list */
 sp_pdparam_list:
           /* Empty */
@@ -18039,6 +18025,20 @@ sp_decl_variable_list_anchored:
           }
         ;
 
+sp_param_name_and_type:
+          sp_param_name field_type
+          {
+            if (unlikely(Lex->sp_param_fill_definition($$= $1, $2)))
+              MYSQL_YYABORT;
+          }
+        | sp_param_name ROW_SYM row_type_body
+          {
+            if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $3)))
+              MYSQL_YYABORT;
+          }
+        | sp_param_name_and_type_anchored
+        ;
+
 sp_param_name_and_type_anchored:
           sp_param_name TYPE_SYM OF_SYM ident '.' ident
           {
@@ -18913,25 +18913,45 @@ sp_decl_variable_list_anchored:
           }
         ;
 
+sp_param_name_and_type:
+          sp_param_name sp_opt_inout field_type
+          {
+            $1->mode= $2;
+            if (unlikely(Lex->sp_param_fill_definition($$= $1, $3)))
+              MYSQL_YYABORT;
+          }
+        | sp_param_name sp_opt_inout ROW_SYM row_type_body
+          {
+            $1->mode= $2;
+            if (unlikely(Lex->sphead->spvar_fill_row(thd, $$= $1, $4)))
+              MYSQL_YYABORT;
+          }
+        | sp_param_name_and_type_anchored
+        ;
+
 sp_param_name_and_type_anchored:
-          sp_param_name sp_decl_ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM
+          sp_param_name sp_opt_inout sp_decl_ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM
           {
-            if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $2, $4)))
+            $1->mode= $2;
+            if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $3, $5)))
               MYSQL_YYABORT;
           }
-        | sp_param_name sp_decl_ident '.' ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM
+        | sp_param_name sp_opt_inout sp_decl_ident '.' ident '.' ident PERCENT_ORACLE_SYM TYPE_SYM
           {
-            if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $2, $4, $6)))
+            $1->mode= $2;
+            if (unlikely(Lex->sphead->spvar_fill_type_reference(thd, $$= $1, $3, $5, $7)))
               MYSQL_YYABORT;
           }
-        | sp_param_name sp_decl_ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM
+        | sp_param_name sp_opt_inout sp_decl_ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM
           {
-            if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $2)))
+            $1->mode= $2;
+            if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $3)))
               MYSQL_YYABORT;
           }
-        | sp_param_name sp_decl_ident '.' ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM
+        | sp_param_name sp_opt_inout sp_decl_ident '.' ident PERCENT_ORACLE_SYM ROWTYPE_ORACLE_SYM
           {
-            if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $2, $4)))
+            $1->mode= $2;
+            if (unlikely(Lex->sphead->spvar_fill_table_rowtype_reference(thd, $$= $1, $3, $5)))
               MYSQL_YYABORT;
           }
         ;