[MDEV-27331] FUNCTION declaration fails with ERROR 1064 (error in SQL) when using oracle sql_mode and OUT parameter Created: 2021-12-21  Updated: 2021-12-30  Resolved: 2021-12-30

Status: Closed
Project: MariaDB Server
Component/s: Parser, Stored routines
Affects Version/s: 10.8
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-10654 IN, OUT, INOUT parameters in CREATE F... Closed

 Description   

10.8.0 aff084d139a9fca9d569fedd9ec80bd9576614b1 preview-10.8-MDEV-10654-inout-params

 
10.8.0>SET sql_mode='';  # Optional, here to show the clear difference oracle mode makes
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.8.0>DELIMITER #
 
10.8.0>CREATE FUNCTION f(OUT c INT) RETURNS INT BEGIN SET c=1; RETURN c; END; #
 
Query OK, 0 rows affected (0.010 sec)
 
 
 
10.8.0>DELIMITER ;

Yet

10.8.0 aff084d139a9fca9d569fedd9ec80bd9576614b1 preview-10.8-MDEV-10654-inout-params

 
10.8.0>SET sql_mode='oracle';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10.8.0>DELIMITER #
 
10.8.0>CREATE FUNCTION f(OUT c INT) RETURNS INT BEGIN SET c=1; RETURN c; END; #
 
ERROR 1064 (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 'OUT c INT) RETURNS INT BEGIN SET c=1; RETURN c; END' at line 1
 
10.8.0>DELIMITER ;



 Comments   
Comment by Alexander Barkov [ 2021-12-22 ]

Hi Roel, I don't think this is a bug.

Oracle uses different order of parameter name and parameter mode.
Try to move OUT after the parameter name when using sql_mode=ORACLE, as in here:

CREATE FUNCTION f(OUT c INT) ... ;  -- this is SQL/PSM syntax
 
CREATE FUNCTION f(c OUT INT) ...; -- this is PL/SQL syntax

Generated at Thu Feb 08 09:52:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.