MySQL Bugs: #10054: Stored proccedures: function that returns TEXT causes server crash

Bug #10054	Stored proccedures: function that returns TEXT causes server crash
Submitted:	21 Apr 2005 9:11	Modified:	11 May 2005 10:07
Reporter:	Teemu Kuulasmaa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.4 beta	OS:	Windows (windows xp)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
If a CREATE FUNCTION statement contains RETURNS TEXt, MySQL server crashes.

How to repeat:
I used Query Browser to execute query:

CREATE FUNCTION TEST (var TEXT)
  RETURNS TEXT
BEGIN
  RETURN LOVER(var);
END;

I got the following error message:

"Lost connection to MySQL server during query"

MySQL server crasher and I have to start service again.

mysql> CREATE FUNCTION TEST (var TEXT)
    ->   RETURNS TEXT
    -> BEGIN
    ->   RETURN LOVER(var);
    -> END;
    -> //
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
Number of processes running now: 0
050421 10:18:15  mysqld restarted

The crash is gone, but instead we get this, which is even more strange:

mysql> select test('Foo');
ERROR 1305 (42000): FUNCTION test.LOVER does not exist

I have following table (in MySQL 5.0.4-beta, Windows 2000): 

CREATE TABLE `emails` ( 
`email` varchar(50) NOT NULL, 
PRIMARY KEY (`email`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO `emails` VALUES ('abc@def.gh'); 
INSERT INTO `emails` VALUES ('bcd@efg.hi'); 
INSERT INTO `emails` VALUES ('cde@fgh.ij'); 
INSERT INTO `emails` VALUES ('def@ghi.jk'); 
INSERT INTO `emails` VALUES ('efg@hij.kl'); 
INSERT INTO `emails` VALUES ('fgh@ijk.lm'); 

and following function: 

CREATE FUNCTION domain(email varchar(100)) RETURNS varchar(100) 
begin 
return lower( right( email, length( email ) - locate( '@', email ) ) ); 
end // 

Doing the query ... 

SELECT email, domain(email) from emails 

... the server crashes :(. 

However, if I query ... 

SELECT domain('xyz@abc.de') 

... the function works.

No longer repeatable.

(Note the typo in the first test case, it should be LOWER.)

Did you try the corect syntax (LOWER)? The real problem was the server crash if fucntion returns TEXT! Has this bug been fixed? If so when new version 5.0.5 will be released?

Teemu

Of course I tried with LOWER.

However, it turns out that the second test case, which was added later, still crashes, but it depends on the engine type. (Crashes with innodb only.) This is a different problem, so it will be handled as a separate bug.

The second test case reported as BUG#10540.