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

A column is extracted as NULL in a stored procedure when the procedure has an output param of the same name

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.5, 10.6
    • Fix Version/s: N/A
    • Component/s: Stored routines
    • Labels:
      None

      Description

      If a stored procedure contains an output parameter with a certain name, and if the procedure selects a column of with the same name, the selected value is reported inside the procedure as NULL.

      The issue also present when the SELECT is run in a cursor with a subsequent FETCH - the fetched value is NULL.

      The error occurs on any data type (the example below shows JSON, but it also happens on INT etc.)

      Example:

      CREATE TABLE `t` (
        `id` int(11) DEFAULT NULL,
        `value` JSON
      ) ENGINE=InnoDB
       
      INSERT INTO t VALUES (1, '[42]');
      INSERT INTO t VALUES (2, '[69]');
       
      CREATE OR REPLACE PROCEDURE TEST_JSON()
          BEGIN
              SELECT id, value FROM t;
          END;
       
      MariaDB [test]> call TEST_JSON();
      +------+-------+
      | id   | value |
      +------+-------+
      |    1 | [42]  |
      |    1 | [69]  |
      +------+-------+
       
      CREATE OR REPLACE PROCEDURE TEST_JSON(OUT value JSON)
          BEGIN
              SELECT id, value FROM t;
          END;
       
      MariaDB [test]> call TEST_JSON(@a);
      +------+-------+
      | id   | value |
      +------+-------+
      |    1 | NULL  |
      |    1 | NULL  |
      +------+-------+
      

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            assen.totin Assen Totin (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            3 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.