Bug #46692 Crash occurring on query with subquery using STRAIGHT_JOIN
Submitted: 13 Aug 2009 11:45 Modified: 23 Nov 2010 2:56
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: crashing bug, from, materialization, optimizer_switch, subquery

[13 Aug 2009 11:45] Patrick Crews
Description:
Crashes are occurring in azalea for queries that have a nested (2 levels deep ) subquery in the FROM clause:

This only occurs when materialization is on

Example query (from attached test case):
SELECT `int_key`  
FROM (  
SELECT * 
FROM CC  
WHERE (  140  ,  4  )  IN (  
SELECT CHILD_SUBQUERY1_t1 .`int_key`  , CHILD_SUBQUERY1_t1 .`pk`  
FROM B CHILD_SUBQUERY1_t1  STRAIGHT_JOIN CC  ON CHILD_SUBQUERY1_t1 .`int_key`  )  ) table1   ;

This processes without problem if materialization is off

Partial failure output (full rqg crash information attached separately)
090813  7:10:29 - mysqld got signal 11 ;
<snip>

thd: 0x1a49218
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb0512f64 thread_stack 0x30c00
0   mysqld                              0x009ac1a9 my_print_stacktrace + 63
1   mysqld                              0x001bf0bd handle_segfault + 1301
2   libSystem.B.dylib                   0x90e8e2bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x002f3479 _Z23is_indexed_agg_distinctP4JOINP4ListI10Item_fieldE + 12859
5   mysqld                              0x002f4867 _ZN4JOIN8optimizeEv + 4439
6   mysqld                              0x003019c9 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 1027
7   mysqld                              0x004ffbde _Z21mysql_derived_fillingP3THDP3LEXP10TABLE_LIST + 638
8   mysqld                              0x004ff0d5 _Z20mysql_handle_derivedP3LEXPFbP3THDS0_P10TABLE_LISTE + 197
9   mysqld                              0x0028e10f _Z28open_and_lock_tables_derivedP3THDP10TABLE_LISTbj + 847
10  mysqld                              0x001ff89b _Z20open_and_lock_tablesP3THDP10TABLE_LIST + 65
11  mysqld                              0x001de010 _Z20prepare_schema_tableP3THDP3LEXP11Table_ident18enum_schema_tables + 2102
12  mysqld                              0x001e9197 _Z21mysql_execute_commandP3THD + 4783
13  mysqld                              0x001f9925 _Z11mysql_parseP3THDPKcjPS2_ + 1041
14  mysqld                              0x001fabbf _Z16dispatch_command19enum_server_commandP3THDPcj + 3959
15  mysqld                              0x001fce21 _Z10do_commandP3THD + 1021
16  mysqld                              0x001dabe8 handle_one_connection + 680
17  libSystem.B.dylib                   0x90e53155 _pthread_start + 321
18  libSystem.B.dylib                   0x90e53012 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1aebc28 = SELECT    ( SELECT   MIN( SUBQUERY1_t1 . `int_key` ) AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 ) WHERE ( SUBQUERY1_t1 . `pk` > SUBQUERY1_t1 . `int_nokey` AND SUBQUERY1_t1 . `varchar_nokey` != 'n' ) ) AS field1 , table1 . `int_key` AS field2 FROM ( (  ( SELECT   SUBQUERY2_t1 . * FROM ( CC AS SUBQUERY2_t1 ) WHERE  ( 140, 4 )  IN ( SELECT   CHILD_SUBQUERY1_t1 . `int_key` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `pk` AS child_subquery1_field2 FROM ( B AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN CC AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `int_nokey` = CHILD_SUBQUERY1_t1 . `int_key` ) )    ) ) ) AS table1 LEFT OUTER JOIN B AS table2 ON (table2 . `varchar_nokey` = table1 . `pk` ) ) WHERE (  table1 . `varchar_nokey`  IN ( SELECT   SUBQUERY3_t1 . `varchar_key` AS SUBQUERY3_field1 FROM ( C AS SUBQUERY3_t1 LEFT OUTER JOIN ( ( D AS SUBQUERY3_t2 LEFT OUTER JOIN B AS SUBQUERY3_t3 ON (SUBQUERY3_t3 . `int_key` = SUBQUERY3_t2 . `pk` ) ) ) ON (SUBQUERY3_t3 . `pk` = SUBQUERY3_t2 . `pk` ) ) WHERE ( SUBQUERY3_t2 . `varc
thd->thread_id=6
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file
# 00:11:11 datadir is<path>/azalea_ogb/mysql-test/var/master-data/
# 00:11:11 Core file appears to be /cores/core.83560
# 00:11:14 Reading symbols for shared libraries .... done
# 00:11:14 Core was generated by `/Users/pcrews/usr/local/bin/data1/work/azalea_lcov/azalea_ogb/sql/mysqld'.
# 00:11:14 Reading symbols for shared libraries . done
# 00:11:14 Reading symbols for shared libraries .... done
# 00:11:14 #0  0x90e716fa in select$DARWIN_EXTSN ()
# 00:11:14 #0  0x90e716fa in select$DARWIN_EXTSN ()
# 00:11:14 #1  0x001c5bd6 in handle_connections_sockets () at mysqld.cc:5256
# 00:11:14 #2  0x001d0d49 in main (argc=41, argv=0xbfffad4c) at mysqld.cc:4762
# 00:11:16 Reading symbols for shared libraries .... done
# 00:11:16 Core was generated by `/Users/pcrews/usr/local/bin/data1/work/azalea_lcov/azalea_ogb/sql/mysqld'.
# 00:11:16 Reading symbols for shared libraries . done
# 00:11:16 Reading symbols for shared libraries .... done
# 00:11:16 #0  0x90e716fa in select$DARWIN_EXTSN ()
# 00:11:16 
# 00:11:16 Thread 11 (core thread 10):
# 00:11:16 #0  0x90f03402 in __assert_rtn ()
# 00:11:16 #1  0x009ac384 in my_write_core (sig=11) at stacktrace.c:309
# 00:11:16 #2  0x001bf478 in handle_segfault (sig=11) at mysqld.cc:2734
# 00:11:16 #3  <signal handler called>
# 00:11:16 #4  0x002c754b in optimize_semijoin_nests (join=0x21d6028, all_table_map=7) at sql_select.cc:4575
# 00:11:16 #5  0x002f3479 in make_join_statistics (join=0x21d6028, tables_arg=0x1b12ec0, conds=0x15fc578, keyuse_array=0x21dad54) at sql_select.cc:4459
# 00:11:16 #6  0x002f4867 in JOIN::optimize (this=0x21d6028) at sql_select.cc:1609
# 00:11:16 #7  0x003019c9 in mysql_select (thd=0x1a49218, rref_pointer_array=0x1b1284c, tables=0x1b12ec0, wild_num=0, fields=@0x1b127dc, conds=0x1b43f70, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x1b1ada8, unit=0x1b128e0, select_lex=0x1b12748) at sql_select.cc:3077
# 00:11:16 #8  0x004ffbde in mysql_derived_filling (thd=0x1a49218, lex=0x1a4a11c, orig_table_list=0x1b442e0) at sql_derived.cc:295
# 00:11:16 #9  0x004ff0d5 in mysql_handle_derived (lex=0x1a4a11c, processor=0x4ff960 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
# 00:11:16 #10 0x0028e10f in open_and_lock_tables_derived (thd=0x1a49218, tables=0x1b442e0, derived=true, flags=0) at sql_base.cc:4331
# 00:11:16 #11 0x001ff89b in open_and_lock_tables (thd=0x1a49218, tables=0x1b442e0) at mysql_priv.h:1520
# 00:11:16 #12 0x001de010 in execute_sqlcom_select (thd=0x1a49218, all_tables=0x1b442e0) at sql_parse.cc:4892
# 00:11:16 #13 0x001e9197 in mysql_execute_command (thd=0x1a49218) at sql_parse.cc:2112
# 00:11:16 #14 0x001f9925 in mysql_parse (thd=0x1a49218, inBuf=0x1aebc28 "SELECT    ( SELECT   MIN( SUBQUERY1_t1 . `int_key` ) AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 ) WHERE ( SUBQUERY1_t1 . `pk` > SUBQUERY1_t1 . `int_nokey` AND SUBQUERY1_t1 . `varchar_nokey` != 'n' )"..., length=1189, found_semicolon=0xb0512e14) at sql_parse.cc:5942
# 00:11:16 #15 0x001fabbf in dispatch_command (command=COM_QUERY, thd=0x1a49218, packet=0x21cc019 " SELECT    ( SELECT   MIN( SUBQUERY1_t1 . `int_key` ) AS SUBQUERY1_field1 FROM ( D AS SUBQUERY1_t1 ) WHERE ( SUBQUERY1_t1 . `pk` > SUBQUERY1_t1 . `int_nokey` AND SUBQUERY1_t1 . `varchar_nokey` != 'n' "..., packet_length=1193) at sql_parse.cc:1062
# 00:11:16 #16 0x001fce21 in do_command (thd=0x1a49218) at sql_parse.cc:744
# 00:11:16 #17 0x001dabe8 in handle_one_connection (arg=0x1a49218) at sql_connect.cc:1163
# 00:11:16 #18 0x90e53155 in _pthread_start ()
# 00:11:16 #19 0x90e53012 in thread_start ()

How to repeat:
MTR test case:

Toggle the materialization switch on/off and observe that the crash will go away:

/*!50400 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' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;

#/* Begin test case for query 0 */

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

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1),(17,8),(18,5),(19,5),(20,8),(21,6),(22,51),(23,4),(24,7),(25,6),(26,4),(27,7),(28,3),(29,2);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7),(2,2);

 
SELECT `int_key`  
FROM (  
SELECT * 
FROM CC  
WHERE (  140  ,  4  )  IN (  
SELECT CHILD_SUBQUERY1_t1 .`int_key`  , CHILD_SUBQUERY1_t1 .`pk`  
FROM B CHILD_SUBQUERY1_t1  STRAIGHT_JOIN CC  ON CHILD_SUBQUERY1_t1 .`int_key`  )  ) table1   ;

DROP TABLE CC;
DROP TABLE B;
#/* End of test case for query 0 */

#/* Begin test case for query 1 */

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

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,3,186,'m','m'),(11,6,NULL,'y','y'),(12,92,2,'j','j'),(13,7,3,'d','d'),(14,NULL,0,'z','z'),(15,3,133,'e','e'),(16,5,1,'h','h'),(17,1,8,'b','b'),(18,2,5,'s','s'),(19,NULL,5,'e','e'),(20,1,8,'j','j'),(21,0,6,'e','e'),(22,210,51,'f','f'),(23,8,4,'v','v'),(24,7,7,'x','x'),(25,5,6,'m','m'),(26,NULL,4,'c','c'),(27,6,7,'m','m'),(28,9,3,'b','b'),(29,3,2,'x','x');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,7,9,'m','m'),(2,9,3,'m','m'),(3,7,9,'k','k'),(4,4,NULL,'r','r'),(5,2,9,'t','t'),(6,6,3,'j','j'),(7,8,8,'u','u'),(8,NULL,8,'h','h'),(9,5,53,'o','o'),(10,NULL,0,NULL,NULL),(11,6,5,'k','k'),(12,188,166,'e','e'),(13,2,3,'n','n'),(14,1,0,'t','t'),(15,1,1,'c','c'),(16,0,9,'m','m'),(17,9,5,'y','y'),(18,NULL,6,'f','f'),(19,4,2,'d','d'),(20,6,NULL,'r','r');
CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,8,0,'c','c'),(2,6,0,'o','o'),(3,6,7,'c','c'),(4,3,8,'d','d'),(5,9,4,'v','v'),(6,2,6,'m','m'),(7,1,5,'j','j'),(8,8,NULL,'f','f'),(9,0,NULL,'n','n'),(10,9,8,'z','z'),(11,8,8,'h','h'),(12,NULL,8,'q','q'),(13,0,1,'w','w'),(14,5,1,'z','z'),(15,1,5,'j','j'),(16,1,2,'a','a'),(17,6,7,'m','m'),(18,6,6,'n','n'),(19,1,4,'e','e'),(20,8,7,'u','u'),(21,1,0,'s','s'),(22,0,9,'u','u'),(23,4,3,'r','r'),(24,9,5,'g','g'),(25,8,1,'o','o'),(26,5,1,'w','w'),(27,9,5,'b','b'),(28,5,9,NULL,NULL),(29,NULL,2,'y','y'),(30,NULL,5,'y','y'),(31,105,248,'u','u'),(32,0,0,'p','p'),(33,3,8,'s','s'),(34,1,1,'e','e'),(35,75,255,'d','d'),(36,9,9,'d','d'),(37,7,9,'c','c'),(38,NULL,3,'b','b'),(39,NULL,9,'t','t'),(40,4,6,NULL,NULL),(41,0,4,'y','y'),(42,204,60,'c','c'),(43,0,7,'d','d'),(44,9,1,'x','x'),(45,8,6,'p','p'),(46,7,4,'e','e'),(47,8,NULL,'g','g'),(48,NULL,8,'x','x'),(49,6,0,'s','s'),(50,5,8,'e','e'),(51,2,151,'l','l'),(52,3,7,'p','p'),(53,7,6,'h','h'),(54,NULL,NULL,'m','m'),(55,145,23,'n','n'),(56,0,2,'v','v'),(57,1,4,'b','b'),(58,7,NULL,'x','x'),(59,3,NULL,'r','r'),(60,NULL,77,'t','t'),(61,2,NULL,'w','w'),(62,2,NULL,'w','w'),(63,2,7,'k','k'),(64,8,1,'a','a'),(65,6,9,'t','t'),(66,1,6,'z','z'),(67,NULL,2,'e','e'),(68,1,3,'q','q'),(69,0,0,'e','e'),(70,4,NULL,'v','v'),(71,1,6,'d','d'),(72,1,3,'u','u'),(73,27,195,'o','o'),(74,4,5,'b','b'),(75,6,2,'c','c'),(76,2,7,'q','q'),(77,248,25,NULL,NULL),(78,NULL,NULL,'h','h'),(79,9,0,'d','d'),(80,75,98,'w','w'),(81,2,6,'m','m'),(82,9,5,'i','i'),(83,4,0,'w','w'),(84,0,3,'f','f'),(85,0,1,'k','k'),(86,1,1,'v','v'),(87,119,147,'c','c'),(88,1,3,'y','y'),(89,7,3,'h','h'),(90,2,NULL,NULL,NULL),(91,7,2,'t','t'),(92,2,1,'l','l'),(93,6,8,'a','a'),(94,4,8,'r','r'),(95,5,8,'s','s'),(96,7,0,'z','z'),(97,1,1,'j','j'),(98,7,8,'c','c'),(99,2,5,'f','f'),(100,1,4,'g','g');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) DEFAULT NULL,
  `int_key` int(11) DEFAULT NULL,
  `varchar_key` varchar(1) DEFAULT NULL,
  `varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`varchar_key`,`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'f','f'),(2,NULL,2,'w','w');

