MySQL Bugs: #19739: stored function call in primary select causes memory leak

Bug #19739	stored function call in primary select causes memory leak
Submitted:	11 May 2006 20:10	Modified:	18 May 2006 23:28
Reporter:	Michael Del Monte	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Calling stored function in a select statement causes excessive memory usage

How to repeat:
It is easiest to see this with a table of about 10 million rows, but it can be reproduced with a smaller table.  Fill a table with 10 million smallints and then a stored function that converts a smallint to a tinyint.  My test function was this one:

return 10*((s-(s div 100))*100)-(s mod 10))+10*(s div 100)+(s mod 10);

Now attempt insertion into a temp table of tiny ints, like this:

insert into temp select small2tiny(s) from test

The server process will use tons of memory and I/O usage will drop to zero.

However, doing it without a stored function works fine:

insert into temp select 10*((s-(s div 100))*100)-(s mod 10))+10*(s div 100)+(s mod 10) from test

Suggested fix:
Determine whether the memory usage results from conversion to bigint, bigint math, or the use of the stored function system.

Thank you for the bug report. Could you please provide the complete test
case not just its description, create table, create procedure and so on.

Thanks in advance.

Problem can be demonstrated with the following test:

# create test table
create table t1 (id int unsigned not null auto_increment primary key, s smallint unsigned) engine=myisam;
# create fill and test functions
drop procedure if exists fill;
drop function if exists testfunc;
delimiter $$
create procedure fill()
  begin
    declare i int default 0;
    while i < 1000000 do insert ignore t1 (s) values (rand()*256); set i=i+1; end while;
  end;
$$
create function testfunc (s smallint unsigned) returns tinyint unsigned
  begin
    # function is irrelevant; the fact that it is called at all is the problem
    return 10*s;
  end;
$$
delimiter ;
# fill t1 with a million rows
call fill();
# run test with inline function; will take very little time
create table t2 (s smallint unsigned) engine=myisam;
insert t2 select 10*s from t1;
# run test with function call; it will take a very LONG time and eat tons of memory
create table t3 (s smallint unsigned) engine=myisam;
insert t3 as select testfunc(s) from t1;

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Please see bug:

http://bugs.mysql.com/bug.php?id=17260

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (id int unsigned not null auto_increment primary key, s smallint
    -> unsigned) engine=myisam;
Query OK, 0 rows affected (0.09 sec)

mysql> # create fill and test functions
mysql> drop procedure if exists fill;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop function if exists testfunc;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter $$
mysql> create procedure fill()
    ->   begin
    ->     declare i int default 0;
    ->     while i < 1000000 do insert ignore t1 (s) values (rand()*256); set i=i+1;
    -> end while;
    ->   end;
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql> create function testfunc (s smallint unsigned) returns tinyint unsigned
    ->   begin
    ->     # function is irrelevant; the fact that it is called at all is the problem
    ->     return 10*s;
    ->   end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call fill();
Query OK, 1 row affected (1 min 7.34 sec)

mysql> create table t2 (s smallint unsigned) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert t2 select 10*s from t1;
Query OK, 1000000 rows affected (0.61 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> create table t3 (s smallint unsigned) engine=myisam;
Query OK, 0 rows affected (0.08 sec)

mysql> insert t3 select testfunc(s) from t1;
Query OK, 1000000 rows affected, 65535 warnings (50.22 sec)
Records: 1000000  Duplicates: 0  Warnings: 900810

mysql>

I forgot to paste the version:

mysql> show variables like "%version%"\G
*************************** 1. row ***************************
Variable_name: protocol_version
        Value: 10
*************************** 2. row ***************************
Variable_name: version
        Value: 5.0.22
*************************** 3. row ***************************
Variable_name: version_comment
        Value: Source distribution
*************************** 4. row ***************************
Variable_name: version_compile_machine
        Value: ia32
*************************** 5. row ***************************
Variable_name: version_compile_os
        Value: Win32
5 rows in set (0.00 sec)

mysql>