Bug #28375 mysql crash with subselect and null values
Submitted: 11 May 2007 12:13 Modified: 15 May 2007 2:49
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.37, 5.0.42BK, 5.1.19BK, 6.0.1BK OS:Any
Assigned to: Igor Babaev CPU Architecture:Any
Tags: crash, regression, subquery

[11 May 2007 12:13] Shane Bester
Description:
I got a crash with this stack trace:

mysqld-debug.exe!join_read_always_key_or_null
mysqld-debug.exe!sub_select
mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!subselect_single_select_engine::exec
mysqld-debug.exe!Item_subselect::exec
mysqld-debug.exe!Item_in_subselect::val_bool
mysqld-debug.exe!Item::val_bool_result
mysqld-debug.exe!Item_in_optimizer::val_int
mysqld-debug.exe!Item::val_bool
mysqld-debug.exe!Item_func_not::val_int
mysqld-debug.exe!evaluate_join_record
mysqld-debug.exe!sub_select
mysqld-debug.exe!do_select
mysqld-debug.exe!JOIN::exec
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart

How to repeat:
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
create table t1(id decimal not null primary key)engine=myisam;
create table t2(id varchar(200))engine=myisam;
create table t3(id decimal not null primary key,name varchar(40))engine=myisam;
insert into t1(id) values (null),(234),(345),(456),(567),(537);
insert into t2(id) values (null),('test'),('blue'),('red');
insert into t3(id,name) values (111,'aaa'),(222,'bbb'),(333,'ccc'),(444,'ddd'),(555,'eee');
select count(*) from t2 where t2.id not in(select t1.id from t1,t3 where t3.name='test' and t1.id=t3.id);

Suggested fix:
don't crash
[11 May 2007 12:17] Shane Bester
regression, since 5.0.27 doesn't crash
[11 May 2007 12:18] Valeriy Kravchuk
Same crash on 5.0.42-BK on Linux:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld5.sym 28375.stack
0x820f605 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 691
0x81fd9cd _ZN4JOIN4execEv + 6927
0x8173a5e _ZN30subselect_single_select_engine4execEv + 988
0x816f4ee _ZN14Item_subselect4execEv + 54
0x8170e43 _ZN17Item_in_subselect8val_boolEv + 67
0x811de0c _ZN4Item15val_bool_resultEv + 24
0x8145367 _ZN17Item_in_optimizer7val_intEv + 641
0x810c11e _ZN4Item8val_boolEv + 70
0x8142340 _ZN13Item_func_not7val_intEv + 68
0x820fbe8 _Z20evaluate_join_recordP4JOINP13st_join_tableiPc + 220
0x820fac0 _Z10sub_selectP4JOINP13st_join_tableb + 360
0x820f605 _Z9do_selectP4JOINP4ListI4ItemEP8st_tableP9Procedure + 691
0x81fd9cd _ZN4JOIN4execEv + 6927
0x81fdf1e _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orde
rSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_sel + 652
0x81f853a _Z13handle_selectP3THDP6st_lexP13select_resultm + 342
0x81bd1fd _Z21mysql_execute_commandP3THD + 1709
0x81c4df0 _Z11mysql_parseP3THDPcj + 358
0x81bb5ff _Z16dispatch_command19enum_server_commandP3THDPcj + 1945
0x81bae56 _Z10do_commandP3THD + 534
0x81b9fe0 handle_one_connection + 1046
0x40050aa7 _end + 933076807
0x40247c2e _end + 935137486
[12 May 2007 2:35] 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:

  http://lists.mysql.com/commits/26543

ChangeSet@1.2488, 2007-05-11 19:37:32-07:00, igor@olga.mysql.com +4 -0
  Fixed bug #28375: a query with an NOT IN subquery predicate may cause
  a crash when the left operand of the predicate is evaluated to NULL.
  It happens when the rows from the inner tables (tables from the subquery)
  are accessed by index methods with key values obtained by evaluation of
  the left operand of the subquery predicate. When this predicate is
  evaluated to NULL an alternative access with full table scan is used
  to check whether the result set returned by the subquery is empty or not.
  The crash was due to the fact the info about the access methods used for
  regular key values was not properly restored after a switch back from the
  full scan access method had occurred.
  The patch restores this info properly.
  The same problem existed for queries with IN subquery predicates if they
  were used not at the top level of the queries.
[12 May 2007 2:50] 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:

  http://lists.mysql.com/commits/26544

ChangeSet@1.2488, 2007-05-11 19:31:51-07:00, igor@olga.mysql.com +4 -0
  Fixed bug #28375: a query with an NOT IN subquery predicate may cause
  a crash when the left operand of the predicate is evaluated to NULL.
  It happens when the rows from the inner tables (tables from the subquery)
  are accessed by index methods with key values obtained by evaluation of
  the left operand of the subquery predicate. When this predicate is
  evaluated to NULL an alternative access with full table scan is used
  to check whether the result set returned by the subquery is empty or not.
  The crash was due to the fact the info about the access methods used for
  regular key values was not properly restored after a switch back from the
  full scan access method had occurred.
  The patch restores this info properly.
  The same problem existed for queries with IN subquery predicates if they
  were used not at the top level of the queries.
[13 May 2007 6:16] Bugs System
Pushed into 5.1.19-beta
[13 May 2007 6:19] Bugs System
Pushed into 5.0.42
[15 May 2007 2:49] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.