MySQL Bugs: #8137: An existing view with function crashes the server on connection

Bug #8137	An existing view with function crashes the server on connection
Submitted:	25 Jan 2005 19:29	Modified:	22 Jun 2005 9:53
Reporter:	Per-Erik Martin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (Any)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
Creating a view that's using a stored function, and using it, works.
But at the next connection with the 'mysql' the server crashes.

How to repeat:
% mysql test
...
mysql> create function f5 () returns int return 5;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select f5 ();
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
% mysql test

Suggested fix:
Not a fix, but workaround: Start 'mysql' with the --no-auto-rehash (or -A) option.

(gdb) bt
#0  0xb7ceea21 in kill () from /lib/libc.so.6
#1  0xb7e49051 in pthread_kill () from /lib/libpthread.so.0
#2  0xb7e4942b in raise () from /lib/libpthread.so.0
#3  0xb7cee7b4 in raise () from /lib/libc.so.6
#4  0xb7cefc7d in abort () from /lib/libc.so.6
#5  0xb7ce7def in __assert_fail () from /lib/libc.so.6
#6  0x081a52f4 in open_ltable (thd=0x8cdcbd0, table_list=0xbe5ff074, lock_type=TL_READ) at sql_base.cc:1804
#7  0x082839f8 in db_find_routine_aux (thd=0x8cdcbd0, type=1, name=0x8d063c8, ltype=TL_READ, tablep=0xbe5ff2a4,
    opened=0xbe5ff2ab) at sp.cc:113
#8  0x08283b98 in db_find_routine (thd=0x8cdcbd0, type=1, name=0x8d063c8, sphp=0xbe5ff3f8) at sp.cc:157
#9  0x08285bca in sp_find_function (thd=0x8cdcbd0, name=0x8d063c8) at sp.cc:865
#10 0x0811e30c in Item_func_sp::fix_length_and_dec (this=0x8d06400) at item_func.cc:3634
#11 0x081169ca in Item_func::fix_fields (this=0x8d06400, thd=0x8cdcbd0, tables=0x0, ref=0x8d064a0) at item_func.cc:324
#12 0x081a6b7b in setup_fields (thd=0x8cdcbd0, ref_pointer_array=0x8d074b0, tables=0x0, fields=@0x8d05ed8,
    set_query_id=true, sum_func_list=0x8d073e8, allow_sum_func=true) at sql_base.cc:2765
#13 0x081adaca in JOIN::prepare (this=0x8d066b8, rref_pointer_array=0x8d05f90, tables_init=0x0, wild_num=0, conds_init=0x0,
    og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8d05e6c,
    unit_arg=0x8d05cac) at sql_select.cc:331
#14 0x08268ead in st_select_lex_unit::prepare (this=0x8d05cac, thd_arg=0x8cdcbd0, sel_result=0x8d065e8, additional_options=0)
    at sql_union.cc:243
#15 0x0826a6c5 in mysql_derived_prepare (thd=0x8cdcbd0, lex=0x8cdcc10, orig_table_list=0xbe5ff844) at sql_derived.cc:118
#16 0x0826a5d6 in mysql_handle_derived (lex=0x8cdcc10,
    processor=0x826a618 <mysql_derived_prepare(THD *, st_lex *, st_table_list *)>) at sql_derived.cc:57
#17 0x081a546a in open_and_lock_tables (thd=0x8cdcbd0, tables=0xbe5ff844) at sql_base.cc:1867
#18 0x082420a0 in mysqld_list_fields (thd=0x8cdcbd0, table_list=0xbe5ff844, wild=0x8d05c50 "") at sql_show.cc:541
#19 0x081821c4 in dispatch_command (command=COM_FIELD_LIST, thd=0x8cdcbd0, packet=0x8cfdbec "", packet_length=4)
    at sql_parse.cc:1636
#20 0x08181798 in do_command (thd=0x8cdcbd0) at sql_parse.cc:1338
#21 0x08180c2a in handle_one_connection (arg=0x8cdcbd0) at sql_parse.cc:1057
#22 0xb7e4614b in pthread_start_thread () from /lib/libpthread.so.0
#23 0xb7e461df in pthread_start_thread_event () from /lib/libpthread.so.0
#24 0xb7d7950a in clone () from /lib/libc.so.6

Since selecting from information_schema.tables performs some of the same steps,
the following will also cause a crash:

create function ff4 () returns int return 5;
create view vf4 as select ff4();
select count(*) from information_schema.tables;

Thank you for bugreport!
It looks like this bug is already fixed in current 5.0 repository.
(I can't repeat both test suites)