MySQL Bugs: #45933: Crash in optimize_semijoin_nests on JOIN in subquery + AND in outer query

Bug #45933	Crash in optimize_semijoin_nests on JOIN in subquery + AND in outer query
Submitted:	3 Jul 2009 8:57	Modified:	13 Nov 2009 11:55
Reporter:	Philip Stoev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	crash, materialization, optimizer_switch, outerjoin, semijoin, subquery

Description:
When executing queries such as:

SELECT GRANDPARENT1 .`varchar_key`
FROM CC GRANDPARENT1  JOIN B  ON GRANDPARENT1 .`datetime_key`
WHERE GRANDPARENT1 .`varchar_nokey`  IN (
SELECT `varchar_key`
FROM CC
WHERE ( `pk`  , `int_key`  )  IN (
SELECT CHILD1 .`int_key`  , CHILD1 .`int_nokey`
FROM B CHILD1  LEFT  JOIN CC CHILD2  ON CHILD2 .`varchar_nokey`
WHERE CHILD1 .`date_key`  BETWEEN '2008-06-07' AND '2006-06-26' )  )   ;

or, in other words:

JOIN in the outer query + JOIN in the inner query + WHERE in the inner query

mysqld crashed as follows:

#2  0x000000000066a938 in handle_segfault (sig=11) at mysqld.cc:2715
#3  <signal handler called>
#4  0x000000000070fb14 in optimize_semijoin_nests (join=0x27fdfe0, all_table_map=7) at sql_select.cc:4559
#5  0x0000000000711f70 in make_join_statistics (join=0x27fdfe0, tables_arg=0x27edf28, conds=0x27f1930, keyuse_array=0x2803b78) at sql_select.cc:4443
#6  0x000000000071557b in JOIN::optimize (this=0x27fdfe0) at sql_select.cc:1613
#7  0x0000000000615d92 in subselect_single_select_engine::exec (this=0x27f0558) at item_subselect.cc:2229
#8  0x000000000061acda in Item_subselect::exec (this=0x27f0418) at item_subselect.cc:285
#9  0x000000000061ae48 in Item_in_subselect::exec (this=0x27f0418) at item_subselect.cc:335
#10 0x0000000000612e45 in Item_in_subselect::val_bool (this=0x27f0418) at item_subselect.cc:925
#11 0x00000000005a1dc8 in Item::val_bool_result (this=0x27f0418) at ../item.h:750
#12 0x00000000005d73c1 in Item_in_optimizer::val_int (this=0x27f1498) at item_cmpfunc.cc:1612
#13 0x00000000005904a0 in Item::val_bool (this=0x27f1498) at item.cc:184
#14 0x00000000005d5b61 in Item_cond_and::val_int (this=0x280b2e8) at item_cmpfunc.cc:4276
#15 0x00000000006c7ae9 in SQL_SELECT::skip_record (this=0x280b408) at opt_range.h:749
#16 0x00000000006c52f5 in JOIN_CACHE_BNL::join_matching_records (this=0x280b5e8, skip_last=false) at sql_join_cache.cc:1786
#17 0x00000000006c378f in JOIN_CACHE::join_records (this=0x280b5e8, skip_last=false) at sql_join_cache.cc:1617
#18 0x00000000006f79bd in sub_select_cache (join=0x27f8220, join_tab=0x280ab28, end_of_records=true) at sql_select.cc:16099
#19 0x00000000006f7648 in sub_select (join=0x27f8220, join_tab=0x280a888, end_of_records=true) at sql_select.cc:16257
#20 0x00000000007051f4 in do_select (join=0x27f8220, fields=0x7f8a50332840, table=0x0, procedure=0x0) at sql_select.cc:15862
#21 0x000000000071f3b6 in JOIN::exec (this=0x27f8220) at sql_select.cc:2886
#22 0x0000000000719f04 in mysql_select (thd=0x7f8a50330968, rref_pointer_array=0x7f8a50332920, tables=0x26fb8a8, wild_num=0, fields=@0x7f8a50332840,
    conds=0x27f0418, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147797504, result=0x27f0638, unit=0x7f8a503322d0,
    select_lex=0x7f8a50332738) at sql_select.cc:3075
