Bug #9598 stored procedure call within stored procedure overwrites IN variable
Submitted: 4 Apr 2005 5:43 Modified: 14 Apr 2005 15:45
Reporter: Rob LoPresti Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 OS:Windows (Windows XP)
Assigned to: Bugs System CPU Architecture:Any

[4 Apr 2005 5:43] Rob LoPresti
Description:
I have two stored procedures.  One calls the other.  The first accepts five arguments, one CHAR(16), the rest integer, all for specified as IN.  The second takes three inputs, one CHAR(16), the rest integer.  The char(16) is specified as IN, and the two integers are specified as OUT.  I set values to both integers, but the value assigned to the second integer is instead assigned to the char(16) variable.  This does NOT happen if the first var is also an integer.

How to repeat:
DROP DATABASE IF EXISTS bug_demo;
CREATE DATABASE IF NOT EXISTS bug_demo;
USE bug_demo;

DROP PROCEDURE IF EXISTS bug_procedure_1;
delimiter //
CREATE PROCEDURE bug_procedure_1 (
	IN var_1 CHAR(16),
	OUT var_2 INTEGER,
	OUT var_3 INTEGER
)
BEGIN
	SET var_2 = 50;
	SET var_3 = 60;
END //

delimiter ;

DROP PROCEDURE IF EXISTS bug_procedure_2;
delimiter //
CREATE PROCEDURE bug_procedure_2 (
	IN v1 CHAR(16),
	IN v2 INTEGER,
	IN v3 INTEGER,
	IN v4 INTEGER,
	IN v5 INTEGER
)
BEGIN
	select v1,v2,v3,v4,v5;
	call bug_procedure_1(v1,@tmp1,@tmp2);
	select v1,v2,v3,v4,v5;
END //

delimiter ;

call bug_procedure_2('Test',2,3,4,5);

=======================
produces this output:

v1	v2	v3	v4	v5
Test	2	3	4	5
v1	v2	v3	v4	v5
60	2	3	4	5
[14 Apr 2005 13:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24021