[MDEV-33399] Package variables return a wrong result when changed inside a function Created: 2024-02-07  Updated: 2024-02-07

Status: Open
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4, 11.5
Fix Version/s: 10.5, 11.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-10591 Oracle-style packages Closed
Relates
relates to MDEV-32101 CREATE PACKAGE [BODY] for sql_mode=DE... Closed

 Description   

I create the following package in Oracle-19.0 and call its procedure:

CREATE OR REPLACE PACKAGE pkg AS
 
  PROCEDURE p1;
 
END;
 
/
 
 
 
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  a INT:=11;
 
  b INT:=10;
 
  FUNCTION f1 RETURN INT AS
 
  BEGIN
 
    a:=a-1;
 
    RETURN a;
 
  END;
 
  PROCEDURE p1 AS
 
    res VARCHAR(1024):= a || ' ' || f1() || ' ' || a;
 
  BEGIN
 
    DBMS_OUTPUT.PUT_LINE(res);
 
  END;
 
BEGIN
 
  a:=a-b;
 
END;
 
/
 
 
 
CALL pkg.p1();


Statement processed.
 
0 0 0

Now I create the same package in MariaDB (in Oracle compatibility mode) and call its procedure:

SET sql_mode=ORACLE;
 
DELIMITER /
 
 
 
CREATE OR REPLACE PACKAGE pkg AS
 
  PROCEDURE p1;
 
END;
 
/
 
 
 
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  a INT:=11;
 
  b INT:=10;
 
  FUNCTION f1 RETURN INT AS
 
  BEGIN
 
    a:=a-1;
 
    RETURN a;
 
  END;
 
  PROCEDURE p1 AS
 
    res VARCHAR(1024):= a || ' ' || f1() || ' ' || a;
 
  BEGIN
 
    SELECT res;
 
  END;
 
BEGIN
 
  a:=a-b;
 
END;
 
/
 
 
 
DELIMITER ;
 
CALL pkg.p1();


+-------+
 
| res   |
 
+-------+
 
| 1 0 1 |
 
+-------+

Notice, it returns a different result.

 Comments   
Comment by Alexander Barkov [ 2024-02-07 ]

If I slightly change the script for Oracle-19.0 as follows, it still returns the same result with the first script version for Oracle:

CREATE OR REPLACE PACKAGE pkg AS
 
  PROCEDURE p1;
 
END;
 
/
 
 
 
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  a INT:=11;
 
  b INT:=10;
 
  FUNCTION f1 RETURN INT AS
 
  BEGIN
 
    a:=a-1;
 
    RETURN a;
 
  END;
 
  PROCEDURE p1 AS
 
  BEGIN
 
    -- No variable, just print
 
    DBMS_OUTPUT.PUT_LINE(a || ' ' || f1() || ' ' || a);
 
  END;
 
BEGIN
 
  a:=a-b;
 
END;
 
/
 
 
 
CALL pkg.p1();


Statement processed.
 
0 0 0

If I change the script for MariaDB the same way, it returns a different result (comparing to Oracle and comparing to the first MariaDB script):

SET sql_mode=ORACLE;
 
DELIMITER /
 
 
 
CREATE OR REPLACE PACKAGE pkg AS
 
  PROCEDURE p1;
 
END;
 
/
 
 
 
CREATE OR REPLACE PACKAGE BODY pkg AS
 
  a INT:=11;
 
  b INT:=10;
 
  FUNCTION f1 RETURN INT AS
 
  BEGIN
 
    a:=a-1;
 
    RETURN a;
 
  END;
 
  PROCEDURE p1 AS
 
  BEGIN
 
    -- No variable, just print it
 
    SELECT a || ' ' || f1() || ' ' || a;
 
  END;
 
BEGIN
 
  a:=a-b;
 
END;
 
/
 
 
 
DELIMITER ;
 
CALL pkg.p1();


+------------------------------+
 
| a || ' ' || f1() || ' ' || a |
 
+------------------------------+
 
| 1 0 1                        |
 
+------------------------------+

Generated at Thu Feb 08 10:38:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.