MDEV-10411 Providing compatibility for basic PL/SQL constructs

Part 13: RETURN vs RETURNS in function definition:

  CREATE FUNCTION f1(a INT) RETURN INT ...

Part 12:  No parentheses if no arguments:

  CREATE FUNCTION f1 RETURN INT ...

4 files changed, 61 insertions, 17 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
index 19aa220cfdf..94e08d2fcd7 100644
--- a/mysql-test/suite/compat/oracle/r/sp.result
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -1,5 +1,26 @@
 SET sql_mode=ORACLE;
 # Testing routines with no parameters
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+RETURN 10;
+END;
+/
+SHOW CREATE FUNCTION f1;
+Function	f1
+sql_mode	PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER
+Create Function	CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN int(11)
+AS
+BEGIN
+RETURN 10;
+END
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+SELECT f1();
+f1()
+10
+DROP FUNCTION f1;
 CREATE PROCEDURE p1
 AS
 BEGIN
@@ -24,7 +45,7 @@ SELECT @a;
 10
 DROP PROCEDURE p1;
 # Testing ":=" to set the default value of a variable
-CREATE FUNCTION f1 () RETURNS NUMBER(10) AS
+CREATE FUNCTION f1 () RETURN NUMBER(10) AS
 a NUMBER(10) := 10;
 BEGIN
 DECLARE
@@ -39,7 +60,7 @@ f1()
 13
 DROP FUNCTION f1;
 # Testing labels
-CREATE FUNCTION f1 (a INT) RETURNS CLOB AS
+CREATE FUNCTION f1 (a INT) RETURN CLOB AS
 BEGIN
 <<label1>>
 BEGIN
@@ -58,7 +79,7 @@ SELECT f1(2);
 f1(2)
 IS NOT 1
 DROP FUNCTION f1;
-CREATE FUNCTION f1 (a INT) RETURNS INT IS
+CREATE FUNCTION f1 (a INT) RETURN INT IS
 BEGIN
 <<label1>>
 LOOP
@@ -74,7 +95,7 @@ SELECT f1(4);
 f1(4)
 2
 DROP FUNCTION f1;
-CREATE FUNCTION f1 (a INT) RETURNS INT AS
+CREATE FUNCTION f1 (a INT) RETURN INT AS
 BEGIN
 <<label1>>
 WHILE a>0 DO
@@ -90,7 +111,7 @@ SELECT f1(4);
 f1(4)
 2
 DROP FUNCTION f1;
-CREATE FUNCTION f1 (a INT) RETURNS INT AS
+CREATE FUNCTION f1 (a INT) RETURN INT AS
 BEGIN
 <<label1>>
 REPEAT
@@ -320,7 +341,7 @@ SELECT @p1;
 @p1
 p1new
 DROP PROCEDURE p1;
-CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
 AS
 p2 VARCHAR(10);
 BEGIN
@@ -356,7 +377,7 @@ SELECT @p1;
 @p1
 p1new
 DROP PROCEDURE p1;
-CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
 AS
 BEGIN
 DECLARE
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test
index 4d35d056409..dc06f7c359b 100644
--- a/mysql-test/suite/compat/oracle/t/sp.test
+++ b/mysql-test/suite/compat/oracle/t/sp.test
@@ -2,6 +2,21 @@ SET sql_mode=ORACLE;
 
 --echo # Testing routines with no parameters
 DELIMITER /;
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+  RETURN 10;
+END;
+/
+DELIMITER ;/
+--vertical_results
+SHOW CREATE FUNCTION f1;
+--horizontal_results
+SELECT f1();
+DROP FUNCTION f1;
+
+
+DELIMITER /;
 CREATE PROCEDURE p1
 AS
 BEGIN
@@ -19,7 +34,7 @@ DROP PROCEDURE p1;
 
 --echo # Testing ":=" to set the default value of a variable
 DELIMITER /;
-CREATE FUNCTION f1 () RETURNS NUMBER(10) AS
+CREATE FUNCTION f1 () RETURN NUMBER(10) AS
   a NUMBER(10) := 10;
 BEGIN
   DECLARE
@@ -36,7 +51,7 @@ DROP FUNCTION f1;
 --echo # Testing labels
 
 DELIMITER /;
-CREATE FUNCTION f1 (a INT) RETURNS CLOB AS
+CREATE FUNCTION f1 (a INT) RETURN CLOB AS
 BEGIN
   <<label1>>
   BEGIN
@@ -55,7 +70,7 @@ DROP FUNCTION f1;
 
 
 DELIMITER /;
-CREATE FUNCTION f1 (a INT) RETURNS INT IS
+CREATE FUNCTION f1 (a INT) RETURN INT IS
 BEGIN
   <<label1>>
   LOOP
@@ -73,7 +88,7 @@ DROP FUNCTION f1;
 
 
 DELIMITER /;
-CREATE FUNCTION f1 (a INT) RETURNS INT AS
+CREATE FUNCTION f1 (a INT) RETURN INT AS
 BEGIN
   <<label1>>
   WHILE a>0 DO
@@ -91,7 +106,7 @@ DROP FUNCTION f1;
 
 
 DELIMITER /;
-CREATE FUNCTION f1 (a INT) RETURNS INT AS
+CREATE FUNCTION f1 (a INT) RETURN INT AS
 BEGIN
   <<label1>>
   REPEAT
@@ -345,7 +360,7 @@ SELECT @p1;
 DROP PROCEDURE p1;
 
 DELIMITER /;
-CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
 AS
   p2 VARCHAR(10);
 BEGIN
@@ -384,7 +399,7 @@ SELECT @p1;
 DROP PROCEDURE p1;
 
 DELIMITER /;
-CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURNS VARCHAR(20)
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
 AS
 BEGIN
   DECLARE
diff --git a/sql/sp.cc b/sql/sp.cc
index 382b3abd3a3..0071c67ecea 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -2230,7 +2230,10 @@ show_create_sp(THD *thd, String *buf,
   buf->append(')');
   if (type == TYPE_ENUM_FUNCTION)
   {
-    buf->append(STRING_WITH_LEN(" RETURNS "));
+    if (sql_mode & MODE_ORACLE)
+      buf->append(STRING_WITH_LEN(" RETURN "));
+    else
+      buf->append(STRING_WITH_LEN(" RETURNS "));
     buf->append(returns, returnslen);
   }
   buf->append('\n');
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 49e4d7d1afd..58efd058d33 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -2363,6 +2363,11 @@ sp_no_param:
           }
         ;
 
+opt_sp_parenthesized_fdparam_list:
+          sp_no_param
+        | sp_parenthesized_fdparam_list
+        ;
+
 opt_sp_parenthesized_pdparam_list:
           sp_no_param
         | sp_parenthesized_pdparam_list
@@ -16034,8 +16039,8 @@ sf_tail:
               MYSQL_YYABORT;
             Lex->spname= $3;
           }
-          sp_parenthesized_fdparam_list /* $5 */
-          RETURNS_SYM /* $6 */
+          opt_sp_parenthesized_fdparam_list /* $5 */
+          RETURN_SYM /* $6 */
           { /* $7 */
             LEX *lex= Lex;
             lex->init_last_field(&lex->sphead->m_return_field_def, NULL,