Bug #5963 Stored procedure crash if variable = subquery
Submitted: 7 Oct 2004 20:58 Modified: 16 May 2005 3:06
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[7 Oct 2004 20:58] Peter Gulutzan
A stored procedure which contains an assignment like "variable=(subquery)" will crash if I 
call it twice, the first time while the subquery has zero rows, the second time while the 
subquery has one row. 

How to repeat:
mysql> delimiter // 
mysql> create procedure p244 () begin declare v int; set v = (select s1 from t244); select 
v; end;// 
Query OK, 0 rows affected (0.00 sec) 
mysql> create table t244 (s1 int);// 
Query OK, 0 rows affected (0.28 sec) 
mysql> call p244();// 
| v    | 
| NULL | 
1 row in set (0.00 sec) 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into t244 values (5);// 
Query OK, 1 row affected (0.00 sec) 
mysql> call p244()// 
ERROR 2013 (HY000): Lost connection to MySQL server during query
[8 Oct 2004 12:03] MySQL Verification Team
Verified with 5.0.2-alpha-debug-log.
Test case:
delimiter // 
create procedure p244 () begin declare v int; set v = (select s1 from t244);
select v;
create table t244 (s1 int);// 
call p244();// 
insert into t244 values (5);// 
call p244()//

Back trace:
(gdb) bt
#0  0x40254a21 in kill () from /lib/libc.so.6
#1  0x40177051 in pthread_kill () from /lib/libpthread.so.0
#2  0x4017742b in raise () from /lib/libpthread.so.0
#3  0x402547b4 in raise () from /lib/libc.so.6
#4  0x40255c7d in abort () from /lib/libc.so.6
#5  0x4024ddef in __assert_fail () from /lib/libc.so.6
#6  0x08144d3c in Field_long::val_int (this=0x8797e90) at field.cc:1946
#7  0x080fbec3 in Item_field::val_int_result (this=0x87afed8) at item.cc:602
#8  0x08100276 in Item_cache_int::store (this=0x87ab818, item=0x87afed8) at item.cc:2724
#9  0x0813b466 in Item_singlerow_subselect::store (this=0x87b00b8, i=0, item=0x87afed8)
    at item_subselect.cc:394
#10 0x081519de in select_singlerow_subselect::send_data (this=0x87b0130, items=@0x87afc44)
    at sql_class.cc:1233
#11 0x081ad925 in end_send (join=0x87aaa28, join_tab=0x87abc1c, end_of_records=false)
    at sql_select.cc:8438
#12 0x081abe24 in do_select (join=0x87aaa28, fields=0x87afc44, table=0x0, procedure=0x0)
    at sql_select.cc:7489
#13 0x0819f35c in JOIN::exec (this=0x87aaa28) at sql_select.cc:1540
#14 0x0813e032 in subselect_single_select_engine::exec (this=0x87b0148) at item_subselect.cc:1235
#15 0x0813abc0 in Item_subselect::exec (this=0x87b00b8) at item_subselect.cc:206
#16 0x0813b64a in Item_singlerow_subselect::val_int (this=0x87b00b8) at item_subselect.cc:466
#17 0x0825a8d6 in sp_eval_func_item (thd=0x879df98, it=0x87b00b8, type=MYSQL_TYPE_LONG)
    at sp_head.cc:121
#18 0x0825de13 in sp_instr_set::execute (this=0x87b0170, thd=0x879df98, nextp=0xbe5ff1d0)
    at sp_head.cc:1236
#19 0x0825ba60 in sp_head::execute (this=0x87af948, thd=0x879df98) at sp_head.cc:480
#20 0x0825c1f9 in sp_head::execute_procedure (this=0x87af948, thd=0x879df98, args=0x879e3e4)
    at sp_head.cc:695
#21 0x08175d22 in mysql_execute_command (thd=0x879df98) at sql_parse.cc:3740
#22 0x08177849 in mysql_parse (thd=0x879df98, inBuf=0x87aa8c8 "call p244()", length=11)
    at sql_parse.cc:4562
#23 0x081708e2 in dispatch_command (command=COM_QUERY, thd=0x879df98,
    packet=0x87954b1 "call p244()", packet_length=12) at sql_parse.cc:1517
#24 0x081701c7 in do_command (thd=0x879df98) at sql_parse.cc:1297
#25 0x0816f72c in handle_one_connection (arg=0x879df98) at sql_parse.cc:1033
#26 0x4017414b in pthread_start_thread () from /lib/libpthread.so.0
#27 0x401741df in pthread_start_thread_event () from /lib/libpthread.so.0
#28 0x402df50a in clone () from /lib/libc.so.6
[16 Dec 2004 21:16] Peter Gulutzan
Here is another way to crash the second time that I call a procedure with a subquery: 
CREATE PROCEDURE fk_insert (cfk_value INT) 
  IF cfk_value IN (SELECT cpk FROM tpk) THEN 
    SET @x = 5; 
  END IF; 
CREATE TABLE tpk (cpk INT);// 
CREATE TABLE tfk (cfk INT);// 
CALL fk_insert(1)// 
CALL fk_insert(1);//
[17 Dec 2004 22:15] Sergey Petrunya
Marking BUG#7394 as duplicate of this bug. We'll need to check if the fix fixes that too.
[19 Apr 2005 18:11] Dmitry Lenev
Hi, Peter!