SELECT    ( 
SELECT   MIN( SUBQUERY1_t1 . `int_key` ) AS SUBQUERY1_field1 
FROM ( D AS SUBQUERY1_t1 ) 
WHERE ( SUBQUERY1_t1 . `pk` > SUBQUERY1_t1 . `int_nokey` AND SUBQUERY1_t1 . `varchar_nokey` != 'n' ) ) AS field1 , table1 . `int_key` AS field2 
FROM ( (  ( 
SELECT   SUBQUERY2_t1 . * 
FROM ( CC AS SUBQUERY2_t1 ) 
WHERE  ( 140, 4 )  IN ( 
SELECT   CHILD_SUBQUERY1_t1 . `int_key` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t1 . `pk` AS child_subquery1_field2 
FROM ( B AS CHILD_SUBQUERY1_t1 STRAIGHT_JOIN CC AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `int_nokey` = CHILD_SUBQUERY1_t1 . `int_key` ) )    ) ) ) AS table1 LEFT OUTER JOIN B AS table2 ON (table2 . `varchar_nokey` = table1 . `pk` ) ) 
WHERE (  table1 . `varchar_nokey`  IN ( 
SELECT   SUBQUERY3_t1 . `varchar_key` AS SUBQUERY3_field1 
FROM ( C AS SUBQUERY3_t1 LEFT OUTER JOIN ( ( D AS SUBQUERY3_t2 LEFT OUTER JOIN B AS SUBQUERY3_t3 ON (SUBQUERY3_t3 . `int_key` = SUBQUERY3_t2 . `pk` ) ) ) ON (SUBQUERY3_t3 . `pk` = SUBQUERY3_t2 . `pk` ) ) 
WHERE ( SUBQUERY3_t2 . `varchar_key` <> 'p' AND SUBQUERY3_t1 . `int_key` <> SUBQUERY3_t1 . `int_nokey` )  ) ) AND ( table1 . `int_key`  IN (14, 186, 193, 155) OR table1 . `varchar_key` <= 'c' );

