MySQL Bugs: #60221: Stored FUNCTION with REGEXP in body return wrong result

Bug #60221	Stored FUNCTION with REGEXP in body return wrong result
Submitted:	23 Feb 2011 12:42	Modified:	23 Feb 2011 14:22
Reporter:	Roman Kutyrev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.77 Source distribution	OS:	Linux (CentOS 5.x)
Assigned to:		CPU Architecture:	Any

Description:
Function whith REGEXP in body executes correct only once with param 994xxxxxxx
but with param 77xxxx work fine every time.

How to repeat:
drop table codes;
create table codes (
  `id` int(11) NOT NULL auto_increment,
  `direction_id` int(11) default NULL,
  `code` varchar(16) default NULL,
  `cut_str` varchar(16) default NULL,
  `add_str` varchar(16) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert into codes (direction_id, code, cut_str, add_str) values
(1 ,"^77.*","",""),	
(2 ,"^994.*","","810");

 
drop FUNCTION FIND_PEER;
DELIMITER |
CREATE FUNCTION FIND_PEER (in_called VARCHAR(50))
  RETURNS varchar(255)
  READS SQL DATA
BEGIN
  DECLARE dir_id int;
  select direction_id into dir_id from codes where in_called regexp code limit 1;
  RETURN dir_id;
END|
DELIMITER ;

mysql> select FIND_PEER("9941234");                       
+----------------------+
| FIND_PEER("9941234") |
+----------------------+
| 2                    | 
+----------------------+
1 row in set (0.00 sec)
*** GOOD ****

mysql> select FIND_PEER("9941234");
+----------------------+
| FIND_PEER("9941234") |
+----------------------+
| NULL                 | 
+----------------------+
1 row in set, 1 warning (0.00 sec)
*** WRONG ***
and would be wrong always

mysql> select FIND_PEER("7741234");  
+----------------------+
| FIND_PEER("7741234") |
+----------------------+
| 1                    | 
+----------------------+
1 row in set (0.00 sec)
*** GOOD ****

mysql> select FIND_PEER("7741234");
+----------------------+
| FIND_PEER("7741234") |
+----------------------+
| 1                    | 
+----------------------+
1 row in set (0.00 sec)
*** GOOD ****

Stored PROCEDURE work with same results

drop procedure FIND_PEER_PROC;
DELIMITER |
CREATE procedure FIND_PEER_PROC (in_called VARCHAR(50))
BEGIN
  select direction_id from codes where in_called regexp code limit 1;
END|
DELIMITER ;

mysql> call  FIND_PEER_PROC("9941234");
+--------------+
| direction_id |
+--------------+
|            2 | 
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call  FIND_PEER_PROC("9941234");
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call  FIND_PEER_PROC("7741234");  
+--------------+
| direction_id |
+--------------+
|            1 | 
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call  FIND_PEER_PROC("7741234");
+--------------+
| direction_id |
+--------------+
|            1 | 
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

On Debian with mysql-server 5.0.51a-24+lenny4
thats work good.

Could you please try 5.0.91.

http://downloads.mysql.com/archives.php?p=mysql-5.0&v=5.0.91

Thanks in advance.

Update by yum to version mysql-server-5.0.77-4.el5_5.4.x86_64.rpm solve problem.

Thanks in advance.

So, the problem was probably related to the way your binaries were built from sources (or building environment). One more reason to use MySQL binaries :)