Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36047

Package body variables are not allowed as FETCH targets

    XMLWordPrintable

Details

    Description

      SET sql_mode=ORACLE;
      DROP PACKAGE pkg;
      DELIMITER /
      CREATE PACKAGE pkg AS
        FUNCTION f1 RETURN INT;
      END;
      /
      CREATE PACKAGE BODY pkg AS
        vc INT := 0;
        FUNCTION f1 RETURN INT AS
          CURSOR cur IS SELECT 1 AS c FROM DUAL;
        BEGIN
          OPEN cur;
          FETCH cur INTO vc; -- error happens here
          CLOSE cur;
          RETURN vc;
        END;
      END;
      /
      DELIMITER ;
      SELECT pkg.f1() FROM DUAL;
      

      ERROR 1327 (42000): Undeclared variable: vc
      

      Looks wrong.

      If I run the same script in Oracle, it works fine and returns 1 as expected.

      DROP PACKAGE pkg;
      CREATE PACKAGE pkg AS
        FUNCTION f1 RETURN INT;
      END;
      /
      CREATE PACKAGE BODY pkg AS
        vc INT := 0;
        FUNCTION f1 RETURN INT AS
          CURSOR cur IS SELECT 1 AS c FROM DUAL;
        BEGIN
          OPEN cur;
          FETCH cur INTO vc;
          CLOSE cur;
          RETURN vc;
        END;
      END;
      /
      SELECT pkg.f1() FROM DUAL;
      

      +----------+
      | PKG.F1() |
      +----------+
      |        1 |
      +----------+
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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