DROP TABLE CC;
DROP TABLE C;
DROP TABLE D;
DROP TABLE B;
#/* End of test case for query 1 */

Suggested fix:
Ensure proper, stable query processing regardless of optimizer settings.
[13 Aug 2009 11:47] Patrick Crews
Full rqg-generated crash output - backtrace, etc

Attachment: bug46692_crash_output.txt (text/plain), 17.70 KiB.

[12 Oct 2009 10:52] Øystein Grøvlen
Example query still core dumps on latest version of mysql-6.0-bugfixing
[14 Oct 2009 14:58] Øystein Grøvlen
The subquery does not need to be in the FROM clause.  The following query crash in the same way:

SELECT * FROM CC WHERE (140, 4) IN 
  (SELECT CHILD_SUBQUERY1_t1.int_key, CHILD_SUBQUERY1_t1.pk  
     FROM B CHILD_SUBQUERY1_t1 STRAIGHT_JOIN CC ON CHILD_SUBQUERY1_t1.`int_key`);
[26 Oct 2009 11:08] Øystein Grøvlen
The test case can be simplified to:

CREATE TABLE t1 (i integer);
SELECT * FROM t1 WHERE (1) IN (SELECT a.i FROM t1 a STRAIGHT_JOIN t1 b);

The difference between success and failure is whether JOIN or STRAIGHT_JOIN is specified.  (No practical difference since one is joining a table with itself.)

