Re: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.
Automatic test case:

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

CREATE TABLE `CC` (
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `CC` VALUES (7,8,'v');
INSERT INTO `CC` VALUES (1,9,'r');
INSERT INTO `CC` VALUES (5,9,'a');
INSERT INTO `CC` VALUES (3,186,'m');
INSERT INTO `CC` VALUES (6,NULL,'y');
INSERT INTO `CC` VALUES (92,2,'j');
INSERT INTO `CC` VALUES (7,3,'d');
INSERT INTO `CC` VALUES (NULL,0,'z');
INSERT INTO `CC` VALUES (3,133,'e');
INSERT INTO `CC` VALUES (5,1,'h');
INSERT INTO `CC` VALUES (1,8,'b');
INSERT INTO `CC` VALUES (2,5,'s');
INSERT INTO `CC` VALUES (NULL,5,'e');
INSERT INTO `CC` VALUES (1,8,'j');
INSERT INTO `CC` VALUES (0,6,'e');
INSERT INTO `CC` VALUES (210,51,'f');
INSERT INTO `CC` VALUES (8,4,'v');
INSERT INTO `CC` VALUES (7,7,'x');
INSERT INTO `CC` VALUES (5,6,'m');
INSERT INTO `CC` VALUES (NULL,4,'c');
CREATE TABLE `C` (
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `C` VALUES (NULL,2,'w');
INSERT INTO `C` VALUES (7,9,'m');
INSERT INTO `C` VALUES (9,3,'m');
INSERT INTO `C` VALUES (7,9,'k');
INSERT INTO `C` VALUES (4,NULL,'r');
INSERT INTO `C` VALUES (2,9,'t');
INSERT INTO `C` VALUES (6,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (NULL,8,'h');
INSERT INTO `C` VALUES (5,53,'o');
INSERT INTO `C` VALUES (NULL,0,NULL);
INSERT INTO `C` VALUES (6,5,'k');
INSERT INTO `C` VALUES (188,166,'e');
INSERT INTO `C` VALUES (2,3,'n');
INSERT INTO `C` VALUES (1,0,'t');
INSERT INTO `C` VALUES (1,1,'c');
INSERT INTO `C` VALUES (0,9,'m');
INSERT INTO `C` VALUES (9,5,'y');
INSERT INTO `C` VALUES (NULL,6,'f');
INSERT INTO `C` VALUES (4,2,'d');

EXPLAIN EXTENDED
SELECT `col_int_key`
FROM (
SELECT SUBQUERY1_t1 .*
FROM CC SUBQUERY1_t1 STRAIGHT_JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_varchar_key` AND SUBQUERY1_t1 .`col_int_nokey` NOT IN (
SELECT `col_int_key`
FROM C ) ) table1 ;

DROP TABLE CC;
DROP TABLE C;

Re: RQG: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.
Analsys:

The cause for this bug is that MariaDB 5.3 still processes derived tables (subqueries in the FROM clause)
by fully executing them during the parse phase. This will be remedied by MWL#106 once merged into the
main 5.3.

The assert statement is triggered when MATERIALIZATION is ON for EXPLAIN EXTENDED for derived
tables with an IN subquery as follows:

• mysql_parse calls JOIN::exec for the derived table as if it is regular execution (not explain).
• When materialization is ON, this call goes all the way to subselect_hash_sj_engine::exec, which
  creates a partial match engine because of NULL presence.
• In order to proceed with normal execution, the hash_sj engine substitutes itself with the created
  partial match engine.
• After the parse phase it turns out that this execution was part of EXPLAIN EXTENDED, which in
  turn calls Item_cond::print > ... -> Item_subselect::print, which calls engine>print().
  Since subselect_hash_sj_engine::exec substituted the current Item_subselect engine with a
  partial match engine, eventually we call its ::print() method. However the partial match engines are
  designed only for execution, hence there is no implementation of this print() method.

Possible solutions:
1. best solution - merge MWL#106, then derived tables will not be materialized at all during EXPLAIN,
and we will never execute subqueries inside derived tables during EXPLAIN.
2. Detect somehow that subselect_hash_sj_engine::exec is called during EXPLAIN. This is hard to do
as upper JOIN::exec doesn't set the SELECT_DESCRIBE flag.
3. Implement both print() methods of the partial match engines to call subselect_hash_sj_engine::print().
4. Remove the assert, and wait for (1) above.

Re: RQG: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.
Simplified test case:

--disable_warnings
DROP TABLE /*! IF EXISTS */ t2;
DROP TABLE /*! IF EXISTS */ t1;
--enable_warnings

CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL);
INSERT INTO t1 VALUES (NULL,2);
INSERT INTO t1 VALUES (4,NULL);
CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL);
INSERT INTO t2 VALUES (6,NULL);
INSERT INTO t2 VALUES (NULL,0);

SET @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on';

EXPLAIN EXTENDED
SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1;

DROP TABLE t2;
DROP TABLE t1;

Re: RQG: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.
According to Igor's suggestion, I will take approach 4. above - remove the assert.

Launchpad bug id: 601156