Crash in JOIN::optimize called from subselect_hash_sj_engine::exec with semijoin=off

[Philip Stoev]

With semijoin=off, the following query:

PREPARE st1 FROM "SELECT table1 .`col_varchar_key`
FROM (
SELECT *
FROM BB ) table1 RIGHT JOIN BB ON ( 8 , 4 ) IN (
SELECT `pk` , `pk`
FROM C ) ";

crashes as follows (note the presence of subselect_hash_sj_engine in the backtrace):

#3 0x0827e9e0 in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x082fd446 in JOIN::optimize (this=0xb5f8c7c8) at sql_select.cc:745
#6 0x08237a39 in subselect_hash_sj_engine::exec (this=0xb5f5f228) at item_subselect.cc:4081
#7 0x0822facb in Item_subselect::exec (this=0xb5f5e948) at item_subselect.cc:481
#8 0x0822fcc4 in Item_in_subselect::exec (this=0xb5f5e948) at item_subselect.cc:578
#9 0x0823152c in Item_in_subselect::val_bool (this=0xb5f5e948) at item_subselect.cc:1246
#10 0x081cf442 in Item::val_bool_result (this=0xb5f5e948) at item.h:783
#11 0x081faab7 in Item_in_optimizer::val_int (this=0xb5f5f028) at item_cmpfunc.cc:1873
#12 0x0831e5fe in join_read_const_table (tab=0xb5f32350, pos=0xb5f8a044) at sql_select.cc:13488
#13 0x08304fb2 in make_join_statistics (join=0xb5f87958, tables_arg=0xb5f5db28, conds=0x0, keyuse_array=0xb5f8c694) at sql_select.cc:2937
#14 0x082fddc9 in JOIN::optimize (this=0xb5f87958) at sql_select.cc:939
#15 0x08303865 in mysql_select (thd=0xa81efd0, rref_pointer_array=0xb5f5c7e8, tables=0xb5f5db28, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xb5f5ec98, unit=0xb5f5c44c, select_lex=0xb5f5c6e4) at sql_select.cc:2542
#16 0x082fbfb7 in handle_select (thd=0xa81efd0, lex=0xb5f5c3f0, result=0xb5f5ec98, setup_tables_done_option=0) at sql_select.cc:276
#17 0x0829a6d8 in execute_sqlcom_select (thd=0xa81efd0, all_tables=0xb5f5db28) at sql_parse.cc:5081
#18 0x082910b8 in mysql_execute_command (thd=0xa81efd0) at sql_parse.cc:2265
#19 0x08342d81 in Prepared_statement::execute (this=0xb5f4d6c0, expanded_query=0xb6e53410, open_cursor=false) at sql_prepare.cc:3588
#20 0x08342280 in Prepared_statement::execute_loop (this=0xb5f4d6c0, expanded_query=0xb6e53410, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3263
#21 0x08340c6c in mysql_sql_stmt_execute (thd=0xa81efd0) at sql_prepare.cc:2533
#22 0x082910e2 in mysql_execute_command (thd=0xa81efd0) at sql_parse.cc:2274
#23 0x0829c8b9 in mysql_parse (thd=0xa81efd0, inBuf=0xb5f31900 "EXECUTE st1", length=11, found_semicolon=0xb6e54230) at sql_parse.cc:6027
#24 0x0828eaea in dispatch_command (command=COM_QUERY, thd=0xa81efd0, packet=0xa837329 "EXECUTE st1", packet_length=11) at sql_parse.cc:1184
#25 0x0828df90 in do_command (thd=0xa81efd0) at sql_parse.cc:890
#26 0x0828b0f0 in handle_one_connection (arg=0xa81efd0) at sql_connect.cc:1153
#27 0x00a08919 in start_thread () from /lib/libpthread.so.0
#28 0x00951e5e in clone () from /lib/libc.so.6

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY BB system NULL NULL NULL NULL 1
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
3 SUBQUERY C index NULL PRIMARY 4 NULL 20 Using index
2 DERIVED BB system NULL NULL NULL NULL 1

[Philip Stoev (pstoev-askmonty)]

Test case:

SET @@optimizer_switch = 'semijoin=off';

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (18,5,'y');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (20,2,'d');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `BB` VALUES (10,8,NULL);

PREPARE st1 FROM "SELECT table1 .`col_varchar_key`
FROM (
SELECT *
FROM BB ) table1 RIGHT JOIN BB ON ( 8 , 4 ) IN (
SELECT `pk` , `pk`
FROM C ) ";

EXECUTE st1;
EXECUTE st1;

[Timour Katchaounov (timour)]

The bug is not reproducible in the 5.3-mwl89 tree, most likely as a result
of proper subquery optimization during the optimization phase.

Therefore I will mark the bug as fixed. The test case for the bug will be added
to the 5.3-mwl89 tree, so that it appears in the main 5.3 tree once MWL#89 is
merged.

[Timour Katchaounov (timour)]

Analysis:

In 5.3 main:
---------------
This is a duplicate of MySQL BUG 52344:
  http://bugs.mysql.com/bug.php?id=52344
  http://lists.mysql.com/commits/117753
The cause of the bug is evaluation of subqueries during constant
optimization of JOIN ... ON clauses. In this case it is done before
the JOIN object for subquery has been recreated, hence a crash
when trying to optimize or execute this object.

In 5.3-MWL#89
--------------------
The reason there is no crash in MWL#89 is because the choice
of subquery strategy is delayed, so execution happens to use the
default pre-created subselect_single_select_engine.

However, execution in MWL#89 is wrong as well, because the
IN->EXISTS predicate injections happens too late, already
during the execution of the IN predicate. This is the only case
where there is still lazy optimization of subqueries, and this
lazy optimization happens too late, when execution started,
thus the choice of subquery execution strategy is made too
late in the general case.

[Timour Katchaounov (timour)]

Reduced test case:

CREATE TABLE t1 (c1 int);
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (c2 int);
INSERT INTO t2 VALUES (10);

set @@optimizer_switch='semijoin=off';

PREPARE st1 FROM "
SELECT *
FROM t2 LEFT JOIN (SELECT * FROM t2) t3 ON (8, 4) IN (SELECT c1, c1 FROM t1)";

EXECUTE st1;
EXECUTE st1;

DROP TABLE t1, t2;