MySQL Bugs: #49632: Crash in fix_semijoin_strategies_for_picked_join

Bug #49632	Crash in fix_semijoin_strategies_for_picked_join_order with semijoin=on (return)
Submitted:	11 Dec 2009 20:47	Modified:	15 Jan 2010 6:08
Reporter:	Patrick Crews	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crashing bug, optimizer_switch, outerjoin, regression, semijoin, subquery

Description:
6.0 is crashing on the following query if semijoin=on:

Similar backtrace as Bug#46797, but this bug was closed as a Can't Repeat (was not repeatable at the time).  Re-opening a new bug with the new test case.

SELECT `col_time_key`
FROM (
SELECT *
FROM B SUBQUERY1_t1
WHERE  7  AND `pk`  IN (
SELECT CHILD_SUBQUERY1_t1 .`pk`
FROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQUERY1_t2 .`col_varchar_key`
WHERE SUBQUERY1_t1 .`col_int_nokey`  )  ) table2   ;

Program terminated with signal 11, Segmentation fault.
#0  0x009af422 in __kernel_vsyscall ()
#0  0x009af422 in __kernel_vsyscall ()
#1  0x0085ce93 in __pthread_kill (threadid=3069917040, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x08ca1f17 in my_write_core (sig=11) at stacktrace.c:309
#3  0x08403b76 in handle_segfault (sig=11) at mysqld.cc:2765
#4  <signal handler called>
#5  0x0852ff74 in fix_semijoin_strategies_for_picked_join_order (join=0xadd1e58) at sql_select.cc:7958
#6  0x08530d3e in get_best_combination (join=0xadd1e58) at sql_select.cc:8149
#7  0x0852320b in make_join_statistics (join=0xadd1e58, tables_arg=0xacf47b8, conds=0xacf77e8, keyuse_array=0xadd6b84) at sql_select.cc:4534
#8  0x08512bd9 in JOIN::optimize (this=0xadd1e58) at sql_select.cc:1623
#9  0x0851c771 in mysql_select (thd=0xad2abe0, rref_pointer_array=0xacf4194, tables=0xacf47b8, wild_num=0, fields=..., conds=0xacf64b8, og_num=0, order=0x0, group=0x0, 
    having=0x0, proc_param=0x0, select_options=2416183808, result=0xacf6ea0, unit=0xacf4228, select_lex=0xacf4090) at sql_select.cc:3121
#10 0x087c9887 in mysql_derived_filling (thd=0xad2abe0, lex=0xad2bb2c, orig_table_list=0xacf6a08) at sql_derived.cc:295
#11 0x087c8b9a in mysql_handle_derived (lex=0xad2bb2c, processor=0x87c95c0 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#12 0x084e0cd0 in open_and_lock_tables_derived (thd=0xad2abe0, tables=0xacf6a08, derived=true, flags=0, prelocking_strategy=0xb6fb125c) at sql_base.cc:4785
#13 0x08447cf8 in open_and_lock_tables_derived (thd=0xad2abe0, tables=0xacf6a08, derived=true, flags=0) at mysql_priv.h:1519
#14 0x08447d8b in open_and_lock_tables (thd=0xad2abe0, tables=0xacf6a08) at mysql_priv.h:1529
#15 0x08439fa9 in execute_sqlcom_select (thd=0xad2abe0, all_tables=0xacf6a08) at sql_parse.cc:4926
#16 0x08428960 in mysql_execute_command (thd=0xad2abe0) at sql_parse.cc:2156
#17 0x0843e792 in mysql_parse (thd=0xad2abe0, 
    inBuf=0xacf38d0 "SELECT `col_time_key`  \nFROM (  \nSELECT * \nFROM B SUBQUERY1_t1  \nWHERE  7  AND `pk`  IN (  \nSELECT CHILD_SUBQUERY1_t1 .`pk`  \nFROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQ"..., length=278, found_semicolon=0xb6fb298c) at sql_parse.cc:5975
#18 0x08423aef in dispatch_command (command=COM_QUERY, thd=0xad2abe0, 
    packet=0xacf9ad1 "SELECT `col_time_key`  \nFROM (  \nSELECT * \nFROM B SUBQUERY1_t1  \nWHERE  7  AND `pk`  IN (  \nSELECT CHILD_SUBQUERY1_t1 .`pk`  \nFROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQ"..., packet_length=281) at sql_parse.cc:1076
#19 0x08422313 in do_command (thd=0xad2abe0) at sql_parse.cc:758
#20 0x0841f884 in handle_one_connection (arg=0xad2abe0) at sql_connect.cc:1164
#21 0x0085780e in start_thread (arg=0xb6fb3770) at pthread_create.c:300
#22 0x003887ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