From inspecting the core dump, it seems join->best_positions have not been set in this case.  Hence the following code causes a segmentation fault:

  JOIN_TAB *tab= join->best_positions[i].table;
  join->map2table[tab->table->tablenr]= tab;
[6 Nov 2009 15:46] Øystein Grøvlen
The problem appears to be related to const tables.
The following query:

select * from t1 where (11) in (select t2.i from t2 straight_join t3)

fails with segmentation fault unless there is at least two rows in t2
and at least one row in t3.

From tracing the code in the debugger it seems the explanation is as follows:
 
  * If there is 0 or 1 row in t2, t2 is a const table, and it will be
    pulled out of the semi-join.  The way it currently works, t3 will
    never be pulled out due to the straight_join.

  * IF t2 is pulled out, t3 will be the only table in the
    semijoin-nest. It seems cost estimation fails to come up with a
    result if there is zero rows in that table.  As far as I can tell,
    the problem is related to a fanout of 0 which is used as a divisor
    when computing current_record_count (see advance_sj_state(), the
    FirstMatch stratefy part.)
[13 Nov 2009 10:46] Øystein Grøvlen
I started a discussion on internals mailing list about table pull-out and straight join; see http://lists.mysql.com/internals/37479.  Note that with the query given in the previous comment, pullling out t3 when possible would fix the problems in some scenarios, but not all.  If t2 is not a const table, t3 should not be pulled out anyway because of the straight_join requirement.  Then, if t3 is empty, you will still run into this bug.
[13 Nov 2009 11:02] Øystein Grøvlen
It seems the combination of the following two changes, fix the issue:

1. Do not include const tables in the set of tables to be considered
   by optimize_semijon_nests().  This will make sure cost estimation is
   not skipped for tables that depend on const tables.  (This is
   consistent with what is done for top level joins where const tables
   are filtered out from the set out tables considered by
   choose_plan.)

2. Currently, when the table that is added to the join prefix, is
   empty, fanout will become zero since it is the product of the
   number of records of all tables.  A zero fanout will cause the
   computed cost to become infinite.  Hence, in order to get a valid
   cost, we need to skip empty tables when computing the fanout.
[17 Nov 2009 10:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90637

3712 oystein.grovlen@sun.com	2009-11-17
      Bug#46692 Crash occurring on queries with nested FROM subqueries 
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally
      be pulled out of the subquery.  This created scenarioes which the plan search
      was not prepared for, and one ended up without any valid plan.  This caused
      segmentation fault in optimize_semijoin_nests when trying to access the best
      plan.
      
      The problem may occur with either const tables or eq_ref relations that 
      does not have any matches.  Bug#46692 reports the problem for FROM subqueries,
      but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the set out tables considered by
         choose_plan.)
      
      2. Currently, when the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  A zero fanout will cause the
         computed cost to become infinite.  Hence, in order to get a valid
         cost, we need to skip empty tables when computing the fanout.
      
      The fanout issue (issue 2 above) is also present with the duplicate weedout
      strategy, and this patch contains the necessary changes to handle fanout
      in this scenario, too.
     @ mysql-test/r/subselect3.result
        Updated results for test for subqueries with STRAIGHT_JOIN and different
        permutations of const and non-const tables.
     @ mysql-test/r/subselect3_jcl6.result
        Updated results for test for subqueries with STRAIGHT_JOIN and different
        permutations of const and non-const tables.
     @ mysql-test/r/subselect4.result
        Updated with results for Bug#46692 test case.
     @ mysql-test/t/subselect3.test
        Add test cases to check that subqueries with STRAIGHT_JOIN work for
        different permutations of const and non-const tables. Similar checking is
        also done for the duplicate weedout strategy (by turning off FirstMatch and
        Materialization).
     @ mysql-test/t/subselect4.test
        Added a test case that is similar to the query reported in Bug#46692.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by optimize_semijon_nests().  This will make sure cost estimation is
           not skipped for tables that depend on const tables.  (This is
           consistent with what is done for top level joins where const tables
           are filtered out from the set out tables considered by
           choose_plan.)
        
        2. Make sure fanout is always >= 1 when computing plan cost.  
           A zero fanout will cause the computed cost to become infinite.  
           Hence, in order to get a valid cost, we need to skip empty tables when
           computing the fanout.
[18 Nov 2009 4:54] Øystein Grøvlen
The following bug reports has been marked as duplicates of this bug:
Bug#42353, Bug#43977, Bug#45219, Bug#45933.

It has been verified that the committed fix, fixes the issues reported in these reports.

Looking at the bug reports, it is evident that the issue fixed here is not only related to STRAIGHT_JOIN in the subquery.  Also outer joins, both left and right, is affected.  This can be explained with that outer joins will, like STRAIGHT_JOIN, introduce dependencies between tables and prevent pull-outs.