Thanks to the recent bugfixes your first example is no longer crashes server and returns expected results. Unfortunately your second example still causes server to crash.

Stack trace follows:

Version: '5.0.5-beta-debug'  socket: '/home/dlenev/mysql_data/mysql.sock'  port: 3306  Source distribution
[New Thread 163851 (LWP 13097)]
mysqld: item.cc:3905: virtual bool Item_ref::fix_fields(THD*, TABLE_LIST*, Item**): Assertion `*ref' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 163851 (LWP 13097)]
0x4012bdd1 in kill () from /lib/libc.so.6
(gdb) bt
#0  0x4012bdd1 in kill () from /lib/libc.so.6
#1  0x4004fe91 in pthread_kill () from /lib/libpthread.so.0
#2  0x4005026b in raise () from /lib/libpthread.so.0
#3  0x4012bb64 in raise () from /lib/libc.so.6
#4  0x4012d058 in abort () from /lib/libc.so.6
#5  0x4012523f in __assert_fail () from /lib/libc.so.6
#6  0x0810f12e in Item_ref::fix_fields (this=0x8cd2700, thd=0x8c72d48, tables=0x8cd1d00, reference=0x8cd2804) at item.cc:3905
#7  0x08125ac1 in Item_func::fix_fields (this=0x8cd27c0, thd=0x8c72d48, tables=0x8cd1d00, ref=0x8caa57c) at item_func.cc:314
#8  0x08141164 in Item_cond::fix_fields (this=0x8caa4f8, thd=0x8c72d48, tables=0x8cd1d00, ref=0x8cab300) at item_cmpfunc.cc:2327
#9  0x081e0ddb in setup_conds (thd=0x8c72d48, tables=0x8cd1d00, leaves=0x8cd1d00, conds=0x8cab300) at sql_base.cc:3596
#10 0x0820a431 in setup_without_group (thd=0x8c72d48, ref_pointer_array=0x8cd2618, tables=0x8cd1d00, leaves=0x8cd1d00, fields=@0x8cd1974, 
    all_fields=@0x8cab298, conds=0x8cab300, order=0x0, group=0x0, hidden_group_fields=0x8cab27e) at sql_select.cc:273
#11 0x081e8656 in JOIN::prepare (this=0x8caa588, rref_pointer_array=0x8cd1a0c, tables_init=0x8cd1d00, wild_num=0, conds_init=0x8caa4f8, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x8cd2870, proc_param_init=0x0, select_lex_arg=0x8cd1908, unit_arg=0x8cd1a40) at sql_select.cc:329
#12 0x0816749c in subselect_single_select_engine::prepare (this=0x8cd1ef0) at item_subselect.cc:1355
#13 0x08163d07 in Item_subselect::fix_fields (this=0x8cd1e58, thd_param=0x8c72d48, tables=0x0, ref=0xbe5feed8) at item_subselect.cc:144
#14 0x082db56b in sp_eval_func_item (thd=0x8c72d48, it=0x8cd1e58, type=MYSQL_TYPE_TINY) at sp_head.cc:110
#15 0x082dfce3 in sp_instr_jump_if_not::exec_core (this=0x8cd1f40, thd=0x8c72d48, nextp=0xbe5feff4) at sp_head.cc:1598
#16 0x082df1e0 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8cd1f60, thd=0x8c72d48, nextp=0xbe5feff4, open_tables=true, instr=0x8cd1f40)
    at sp_head.cc:1306
#17 0x082dfcbd in sp_instr_jump_if_not::execute (this=0x8cd1f40, thd=0x8c72d48, nextp=0xbe5feff4) at sp_head.cc:1588
#18 0x082dd057 in sp_head::execute (this=0x8cd15a0, thd=0x8c72d48) at sp_head.cc:558
#19 0x082dda74 in sp_head::execute_procedure (this=0x8cd15a0, thd=0x8c72d48, args=0x8c73198) at sp_head.cc:791
#20 0x081b9fdf in mysql_execute_command (thd=0x8c72d48) at sql_parse.cc:4094
#21 0x081bcf39 in mysql_parse (thd=0x8c72d48, inBuf=0x8caa168 "CALL fk_insert(1)", length=17) at sql_parse.cc:5176
#22 0x081b2e1b in dispatch_command (command=COM_QUERY, thd=0x8c72d48, packet=0x8ca2109 "CALL fk_insert(1)", packet_length=18) at sql_parse.cc:1651
#23 0x081b2661 in do_command (thd=0x8c72d48) at sql_parse.cc:1457
#24 0x081b17f0 in handle_one_connection (arg=0x8c72d48) at sql_parse.cc:1114
#25 0x4004d0b1 in pthread_start_thread () from /lib/libpthread.so.0
#26 0x4004d12f in pthread_start_thread_event () from /lib/libpthread.so.0
#27 0x401b68fa in clone () from /lib/libc.so.6
[7 May 2005 0:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[7 May 2005 0:14] Oleksandr Byelkin
  1.1923 05/05/07 03:07:36 bell@book.sanja.is.com.ua +8 -0
  Item::fix_field need correct pointer on item reference to chnge it if itis need, so support of correct item address added to SP commands (BUG#5963)
  some optimisation of IF/NOT IF ptomised to Pem
[8 May 2005 23:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[14 May 2005 7:23] Oleksandr Byelkin
pushed to 5.0.6
[16 May 2005 3:06] Paul DuBois
Noted in 5.0.6 changelog.