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
• Status: Verified
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 6.1.7 and above
• OS: Windows (Windows 7 and 10)
• CPU Architecture: x86
• Tags: LAST_INSERT_ID, out parameter, stored procedure

[17 Oct 2018 9:54] Clive Wardle

Description:
When Executing a Stored Procedure that has an out parameter, for the Last Insert ID, the generated SQL is wrong. An input entry is created for the Out parameter, the data types are shifted by one and the last parameter is missed.

How to repeat:
1. Use the following SQL to create a test table and stored procedure to insert in to the table TESTPROC.

DROP TABLE IF EXISTS `TESTPROC`;
CREATE TABLE `TESTPROC` (
	`KEY_ID`      	BIGINT(10) 	NOT NULL AUTO_INCREMENT,
	`FIELD1`	      VARCHAR(30),
	`FIELD2`	      VARCHAR(30),
	`FIELD3`	      BIGINT(10),
	`FIELD4`	      BIGINT(10),
	`FIELD5`	      VARCHAR(30),
	`FIELD6`	      VARCHAR(30),
PRIMARY KEY (`KEY_ID`)
) ENGINE = INNODB;

DROP PROCEDURE IF EXISTS PI_TESTPROC ;
DELIMITER $$ 
CREATE PROCEDURE PI_TESTPROC (
		OUT PKEY_ID BIGINT(10),
		IN PFIELD1 VARCHAR(30),
		IN PFIELD2 VARCHAR(30),
		IN PFIELD3 BIGINT(10),
	    	IN PFIELD4 BIGINT(10),
		IN PFIELD5 VARCHAR(30),
		IN PFIELD6 VARCHAR(30)
)
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 ;

2. Use Workbench to Execute the Stored Procedure, Enter the parameters 0, field 1,field 2,3,4,field 5,field 6.

3. Press Execute; the following SQL is generated, which fails to execute.

set @PKEY_ID = 0;
call alsidis.PI_TESTPROC(@PKEY_ID, '0', 'field 1', field 2, 3, '4', 'field 5');
select @PKEY_ID;

Issues.
1. 0 should not be present, 
2. the values field 1 and field 2 should be quoted
3. The values 3 & 4 should not be quoted.
4. The entry for field 6 is missing.
 
Correcting the SQL manually and running returns the insert of the last key field.

set @PKEY_ID = 0;
call alsidis.PI_TESTPROC(@PKEY_ID, 'field 1', 'field 2', 3, 4, 'field 5', 'field 6');
select @PKEY_ID;

Suggested fix:
It should not be possible to enter a parameter for an OUT field.

It would appear that entry for the OUT field has shifted the parameters causing a mismatch on the datatypes.

[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;