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:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18-nt-max-log OS:Windows (Win2K3)
Assigned to: CPU Architecture:Any

[2 Oct 2006 19:46] Anthony Willard
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:


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


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.
[2 Oct 2006 22:43] MySQL Verification Team
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.
[4 Oct 2006 18:49] Anthony Willard
I was authorized to upgrade our server to 5.0.24a-community-max-nt-log; with this version the error does not occur.