[MDEV-25382] cannot set local variable in the RETURNING clause of INSERT ... RETURNING Created: 2021-04-10  Updated: 2021-04-14  Resolved: 2021-04-11

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.5.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ashford Nichols Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: insert, insert_into
Environment:

Linux Mint 20.1

Issue Links:
Relates
relates to MDEV-25391 INSERT RETURNING should be used in St... Open

 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



 Comments   
Comment by Sergei Golubchik [ 2021-04-11 ]

RETURNING clause accepts the same syntax as a valid SELECT clause,
You cannot write

SELECT loc_var := col3 FROM tbl1

either

Comment by Ashford Nichols [ 2021-04-11 ]

Please note that the following also doesn't work, despite being valid select syntax:

INSERT INTO tbl1 (col1, col2) VALUES (1, 2) RETURNING col3 INTO loc_var;
 

INSERT INTO tbl1 (col1, col2) VALUES (1, 2) RETURNING (SELECT col3 INTO loc_var);

Comment by Sergei Golubchik [ 2021-04-14 ]

I suspect that

SELECT (SELECT col3 INTO loc_var) FROM tbl1

is not allowed too. That is, it doesn't work in SELECT, so it should not work in RETURNING either.

But your first query RETURNING ... INTO makes perfect sense. It's not supported now, but it's a valid feature that we should implement some day.

Generated at Thu Feb 08 09:37:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.