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

cannot set local variable in the RETURNING clause of INSERT ... RETURNING

    XMLWordPrintable

    Details

      Description

      Given the following table:

      CREATE TABLE `tbl2` (
        `col1` int(11) DEFAULT NULL,
        `col2` int(11) DEFAULT NULL,
        `col3` int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`col3`)
      );
      

      I'm trying to create a stored routine that retrieves the auto-generated value of col3 using the RETURNING clause of the INSERT ... RETURNING statement, as follows:

      CREATE PROCEDURE test_proc(
      	IN col1_param INT,
      	IN col2_param INT
      )
      BEGIN
      	DECLARE loc_var INT;
      	INSERT INTO tbl1 (col1, col2) VALUES (1, 2) RETURNING loc_var := col3;
      	SELECT loc_var;
      END
      

      However, I'm getting the following syntax error:

      You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':= col3;
      	SELECT loc_var;
      END' at line 7
      

      Also, this problem seems to only occur when attempting to set local variable, as I am able to rewrite the routine as follows:

      CREATE PROCEDURE test_proc(
      	IN col1_param INT,
      	IN col2_param INT
      )
      BEGIN
      	INSERT INTO tbl1 (col1, col2) VALUES (1, 2) RETURNING @ses_var := col3;
      	SELECT @ses_var;
      END
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              anichols Ashford Nichols
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.