Thread 3 (Thread 15109):
#0  0x009af422 in __kernel_vsyscall ()
#1  0x00381981 in select () from /lib/tls/i686/cmov/libc.so.6
#2  0x0840cf75 in handle_connections_sockets () at mysqld.cc:5363
#3  0x0840b8b5 in main (argc=8, argv=0xbf97fd94) at mysqld.cc:4856

Thread 2 (Thread 15111):
#0  0x009af422 in __kernel_vsyscall ()
#1  0x0085fd5a in do_sigwait (set=0x867ff4, sig=<value optimized out>) at ../nptl/sysdeps/unix/sysv/linux/../../../../../sysdeps/unix/sysv/linux/sigwait.c:63
#2  0x0085fe00 in __sigwait (set=0xb6fe3ee4, sig=0xb6fe3f7c) at ../nptl/sysdeps/unix/sysv/linux/../../../../../sysdeps/unix/sysv/linux/sigwait.c:100
#3  0x08404cde in signal_hand (arg=0x0) at mysqld.cc:2967
#4  0x0085780e in start_thread (arg=0xb6fe4770) at pthread_create.c:300
#5  0x003887ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

Thread 1 (Thread 15121):
#0  0x009af422 in __kernel_vsyscall ()
#1  0x0085ce93 in __pthread_kill (threadid=3069917040, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x08ca1f17 in my_write_core (sig=11) at stacktrace.c:309
#3  0x08403b76 in handle_segfault (sig=11) at mysqld.cc:2765
#4  <signal handler called>
#5  0x0852ff74 in fix_semijoin_strategies_for_picked_join_order (join=0xadd1e58) at sql_select.cc:7958
#6  0x08530d3e in get_best_combination (join=0xadd1e58) at sql_select.cc:8149
#7  0x0852320b in make_join_statistics (join=0xadd1e58, tables_arg=0xacf47b8, conds=0xacf77e8, keyuse_array=0xadd6b84) at sql_select.cc:4534
#8  0x08512bd9 in JOIN::optimize (this=0xadd1e58) at sql_select.cc:1623
#9  0x0851c771 in mysql_select (thd=0xad2abe0, rref_pointer_array=0xacf4194, tables=0xacf47b8, wild_num=0, fields=..., conds=0xacf64b8, og_num=0, order=0x0, group=0x0, 
    having=0x0, proc_param=0x0, select_options=2416183808, result=0xacf6ea0, unit=0xacf4228, select_lex=0xacf4090) at sql_select.cc:3121
#10 0x087c9887 in mysql_derived_filling (thd=0xad2abe0, lex=0xad2bb2c, orig_table_list=0xacf6a08) at sql_derived.cc:295
#11 0x087c8b9a in mysql_handle_derived (lex=0xad2bb2c, processor=0x87c95c0 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#12 0x084e0cd0 in open_and_lock_tables_derived (thd=0xad2abe0, tables=0xacf6a08, derived=true, flags=0, prelocking_strategy=0xb6fb125c) at sql_base.cc:4785
#13 0x08447cf8 in open_and_lock_tables_derived (thd=0xad2abe0, tables=0xacf6a08, derived=true, flags=0) at mysql_priv.h:1519
#14 0x08447d8b in open_and_lock_tables (thd=0xad2abe0, tables=0xacf6a08) at mysql_priv.h:1529
#15 0x08439fa9 in execute_sqlcom_select (thd=0xad2abe0, all_tables=0xacf6a08) at sql_parse.cc:4926
#16 0x08428960 in mysql_execute_command (thd=0xad2abe0) at sql_parse.cc:2156
#17 0x0843e792 in mysql_parse (thd=0xad2abe0, 
    inBuf=0xacf38d0 "SELECT `col_time_key`  \nFROM (  \nSELECT * \nFROM B SUBQUERY1_t1  \nWHERE  7  AND `pk`  IN (  \nSELECT CHILD_SUBQUERY1_t1 .`pk`  \nFROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQ"..., length=278, found_semicolon=0xb6fb298c) at sql_parse.cc:5975
#18 0x08423aef in dispatch_command (command=COM_QUERY, thd=0xad2abe0, 
    packet=0xacf9ad1 "SELECT `col_time_key`  \nFROM (  \nSELECT * \nFROM B SUBQUERY1_t1  \nWHERE  7  AND `pk`  IN (  \nSELECT CHILD_SUBQUERY1_t1 .`pk`  \nFROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQ"..., packet_length=281) at sql_parse.cc:1076
#19 0x08422313 in do_command (thd=0xad2abe0) at sql_parse.cc:758
#20 0x0841f884 in handle_one_connection (arg=0xad2abe0) at sql_connect.cc:1164
#21 0x0085780e in start_thread (arg=0xb6fb3770) at pthread_create.c:300
#22 0x003887ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

