Bug #320 Crash with stored procedure and derived table

• Submitted: 23 Apr 2003 16:36
• Modified: 23 Aug 2003 12:08
• Reporter: jocelyn fournier (Silver Quality Contributor)
• Status: Closed
• Category: MySQL Server: MyISAM storage engine
• Severity: S1 (Critical)
• Version: 5.0
• OS: Any (all)
• CPU Architecture: Any

[23 Apr 2003 16:36] jocelyn fournier

Description:
MySQL crash if a derived table is used as an argument of a stored procedure.

How to repeat:
create procedure foo1(x int) insert into test.t1 values (x);
call foo1((SELECT 1 FROM (SELECT 1) a));

Stack trace :

0x80af787 handle_segfault + 487
0x82b57d8 pthread_sighandler + 176
0x845ecc5 main_arena + 933
0x80ab1ce mysql_lock_tables(THD*, st_table**, unsigned) + 110
0x80d83e1 lock_tables(THD*, st_table_list*) + 129
0x80d834f open_and_lock_tables(THD*, st_table_list*) + 63
0x80c25e3 mysql_execute_command(THD*) + 18627
0x80c3664 mysql_parse(THD*, char*, unsigned) + 260
0x80bca79 dispatch_command(enum_server_command, THD*, char*, unsigned) + 953
0x80bc667 do_command(THD*) + 135
0x80bbd73 handle_one_connection + 515
0x82b31fa pthread_start_thread + 218
0x82e774a thread_start + 4

[24 Apr 2003 10:14] MySQL Verification Team

There are two ways of doing it.

First one is to add this into sp.cc :

===== sql/sp.cc 1.8 vs edited =====
*** /tmp/sp.cc-1.8-17796        Sun Mar  2 20:17:39 2003
--- edited/sql/sp.cc    Thu Apr 24 18:14:18 2003
***************
*** 105,111 ****
    }
    if (opened)
    {
!     close_thread_tables(thd);
      table= NULL;
    }
  
--- 105,111 ----
    }
    if (opened)
    {
!     close_thread_tables(thd,0,1);
      table= NULL;
    }

But this is not enough. Then, more code has to be added to:

// list creator
bool st_select_lex_unit::create_total_list_n_last_return(THD *thd, st_lex *lex,
							 TABLE_LIST ***result,

in sql_lex.cc.

Particularly here:

    if ((aux= (TABLE_LIST*) sl->table_list.first))
    {

It should be changed that if aux is 0, which is case with SP, that
table_list is allocated and derived table linked in properly..

Second way:

Ban derived tables from SP definitions.

[2 Jun 2003 11:09] MySQL Verification Team

This is fixed in BK 5.0 tree.