MySQL Bugs: #2448: SELECT query crashes mysql

Bug #2448	SELECT query crashes mysql
Submitted:	19 Jan 2004 13:44	Modified:	4 Feb 2004 0:37
Reporter:	Corin Langosch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.17	OS:	Linux (debian linux 2.4.23 686)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
the following select in "how to repeat" or even when using explain select causes mysql to crash.

here's the log output:
-----
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.17-standard'  socket: '/tmp/mysql.sock'  port: 3306
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=16384
read_buffer_size=131072
max_used_connections=0
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 = 19215 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x84567b0
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=0xbff5de28, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8070610
0x8289628
0x80be2e8
0x80be00b
0x80bd682
0x80bdd6a
0x80bca7e
0x80bbc4c
0x80bbba4
0x80bb85a
0x80954b1
0x8095f06
0x8094037
0x80937e6
0x807afaa
0x807eb8a
0x807a0e3
0x8079b3d
0x8079329
0x8286ddc
0x82bc5ba
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/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
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8458278 = EXPLAIN  SELECT  *
FROM user
LEFT  JOIN user_verify AS uv ON ( uv.owner_id = user.id )
LEFT  JOIN friends ON ( friends.owner_id =132270 AND friend_id = user.id )
WHERE is_online =1 AND ( ( sex =1 AND age
BETWEEN 14  AND 21  ) OR ( sex =2 AND age
BETWEEN 16  AND 18  ) OR ( sex =3 AND age
BETWEEN 15  AND 19  ) )
ORDER  BY age
thd->thread_id=8
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
-----

this is our test machine. not under heavy load, mysql restartet and all tables checked with 'repair table' before the error crashes mysql.

How to repeat:
CREATE TABLE friends (
  id int(10) unsigned NOT NULL auto_increment,
  owner_id int(10) unsigned NOT NULL default '0',
  friend_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY combo (owner_id,friend_id),
  KEY friend_id (friend_id)
) TYPE=MyISAM;
CREATE TABLE user (
  id int(10) unsigned NOT NULL auto_increment,
  age tinyint(3) unsigned NOT NULL default '0',
  sex tinyint(3) unsigned NOT NULL default '0',
  is_online tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY age (age),
  KEY sex (sex),
  KEY online_age_sex (is_online,age,sex)
) TYPE=MyISAM;
INSERT INTO user VALUES (87136, 47, 1, 1);
INSERT INTO user VALUES (87137, 70, 1, 1);
INSERT INTO user VALUES (87140, 50, 1, 0);
CREATE TABLE user_verify (
  id int(10) unsigned NOT NULL auto_increment,
  owner_id int(10) unsigned NOT NULL default '0',
  active tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY combo (owner_id,active)
) TYPE=MyISAM;

EXPLAIN SELECT * 
FROM user
LEFT JOIN user_verify AS uv ON ( uv.owner_id = user.id ) 
LEFT JOIN friends ON ( friends.owner_id =132270 AND friend_id = user.id ) 
WHERE is_online =1 AND (
(
sex =1 AND age
BETWEEN 14 AND 21 
) OR (
sex =2 AND age
BETWEEN 16 AND 18 
) OR (
sex =3 AND age
BETWEEN 15 AND 19 
)
)
ORDER BY age;

Suggested fix:
none ;(

Verified against 4.0.18 using the CREATE/SELECT statements supplied; thank you.

Crash occurs with or without EXPLAIN, producing the same stack trace:

0x80fee63 handle_segfault + 645
0xffffe420 _end + -137828520
(nil)
0x815b0f7 _Z6key_orP7SEL_ARGS0_ + 2605
0x815aebc _Z6key_orP7SEL_ARGS0_ + 2034
0x815a1cf _Z7tree_orP13st_qsel_paramP8SEL_TREES2_ + 121
0x81595af _Z11get_mm_treeP13st_qsel_paramP4Item + 1029
0x8159607 _Z11get_mm_treeP13st_qsel_paramP4Item + 1117
0x8158cd1 _ZN10SQL_SELECT17test_quick_selectEP3THDmmmb + 729
0x8137bcb _Z22get_quick_record_countP3THDP10SQL_SELECTP8st_tablemm + 77
0x812c360 _Z20make_join_statisticsP4JOINP13st_table_listP4ItemP16st_dynamic_arr$0x812a17a _Z12mysql_selectP3THDP13st_table_listR4ListI4ItemEPS4_P8st_orderS9_S7$0x8129bc5 _Z13handle_selectP3THDP6st_lexP13select_result + 169
0x810be13 _Z21mysql_execute_commandv + 781
0x81109e0 _Z11mysql_parseP3THDPcj + 334
0x810ae3f _Z16dispatch_command19enum_server_commandP3THDPcj + 1167
0x810a965 _Z10do_commandP3THD + 125
0x810a21f handle_one_connection + 855
0x40158748 _end + 937331328
0x4035ec4a _end + 939454338

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

Additional info:

the minimal test case was:

CREATE TABLE user ( age int not null, sex int not null, KEY online_age_sex (age,sex) );

--                        age | sex
------------------------------+------
INSERT INTO user VALUES ( 47,    1  );
INSERT INTO user VALUES ( 70,    1  );

SELECT * FROM user
WHERE
(
    ( sex =1 AND age BETWEEN 14 AND 21 ) OR
    ( sex =2 AND age BETWEEN 16 AND 18 ) OR
    ( sex =3 AND age BETWEEN 15 AND 19 )
);

I'm sorry, previous modification (closing) was wrong.. 
so as fix isn't approved yet

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

Additional info:

The fix for this will be in 4.0.18 and 4.1.2