How to repeat:
#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */

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

#/* Begin test case for query 0 */

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

CREATE TABLE `A` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,'11:28:45','w');
INSERT INTO `C` VALUES (2,7,'20:25:14','m');
INSERT INTO `C` VALUES (3,9,'13:47:24','m');
INSERT INTO `C` VALUES (4,7,'19:24:11','k');
INSERT INTO `C` VALUES (5,4,'15:59:13','r');
INSERT INTO `C` VALUES (6,2,'00:00:00','t');
INSERT INTO `C` VALUES (7,6,'15:15:04','j');
INSERT INTO `C` VALUES (8,8,'11:32:06','u');
INSERT INTO `C` VALUES (9,NULL,'18:32:33','h');
INSERT INTO `C` VALUES (10,5,'15:19:25','o');
INSERT INTO `C` VALUES (11,NULL,'19:03:19',NULL);
INSERT INTO `C` VALUES (12,6,'00:39:46','k');
INSERT INTO `C` VALUES (13,188,NULL,'e');
INSERT INTO `C` VALUES (14,2,'00:00:00','n');
INSERT INTO `C` VALUES (15,1,'13:12:11','t');
INSERT INTO `C` VALUES (16,1,'04:56:48','c');
INSERT INTO `C` VALUES (17,0,'19:56:05','m');
INSERT INTO `C` VALUES (18,9,'19:35:19','y');
INSERT INTO `C` VALUES (19,NULL,'05:03:03','f');
INSERT INTO `C` VALUES (20,4,'18:38:59','d');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,'01:13:38','f');

 
SELECT `col_time_key`  
FROM (  
SELECT * 
FROM B SUBQUERY1_t1  
WHERE  7  AND `pk`  IN (  
SELECT CHILD_SUBQUERY1_t1 .`pk`  
FROM A CHILD_SUBQUERY1_t1  RIGHT  JOIN C CHILD_SUBQUERY1_t2  ON CHILD_SUBQUERY1_t2 .`col_varchar_key`  
WHERE SUBQUERY1_t1 .`col_int_nokey`  )  ) table2   ;

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

The combination of outer joins in subqueries with constant tables is something I specialize on.

Below is a simplified test case. The significant part for getting this exact seg
fault is "WHERE t1.i" (reference to table of outer query) and that t3 is empty.

CREATE TABLE t1 (i integer);
CREATE TABLE t2 (i integer);
CREATE TABLE t3 (i integer);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);

INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);

SELECT * FROM t1   
WHERE (11) IN (  
  SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);

This issue is fixed with bug#46692.  I will add a test case for this particular variant.

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

3805 oystein.grovlen@sun.com	2009-12-29
      BUG#49632 Crash in fix_semijoin_strategies_for_picked_join_order with
                semijoin=on (return)
      
      This patch is only adding a simplified variant of the test case for this
      bug report.  The issue itself was fixed by Bug#46692.
     @ mysql-test/r/subselect_sj.result
        Updated result file
     @ mysql-test/r/subselect_sj_jcl6.result
        Updated result file.
     @ mysql-test/t/subselect_sj.test
        Add query from Bug#49632 to tests for constant tables in IN-subqueries

Follow-up to Bug#46692.  Adding same reviewers.

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

3828 oystein.grovlen@sun.com	2009-12-29
      BUG#49632 Crash in fix_semijoin_strategies_for_picked_join_order with
                semijoin=on (return)
      
      This patch is only adding a simplified variant of the test case for this
      bug report.  The issue itself was fixed by Bug#46692.
     @ mysql-test/r/subselect_sj.result
        Updated result file
     @ mysql-test/r/subselect_sj_jcl6.result
        Updated result file.
     @ mysql-test/t/subselect_sj.test
        Add query from Bug#49632 to tests for constant tables in IN-subqueries

Test case pushed to mysql-6.0-codebase-bugfixing.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100121083501-but9pj2g3zmu10md) (version source revid:alik@sun.com-20100119194323-gcog2uiox2b7wsln) (merge vers: 6.0.14-alpha) (pib:16)

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

3068 oystein.grovlen@sun.com	2010-04-07
      BUG#49632 Crash in fix_semijoin_strategies_for_picked_join_order with
                semijoin=on (return)
      
      (Backporting of revid:oystein.grovlen@sun.com-20091229155605-86d9mnd2fz4j8226)
      
      This patch is only adding a simplified variant of the test case for this
      bug report.  The issue itself was fixed by Bug#46692.
     @ mysql-test/r/subselect_sj.result
        Updated result file
     @ mysql-test/t/subselect_sj.test
        Add query from Bug#49632 to tests for constant tables in IN-subqueries.

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)