MySQL Bugs: #46797: Crash in fix_semijoin_strategies_for_picked_join

Bug #46797	Crash in fix_semijoin_strategies_for_picked_join_order with semijoin=on
Submitted:	18 Aug 2009 22:04	Modified:	17 Nov 2009 21:15
Reporter:	Patrick Crews	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any
Tags:	crashing bug, optimizer_switch, outerjoin, semijoin, subquery

Description:
There is a crashing bug in azalea with semijoin=on:

SELECT `varchar_key`  
FROM C  
WHERE (  
SELECT `varchar_key`  
FROM C  
WHERE ( `varchar_key`  , `varchar_key`  )  IN (  
SELECT CHILD_SUBQUERY1_t1 .`varchar_key`  , CHILD_SUBQUERY1_t2 .`varchar_key`  
FROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN D CHILD_SUBQUERY1_t2  ON CHILD_SUBQUERY1_t1 .`varchar_key`  )  )   ;

Causes the following crash (full output attached as separate file)
# 17:19:56 Thread 11 (core thread 10):
# 17:19:56 #0  0x90f03402 in __assert_rtn ()
# 17:19:56 #1  0x005aaf04 in my_write_core (sig=11) at stacktrace.c:309
# 17:19:56 #2  0x000fcdd0 in handle_segfault (sig=11) at mysqld.cc:2738
# 17:19:56 #3  <signal handler called>
# 17:19:56 #4  0x0018dd7e in fix_semijoin_strategies_for_picked_join_order (join=0x2b58028) at sql_select.cc:7870
# 17:19:56 #5  0x0018f6dd in get_best_combination (join=0x2b58028) at sql_select.cc:8057
# 17:19:56 #6  0x001b3673 in make_join_statistics (join=0x2b58028, tables_arg=0x12e71c0, conds=0xdfc5f8, keyuse_array=0x2b5cd54) at sql_select.cc:4475
# 17:19:56 #7  0x001b41a7 in JOIN::optimize (this=0x2b58028) at sql_select.cc:1609
# 17:19:56 #8  0x000ac78a in subselect_single_select_engine::exec (this=0x12d5640) at item_subselect.cc:2233
# 17:19:56 #9  0x000a6f72 in Item_subselect::exec (this=0x12d55a8) at item_subselect.cc:285
# 17:19:56 #10 0x000a892f in Item_exists_subselect::val_int (this=0x12d55a8) at item_subselect.cc:787
# 17:19:56 #11 0x00047848 in eval_const_cond (cond=0x12d55a8) at item_func.cc:64
# 17:19:56 #12 0x0019990a in remove_eq_conds (thd=0x12ca018, cond=0x12d55a8, cond_value=0xb0510e74) at sql_select.cc:13601
# 17:19:56 #13 0x0019934e in remove_eq_conds (thd=0x12ca018, cond=0x12510e0, cond_value=0x19fed68) at sql_select.cc:13472
# 17:19:56 #14 0x00199ba7 in optimize_cond (join=0x19fa028, conds=0x12510e0, join_list=0x12cb4e0, build_equalities=true, cond_value=0x19fed68) at sql_select.cc:13439
# 17:19:56 #15 0x001b3aa0 in JOIN::optimize (this=0x19fa028) at sql_select.cc:1482
# 17:19:56 #16 0x001bc323 in mysql_select (thd=0x12ca018, rref_pointer_array=0x12cb520, tables=0x12d2fd0, wild_num=0, fields=@0x12cb4b0, conds=0x12d5908, og_num=3, order=0x12d5e68, group=0x12d5a60, having=0x0, proc_param=0x0, select_options=2147764736, result=0x12d5f08, unit=0x12caf6c, select_lex=0x12cb41c) at sql_select.cc:3077
# 17:19:56 #17 0x001bc705 in handle_select (thd=0x12ca018, lex=0x12caf10, result=0x12d5f08, setup_tables_done_option=0) at sql_select.cc:306
# 17:19:56 #18 0x0010f747 in execute_sqlcom_select (thd=0x12ca018, all_tables=0x12d2fd0) at sql_parse.cc:4930
# 17:19:56 #19 0x00115bd6 in mysql_execute_command (thd=0x12ca018) at sql_parse.cc:2112
# 17:19:56 #20 0x0011f6bf in mysql_parse (thd=0x12ca018, inBuf=0x12d2028 "SELECT   table1 . `int_key` AS field1 , MIN(  table1 . `varchar_key` ) AS field2 , table1 . `varchar_key` AS field3 FROM ( C AS table1 STRAIGHT_JOIN D AS table2 ON (table2 . `varchar_nokey` = table1 ."..., length=882, found_semicolon=0xb0512e14) at sql_parse.cc:5945
# 17:19:56 #21 0x0012023c in dispatch_command (command=COM_QUERY, thd=0x12ca018, packet=0x19f0019 " SELECT   table1 . `int_key` AS field1 , MIN(  table1 . `varchar_key` ) AS field2 , table1 . `varchar_key` AS field3 FROM ( C AS table1 STRAIGHT_JOIN D AS table2 ON (table2 . `varchar_nokey` = table1 "..., packet_length=883) at sql_parse.cc:1062
# 17:19:56 #22 0x00121612 in do_command (thd=0x12ca018) at sql_parse.cc:744
# 17:19:56 #23 0x0010d077 in handle_one_connection (arg=0x12ca018) at sql_connect.cc:1163
# 17:19:56 #24 0x90e53155 in _pthread_start ()
# 17:19:56 #25 0x90e53012 in thread_start ()

