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

OUT and INOUT parameters shouldn't expect a default value

Details

    Description

      Default value for OUT and INOUT parameters in a stored procedure is not supported. But when an OUT or INOUT parameter is supplied along with an IN parameter with a default value we error out expecting the same for OUT or INOUT parameter.

      Repro:

      11.8.0-opt>DELIMITER $$
      		 
      11.8.0-opt>CREATE OR REPLACE PROCEDURE sp1(IN p1 INT DEFAULT 0, OUT p2 INT DEFAULT 5) BEGIN SET p2 = p2 + 1; END$$
      		 
      ERROR 4032 (HY000): Default/ignore value is not supported for such parameter usage
      		 
       
      		 
      11.8.0-opt>CREATE OR REPLACE PROCEDURE sp1(IN p1 INT DEFAULT 0, OUT p2 INT) BEGIN SET p2 = p2 + 1; END$$
      		 
      ERROR 1230 (42000): Variable 'p2' doesn't have a default value
      		 
      11.8.0-opt>

      Expected behavior:
      The following CREATE PROCEDURE should succeed.

      CREATE OR REPLACE PROCEDURE sp1(IN p1 INT DEFAULT 0, OUT p2 INT)

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-10862 Stored procedures: default values for parameters (optional parameters)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            susil.behera Susil Behera created issue -
            susil.behera Susil Behera made changes -
            Field Original Value New Value
            susil.behera Susil Behera made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov added a comment - - edited

            susil.behera, thanks for reporting.

            You're right. For example, this script works fine in Oracle:

            CREATE OR REPLACE PROCEDURE sp1(p1 IN INT DEFAULT 123, p2 OUT INT) AS
            		 
            BEGIN
            		 
              p2:= p1;
            		 
            END;
            		 
            /
            		 
            CREATE OR REPLACE PROCEDURE sp2 AS
            		 
              v INT:=1;
            		 
            BEGIN
            		 
              sp1(p2=>v);
            		 
              DBMS_OUTPUT.PUT_LINE(v);
            		 
            END;
            		 
            /
            		 
            CALL sp2;

            and produces "123" output.

            MariaDB does not allow to pass routine parameters by name, using the arrow syntax like p2=>v.
            Perhaps putting OUT parameters after IN-WITH-DEFAUT parameters should be implemented together with the arrow syntax.

            Do you know any other use cases of this parameter combination other than calling with arrow syntax?

            bar Alexander Barkov added a comment - - edited susil.behera , thanks for reporting. You're right. For example, this script works fine in Oracle: CREATE OR REPLACE PROCEDURE sp1(p1 IN INT DEFAULT 123, p2 OUT INT ) AS BEGIN p2:= p1; END ; / CREATE OR REPLACE PROCEDURE sp2 AS v INT :=1; BEGIN sp1(p2=>v); DBMS_OUTPUT.PUT_LINE(v); END ; / CALL sp2; and produces "123" output. MariaDB does not allow to pass routine parameters by name, using the arrow syntax like p2=>v. Perhaps putting OUT parameters after IN-WITH-DEFAUT parameters should be implemented together with the arrow syntax. Do you know any other use cases of this parameter combination other than calling with arrow syntax?
            susil.behera Susil Behera added a comment -

            Do you know any other use cases of this parameter combination other than calling with arrow syntax?
            barNo. I don't know of any other use case.

            susil.behera Susil Behera added a comment - Do you know any other use cases of this parameter combination other than calling with arrow syntax? bar No. I don't know of any other use case.
            bar Alexander Barkov added a comment -

            This problem was fixed and the patch was incorporated into the main patch for MDEV-10862:

            https://github.com/MariaDB/server/tree/bb-11.8-bar-MDEV-10862-opt-params

            bar Alexander Barkov added a comment - This problem was fixed and the patch was incorporated into the main patch for MDEV-10862 : https://github.com/MariaDB/server/tree/bb-11.8-bar-MDEV-10862-opt-params
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2025-01-28 09:24:04.0 2025-01-28 09:24:03.731
            bar Alexander Barkov made changes -
            Fix Version/s 11.8.1 [ 29961 ]
            Fix Version/s 11.8 [ 29921 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              bar Alexander Barkov
              susil.behera Susil Behera
              Votes:
              0 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.