[MDEV-33399] Package variables return a wrong result when changed inside a function - Jira

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
Fix Version/s: 10.5
Component/s: Stored routines
Labels:
None

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.

is caused by

MDEV-10591 Oracle-style packages

relates to

MDEV-32101 CREATE PACKAGE [BODY] for sql_mode=DEFAULT

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server