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

Details

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

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.

Attachments

Issue Links

is caused by

MDEV-10591 Oracle-style packages

Closed

relates to

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

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2024-02-07 07:21

Alexander Barkov made changes - 2024-02-07 07:22

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-32101~~ [ ~~MDEV-32101~~ ]

Alexander Barkov made changes - 2024-02-07 07:22

Link

This issue is caused by ~~MDEV-10591~~ [ ~~MDEV-10591~~ ]

Alexander Barkov added a comment - 2024-02-07 07:41 - edited

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                        |

+------------------------------+

Alexander Barkov added a comment - 2024-02-07 07:41 - edited 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 | +------------------------------+

Julien Fritsch made changes - 2024-09-10 15:31

Fix Version/s

11.5 [ 29506 ]

People

Assignee:: Unassigned

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-02-07 07:21

Updated:: 2024-09-10 15:31

Git Integration

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

MariaDB Server