MySQL Bugs: #10160: Calling user defined function from query hangs server

Bug #10160	Calling user defined function from query hangs server
Submitted:	25 Apr 2005 21:05	Modified:	26 Apr 2005 15:08
Reporter:	Jan Bouwhuis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.4-beta-nt	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When a user defined function is called from a query the MySQL hangs the query and does not respond if the service is stopped.

How to repeat:
Create a schema 'test'

Create a table 'tbltest'

CREATE TABLE `tbltest` (
 `id` int(11) NOT NULL,
 `testfield` varchar(32) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Put data in the table:
INSERT INTO `tbltest` (`id`,`testfield`) VALUES 
 (0,'rec1'),
 (1,'rec2'),
 (2,'rec3'),
 (3,'rec4');

Create the function:

CREATE FUNCTION test (varID INT) RETURNS VARCHAR(255)
BEGIN
  RETURN (SELECT testfield FROM tbltest WHERE ID = varID);
END

When the function is called like:
SELECT test (2);
The correct value is returned.

When the function is called like:
SELECT test(id) as MyValue FROM tbltest;

The query is hanging. Killing the thread is not terminating the process.
When stopping MySQL: net stop MySQL
My SQL is not stopping, a hard 'kill' is needed to stop the service.

Suggested fix:
Calling a user defined function from a query should be possible, and should not hang the server process.

Repeated this on another computer which leaded to a crash of mysqld-nt.exe. Als log as 1 row is returned there is no problem.
E.g. SELECT test(id) FROM tbltest WHERE id=2;
Works fine.
Where: SELECT test(id) FROM tbltest WHERE id=2 or id = 3;
crashes the server process.

I will test with latest 5.0 BK source. Thank you for the bug report.

lock0lock.c

	/* NOTE that we call this function while holding the search
	system latch. To obey the latching order we must NOT reserve the
	kernel mutex here! */

	trx_id = row_get_rec_trx_id(rec, index, offsets);
	
	if (read_view_sees_trx_id(view, trx_id)) {
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
		return(TRUE);
	}

	return(FALSE);

 	mysqld-debug.exe!read_view_sees_trx_id(read_view_struct * view=0x00000000, dulint_struct trx_id={...})  Line 53 + 0x3	C
>	mysqld-debug.exe!lock_clust_rec_cons_read_sees(unsigned char * rec=0x01e6c09b, dict_index_struct * index=0x01d2cda0, const unsigned long * offsets=0x03eee504, read_view_struct * view=0x00000000)  Line 495 + 0x11	C
 	mysqld-debug.exe!row_search_for_mysql(unsigned char * buf=0x02ff5a60, unsigned long mode=1, row_prebuilt_struct * prebuilt=0x01d2dca0, unsigned long match_mode=0, unsigned long direction=1)  Line 3736 + 0x31	C
 	mysqld-debug.exe!ha_innobase::general_fetch(unsigned char * buf=0x02ff5a60, unsigned int direction=1, unsigned int match_mode=0)  Line 3634 + 0x17	C++
 	mysqld-debug.exe!ha_innobase::index_next(unsigned char * buf=0x02ff5a60)  Line 3672	C++
 	mysqld-debug.exe!join_read_next(st_read_record * info=0x03006fb8)  Line 9801 + 0x18	C++
 	mysqld-debug.exe!sub_select(JOIN * join=0x03005e68, st_join_table * join_tab=0x03006f78, int end_of_records=0)  Line 9313 + 0xa	C++
 	mysqld-debug.exe!do_select(JOIN * join=0x03005e68, List<Item> * fields=0x0300b318, st_table * table=0x00000000, Procedure * procedure=0x00000000)  Line 8989 + 0xf	C++
 	mysqld-debug.exe!JOIN::exec()  Line 1644 + 0x19	C++
 	mysqld-debug.exe!mysql_select(THD * thd=0x0300b040, Item * * * rref_pointer_array=0x0300b3b0, st_table_list * tables=0x03005a70, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x00000000, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=2158250496, select_result * result=0x03005e58, st_select_lex_unit * unit=0x0300b0a0, st_select_lex * select_lex=0x0300b2a8)  Line 2064	C++
 	mysqld-debug.exe!handle_select(THD * thd=0x0300b040, st_lex * lex=0x0300b088, select_result * result=0x03005e58, unsigned long setup_tables_done_option=0)  Line 240 + 0x83	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x0300b040)  Line 2387 + 0x13	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x0300b040, char * inBuf=0x03005290, unsigned int length=39)  Line 5172 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0300b040, char * packet=0x02ffd1d1, unsigned int packet_length=41)  Line 1647 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x0300b040)  Line 1453 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x0300b040)  Line 1110 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x02fed1b0)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x00f3fe90)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()

Hi, Jan!

Thank you for your interest in MySQL!

This is duplicate of bug #10015.