#23 0x000000000071f6d5 in handle_select (thd=0x7f8a50330968, lex=0x7f8a50332230, result=0x27f0638, setup_tables_done_option=0) at sql_select.cc:310
#24 0x000000000067a933 in execute_sqlcom_select (thd=0x7f8a50330968, all_tables=0x26fb8a8) at sql_parse.cc:4987
#25 0x000000000067c181 in mysql_execute_command (thd=0x7f8a50330968) at sql_parse.cc:2172
#26 0x0000000000684966 in mysql_parse (thd=0x7f8a50330968,
    inBuf=0x26fb170 "SELECT GRANDPARENT1 .`varchar_key`\nFROM CC GRANDPARENT1  JOIN B  ON GRANDPARENT1 .`datetime_key`\nWHERE GRANDPARENT1 .`varchar_nokey`  IN (\nSELECT `varchar_key`\nFROM CC\nWHERE ( `pk`  \n#, `int_key` \n)  "..., length=387, found_semicolon=0x7f8a4fdbbf10) at sql_parse.cc:6002
#27 0x000000000068554d in dispatch_command (command=COM_QUERY, thd=0x7f8a50330968, packet=0x7f8a5036c2a9 "", packet_length=390) at sql_parse.cc:1064
#28 0x0000000000686a35 in do_command (thd=0x7f8a50330968) at sql_parse.cc:746
#29 0x0000000000673e34 in handle_one_connection (arg=0x7f8a50330968) at sql_connect.cc:1158
#30 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
#31 0x000000315a4e627d in clone () from /lib64/libc.so.6

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS CC, B;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w','w'),(11,7,0,'0000-00-00','0000-00-00 00:00:00','s','s'),(12,4,0,'2003-07-14','2006-09-14 04:01:02','y','y'),(13,0,4,'2002-07-25','0000-00-00 00:00:00','c','c'),(14,1,8,'2007-07-03','0000-00-00 00:00:00','q','q'),(15,6,5,'2001-11-12','0000-00-00 00:00:00','',''),(16,2,9,'0000-00-00','0000-00-00 00:00:00','d','d'),(17,6,8,'2004-01-03','2007-04-01 11:04:17','',''),(18,0,1,'0000-00-00','0000-00-00 00:00:00','p','p'),(19,4,7,'0000-00-00','2009-01-12 00:00:00','x','x'),(20,4,0,'2006-05-18','2009-06-05 00:00:00','f','f'),(21,7,3,'0000-00-00','2006-02-14 18:06:35','x','x'),(22,3,5,'0000-00-00','2006-02-21 07:08:16','h','h'),(23,7,0,'0000-00-00','0000-00-00 00:00:00','c','c'),(24,8,7,'0000-00-00','0000-00-00 00:00:00','m','m'),(25,4,0,'2005-07-23','0000-00-00 00:00:00','s','s'),(26,6,0,'2009-11-27','2007-02-13 00:00:00','b','b'),(27,9,1,'2005-03-21','0000-00-00 00:00:00','o','o'),(28,0,0,'0000-00-00','0000-00-00 00:00:00','j','j'),(29,9,1,'0000-00-00','2003-08-11 00:00:00','m','m');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `datetime_key` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,9,7,'0000-00-00','0000-00-00 00:00:00','b','b'),(2,2,9,'2002-09-17','2007-03-03 01:12:45','h','h');

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=on,semijoin=on';
SET SESSION optimizer_use_mrr = 'disable';
SET SESSION engine_condition_pushdown = 'ON';
SET SESSION join_cache_level = 1;

SELECT GRANDPARENT1 .`varchar_key`
FROM CC GRANDPARENT1  JOIN B  ON GRANDPARENT1 .`datetime_key`
WHERE GRANDPARENT1 .`varchar_nokey`  IN (
SELECT `varchar_key`
FROM CC
WHERE ( `pk`  , `int_key`  )  IN (
SELECT CHILD1 .`int_key`  , CHILD1 .`int_nokey`
FROM B CHILD1  LEFT  JOIN CC CHILD2  ON CHILD2 .`varchar_nokey`
WHERE CHILD1 .`date_key`  BETWEEN '2008-06-07' AND '2006-06-26' )  )   ;

DROP TABLE CC, B;

Suggested fix:
Don't crash.

Further examination with different query simplification methods revealed that queries of the form are also affected:

SELECT GRANDPARENT1 .  `varchar_key`  AS G1
FROM B AS GRANDPARENT1
WHERE GRANDPARENT1 .  `pk`  IN (
 SELECT PARENT1 .  `pk`  AS P1
 FROM B AS PARENT1
 LEFT JOIN B AS PARENT2 USING (  `varchar_key`  )
) AND GRANDPARENT1 .  `pk`  =  digit;

Which is essentially a JOIN in the inner query + AND in the outer query.

If materialization *and* semijoin are both on in optimizer_switch, this crash will occur.
Setting either one of these values to 'off' prevents the test from crashing the server.

This is a duplicate of Bug#46692.  It has been verified that a fix for that bug fixes this issue too.