Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.9
-
Linux Mint 20.1
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
- relates to
-
MDEV-25391 INSERT RETURNING should be used in Stored Procedures like a SELECT
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | N/A [ 14700 ] | |
| Assignee | Sergei Golubchik [ serg ] | |
| Resolution | Not a Bug [ 6 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
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); |
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.
| Link | This issue relates to MDEV-25391 [ MDEV-25391 ] |
| Workflow | MariaDB v3 [ 120901 ] | MariaDB v4 [ 159131 ] |
RETURNING clause accepts the same syntax as a valid SELECT clause,
You cannot write
either