I will update the patch with test cases for subqueries with outer join.
[18 Nov 2009 17:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90895

3717 oystein.grovlen@sun.com	2009-11-18
      Bug#46692 Crash occurring on queries with nested FROM subqueries 
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally
      be pulled out of the subquery.  This created scenarioes which the plan search
      was not prepared for, and one ended up without any valid plan.  This caused
      segmentation fault in optimize_semijoin_nests when trying to access the best
      plan.
      
      The problem may occur with either const tables or eq_ref relations that 
      does not have any matches.  Bug#46692 reports the problem for FROM subqueries,
      but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the set out tables considered by
         choose_plan.)
      
      2. Currently, when the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  A zero fanout will cause the
         computed cost to become infinite.  Hence, in order to get a valid
         cost, we need to skip empty tables when computing the fanout.
      
      The fanout issue (issue 2 above) is also present with the duplicate weedout
      strategy, and this patch contains the necessary changes to handle fanout
      in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both right 
      and left outer join.  This patch also adds some test cases for these
      duplicate reports as well as general testing of outer joins in subqueries
      when constant tables are involved.
     @ mysql-test/r/subselect3.result
        Updated results for test for subqueries with STRAIGHT_JOIN and outer join for 
        different permutations of const and non-const tables.
     @ mysql-test/r/subselect3_jcl6.result
        Updated results for test for subqueries with STRAIGHT_JOIN and outer join for 
        different permutations of const and non-const tables.
     @ mysql-test/r/subselect4.result
        Updated with results for Bug#46692 test case and test cases for duplicate
        bug reports.
     @ mysql-test/t/subselect3.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables. Similar
        checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization).
        Note that due to a still existing bug, left outer join is not tested.
     @ mysql-test/t/subselect4.test
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by optimize_semijon_nests().  This will make sure cost estimation is
           not skipped for tables that depend on const tables.  (This is
           consistent with what is done for top level joins where const tables
           are filtered out from the set out tables considered by
           choose_plan.)
        
        2. Make sure fanout is always >= 1 when computing plan cost.  
           A zero fanout will cause the computed cost to become infinite.  
           Hence, in order to get a valid cost, we need to skip empty tables when
           computing the fanout.
[30 Nov 2009 15:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92110

3738 oystein.grovlen@sun.com	2009-11-30
      Bug#46692 Crash occurring on queries with nested FROM subqueries 
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally
      be pulled out of the subquery.  This created scenarioes which the plan search
      was not prepared for, and one ended up without any valid plan.  This caused
      segmentation fault in optimize_semijoin_nests when trying to access the best
      plan.
      
      The problem may occur with either const tables or eq_ref relations that 
      does not have any matches.  Bug#46692 reports the problem for FROM subqueries,
      but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the set out tables considered by
         choose_plan.)
      
      2. Change computation of record count so that division by inner fanout
         is not needed.  When the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  When using division, a zero inner 
         fanout caused the computed cost to become infinite.  
      
      The fanout issue (issue 2 above) is also present with the duplicate weedout
      strategy, and this patch contains the necessary changes to avoid division
      in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both right 
      and left outer join.  This patch also adds some test cases for these
      duplicate reports as well as general testing of outer joins in subqueries
      when constant tables are involved.
     @ mysql-test/r/subselect3.result
        Updated results for test for subqueries with STRAIGHT_JOIN and outer join for 
        different permutations of const and non-const tables.
     @ mysql-test/r/subselect3_jcl6.result
        Updated results for test for subqueries with STRAIGHT_JOIN and outer join for 
        different permutations of const and non-const tables.
     @ mysql-test/r/subselect4.result
        Updated with results for Bug#46692 test case and test cases for duplicate
        bug reports.
     @ mysql-test/t/subselect3.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables. Similar
        checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization).
        Note that due to a still existing bug, left outer join is not tested.
     @ mysql-test/t/subselect4.test
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by choose_plan when called from in optimize_semijon_nests().  This
           will make sure cost estimation is not skipped for tables that
           depend on const tables.  (This is consistent with what is done for
           top level joins where const tables are filtered out from the set
           out tables considered by choose_plan.)
        
        2. Let the record count returned by optimize_wo_join_buffering() be the
           fanout for outer tables.  This way, it is not necessary to divide by
           inner fanout which created problems when record count from inner 
           tables was 0. 
        
        3. Change the current_record_count computation for duplicate weed-out
           so that division and potential problems associated with that are
           avoided.
