MySQL Bugs: #2460: Crash wih Stored Procedure and UNOIN

Bug #2460	Crash wih Stored Procedure and UNOIN
Submitted:	20 Jan 2004 7:17	Modified:	28 May 2004 19:52
Reporter:	Alexander Keremidarski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:		OS:
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
With Stored procedure as declared in How-To-Repeat 5.0 crashes with backtrace below.

Crash happens only if:
* Query is called from within Stored Procedure 
* Query consists of at least 3 UNION'ed SELECTs
* Queries are complex enough as below.

I failed to repeat this with simpler queries.
Also running query alone outside of SP is not a problem even if it is much more complex. Original report test case creates UNION of 32 queries which run well if not in SP.

In addition calling procedure for first time after restarting mysqld is not a problem. It succeeds, but when called for second time it crashes.

(gdb) bt
#0  0x0815f430 in get_lock_data (thd=0x86b3360, table_ptr=0x86b4b40, count=4, get_old_locks=false, write_lock_used=0x4620da40) at lock.cc:406
#1  0x0815e8fd in mysql_lock_tables(THD*, st_table**, unsigned) (thd=0x86b3360, tables=0x86b4b40, count=4) at lock.cc:93
#2  0x0819f518 in lock_tables(THD*, st_table_list*) (thd=0x86b3360, tables=0x86b49e0) at sql_base.cc:1572
#3  0x0819f44e in open_and_lock_tables(THD*, st_table_list*) (thd=0x86b3360, tables=0x86b49e0) at sql_base.cc:1531
#4  0x081794a6 in mysql_execute_command(THD*) (thd=0x86b3360) at sql_parse.cc:1874
#5  0x08258430 in sp_instr_stmt::exec_stmt(THD*, st_lex*) (this=0x86bc768, thd=0x86b3360, lex=0x86bd3e8) at sp_head.cc:829
#6  0x08258275 in sp_instr_stmt::execute(THD*, unsigned*) (this=0x86bc768, thd=0x8f8f8f8f, nextp=0x8f8f8f8f) at sp_head.cc:768
#7  0x08256825 in sp_head::execute(THD*) (this=0x86bb3e0, thd=0x86b3360) at sp_head.cc:299
#8  0x08256fd8 in sp_head::execute_procedure(THD*, List<Item>*) (this=0x86bb3e0, thd=0x86b3360, args=0x86b366c) at sp_head.cc:484
#9  0x0817e310 in mysql_execute_command(THD*) (thd=0x86b3360) at sql_parse.cc:3477
#10 0x0817fb66 in mysql_parse(THD*, char*, unsigned) (thd=0x86b3360, inBuf=0x86b46c0 "call self_and_up(1)", length=141243248) at sql_parse.cc:4149
#11 0x08178157 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x86b3360, packet=0x86a7a81 "call self_and_up(1)", packet_length=20) at sql_parse.cc:1396
#12 0x08177a74 in do_command(THD*) (thd=0x86b3360) at sql_parse.cc:1226
#13 0x08176f45 in handle_one_connection (arg=0x8f8f8f8f) at sql_parse.cc:991
#14 0x45255484 in start_thread () from /lib/tls/libpthread.so.0
#15 0x450a9147 in clone () from /lib/tls/libc.so.6

The problem occurs at lock.cc:406

static MYSQL_LOCK *get_lock_data(THD *thd, TABLE **table_ptr, uint count,
         bool get_old_locks, TABLE **write_lock_used)
{

...

  for (i=tables=lock_count=0 ; i < count ; i++)
  {
    if (table_ptr[i]->tmp_table != TMP_TABLE)
    {
      tables+=table_ptr[i]->file->lock_count();

 

How to repeat:
drop procedure if exists self_and_up;
delimiter |
create procedure self_and_up (IN v INT)
begin
 
(select n0.id
        from node as n0
        where (
                (n0.id = v)
                )
)
union
(select n0.id
        from node as n0, node as n1
        where (
                (n0.id = n1.rid and n1.id = v)
                )
)
union
(select n0.id
        from node as n0, node as n1, node as n2
        where (
                (n0.id = n1.rid and n1.id = n2.rid and n2.id = v)
                )
);
end |
delimiter ;

call self_and_up(1);
call self_and_up(1);

-- calling procedure once sometimes succeeds

Forgot to include table definition into How-to-repeat section:

CREATE TABLE `node` (
  `id` int(10) unsigned NOT NULL default '0',
  `rid` int(10) unsigned NOT NULL default '0',
  `msg` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `rid` (`rid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

There's another way to cause a crash with a stored procedure and a UNION. 
How to repeat: 
 
mysql> create procedure p4 () begin drop table if exists t1; create table t1 (s1 int); insert 
into t1 select 1 union select 1; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p4()// 
Query OK, 0 rows affected (0.68 sec) 
 
mysql> call p4()// 
ERROR 2013 (HY000): Lost connection to MySQL server during query

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