MySQL Bugs: #7992: SELECT .. INTO variable .. within Stored Procedure crashes the server

Bug #7992	SELECT .. INTO variable .. within Stored Procedure crashes the server
Submitted:	18 Jan 2005 18:37	Modified:	18 Feb 2005 18:04
Reporter:	Alexander Keremidarski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (any)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When SELECT .. INTO variables appears in Stored Procedure mysqld crashes with the backtrace provided below.

Tested against mysql-5.0 build from Bitkeeper tree
ChangeSet@1.1791, 2005-01-15 10:14:34-08:00, jimw@mysql.com

(gdb) bt
#0  0xffffe410 in ?? ()
#1  0xb275df40 in ?? ()
#2  0x00000006 in ?? ()
#3  0x00006d34 in ?? ()
#4  0x4cc9a955 in raise () from /lib/tls/libc.so.6
#5  0x4cc9c319 in abort () from /lib/tls/libc.so.6
#6  0x4cc93f41 in __assert_fail () from /lib/tls/libc.so.6
#7  0x08198952 in mysql_parse(THD*, char*, unsigned) (thd=0x8bd1e70, inBuf=0x8bed490 "call crash()", length=146611888) at sql_parse.cc:4762
#8  0x0818fe69 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8bd1e70, packet=0x8be5431 "", packet_length=13) at sql_parse.cc:1533
#9  0x0818f73c in do_command(THD*) (thd=0x8bd1e70) at sql_parse.cc:1341
#10 0x0818eaa1 in handle_one_connection (arg=0x0) at sql_parse.cc:1060
#11 0x4cebb3ae in start_thread () from /lib/tls/libpthread.so.0
#12 0x4cd39b6e in clone () from /lib/tls/libc.so.6

How to repeat:
drop table if exists t;
create table t(id int);
insert into t values(1);
drop procedure if exists crash;
delimiter //
create procedure crash()
begin
  declare i int;
  select max(id)+1 into i from t;
end
//

call crash()//

While testing further I got exatcly the same crash with:

create procedure crash2()
begin
   insert into t (id) select max(id)+1 from t;
end
//
call crash2()//

(gdb) bt
#0  0xffffe410 in ?? ()
#1  0xb2778f40 in ?? ()
#2  0x00000006 in ?? ()
#3  0x00000190 in ?? ()
#4  0x4cc9a955 in raise () from /lib/tls/libc.so.6
#5  0x4cc9c319 in abort () from /lib/tls/libc.so.6
#6  0x4cc93f41 in __assert_fail () from /lib/tls/libc.so.6
#7  0x08198952 in mysql_parse(THD*, char*, unsigned) (thd=0x8bd1e70, inBuf=0x8bed490 "call crash2()", length=146611888) at sql_parse.cc:4762
#8  0x0818fe69 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8bd1e70, packet=0x8be5431 "", packet_length=14) at sql_parse.cc:1533
#9  0x0818f73c in do_command(THD*) (thd=0x8bd1e70) at sql_parse.cc:1341
#10 0x0818eaa1 in handle_one_connection (arg=0x0) at sql_parse.cc:1060
#11 0x4cebb3ae in start_thread () from /lib/tls/libpthread.so.0
#12 0x4cd39b6e in clone () from /lib/tls/libc.so.6

ChangeSet
  1.1822 05/01/20 17:04:24 bell@sanja.is.com.ua +3 -0
  fixed using in SP sentences which use Item tree temporary changes rollback (BUG#7992)

Patch ok after adjusting the test case (see email).

The second test case still crashes mysqld built from 
ChangeSet@1.1844, 2005-02-13 20:48:02+02:00, heikki@hundin.mysql.fi

create procedure crash2()
begin
   insert into t (id) select max(id)+1 from t;
end
//
call crash2()//

(gdb) bt
#0  0xffffe410 in ?? ()
#1  0xb2778f30 in ?? ()
#2  0x00000006 in ?? ()
#3  0x00006825 in ?? ()
#4  0x4cc9a955 in raise () from /lib/tls/libc.so.6
#5  0x4cc9c319 in abort () from /lib/tls/libc.so.6
#6  0x4cc93f41 in __assert_fail () from /lib/tls/libc.so.6
#7  0x081ab8da in mysql_parse(THD*, char*, unsigned) (thd=0x8c07fd0, inBuf=0x8c36cf0 "call crash2()", length=146833424) at sql_parse.cc:4983
#8  0x081a2709 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8c07fd0, packet=0x8c2ec91 "call crash2()", packet_length=14) at sql_parse.cc:1675
#9  0x081a1fdc in do_command(THD*) (thd=0x8c07fd0) at sql_parse.cc:1483
#10 0x081a0ffb in handle_one_connection (arg=0x0) at sql_parse.cc:1081
#11 0x4cebb3ae in start_thread () from /lib/tls/libpthread.so.0
#12 0x4cd39b6e in clone () from /lib/tls/libc.so.6

The second test case was modified thus:
create procedure bug7992_2()
  insert into t3 (s1) select max(t4.s1)+1 from t3 as t4;

to avoid some confusion over the multiple references to the same field.
(If this is a bug, it's a different one than this one in any case.)