[9 Dec 2009 7:44] Tor Didriksen
See also
Bug #46305 Wrong and variable result on simple JOIN + XOR + subquery optimizations
[16 Dec 2009 11:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94456

3776 oystein.grovlen@sun.com	2009-12-16
      Bug#46692 Crash occurring on queries with nested FROM subqueries 
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would
      normally be pulled out of the subquery.  This created scenarioes which
      the plan search was not prepared for, and one ended up without any valid
      plan.  This caused segmentation fault in optimize_semijoin_nests when
      trying to access the best plan.
      
      The problem may occur with either const tables or eq_ref relations that 
      does not have any matches.  Bug#46692 reports the problem for FROM
      subqueries, but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the set out tables considered by
         choose_plan.)
      
      2. Change computation of record count so that division by inner fanout
         is not needed.  When the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  When using division, a zero inner 
         fanout caused the computed cost to become infinite.  
      
      The fanout issue (issue 2 above) is also present with the duplicate 
      weedout strategy, and this patch contains the necessary changes to avoid
      division in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both right 
      and left outer join.  Note that while this fix, fixes the issue for outer
      joins, a more proper fix would be to make sure that the dependent
      constant tables could be pulled out.  This will be fixed by bug 49494.
      
      This patch also adds some test cases for these duplicate reports as well
      as general testing of outer joins in subqueries when constant tables are 
      involved.
     @ mysql-test/r/subselect_sj.result
        Add results for new test cases.
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/t/subselect_sj.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables.
        Similar checking is also done for the duplicate weedout strategy (by 
        turning off FirstMatch and Materialization).
        Note that due to a still existing bug, left outer join is not tested.
        
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by choose_plan when called from in optimize_semijon_nests().  This
           will make sure cost estimation is not skipped for tables that
           depend on const tables.  (This is consistent with what is done for
           top level joins where const tables are filtered out from the set
           out tables considered by choose_plan.)
        
        2. Let the record count returned by optimize_wo_join_buffering() be the
           fanout for outer tables.  This way, it is not necessary to divide by
           inner fanout which created problems when record count from inner 
           tables was 0. 
        
        3. Change the current_record_count computation for duplicate weed-out
           so that division and potential problems associated with that are
           avoided.
[29 Dec 2009 10:13] Øystein Grøvlen
Bug#37893 is also a duplicate of this bug.  I am not adding another test case for that bug since it is already covered other test cases where the right table of an left outer join is empty.
[29 Dec 2009 10:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95783

3802 oystein.grovlen@sun.com	2009-12-29
      Bug#46692 Crash occurring on queries with nested FROM subqueries
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would
      normally be pulled out of the subquery.  This created scenarioes which
      the plan search was not prepared for, and one ended up without any valid
      plan.  This caused segmentation fault in optimize_semijoin_nests when
      trying to access the best plan.
      
      The problem may occur with either const tables or eq_ref relations
      that does not have any matches.  Bug#46692 reports the problem for
      FROM subqueries, but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the tables considered by choose_plan.)
      
      2. Change computation of record count so that division by inner fanout
         is not needed.  When the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  When using division, a zero inner
         fanout caused the computed cost to become infinite.
      
      The fanout issue (issue 2 above) is also present with the duplicate
      weedout strategy, and this patch contains the necessary changes to
      avoid division in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both
      right and left outer join.  Note that while this fix, fixes the issue
      for outer joins, a more proper fix would be to make sure that the
      dependent constant tables could be pulled out.  This will be fixed by
      bug 49952.
      
      This patch also adds some test cases for these duplicate reports as well
      as general testing of outer joins in subqueries when constant tables are
      involved.
     @ mysql-test/r/subselect_sj.result
        Add results for new test cases.
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/t/subselect_sj.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables.
        Note that due to a still existing bug, left outer join is not tested.
        
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by choose_plan when called from in optimize_semijon_nests().  This
           will make sure cost estimation is not skipped for tables that
           depend on const tables.  (This is consistent with what is done for
           top level joins where const tables are filtered out from the set
           out tables considered by choose_plan.)
        
        2. Let the record count returned by optimize_wo_join_buffering() be the
           fanout for outer tables.  This way, it is not necessary to divide by
           inner fanout which created problems when record count from inner
           tables was 0.
        
        3. Change the current_record_count computation for duplicate weed-out
           so that division and potential problems associated with that are
           avoided.
[29 Dec 2009 13:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95790

3804 oystein.grovlen@sun.com	2009-12-29
      Bug#46692 Crash occurring on queries with nested FROM subqueries
                using materialization
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would
      normally be pulled out of the subquery.  This created scenarioes which
      the plan search was not prepared for, and one ended up without any valid
      plan.  This caused segmentation fault in optimize_semijoin_nests when
      trying to access the best plan.
      
      The problem may occur with either const tables or eq_ref relations
      that does not have any matches.  Bug#46692 reports the problem for
      FROM subqueries, but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the tables considered by choose_plan.)
      
      2. Change computation of record count so that division by inner fanout
         is not needed.  When the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  When using division, a zero inner
         fanout caused the computed cost to become infinite.
      
      The fanout issue (issue 2 above) is also present with the duplicate
      weedout strategy, and this patch contains the necessary changes to
      avoid division in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both
      right and left outer join.  Note that while this fix, fixes the issue
      for outer joins, a more proper fix would be to make sure that the
      dependent constant tables could be pulled out.  This will be fixed by
      bug 49952.
      
      This patch also adds some test cases for these duplicate reports as well
      as general testing of outer joins in subqueries when constant tables are
      involved.
     @ mysql-test/r/subselect_sj.result
        Add results for new test cases.
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug45219.result
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug45219.test
        Remove the test for bug 45219 from optimizer_unfixed_bugs test suite
        since this issue is fixed by this patch and a similar test case has
        been added to subselect_sj.test.
     @ mysql-test/t/subselect_sj.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables.
        Note that due to a still existing bug, left outer join is not tested.
        
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by choose_plan when called from in optimize_semijon_nests().  This
           will make sure cost estimation is not skipped for tables that
           depend on const tables.  (This is consistent with what is done for
           top level joins where const tables are filtered out from the set
           out tables considered by choose_plan.)
        
        2. Let the record count returned by optimize_wo_join_buffering() be the
           fanout for outer tables.  This way, it is not necessary to divide by
           inner fanout which created problems when record count from inner
           tables was 0.
        
        3. Change the current_record_count computation for duplicate weed-out
           so that division and potential problems associated with that are
           avoided.
