MySQL Bugs: #62151: procedure call with parameter name value pair processes ignores param name

Bug #62151	procedure call with parameter name value pair processes ignores param name
Submitted:	12 Aug 2011 11:13	Modified:	12 Aug 2011 12:01
Reporter:	Girish Sumaria	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.45 Community and Linux	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Parameter order with parameter names

Description:
Calls to stored procedure with parameter value name pair ignore the parameter name and relate the passed values as per column definitions.

Procedure proc1(p1 text, p2 text, p3 text);
# call w/o parameter name 
call proc1('A','B','C'); 

#call with parameter name value pair in order of actual parameter order
call proc1(@p1:='A',@p2:='B',@p3:='C'); 

#call with parameter name value pair in diff order of actual parameter order
call proc1(@p2:='B',@p1:='A',@p3:='C');

#Since I am supplying same values for same parameters, output should be same.
#But MySQL processes this as 
call proc1('A','B','C'); 
#and
call proc1('B','A','C'); 

How to repeat:
DELIMITER $$

USE `metasea`$$

DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`mydbadmin`@`%` PROCEDURE `test`(
 P1 TEXT CHARSET utf8,
 P2 TEXT CHARSET utf8,
 P3 TEXT CHARSET utf8,
 P4 TEXT CHARSET utf8,
 P5 TEXT CHARSET utf8)
BEGIN
 DECLARE XYZ INT;
 SELECT P1 ,P2 ,P3 ,P4 ,P5 ;
END$$
DELIMITER ;
CALL test(
'P1',
'P2',
'P3',
'P4',
'P5'
);
CALL test(
@P1:='P1',
@P2:='P2',
@P3:='P3',
@P4:= 'P4',
@P5:= 'P5'
);
CALL test(
@P1:='P1',
@P3:='P3',
@P2:='P2',
@P4:= 'P4',
@P5:= 'P5'
);
Note the output of 3rd procedure. Even if @P3 is supplied as P3 and @P2 as P2, the output value is @P3 becomes P2 and vice versa. 

Suggested fix:
NA

Interesting!

SQL server has support for stored procedure with parameter value name pairs where value name pairs may be specified in any order.  I found this http://dba.fyicenter.com/faq/sql_server_2/Passing_Name_Value_Pairs_as_Parameters.html

But I don't think MySQL has support for this. You just assign values to user variables. See 

CALL test(
@P1:='P1',
@P2:='P2',
@P3:='P3',
@P4:= 'P4',
@P5:= 'P5'
);
SELECT @P1, @P2, @P3, @P4, @P5;

CALL test(
@P1:='P1',
@P2:='P3',
@P3:='P2',
@P4:= 'P7',
@P5:= 'P5'
);
SELECT @P1, @P2, @P3, @P4, @P5;

(and I am actually surpised that you can assign values to user variables in the parameter-list of a stored procedure call (but you can what my snippet shows!).  I wonder if this is documented at all!)

This page:
http://dev.mysql.com/doc/refman/5.1/en/call.html

.. does not mention the option to use value name pairs in CALL.  It also does not tell that user variables can be assigned in parameters for CALL.

And this 
http://dev.mysql.com/doc/refman/5.1/en/user-variables.html
.. does not either!

This is a real 'gotcha' I think!

As Peter explained, when you set values to @P1 etc you set them for user variables, then values of user variables, by position, are used as values of parameter in the same position. 

For IN parameters (IN type is used by default) you can use expressions that are evaluated first. Setting value for user variable with := is just a special case of expression. It works the same way as in SELECT below:

mysql> select @a:=1;
+-------+
| @a:=1 |
+-------+
|     1 |
+-------+
1 row in set (0.03 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

So, user variable gets value 1 and value 1 is used for the first "positional parameter" in SELECT list.

This is not a bug.

@Valeriy: don't you think that this page http://dev.mysql.com/doc/refman/5.1/en/call.html should have elaborated that "a 'parameter' can be a constant or an expression". Currently there is only the syntax definition listed as: "CALL sp_name([parameter[,...]])" wiht no explanation of what a 'parameter' exactly can be.

You may even use functions in the expression:
CALL test(
@P1:='P1',
@P2:='P2',
@P3:='P3',
@P4:= 'P4',
@P5:= ABS(-1)
); -- works fine (also without introducing @P5 at all)

.. but not SELECT (ie. you cannot read parameters directly from a table)
CALL test(
@P1:='P1',
@P2:='P2',
@P3:='P3',
@P4:= 'P4',
@P5:= SELECT 1
); -- returns syntax error

Documentation also tells "For an INOUT parameter, initialize its value before passing it to the procedure." Assuming that IN and INOUT parameters are the same here I think an INOUT parameter can be intitialized in he parameter-list itself and then "before passing it" is not a very good description.  

So I think this shows that a docs could use a clarification that (and how) expressions an be used in parameters and.

The feature request for this is
Bug#15813 Feature Request for Named Parameters in the CALL statement