MySQL Bugs: #12392: Server crashes in complex join query

Bug #12392	Server crashes in complex join query
Submitted:	4 Aug 2005 22:03	Modified:	12 Aug 2005 19:36
Reporter:	Mark Matthews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.11 (and 4.1.12)	OS:	Linux (Fedora Core 3)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The following query crashes the server with the following stacktrace:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8c60f68
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x6e637c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81593cf handle_segfault + 639
0x38f420 (?)
0x810f617 _ZN12Item_func_in7val_intEv + 129
0x810a02f _ZN12cmp_item_row7compareEP8cmp_item + 63
0x810f617 _ZN12Item_func_in7val_intEv + 129
0x81a5e13 _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 93
0x81aabed _Z10sub_selectP4JOINP13st_join_tableb + 127
0x81b0dac _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 574
0x81c0b91 _ZN4JOIN4execEv + 2361
0x81c2a2f _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 1361
0x81c2e5a _Z13handle_selectP3THDP6st_lexP13select_resultm + 252
0x8171dec _Z21mysql_execute_commandP3THD + 14594
0x8177fb5 _Z11mysql_parseP3THDPcj + 529
0x817865e _Z16dispatch_command19enum_server_commandP3THDPcj + 1464
0x817a5de handle_one_connection + 2562
0x7a5b80 (?)
0x616dee (?)
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it

How to repeat:
drop table if exists UCustomer;
drop table if exists UEmployee;
drop table if exists UPerson;

create table UCustomer (person_id bigint not null, name varchar(80) not null, sex char(1) not null, address varchar(255), zip varchar(255), country varchar(255), comments varchar(255), salesperson bigint, primary key (person_id)) type=InnoDB;

create table UEmployee (person_id bigint not null, name varchar(80) not null, sex char(1) not null, address varchar(255), zip varchar(255), country varchar(255), title varchar(20) not null, salary numeric(19,2), manager bigint, primary key (person_id)) type=InnoDB;

create table UPerson (person_id bigint not null, name varchar(80) not null, sex char(1) not null, address varchar(255), zip varchar(255), country varchar(255), primary key (person_id)) type=InnoDB;

alter table UCustomer add index FK62B4BF5398311FBC (salesperson), add constraint FK62B4BF5398311FBC foreign key (salesperson) references UEmployee (person_id);

alter table UEmployee add index FK85B6262346644408 (manager), add constraint FK85B6262346644408 foreign key (manager) references UEmployee (person_id);

SET autocommit=0;

insert into UPerson (name, sex, address, zip, country, person_id) values ('mum', 'F', null, null, null, 1);

insert into UEmployee (name, sex, address, zip, country, title, salary, manager, person_id) values ('Mark', 'M', 'buckhead', '30305', 'USA', 'internal sales', null, null, 2);

insert into UCustomer (name, sex, address, zip, country, comments, salesperson, person_id) values ('Joe', 'M', 'San Francisco', 'XXXXX', 'USA', 'Very demanding', 2, 3);

select this_.person_id as person1_16_0_, this_.name as name16_0_, this_.sex as sex16_0_, this_.address as address16_0_, this_.zip as zip16_0_, this_.country as country16_0_, this_.title as title17_0_, this_.salary as salary17_0_, this_.manager as manager17_0_, this_.comments as comments18_0_, this_.salesperson as salesper2_18_0_, this_.clazz_ as clazz_0_ from ( select null as comments, sex, country, null as title, person_id, address, null as manager, zip, name, null as salesperson, null as salary, 0 as clazz_ from UPerson union select null as comments, sex, country, title, person_id, address, manager, zip, name, null as salesperson, salary, 1 as clazz_ from UEmployee union select comments, sex, country, null as title, person_id, address, null as manager, zip, name, salesperson, null as salary, 2 as clazz_ from UCustomer ) this_ where (this_.address, this_.zip, this_.country) in (('buckhead', 'San Francisco', '30306'), ('XXXXX', 'USA', 'USA'));

Thank you for the bug report.

050804 19:14:09 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.12-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132370864 (LWP 18826)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132370864 (LWP 18826)]
0x081522aa in String::length (this=0x0) at sql_string.h:82
82        inline uint32 length() const { return str_length;}
(gdb) backtrace full
#0  0x081522aa in String::length (this=0x0) at sql_string.h:82
No locals.
#1  0x081cbef4 in sortcmp (s=0x8e3eb30, t=0x0, cs=0x8791e00) at sql_string.cc:722
No locals.
#2  0x081839dc in cmp_item_sort_string_in_static::compare (this=0x8e6ce40, c=0x8e6cc70) at item_cmpfunc.h:811
        cmp = (cmp_item_string *) 0x8e6cc70
#3  0x0817c878 in cmp_item_row::compare (this=0x8e6cc4c, c=0x8e6cc24) at item_cmpfunc.cc:2116
        res = 1132365960
        i = 0
        cmp = (cmp_item_row *) 0x8e6cc24
#4  0x0817b201 in cmp_row (cmp_arg=0x0, a=0x8e6cc4c, b=0x8e6cc24) at item_cmpfunc.cc:1805
No locals.
#5  0x0817b2c5 in in_vector::find (this=0x8e6cc08, item=0x8e3e800) at item_cmpfunc.cc:1833
        mid = 1
        res = 72
        result = (byte *) 0x8e6cc24 "H×Z\bà\ry\b`Ìæ\b\003"
        start = 0
        end = 1
#6  0x0817ce59 in Item_func_in::val_int (this=0x8e3edd0) at item_cmpfunc.cc:2263
<cut>

Crashes also with MyISAM type tables.

We can easily get a crash with the following example:

mysql> CREATE TABLE t1 (a char(5), b char(5));
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb'));
ERROR 2013 (HY000): Lost connection to MySQL server during query

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:

  http://lists.mysql.com/internals/28042

ChangeSet
  1.2367 05/08/08 16:51:12 igor@rurik.mysql.com +3 -0
  subselect.test, subselect.result:
    Added a test case for bug #12392.
  item_cmpfunc.cc:
    Fixed bug #12392.
    Missing handling of rows containing NULL components
    when evaluating IN predicates caused a crash.

The fix will appear in 4.1.14 and 5.0.12.

Noted in 4.1.14, 5.0.12 changelogs.