MySQL Bugs: #22903: "Out of Memory" error with user function in where clause

Bug #22903	"Out of Memory" error with user function in where clause
Submitted:	2 Oct 2006 19:46	Modified:	4 Oct 2006 18:49
Reporter:	Anthony Willard (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-nt-max-log	OS:	Windows (Win2K3)
Assigned to:		CPU Architecture:	Any

Description:
When I select a few columns with a user defined function to compare two fields, I receive an "Out of memory (Needed 2302248 bytes)" error message.  I'm trying to process about 1.4 million data rows.

How to repeat:
Create a simple table:

create table bug_test(
claimnum char(10) not null,
paiddate date not null
)
;

Fill claimnum with a value in the format "######", like the date_format( now(),'%y%j') produces, and a date.  On my configuration, I loaded 600,000 entries.

Create a function:

DELIMITER $$

DROP FUNCTION IF EXISTS `mysql_bug`.`f_julian_diff` $$
CREATE FUNCTION `mysql_bug`.`f_julian_diff` ( jTo char(5), jFrom char(5) ) RETURNS INT
DETERMINISTIC
BEGIN
  RETURN ( jTo - jFrom ) - ( ( left( jTo, 2 ) - left( jFrom, 2 ) ) * 635 );
END $$

DELIMITER ;

Run the query:
select claimnum, paiddate
from bug_test
where f_julian_diff( date_format( paiddate, '%y%j' ), left( claimnum, 5 ) ) <> 0;

I receive the error after about 537,024 rows.

Suggested fix:
It appears that the function invocation may not be releasing resources over many thousands of invocations.

Thank you for the bug report. Could you please upgrade with the latest
released version, if still you get the same behavior provide the complete
script test. Thanks in advance.

I was authorized to upgrade our server to 5.0.24a-community-max-nt-log; with this version the error does not occur.