How to repeat:
MTR test case:
Toggle the semijoin switch on and off and observe that the crash will vanish when the variable = off

/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=on,semijoin=on' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ A;
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `A` (
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `D` (
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'),('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'),('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'),('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'),('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'),('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'),('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z');
CREATE TABLE `C` (
  `varchar_key` varchar(1) DEFAULT NULL,
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'),('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y');

 
SELECT `varchar_key`  
FROM C  
WHERE (  
SELECT `varchar_key`  
FROM C  
WHERE ( `varchar_key`  , `varchar_key`  )  IN (  
SELECT CHILD_SUBQUERY1_t1 .`varchar_key`  , CHILD_SUBQUERY1_t2 .`varchar_key`  
FROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN D CHILD_SUBQUERY1_t2  ON CHILD_SUBQUERY1_t1 .`varchar_key`  )  )   ;

DROP TABLE A;
DROP TABLE D;
DROP TABLE C;
#/* End of test case for query 0 */

#/* Begin test case for query 1 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ A;
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `A` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `D` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (NULL,'r','r'),(0,'c','c'),(0,'o','o'),(7,'c','c'),(8,'d','d'),(4,'v','v'),(6,'m','m'),(5,'j','j'),(NULL,'f','f'),(NULL,'n','n'),(8,'z','z'),(8,'h','h'),(8,'q','q'),(1,'w','w'),(1,'z','z'),(5,'j','j'),(2,'a','a'),(7,'m','m'),(6,'n','n'),(4,'e','e'),(7,'u','u'),(0,'s','s'),(9,'u','u'),(3,'r','r'),(5,'g','g'),(1,'o','o'),(1,'w','w'),(5,'b','b'),(9,NULL,NULL),(2,'y','y'),(5,'y','y'),(248,'u','u'),(0,'p','p'),(8,'s','s'),(1,'e','e'),(255,'d','d'),(9,'d','d'),(9,'c','c'),(3,'b','b'),(9,'t','t'),(6,NULL,NULL),(4,'y','y'),(60,'c','c'),(7,'d','d'),(1,'x','x'),(6,'p','p'),(4,'e','e'),(NULL,'g','g'),(8,'x','x'),(0,'s','s'),(8,'e','e'),(151,'l','l'),(7,'p','p'),(6,'h','h'),(NULL,'m','m'),(23,'n','n'),(2,'v','v'),(4,'b','b'),(NULL,'x','x'),(NULL,'r','r'),(77,'t','t'),(NULL,'w','w'),(NULL,'w','w'),(7,'k','k'),(1,'a','a'),(9,'t','t'),(6,'z','z'),(2,'e','e'),(3,'q','q'),(0,'e','e'),(NULL,'v','v'),(6,'d','d'),(3,'u','u'),(195,'o','o'),(5,'b','b'),(2,'c','c'),(7,'q','q'),(25,NULL,NULL),(NULL,'h','h'),(0,'d','d'),(98,'w','w'),(6,'m','m'),(5,'i','i'),(0,'w','w'),(3,'f','f'),(1,'k','k'),(1,'v','v'),(147,'c','c'),(3,'y','y'),(3,'h','h'),(NULL,NULL,NULL),(2,'t','t'),(1,'l','l'),(8,'a','a'),(8,'r','r'),(8,'s','s'),(0,'z','z'),(1,'j','j'),(8,'c','c'),(5,'f','f');
CREATE TABLE `C` (
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'w','w'),(9,'m','m'),(3,'m','m'),(9,'k','k'),(NULL,'r','r'),(9,'t','t'),(3,'j','j'),(8,'u','u'),(8,'h','h'),(53,'o','o'),(0,NULL,NULL),(5,'k','k'),(166,'e','e'),(3,'n','n'),(0,'t','t'),(1,'c','c'),(9,'m','m'),(5,'y','y'),(6,'f','f'),(2,'d','d');

 
SELECT   table1 . `int_key` AS field1 , MIN(  table1 . `varchar_key` ) AS field2 , table1 . `varchar_key` AS field3 
FROM ( C AS table1 STRAIGHT_JOIN D AS table2 ON (table2 . `varchar_nokey` = table1 . `varchar_key`  ) ) 
WHERE (   EXISTS ( ( 
SELECT   SUBQUERY1_t1 . `varchar_key` AS SUBQUERY1_field1 
FROM ( C AS SUBQUERY1_t1 ) 
WHERE (  ( SUBQUERY1_t1 . `varchar_key` , SUBQUERY1_t1 . `varchar_key` )  IN ( 
SELECT   CHILD_SUBQUERY1_t1 . `varchar_key` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t2 . `varchar_key` AS CHILD_SUBQUERY1_field2 
FROM ( A AS CHILD_SUBQUERY1_t1 RIGHT  JOIN D AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `varchar_nokey` = CHILD_SUBQUERY1_t1 . `varchar_key` ) )    ) AND SUBQUERY1_t1 . `int_key` < 2 )  ) ) ) AND table1 . `varchar_key` < table2 . `varchar_key`  
GROUP BY field1, field3  
ORDER BY CONCAT ( table1 . `varchar_nokey`, table2 . `varchar_nokey` );

DROP TABLE A;
DROP TABLE D;
DROP TABLE C;
#/* End of test case for query 1 */

Suggested fix:
Ensure crash-free query processing regardless of optimizer settings.

Full crash output

Attachment: bug46797_crash_output.txt (text/plain), 18.69 KiB.

I have so far not been able to repeat this bug.  I am currently checking whether I am able to repeat it on older versions.

I have not been able to repeat this on earlier versions of the 6.0 branch either.

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

3718 oystein.grovlen@sun.com	2009-11-19
      Bug#46797 - Crash in fix_semijoin_strategies_for_picked_join_order
                  with semijoin=on
      
      Adding test case from this bug report. Has not been able to reproduce the 
      failure, not even on a few older versions of the code.
     @ mysql-test/r/subselect_sj.result
        Updated result file with new test for Bug#46797
     @ mysql-test/r/subselect_sj_jcl6.result
        Updated result file with new test for Bug#46797
     @ mysql-test/t/subselect_sj.test
        Added test case from Bug#46797

Regression test approved with minor changes.

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

3714 oystein.grovlen@sun.com	2009-11-20
      Bug#46797 - Crash in fix_semijoin_strategies_for_picked_join_order
                  with semijoin=on
      
      Adding test case from this bug report. Has not been able to reproduce the 
      failure, not even on a few older versions of the code.
     @ mysql-test/r/subselect_sj.result
        Updated result file with new test for Bug#46797
     @ mysql-test/r/subselect_sj_jcl6.result
        Updated result file with new test for Bug#46797
     @ mysql-test/t/subselect_sj.test
        Added test case from Bug#46797

Test case pushed to mysql-6.0-codebase-bugfixing with revid oystein.grovlen@sun.com-20091120114850-lkzdwni27ug1eq6k

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

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

3062 oystein.grovlen@sun.com	2010-04-06
      Bug#46797 - Crash in fix_semijoin_strategies_for_picked_join_order
                  with semijoin=on
      
      Adding test case from this bug report. Has not been able to reproduce the 
      failure, not even on a few older versions of the code.
     @ mysql-test/r/subselect_sj.result
        Updated result file with new test for Bug#46797
     @ mysql-test/t/subselect_sj.test
        Added test case from Bug#46797

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

3062 oystein.grovlen@sun.com	2010-04-06
      Bug#46797 - Crash in fix_semijoin_strategies_for_picked_join_order
                  with semijoin=on
      
      (Backporting of revid:oystein.grovlen@sun.com-20091120114850-lkzdwni27ug1eq6k)
      
      Adding test case from this bug report. Has not been able to reproduce the 
      failure, not even on a few older versions of the code.
     @ mysql-test/r/subselect_sj.result
        Updated result file with new test for Bug#46797
     @ mysql-test/t/subselect_sj.test
        Added test case from Bug#46797

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)