MySQL Bugs: #8474: using cursor rewrites parameters in function

Bug #8474	using cursor rewrites parameters in function
Submitted:	12 Feb 2005 12:57	Modified:	13 Feb 2005 14:53
Reporter:	ury stankevich	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
it seems opening cursor can rewrite my function parameters.
steps to reproduce follows,

if i comment out OPEN cur_n .. CLOSE cur_n;
then function returns correct parameter value.

How to repeat:
mysql> select * from services;
+------------+-------+--------+
| account_id | login | passwd |
+------------+-------+--------+
|          1 | qwe   | asd    |
|          5 | QWE   | ASD    |
+------------+-------+--------+
2 rows in set (0,00 sec)

// TEST CASE
DROP FUNCTION if exists ury_search_user;
delimiter //
CREATE FUNCTION ury_search_user ( m_login varchar(255), m_passwd varchar(255), m_sensitive INT )
 RETURNS int
BEGIN
 DECLARE l_account_id INT;
 DECLARE l_a INT;
 DECLARE cur_s cursor for SELECT account_id FROM services where account_id=l_a AND BINARY login=m_login ;
 DECLARE cur_n cursor for SELECT account_id FROM services where login=m_login AND BINARY passwd=m_passwd ;
 DECLARE CONTINUE HANDLER FOR 1329 SET l_account_id=NULL;

 OPEN cur_n;
 FETCH cur_n INTO l_a;
 CLOSE cur_n;

 RETURN m_sensitive;

END
//
delimiter ;

+--------------------------------+
| ury_search_user('qwE','asd',3) |
+--------------------------------+
|                              1 |
+--------------------------------+

+--------------------------------+
| ury_search_user('qwE','ASD',2) |
+--------------------------------+
|                              5 |
+--------------------------------+

Suggested fix:
i don't know..
may be this bug related to #3535 ?

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Please elaborate more on what exactly happens with full test case. Current example selects from unknown table.

SELECT statements are not allowed for funtions which means opening a cursor should return an error.

Also please note that Stored Procedures and Functions has nothing to do with UDF (User Defined Functions)

ok, thanks

i convert function to procedure and cursors now OK.