MySQL Bugs: #37337: Function returns different results

Bug #37337	Function returns different results
Submitted:	11 Jun 2008 11:19	Modified:	16 Sep 2008 4:57
Reporter:	David Werner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.25, 5.1.26	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
My function returns two different results when called twice consecutive. Every time I call this function, it returns other result - first time it is "NULL", second time it is "Internet Explorer 7". "Internet Explorer 7" is the right result.

How to repeat:
Create a table:

CREATE TABLE IF NOT EXISTS `agentparse` (
  `Typ` varchar(16) NOT NULL,
  `RegularniVyraz` varchar(128) NOT NULL,
  `Retezec` text NOT NULL,
  PRIMARY KEY (`Typ`,`RegularniVyraz`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250;

INSERT INTO `agentparse` (`Typ`, `RegularniVyraz`, `Retezec`) VALUES
('os', 'windows nt 5.1', 'Windows XP, Windows Fundamentals for Legacy PCs'),
('os', 'windows nt 5.2', 'Windows XP, Windows Server 2003, Windows Home Server'),
('os', 'windows nt 3.1', 'Windows NT 3.1'),
('os', 'windows nt 3.5', 'Windows NT 3.5'),
('os', 'windows nt 3.51', 'Windows NT 3.51'),
('os', 'windows nt 4.0', 'Windows NT 4.0'),
('os', 'windows nt 5.0', 'Windows 2000'),
('os', 'windows nt 6.0', 'Windows Vista, Windows Server 2008'),
('os', 'windows nt 6.1', 'Windows 7'),
('os', 'linux i686', 'Linux 686'),
('os', 'linux x86_64', 'Linux 64-bit'),
('browser', 'msie 6.0', 'Internet Explorer 6'),
('browser', 'msie 7.0', 'Internet Explorer 7'),
('browser', 'msie 8.0', 'Internet Explorer 8');

Then create a function:

CREATE DEFINER=`root`@`%` FUNCTION `GetAgentInfo`(AgentString TEXT, HledanyTyp VARCHAR(16)) RETURNS text CHARSET cp1250
BEGIN
DECLARE Navrat TEXT DEFAULT NULL;
SELECT AP.Retezec INTO Navrat FROM master.AgentParse AS AP WHERE AP.Typ = HledanyTyp AND AgentString REGEXP AP.RegularniVyraz LIMIT 1;
RETURN Navrat;
END

Then try to call this function like:

SELECT GetAgentInfo('mozilla/4.0 (compatible; msie 7.0; windows nt 5.1; sv1; .net clr 1.1.4322; .net clr 2.0.50727)', 'browser');

It returns 'Internet Explorer 7'.

Call the same function again:

SELECT GetAgentInfo('mozilla/4.0 (compatible; msie 7.0; windows nt 5.1; sv1; .net clr 1.1.4322; .net clr 2.0.50727)', 'browser');

It returns 'NULL' and generates a warning '1329 No data - zero rows fetched, selected, or processed'.

Many thanks for writing a bug report.

This is a very weird behaviour.

First I took the test from above and then I made several tests and used a proper LATIN1/ISO-8859-15 environment also I tested by using only varchar.

Sometimes it works, sometimes it just returns NULL.

Its very probable that when you have spaces and a dotted number at the string that you use for REGEXP that the function just will return the right result once and after that it will return NULL.

Here is my test:

create table t(
a varchar(16) charset latin1 not null,
b varchar(100) charset latin1 not null,
c text charset latin1 not null,
primary key(a,b))engine=myisam;

insert into t(a,b,c) 
values('a','abc','a abc'),
('a','def','a def'),
('a','ghi','a ghi'),
('a','jkl','a jkl'), 
('b','bla 6.1','b 6.1'),
('browser','bla 7.1','b 7.1'),
('browser','bla 6.0','b 6.0'),
('b','bla 7.0','b 7.0'),
('b','bla 8.0','b 8.0');

insert into t(a,b,c)
values ('os', 'windows nt 5.1', 'Windows XP, Windows Fundamentals for Legacy PCs'),
('os', 'windows nt 5.2', 'Windows XP, Windows Server 2003, Windows Home Server'),
('os', 'windows nt 3.1', 'Windows NT 3.1'),
('os', 'windows nt 3.5', 'Windows NT 3.5'),
('os', 'windows nt 3.51', 'Windows NT 3.51'),
('os', 'windows nt 4.0', 'Windows NT 4.0'),
('os', 'windows nt 5.0', 'Windows 2000'),
('os', 'windows nt 6.0', 'Windows Vista, Windows Server 2008'),
('os', 'windows nt 6.1', 'Windows 7'),
('os', 'linux i686', 'Linux 686'),
('os', 'linux x86_64', 'Linux 64-bit'),
('browser', 'msie 6.0', 'Internet Explorer 6'),
('browser', 'msie 7.0', 'Internet Explorer 7'),
('browser', 'msie 8.0', 'Internet Explorer 8');

drop function if exists f;

delimiter //

create function f(d varchar(100), e varchar(16)) returns varchar(100)
begin
declare rtrn varchar(100) default NULL;
select c into rtrn from t where a=e and d regexp b limit 1;

return rtrn;
end //

delimiter ;

select f('mozilla 4.0 (def; abc; ghi; jkl; msie 6.0; bla 7.0)', 'b');
+---------------------------------------------------------------+
| f('mozilla 4.0 (def; abc; ghi; jkl; msie 6.0; bla 7.0)', 'b') |
+---------------------------------------------------------------+
| b 7.0                                                         | 
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select f('mozilla 4.0 (def; abc; ghi; jkl; msie 6.0; bla 7.0)', 'b');
+---------------------------------------------------------------+
| f('mozilla 4.0 (def; abc; ghi; jkl; msie 6.0; bla 7.0)', 'b') |
+---------------------------------------------------------------+
| NULL                                                          | 
+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

I used newest bzr tree: 5.1.26-rc

I forgot:

this won't occur when you make the select directly without using the function. So its function plus regex related.

doesn't need a stored procedure either:

drop table if exists t;
create table t(a int,b char(4))engine=myisam;
insert t values (1,'6.1'),(1,'7.0'),(1,'8.0');
prepare stmt from "select a from t where a=1 and '7.0' regexp b limit 1";
execute stmt;
execute stmt;
execute stmt;

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/commits/48471

2674 Ramil Kalimullin	2008-06-25
      Fix for bug#37337: Function returns different results
      
      Problem: REGEXP in functions/PSs may return wrong results
      due to improper initialization.
      
      Fix: initialize required REGEXP params.

OK to push.

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/commits/51693

2699 Ramil Kalimullin	2008-08-15
      Fix for bug#37337: Function returns different results
      
      Problem: REGEXP in functions/PSs may return wrong results
      due to improper initialization.
      
      Fix: initialize required REGEXP params.

Pushed in 5.1.28

Noted in 5.1.28 changelog.

Within stored programs or prepared statements, REGEXP could return
incorrect results due to improper initialization.

Setting report to NDI pending push of fix into 6.0.x.

Pushed into 6.0.6-alpha  (revid:ramil@mysql.com-20080815055325-vu48ls6mzo4szzok) (version source revid:sergefp@mysql.com-20080611231653-nmuqmw6dedjra79i) (pib:3)

Noted in 6.0.6 changelog.