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
Triage: D1 (Critical)

[16 Apr 2008 10:33] Shane Bester
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) ;
[16 Apr 2008 13:29] Valeriy Kravchuk
Thank you for a bug report. Verified just as described. This is a regression comparing to 5.1.x.
[23 Apr 2008 20:21] Sergey Petrunya
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
[23 Apr 2008 22:02] 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/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)
[28 May 2008 10:01] Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 18:20] Paul Dubois
Noted in 6.0.6 changelog.

A server crash could occur during the cleanup phase of subquery
execution.
[16 Aug 2010 6:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:18] Bugs System
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)
[20 Nov 2010 18:20] Paul Dubois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:06] Paul Dubois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.