Re: Crash in JOIN::optimize called from subselect_hash_sj_engine::exec with semijoin=off
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;

Re: Crash in JOIN::optimize called from subselect_hash_sj_engine::exec with semijoin=off
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.

Re: Crash in JOIN::optimize called from subselect_hash_sj_engine::exec with semijoin=off
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.

Re: Crash in JOIN::optimize called from subselect_hash_sj_engine::exec with semijoin=off
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;

Launchpad bug id: 611622