MySQL Bugs: #36128: not in subquery causes crash in cleanup..

Bug #36128	not in subquery causes crash in cleanup..
Submitted:	16 Apr 2008 10:33	Modified:	20 Nov 2010 18:20
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	6.0.4-nt, 6.0.5-bk	OS:	Any
Assigned to:	Sergey Petrunya	CPU Architecture:	Any
Tags:	regression

Description:
I can only repeat this on 6.0 tree.  5.1.x didn't crash.

Version: '6.0.5-alpha-debug'  socket: '/tmp/mysql.sock'  port: 3307  yes
/mysqld(handle_segfault)
[0x158420]
mysqld(JOIN::cleanup
mysqld(JOIN::destroy
mysqld(JOIN::destroy
mysqld(st_select_lex::cleanup
mysqld(st_select_lex_unit::cleanup
mysqld(st_select_lex::cleanup
mysqld(mysql_select
mysqld(handle_select
mysqld[0x82262d7
mysqld(mysql_execute_command
mysqld(mysql_parse
mysqld(dispatch_command
mysqld(do_command
mysqld(handle_one_connection

How to repeat:
drop table if exists t1; 
create table t1(a int,b int,key(a),key(b))engine=innodb; 
insert into t1 values (1,1),(2,2),(3,3);
select 1 from t1 where t1.a not in (select 1 from t1 where t1.a in (select 1 from t1) group by  t1.b) ;

Thank you for a bug report. Verified just as described. This is a regression comparing to 5.1.x.

This subquery is affected by fix for BUG#36137. After fix for BUG#36137 is applied, I get this crash:

  Program received signal SIGSEGV, Segmentation fault.
  0x08446930 in handler::ha_thd (this=0x8f8f8f8f) at handler.cc:1969
(gdb) wher
  #0  0x08446930 in handler::ha_thd (this=0x8f8f8f8f) at handler.cc:1969
  #1  0x0844ee6d in handler::mark_trx_read_write (this=0x8f8f8f8f) at handler.cc:2974
  #2  0x0844705d in handler::ha_delete_all_rows (this=0x8f8f8f8f) at handler.cc:3039
  #3  0x08391eb3 in do_sj_reset (sj_tbl=0x93a7cf8) at sql_select.cc:13481
  #4  0x0839440c in sub_select (join=0x93cc6e8, join_tab=0x93a95d0, end_of_records=false) at sql_select.cc:13342
  #5  0x0839b916 in do_select (join=0x93cc6e8, fields=0x0, table=0x939d140, procedure=0x0) at sql_select.cc:13124
  #6  0x083b59ad in JOIN::exec (this=0x93a7d70) at sql_select.cc:2348
  #7  0x082cd25b in subselect_single_select_engine::exec (this=0x93a1228) at item_subselect.cc:2277
  #8  0x082c9ba3 in Item_subselect::exec (this=0x93a1168) at item_subselect.cc:280
  #9  0x082d1a4b in Item_in_subselect::exec (this=0x93a1168) at item_subselect.cc:330
  #10 0x082ca7b0 in Item_in_subselect::val_bool (this=0x93a1168) at item_subselect.cc:899
  #11 0x082633b8 in Item::val_bool_result (this=0x93a1168) at ../item.h:742
  #12 0x08292933 in Item_in_optimizer::val_int (this=0x93a14d8) at item_cmpfunc.cc:1596
  #13 0x082542fe in Item::val_bool (this=0x93a14d8) at item.cc:184
  #14 0x08292b45 in Item_func_not::val_int (this=0x93a1250) at item_cmpfunc.cc:275
  #15 0x08394089 in evaluate_join_record (join=0x93a5d88, join_tab=0x93a7178, error=0) at sql_select.cc:13511
  #16 0x083944f8 in sub_select (join=0x93a5d88, join_tab=0x93a7178, end_of_records=false) at sql_select.cc:13384
  #17 0x0839b916 in do_select (join=0x93a5d88, fields=0x93617c8, table=0x0, procedure=0x0) at sql_select.cc:13124
  #18 0x083b7119 in JOIN::exec (this=0x93a5d88) at sql_select.cc:2747
  #19 0x083b236c in mysql_select (thd=0x9360298, rref_pointer_array=0x9361838, tables=0x939fee0, wild_num=0, fields=@0x93617c8, conds=0x93a1250, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x93a1338, unit=0x93614ac, select_lex=0x9361734) at sql_select.cc:2935
  #20 0x083b7440 in handle_select (thd=0x9360298, lex=0x9361450, result=0x93a1338, setup_tables_done_option=0) at sql_select.cc:289
  #21 0x0832a67b in execute_sqlcom_select (thd=0x9360298, all_tables=0x939fee0) at sql_parse.cc:4806
  #22 0x0832bb3a in mysql_execute_command (thd=0x9360298) at sql_parse.cc:1994
  #23 0x083349b0 in mysql_parse (thd=0x9360298, inBuf=0x939fd38 "select 1 from t1 where t1.a not in (select 1 from t1 where t1.a in (select 1 from t1) group by  t1.b)", length=101, found_semicolon=0xad191260) at sql_parse.cc:5767

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/45923

ChangeSet@1.2625, 2008-04-24 02:01:12+04:00, sergefp@mysql.com +3 -0
  BUG#36128: not in subquery causes crash in cleanup..
  - Handle temporary tables created by NL-semijoin runtime in the same way
    as grouping/sorting temporary tables are handled:
    = delete temptable contents in JOIN::reinit()
    = delete the temptables in JOIN::destroy() 
  (the bug was that we would destroy the temptable after the first join 
   execution and then crash on the next join execution)

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

A server crash could occur during the cleanup phase of subquery
execution.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.