Re: Crash in select_describe() with nested subqueries
Test case:

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');

EXPLAIN
SELECT `col_varchar_key`
FROM CC
WHERE (
SELECT SUBQUERY2_t1 .`col_int_nokey`
FROM CC SUBQUERY2_t1 JOIN CC ON ( 150 , 5 ) IN (
SELECT `col_int_key` CHILD_SUBQUERY1_field1 , `col_int_key` child_subquery1_field2
FROM CC
GROUP BY child_subquery1_field1 , child_subquery1_field2 ) ) ;

Re: Crash in select_describe() with nested subqueries
Another test case

--disable_abort_on_error
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,table_elimination=on';
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 1;
SET SESSION debug = '';
--enable_abort_on_error

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

CREATE TABLE `CC` (
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,'v','v');
INSERT INTO `CC` VALUES (9,'r','r');
INSERT INTO `CC` VALUES (9,'a','a');
INSERT INTO `CC` VALUES (186,'m','m');
INSERT INTO `CC` VALUES (NULL,'y','y');
INSERT INTO `CC` VALUES (2,'j','j');
INSERT INTO `CC` VALUES (3,'d','d');
INSERT INTO `CC` VALUES (0,'z','z');
INSERT INTO `CC` VALUES (133,'e','e');
INSERT INTO `CC` VALUES (1,'h','h');
INSERT INTO `CC` VALUES (8,'b','b');
INSERT INTO `CC` VALUES (5,'s','s');
INSERT INTO `CC` VALUES (5,'e','e');
INSERT INTO `CC` VALUES (8,'j','j');
INSERT INTO `CC` VALUES (6,'e','e');
INSERT INTO `CC` VALUES (51,'f','f');
INSERT INTO `CC` VALUES (4,'v','v');
INSERT INTO `CC` VALUES (7,'x','x');
INSERT INTO `CC` VALUES (6,'m','m');
INSERT INTO `CC` VALUES (4,'c','c');
CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'w','w');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (3,'m','m');
INSERT INTO `C` VALUES (9,'k','k');
INSERT INTO `C` VALUES (NULL,'r','r');
INSERT INTO `C` VALUES (9,'t','t');
INSERT INTO `C` VALUES (3,'j','j');
INSERT INTO `C` VALUES (8,'u','u');
INSERT INTO `C` VALUES (8,'h','h');
INSERT INTO `C` VALUES (53,'o','o');
INSERT INTO `C` VALUES (0,NULL,NULL);
INSERT INTO `C` VALUES (5,'k','k');
INSERT INTO `C` VALUES (166,'e','e');
INSERT INTO `C` VALUES (3,'n','n');
INSERT INTO `C` VALUES (0,'t','t');
INSERT INTO `C` VALUES (1,'c','c');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (5,'y','y');
INSERT INTO `C` VALUES (6,'f','f');
INSERT INTO `C` VALUES (2,'d','d');

EXPLAIN
SELECT `col_int_key`
FROM C
WHERE (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM CC SUBQUERY2_t1 STRAIGHT_JOIN CC SUBQUERY2_t2 ON SUBQUERY2_t2 .`col_varchar_key`
WHERE SUBQUERY2_t2 .`col_varchar_nokey` IN (
SELECT `col_varchar_nokey` CHILD_SUBQUERY1_field1
FROM CC
GROUP BY child_subquery1_field1 ) ) ;

DROP TABLE CC;
DROP TABLE C;

Re: Crash in select_describe() with nested subqueries
Reproducible with maria-5.3-mwl89

Re: Crash in select_describe() with nested subqueries
The first query in #1 works both in 5.3 and 5.3-mwl89.

The second query:
= works with current 5.3-mwl#89 (tip: revno: 2850, message: Fix LP BUG#680038)
= produces an incorrect error in 5.3-MWL#89 merged with 5.3:
ERROR 1054 (42S22): Unknown column 'SUBQUERY2_t2.col_varchar_nokey' in 'IN/ALL/ANY subquery'
= exposes a crash with materialization=on in 5.3 (tip: revno: 2853, message: Fix LP BUG#680058):

• with materialization=on: crash in select_describe() in the lines:
  TABLE_LIST *real_table= table->pos_in_table_list;
  item_list.push_back(new Item_string(real_table->alias,
  strlen(real_table->alias),
  because real_table == NULL.

• 5.3 with materialization=off:
  -------------------------------------------------------------------------------------------------------------+

  id

  select_type

  table

  type

  possible_keys

  key

  key_len

  ref

  rows

  Extra

  -------------------------------------------------------------------------------------------------------------+

  1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
  2	SUBQUERY	SUBQUERY2_t1	index	NULL	col_int_key	5	NULL	20	Using index
  2	SUBQUERY	SUBQUERY2_t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer
  3	DEPENDENT SUBQUERY	CC	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort

  -------------------------------------------------------------------------------------------------------------+

Re: Crash in select_describe() with nested subqueries
The bug is no longer present in 5.3-mwl#89. From the EXPLAIN it is clear that
the cause is that the optimizer no longer tries to evaluate subqueries during
optimization in order to detect "impossible where".

Re: Crash in select_describe() with nested subqueries
Re-confirmed the bug is not present in 5.3-mwl89.

Re: Crash in select_describe() with nested subqueries
Test case pushed to 5.3. The bug itself has been fixed by MWL#89.

Launchpad bug id: 611690