[29 Dec 2009 13:36] Øystein Grøvlen
Patch pushed to mysql-6.0-codebase-bugfixing (revid:oystein.grovlen@sun.com-20091229132523-qv9gud3wyrb1ps4a).
[12 Jan 2010 16:25] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100112162328-2sblcul1kl08bbib) (version source revid:guilhem@mysql.com-20100108092756-k0zzf4kvx9b7bh38) (merge vers: 6.0.14-alpha) (pib:15)
[21 Jan 2010 8:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100121083501-but9pj2g3zmu10md) (version source revid:alik@sun.com-20100119194323-gcog2uiox2b7wsln) (merge vers: 6.0.14-alpha) (pib:16)
[23 Jan 2010 0:33] Paul DuBois
Noted in 6.0.14 changelog.

Queries with nested subqueries in the FROM clause using
materialization could cause a server crash.
[7 Apr 2010 10:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/105143

3067 oystein.grovlen@sun.com	2010-04-07
      Bug#46692 Crash occurring on queries with nested FROM subqueries
                using materialization
      
      (Backporting of revid:oystein.grovlen@sun.com-20091229132523-qv9gud3wyrb1ps4a)
      
      Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would
      normally be pulled out of the subquery.  This created scenarioes which
      the plan search was not prepared for, and one ended up without any valid
      plan.  This caused segmentation fault in optimize_semijoin_nests when
      trying to access the best plan.
      
      The problem may occur with either const tables or eq_ref relations
      that does not have any matches.  Bug#46692 reports the problem for
      FROM subqueries, but ordinary IN subqueries will have the same issue.
      
      The following changes are needed to handle this problem:
      1. Do not include const tables in the set of tables to be considered
         by optimize_semijon_nests().  This will make sure cost estimation is
         not skipped for tables that depend on const tables.  (This is
         consistent with what is done for top level joins where const tables
         are filtered out from the tables considered by choose_plan.)
      
      2. Change computation of record count so that division by inner fanout
         is not needed.  When the table that is added to the join prefix, is
         empty, fanout will become zero since it is the product of the
         number of records of all tables.  When using division, a zero inner
         fanout caused the computed cost to become infinite.
      
      The fanout issue (issue 2 above) is also present with the duplicate
      weedout strategy, and this patch contains the necessary changes to
      avoid division in this scenario, too.
      
      A number of bug reports have been marked as duplicate of this bug report
      based on that this fix also fixes these issues.  Those bug reports show
      that this is not just an issue for STRAIGHT_JOIN, but also for both
      right and left outer join.  Note that while this fix, fixes the issue
      for outer joins, a more proper fix would be to make sure that the
      dependent constant tables could be pulled out.  This will be fixed by
      bug 49952.
      
      This patch also adds some test cases for these duplicate reports as well
      as general testing of outer joins in subqueries when constant tables are
      involved.
     @ mysql-test/r/subselect_sj.result
        Add results for new test cases.
     @ mysql-test/t/subselect_sj.test
        Add test cases to check that subqueries with STRAIGHT_JOIN or outer
        join work for different permutations of const and non-const tables.
        Note that due to a still existing bug, left outer join is not tested.
        
        Added a test case that is similar to the query reported in Bug#46692.
        Also adds a few test cases for duplicate bug reports.
     @ sql/sql_select.cc
        1. Do not include const tables in the set of tables to be considered
           by choose_plan when called from in optimize_semijon_nests().  This
           will make sure cost estimation is not skipped for tables that
           depend on const tables.  (This is consistent with what is done for
           top level joins where const tables are filtered out from the set
           out tables considered by choose_plan.)
        
        2. Let the record count returned by optimize_wo_join_buffering() be the
           fanout for outer tables.  This way, it is not necessary to divide by
           inner fanout which created problems when record count from inner
           tables was 0.
        
        3. Change the current_record_count computation for duplicate weed-out
           so that division and potential problems associated with that are
           avoided.
[16 Aug 2010 6:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:27] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 2:56] Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.