MySQL Bugs: #10328: stored function used in select from information

Bug #10328	stored function used in select from information_schema table crashes server
Submitted:	3 May 2005 6:05	Modified:	28 Jul 2005 9:13
Reporter:	Guy Harrison	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.4/BK Source	OS:	Windows (Windows XP SP2/Linux Slackware)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
An SQL statement that includes a stored function in the select list and a FROM clause seems to consistently crash the server.

I saw the similar bug that was attributed to the query cache;  however this happens for me even if I use SQL_NO_CACHE

How to repeat:
create function crash_the_server(inv int) RETURNS VARCHAR(20)
BEGIN
	DECLARE r_value VARCHAR(20);
	IF (inv=1) THEN SET r_value='One';
	ELSEIF (inv=2) THEN SET r_value='Two';
	ELSE SET r_value='Three';
	END IF;
	
	RETURN(r_value);
END;

select sql_no_cache crash_the_server(ordinal_position) from information_schema.columns

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.6-beta-debug

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

mysql> delimiter //
mysql> create function crash_the_server(inv int) RETURNS VARCHAR(20)
    -> BEGIN
    -> DECLARE r_value VARCHAR(20);
    -> IF (inv=1) THEN SET r_value='One';
    -> ELSEIF (inv=2) THEN SET r_value='Two';
    -> ELSE SET r_value='Three';
    -> END IF;
    -> 
    -> RETURN(r_value);
    -> END;//
Query OK, 0 rows affected (0.07 sec)

mysql> select sql_no_cache crash_the_server(ordinal_position) from
    -> information_schema.columns//
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

Call stack on windows:

>	mysqld-debug.exe!_NMSG_WRITE(int rterrnum=10)  Line 221	C
 	mysqld-debug.exe!abort()  Line 44 + 0x7	C
 	mysqld-debug.exe!_assert(void * expr=0x0084ac34, void * filename=0x0084ac0c, unsigned int lineno=2200)  Line 267	C
 	mysqld-debug.exe!lock_tables(THD * thd=0x02fed3a0, st_table_list * tables=0x03001ae0, unsigned int count=0)  Line 2200 + 0x32	C++
 	mysqld-debug.exe!open_and_lock_tables(THD * thd=0x02fed3a0, st_table_list * tables=0x03001ae0)  Line 2085 + 0x29	C++
 	mysqld-debug.exe!get_all_tables(THD * thd=0x02fed3a0, st_table_list * tables=0x03004e50, Item * cond=0x00000000)  Line 1977 + 0x10	C++
 	mysqld-debug.exe!get_schema_tables_result(JOIN * join=0x030004d0)  Line 3465 + 0x18	C++
 	mysqld-debug.exe!JOIN::exec()  Line 1273 + 0x1f	C++
 	mysqld-debug.exe!mysql_select(THD * thd=0x02fed3a0, Item * * * rref_pointer_array=0x02fed710, st_table_list * tables=0x03004e50, 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=2695121408, select_result * result=0x030064b0, st_select_lex_unit * unit=0x02fed400, st_select_lex * select_lex=0x02fed608)  Line 2010	C++
 	mysqld-debug.exe!handle_select(THD * thd=0x02fed3a0, st_lex * lex=0x02fed3e8, select_result * result=0x030064b0, unsigned long setup_tables_done_option=0)  Line 254 + 0x83	C++
 	mysqld-debug.exe!mysql_execute_command(THD * thd=0x02fed3a0)  Line 2393 + 0x13	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x02fed3a0, char * inBuf=0x03004528, unsigned int length=86)  Line 5182 + 0x9	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x02fed3a0, char * packet=0x02fe47d1, unsigned int packet_length=87)  Line 1651 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x02fed3a0)  Line 1457 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x02fed3a0)  Line 1114 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x00e4bc70)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x02ff5c88)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3()

Just to complete the error log:

Version: '5.0.6-beta-debug'  socket: ''  port: 3306  Source distribution
Assertion failed: !thd->prelocked_mode || !thd->lex->requires_prelocking(), file C:\mysql-5.0.6-beta\sql\sql_base.cpp, line 2200

I can repeat this crash if and only if I select from an information_schema view.

I can repeat this in the world database:

DELIMITER //

DROP FUNCTION IF EXISTS lang_type //

CREATE FUNCTION lang_type(p_lang VARCHAR(20))
	RETURNS VARCHAR(20)
	DETERMINISTIC NO SQL
BEGIN
		DECLARE l_lang_type VARCHAR(30);
		
		CASE p_lang
			WHEN 'English' THEN 
				SET l_lang_type='ENG';
			WHEN 'French' THEN
				SET l_lang_type='FRE';
			ELSE
				SET l_lang_type='OTH';
		END CASE;
		RETURN(l_lang_type);
END;
//

select CountryCode, lang_type(Language) from world.countrylanguage //

mysql> delimiter //
mysql> select version();
    -> //
+---------------+
| version()     |
+---------------+
| 5.0.6-beta-nt |
+---------------+
1 row in set (0.06 sec)

mysql> source bug10328.sql
ERROR:
DELIMITER must be followed by a 'delimiter' character or string
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 2013 (HY000): Lost connection to MySQL server during query

Hi, Guy!

This last crash you have experienced is a different issue. It is caused by usage of stored function in query reading data from InnoDB table. It was already reported as bug #10015
(see http://bugs.mysql.com/bug.php?id=10015 for more information) and is already fixed in current 5.0 tree. So this fix will be the part of upcoming 5.0.7 release.

Hi!

Since exactly this crash is also described in bug #10055, I will mark
this bug as duplicate.