Description:
declare a table:
------------------
CREATE TABLE `srchm` (
`idu` int(10) unsigned NOT NULL default '0',
`idm` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`idu`,`idm`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into srchm values (2,5);
and declare the following function:
-----------------------------------------
create function f_get(p_u int(10),
p_m tinyint(3))
returns tinyint(3)
begin
declare v_tempdata tinyint(3) default 0;
declare v_percent tinyint(3) default 10;
declare c_m cursor for
select 1 from srchm m
where m.idu =p_u and m.idm =p_m limit 1;
declare continue handler for not found set v_percent =v_percent -1;
open c_m;
fetch c_m into v_tempdata;
close c_m;
return v_percent;
end
and now:
-----------
mysql> select f_getcomppers(2,5);
+------------------------+
| f_getcomppers(2,5) |
+-----------------------+
| 1 | (it would return 10 but it return the value of the select)
+-----------------------+ (why?)
1 row in set (0.08 sec)
mysql> select f_getcomppers(2,1212);
+----------------------------+
| f_getcomppers(2,1212) |
+----------------------------+
| 9 | (this result is Ok)
+----------------------------+
1 row in set (0.00 sec)
mysql> select f_getcomppers(2,5), 'otro campo';
+-----------------------+--------------+
| f_getcomppers(2,5) | otro campo | (here is happening equal the case one and
+-----------------------+--------------+ the real result is now in the second field,
| 1 | 10 | but the second field doesn't appear)
+-----------------------+--------------+
1 row in set (0.00 sec)
How to repeat:
this bug is present in the call to any created function, whenever it contains cursors
Description: declare a table: ------------------ CREATE TABLE `srchm` ( `idu` int(10) unsigned NOT NULL default '0', `idm` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`idu`,`idm`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into srchm values (2,5); and declare the following function: ----------------------------------------- create function f_get(p_u int(10), p_m tinyint(3)) returns tinyint(3) begin declare v_tempdata tinyint(3) default 0; declare v_percent tinyint(3) default 10; declare c_m cursor for select 1 from srchm m where m.idu =p_u and m.idm =p_m limit 1; declare continue handler for not found set v_percent =v_percent -1; open c_m; fetch c_m into v_tempdata; close c_m; return v_percent; end and now: ----------- mysql> select f_getcomppers(2,5); +------------------------+ | f_getcomppers(2,5) | +-----------------------+ | 1 | (it would return 10 but it return the value of the select) +-----------------------+ (why?) 1 row in set (0.08 sec) mysql> select f_getcomppers(2,1212); +----------------------------+ | f_getcomppers(2,1212) | +----------------------------+ | 9 | (this result is Ok) +----------------------------+ 1 row in set (0.00 sec) mysql> select f_getcomppers(2,5), 'otro campo'; +-----------------------+--------------+ | f_getcomppers(2,5) | otro campo | (here is happening equal the case one and +-----------------------+--------------+ the real result is now in the second field, | 1 | 10 | but the second field doesn't appear) +-----------------------+--------------+ 1 row in set (0.00 sec) How to repeat: this bug is present in the call to any created function, whenever it contains cursors