MySQL Bugs: #11112: Server crashes on UPDATE with EXISTS in WHERE clause

Bug #11112	Server crashes on UPDATE with EXISTS in WHERE clause
Submitted:	6 Jun 2005 12:29	Modified:	7 Jun 2005 13:36
Reporter:	Sergey Nikitin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.6	OS:	Any (any)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
In some cases server crashes on UPDATE statement with EXISTS subquery in WHERE clause

How to repeat:
mysql> set @@sql_mode='ORACLE';
Query OK, 0 rows affected (0.00 sec)

mysql> create table loca(adrno int not null, lno int not null, primary key (adrno, lno)) engine = innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> create table KNTK(adrno int not null, lno int not null, kno int not null, ident varchar(20), primary key (adrno, lno, kno), foreign key(adrno, lno) references loca(adrno, lno)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into loca values(1, 1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into kntk values(1, 1, 1, null);
Query OK, 1 row affected (0.03 sec)

mysql> UPDATE KNTK SET ident='0' WHERE EXISTS (SELECT * FROM loca l WHERE KNTK.adrno = l.adrno AND KNTK.lno = l.lno and l.adrno = 1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Hello,

Thank you for the report!
I was able reproduce it with 5.0 bk tree (server crashes for MyISAM tables too).

(gdb) bt
#0  Bitmap<64>::merge (this=0xb4, map2=@0xbe3fe908) at sql_bitmap.h:121
#1  0x081f8671 in add_key_field (key_fields=0xbe3fecb4, and_level=0, cond=0x8ee2598, field=0x8ee1fc0, eq_func=true, value=0xbe3feaa4, num_values=1,
    usable_tables=18446744073709551615) at sql_select.cc:2614
#2  0x081f9592 in add_key_fields (key_fields=0xbe3fecb4, and_level=0xbe3fecc4, cond=0x8ee2598, usable_tables=18446744073709551615) at sql_select.cc:2858
#3  0x081f8b0c in add_key_fields (key_fields=0xbe3fecb4, and_level=0xbe3fecc4, cond=0x8eb33c0, usable_tables=18446744073709551615) at sql_select.cc:2756
#4  0x081f9ef6 in update_ref_and_keys (thd=0x8eb84d0, keyuse=0x8eb42a0, join_tab=0x8ee2748, tables=1, cond=0x8eb33c0, cond_equal=0x8eb3444,
    normal_tables=18446744073709551615, select_lex=0x8eb28c0) at sql_select.cc:3063
#5  0x081f7435 in make_join_statistics (join=0x8eb3578, tables=0x0, conds=0x8eb33c0, keyuse_array=0x8eb42a0) at sql_select.cc:2203
#6  0x081f2a59 in JOIN::optimize (this=0x8eb3578) at sql_select.cc:659
#7  0x08176439 in subselect_single_select_engine::exec (this=0x8eb3508) at item_subselect.cc:1444
#8  0x0817309a in Item_subselect::exec (this=0x8eb3480) at item_subselect.cc:204
#9  0x081741f8 in Item_exists_subselect::val_int (this=0x8eb3480) at item_subselect.cc:624
#10 0x08260d15 in SQL_SELECT::skip_record (this=0x8eb4410) at opt_range.h:695
#11 0x082218f7 in mysql_update (thd=0x8eb84d0, table_list=0x8eb2628, fields=@0x8eb876c, values=@0x8eb8974, conds=0x8eb3480, order_num=0, order=0x0,
    limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false) at sql_update.cc:398
#12 0x081bfe64 in mysql_execute_command (thd=0x8eb84d0) at sql_parse.cc:3120
#13 0x081c5e05 in mysql_parse (thd=0x8eb84d0,
    inBuf=0x8eb2550 "UPDATE KNTK SET ident='0' WHERE EXISTS (SELECT * FROM loca l WHERE\nKNTK.adrno = l.adrno AND KNTK.lno = l.lno and l.adrno = 1)",
    length=125) at sql_parse.cc:5243
#14 0x081bc0ae in dispatch_command (command=COM_QUERY, thd=0x8eb84d0,
    packet=0x8ed3d11 "UPDATE KNTK SET ident='0' WHERE EXISTS (SELECT * FROM loca l WHERE\nKNTK.adrno = l.adrno AND KNTK.lno = l.lno and l.adrno = 1)",
    packet_length=126) at sql_parse.cc:1651
#15 0x081bb95e in do_command (thd=0x8eb84d0) at sql_parse.cc:1457
#16 0x081baad8 in handle_one_connection (arg=0x8eb84d0) at sql_parse.cc:1114
#17 0xb7e41f1b in pthread_start_thread () from /lib/libpthread.so.0
#18 0xb7e41f9f in pthread_start_thread_event () from /lib/libpthread.so.0
#19 0xb7d70fda in clone () from /lib/libc.so.6

I failed to reproduce this bug with the current bk 5.0 tree.

I was unable to repeat too:

mysql>  UPDATE KNTK SET ident='0' WHERE EXISTS (SELECT * FROM loca l WHERE
    -> KNTK.adrno = l.adrno AND KNTK.lno = l.lno and l.adrno = 1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0