Bug #92822 | Executing a Stored Procedure with an Out Parameter generates incorrect SQL | ||
---|---|---|---|
Submitted: | 17 Oct 2018 9:54 | Modified: | 4 Jul 2019 9:32 |
Reporter: | Clive Wardle | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Workbench | Severity: | S3 (Non-critical) |
Version: | 6.1.7 and above | OS: | Windows (Windows 7 and 10) |
Assigned to: | CPU Architecture: | x86 | |
Tags: | LAST_INSERT_ID, out parameter, stored procedure |
[17 Oct 2018 9:54]
Clive Wardle
[17 Oct 2018 10:11]
Clive Wardle
SQL and Screen shots to show the issue.
Attachment: Bug #92822 Insert Procedure Error.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 0 bytes.
[17 Oct 2018 13:11]
MySQL Verification Team
Thank you for the bug report. Please print here the exactly command for your step 2 and run the test case using command client mysql.exe. Thanks.
[17 Oct 2018 13:45]
Clive Wardle
The issue is reported for MySQL Workbench only; it cannot be reproduced using command client mysql.exe.
[17 Oct 2018 15:06]
MySQL Verification Team
Thank you for the feedback. Your attachment is 0 bytes, please attach the correct one thanks.
[17 Oct 2018 21:07]
Clive Wardle
SQL and Screen shots to show the issue.
Attachment: Bug #92822 Insert Procedure Error.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 156.97 KiB.
[18 Oct 2018 8:19]
MySQL Verification Team
Thank you for the feedback. Verified as described.
[4 Jul 2019 9:32]
Clive Wardle
A workaround is to create the procedure with the OUT as the last parameter. e.g. DROP PROCEDURE IF EXISTS PI_TESTPROC ; DELIMITER $$ CREATE PROCEDURE PI_TESTPROC ( IN PFIELD1 VARCHAR(30), IN PFIELD2 VARCHAR(30), IN PFIELD3 BIGINT(10), IN PFIELD4 BIGINT(10), IN PFIELD5 VARCHAR(30), IN PFIELD6 VARCHAR(30), OUT PKEY_ID BIGINT(10) ) BEGIN INSERT INTO TESTPROC( FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6) VALUES ( PFIELD1, PFIELD2, PFIELD3, PFIELD4, PFIELD5, PFIELD6); SET PKEY_ID = LAST_INSERT_ID(); END$$ DELIMITER ; When the Procedure is run using Workbench, the following SQL is generated, which runs set @PKEY_ID = 0; call alsidis.PI_TESTPROC('Field 1', 'Field 2', 3, 4, 'Field 5', 'Field 6', @PKEY_ID); select @PKEY_ID;