List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:March 15 2012 10:14am
Subject:bzr push into mysql-trunk branch (olav.sandstaa:3779 to 3780) Bug#12365385
View as plain text  
 3780 Olav Sandstaa	2012-03-15
      Fix for Bug#12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
                           MEMORY LEADING TO SYSTEM CRASH
      
      The memory leak is caused by the following:
      
      1. One of the tables in the join is accessed using "Dynamic Range" quick
         select access (the explain output contains "Range checked for each
         record").
      2. Each time the join operation needs to check a record in this table,
         a new QUICK_RANGE_SELECT object is created. This is allocated on
         the heap (and the previous object is deleted from the heap).
      3. The QUICK_RANGE_SELECT object finds the handler object for the table and
         instructs this to use DS-MRR. Note that this is an existing handler object
         that it gets from the table object (no memory allocated).
      4. When the QUICK_RANGE_SELECT object calls
         handler::multi_range_read_init() the DS-MRR implementation will
         create a second handler object ("h2") that will be used for
         accessing the index during the DS-MRR scan. This second handler object
         is created using the current THD's memroot.
      5. When the range scan is completed and the QUICK_SELECT_OBJECT is
         deleted, this will end the index scan on the handler which will
         clean up after the DS-MRR scan. Among the things it will do it to
         "delete" the second handler object ("h2"). Given that this was
         created on a memroot the actual memory used by it is not freed.
      
      As a consequence the statement's memroot gets filled with 
      handler objects until the server runs out of memory.
      
      The fix for this problem is to be able to re-use the allocated "h2"
      handler for the entire statement. The main changes done to achieve this
      is:
      
      1. When completing the DS-MRR scan we no longer delete the "h2" handler 
         object but keep it in case a new scan is started on the same handler
         within the statement.
      2. Create a new function DsMrr_impl::reset() that takes care of
         closing and freeing the "h2" handler. This must be called when
         the statement is completed. This is done by adding calls for this
         into MyISAM and InnoDB's ::reset() methods (which are called from
         handler::ha_reset() when returning the open table objects).
      3. The initialization code in DsMrr_impl::dsmrr_init() has been 
         re-written to be able to handle re-use of an already allocated
         "h2" handler.
      
      A reduced version of the test case for this bug is included in 
      mrr_tests.inc. This will be run when the main suite is run. This test
      is too small to reproduce the "out of memory" problem but it will test
      the  functionality of the changed code. To reproduce and have a regression
      test for the "out of memory problem" an new test is included
      (ds_mrr-big.test). Due to the time to run this it will only be run when MTR
      is started with the --big option.
     @ mysql-test/include/mrr_tests.inc
        Test case for Bug#12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
                                   MEMORY LEADING TO SYSTEM CRASH
        
        Note that this test case will not reproduce the out of memory situation
        but will test the functionality changed by this patch. A larger test
        that will function as a regression test for the out of memory situation
        is included in ds_mrr-big.test.
     @ mysql-test/r/ds_mrr-big.result
        Test case for Bug#12365385.
     @ mysql-test/r/innodb_mrr.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_all.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_cost.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_cost_all.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_cost_icp.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_icp.result
        Test case for Bug#12365385
     @ mysql-test/r/innodb_mrr_none.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_all.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_cost.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_cost_all.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_cost_icp.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_icp.result
        Test case for Bug#12365385
     @ mysql-test/r/myisam_mrr_none.result
        Test case for Bug#12365385
     @ mysql-test/t/ds_mrr-big.test
        Test case for Bug#12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
                                   MEMORY LEADING TO SYSTEM CRASH
     @ sql/handler.cc
        Change to the DS-MRR implementation:
        
        Move the closing and deleting of the "h2" handler object
        from dsmrr_close() to a new function reset(). This will change
        the deletion of the "h2" handler from being done when closing the 
        DS-MRR scan to when resetting the handler object.
     @ sql/handler.h
        Extend DsMrr_impl with a new function named reset(). This should be
        called from handler::ha_reset(). This function will reset and free any
        resources allocated by the DS-MRR object.
     @ storage/innobase/handler/ha_innodb.cc
        In ha_innobase::reset(): replace the call to ds_mrr.dsmrr_close() with a
        call to ds_mrr.reset() in order to reset and an release resources
        held by the DsMrr_impl object.
     @ storage/myisam/ha_myisam.cc
        In ha_myisam::reset(): replace the call to ds_mrr.dsmrr_close() with a
        call to ds_mrr.reset() in order to reset and an release resources
        held by the DsMrr_impl object.

    added:
      mysql-test/r/ds_mrr-big.result
      mysql-test/t/ds_mrr-big.test
    modified:
      mysql-test/include/mrr_tests.inc
      mysql-test/r/innodb_mrr.result
      mysql-test/r/innodb_mrr_all.result
      mysql-test/r/innodb_mrr_cost.result
      mysql-test/r/innodb_mrr_cost_all.result
      mysql-test/r/innodb_mrr_cost_icp.result
      mysql-test/r/innodb_mrr_icp.result
      mysql-test/r/innodb_mrr_none.result
      mysql-test/r/myisam_mrr.result
      mysql-test/r/myisam_mrr_all.result
      mysql-test/r/myisam_mrr_cost.result
      mysql-test/r/myisam_mrr_cost_all.result
      mysql-test/r/myisam_mrr_cost_icp.result
      mysql-test/r/myisam_mrr_icp.result
      mysql-test/r/myisam_mrr_none.result
      sql/handler.cc
      sql/handler.h
      storage/innobase/handler/ha_innodb.cc
      storage/myisam/ha_myisam.cc
 3779 Jon Olav Hauglid	2012-03-15
      Bug#13833438 ERROR CODES MUST HAVE SAME VALUE ACROSS MYSQL VERSIONS
      
      All MySQL error numbers should be stable between major versions
      as this allows client applications to handle errors based on
      error number without rebuilding/rewriting between MySQL versions.
      
      MySQL error numbers are determined by the order MySQL errors
      are listed in errmsg-utf8.txt.
      
      This patch reorders the MySQL errors so that new errors
      introduced in 5.6 are listed last and that MySQL errors
      also present in 5.5 are listed in the same order so that they
      keep their error number.

    modified:
      mysql-test/r/cast.result
      mysql-test/r/explain.result
      mysql-test/r/get_diagnostics.result
      mysql-test/r/innodb_explain_json_non_select_all.result
      mysql-test/r/innodb_explain_json_non_select_none.result
      mysql-test/r/innodb_explain_non_select_all.result
      mysql-test/r/innodb_explain_non_select_none.result
      mysql-test/r/myisam_explain_json_non_select_all.result
      mysql-test/r/myisam_explain_json_non_select_none.result
      mysql-test/r/myisam_explain_non_select_all.result
      mysql-test/r/myisam_explain_non_select_none.result
      mysql-test/r/packet.result
      mysql-test/r/partition_exchange.result
      mysql-test/r/read_only_innodb.result
      mysql-test/r/shm.result
      mysql-test/r/sp-destruct.result
      mysql-test/r/type_time.result
      mysql-test/r/variables-notembedded.result
      mysql-test/r/variables.result
      mysql-test/suite/innodb/r/innodb_16k.result
      mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result
      mysql-test/suite/rpl/r/rpl_alter_repository.result
      mysql-test/suite/rpl/r/rpl_heartbeat.result
      mysql-test/suite/rpl/r/rpl_heartbeat_basic.result
      mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result
      mysql-test/suite/rpl/r/rpl_packet.result
      mysql-test/suite/rpl/r/rpl_parallel_start_stop.result
      mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result
      mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
      mysql-test/suite/rpl/t/rpl_alter_repository.test
      mysql-test/suite/rpl/t/rpl_parallel_start_stop.test
      mysql-test/suite/sys_vars/r/max_allowed_packet_basic.result
      mysql-test/suite/sys_vars/r/max_allowed_packet_func.result
      mysql-test/suite/sys_vars/r/max_binlog_cache_size_basic.result
      mysql-test/suite/sys_vars/r/max_binlog_stmt_cache_size_basic.result
      sql/share/errmsg-utf8.txt
=== modified file 'mysql-test/include/mrr_tests.inc'

=== modified file 'mysql-test/include/mrr_tests.inc'
--- a/mysql-test/include/mrr_tests.inc	2011-11-23 19:55:56 +0000
+++ b/mysql-test/include/mrr_tests.inc	2012-03-15 10:13:19 +0000
@@ -452,3 +452,77 @@
 
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 select @@read_rnd_buffer_size;
+
+--echo #
+--echo # Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+--echo #              MEMORY LEADING TO SYSTEM CRASH
+--echo #
+
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE t1 (
+  pk INTEGER NOT NULL,
+  i1 INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1
+  SELECT a, 1, 'MySQL' FROM ten;
+
+CREATE TABLE t2 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  c2 varchar(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2
+  SELECT a, 'MySQL', 'MySQL' FROM ten;
+
+CREATE TABLE t3 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t3
+  SELECT a, 'MySQL' FROM ten;
+
+CREATE TABLE t4 (
+  pk int(11) NOT NULL,
+  c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+  c2 varchar(10) NOT NULL,
+  c3 varchar(10) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY k1 (c1_key)
+);
+
+# t4 is empty
+
+CREATE TABLE t5 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t5
+  SELECT a, 'MySQL' FROM ten;
+
+let query=
+SELECT STRAIGHT_JOIN *
+FROM 
+  (t1 LEFT JOIN 
+     (t2 LEFT JOIN
+       (t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+      ON t2.c1 = t4.c3) 
+   ON t1.c1 = t4.c2)
+  RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE ten, t1, t2, t3, t4, t5;
+

=== added file 'mysql-test/r/ds_mrr-big.result'
--- a/mysql-test/r/ds_mrr-big.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/ds_mrr-big.result	2012-03-15 10:13:19 +0000
@@ -0,0 +1,78 @@
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE hundred (a INTEGER);
+INSERT INTO hundred
+SELECT a1.a + 10 * a2.a FROM ten a1, ten a2;
+CREATE TABLE thousand (a INTEGER);
+INSERT INTO thousand
+SELECT a1.a + 10 * a2.a + 100 * a3.a FROM ten a1, ten a2, ten a3;
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM thousand;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM hundred;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+set optimizer_switch=`mrr=on,mrr_cost_based=off`;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	100	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, hundred, thousand;
+DROP TABLE t1, t2, t3, t4, t5;

=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_all.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result	2012-03-15 10:13:19 +0000
@@ -605,6 +605,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/innodb_mrr_none.result	2012-03-15 10:13:19 +0000
@@ -604,6 +604,76 @@
 @@read_rnd_buffer_size
 262144
 #
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	1	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
+#
 # Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
 #
 SET AUTOCOMMIT=0;

=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_all.result'
--- a/mysql-test/r/myisam_mrr_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_all.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost.result'
--- a/mysql-test/r/myisam_mrr_cost.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost_all.result'
--- a/mysql-test/r/myisam_mrr_cost_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_cost_all.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
--- a/mysql-test/r/myisam_mrr_cost_icp.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_icp.result'
--- a/mysql-test/r/myisam_mrr_icp.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result	2012-03-15 10:13:19 +0000
@@ -606,5 +606,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/myisam_mrr_none.result'
--- a/mysql-test/r/myisam_mrr_none.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/myisam_mrr_none.result	2012-03-15 10:13:19 +0000
@@ -605,5 +605,75 @@
 select @@read_rnd_buffer_size;
 @@read_rnd_buffer_size
 79
+#
+# Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+#              MEMORY LEADING TO SYSTEM CRASH
+#
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+i1 INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1
+SELECT a, 1, 'MySQL' FROM ten;
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+c2 varchar(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2
+SELECT a, 'MySQL', 'MySQL' FROM ten;
+CREATE TABLE t3 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t3
+SELECT a, 'MySQL' FROM ten;
+CREATE TABLE t4 (
+pk int(11) NOT NULL,
+c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+c2 varchar(10) NOT NULL,
+c3 varchar(10) NOT NULL,
+PRIMARY KEY (pk),
+KEY k1 (c1_key)
+);
+CREATE TABLE t5 (
+pk INTEGER NOT NULL,
+c1 VARCHAR(10) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t5
+SELECT a, 'MySQL' FROM ten;
+EXPLAIN SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	10	NULL
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (Block Nested Loop)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (Block Nested Loop)
+1	SIMPLE	t4	ALL	k1	NULL	NULL	NULL	0	Range checked for each record (index map: 0x2)
+SELECT STRAIGHT_JOIN *
+FROM 
+(t1 LEFT JOIN 
+(t2 LEFT JOIN
+(t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+ON t2.c1 = t4.c3) 
+ON t1.c1 = t4.c2)
+RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+pk	i1	c1	pk	c1	c2	pk	c1	pk	c1_key	c2	c3	pk	c1
+DROP TABLE ten, t1, t2, t3, t4, t5;
 set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
 set optimizer_switch=default;

=== added file 'mysql-test/t/ds_mrr-big.test'
--- a/mysql-test/t/ds_mrr-big.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/ds_mrr-big.test	2012-03-15 10:13:19 +0000
@@ -0,0 +1,90 @@
+--source include/big_test.inc
+
+--echo #
+--echo # Bug 12365385 STRAIGHT_JOIN QUERY QUICKLY EXHAUSTS SYSTEM+VIRT. 
+--echo #              MEMORY LEADING TO SYSTEM CRASH
+--echo #
+
+# This test will cause the server to allocate many GBs of memory and most 
+# likely run out of memory if this bug gets re-introduced. 
+# A reduced version of this test is include in include/mrr_tests.inc
+
+CREATE TABLE ten (a INTEGER);
+INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE hundred (a INTEGER);
+INSERT INTO hundred
+SELECT a1.a + 10 * a2.a FROM ten a1, ten a2;
+
+CREATE TABLE thousand (a INTEGER);
+INSERT INTO thousand
+SELECT a1.a + 10 * a2.a + 100 * a3.a FROM ten a1, ten a2, ten a3;
+
+CREATE TABLE t1 (
+  pk INTEGER NOT NULL,
+  i1 INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t1
+  SELECT a, 1, 'MySQL' FROM thousand;
+
+CREATE TABLE t2 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  c2 varchar(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t2
+  SELECT a, 'MySQL', 'MySQL' FROM ten;
+
+CREATE TABLE t3 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t3
+  SELECT a, 'MySQL' FROM hundred;
+
+CREATE TABLE t4 (
+  pk int(11) NOT NULL,
+  c1_key varchar(10) CHARACTER SET utf8 NOT NULL,
+  c2 varchar(10) NOT NULL,
+  c3 varchar(10) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY k1 (c1_key)
+);
+
+# t4 is empty
+
+CREATE TABLE t5 (
+  pk INTEGER NOT NULL,
+  c1 VARCHAR(10) NOT NULL,
+  PRIMARY KEY (pk)
+);
+
+INSERT INTO t5
+  SELECT a, 'MySQL' FROM ten;
+
+set optimizer_switch=`mrr=on,mrr_cost_based=off`;
+#set optimizer_switch='mrr=off';
+
+let query=
+SELECT STRAIGHT_JOIN *
+FROM 
+  (t1 LEFT JOIN 
+     (t2 LEFT JOIN
+       (t3 LEFT OUTER JOIN t4 ON t3.c1 <= t4.c1_key)
+      ON t2.c1 = t4.c3) 
+   ON t1.c1 = t4.c2)
+  RIGHT OUTER JOIN t5 ON t2.c2 <= t5.c1
+WHERE t1.i1 = 1;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE ten, hundred, thousand;
+DROP TABLE t1, t2, t3, t4, t5;

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-03-14 10:34:57 +0000
+++ b/sql/handler.cc	2012-03-15 10:13:19 +0000
@@ -4859,8 +4859,27 @@
   Initialize and start the MRR scan. Depending on the mode parameter, this
   may use default or DS-MRR implementation.
 
-  @param h               Table handler to be used
-  @param key             Index to be used
+  The DS-MRR implementation will use a second handler object (h2) for
+  doing scan on the index:
+  - on the first call to this function the h2 handler will be created
+    and h2 will be opened using the same index as the main handler
+    is set to use. The index scan on the main index will be closed
+    and it will be re-opened to read records from the table using either 
+    no key or the primary key. The h2 handler will be deleted when
+    reset() is called (which should happen on the end of the statement).
+  - when dsmrr_close() is called the index scan on h2 is closed.
+  - on following calls to this function one of the following must be valid:
+    a. if dsmrr_close has been called:
+       the main handler (h) must be open on an index, h2 will be opened
+       using this index, and the index on h will be closed and 
+       h will be re-opened to read reads from the table using either
+       no key or the primary key.
+    b. dsmrr_close has not been called:
+       h2 will already be open, the main handler h must be set up
+       to read records from the table (handler->inited is RND) either
+       using the primary index or using no index at all.
+
+  @param h_arg           Table handler to be used
   @param seq_funcs       Interval sequence enumeration functions
   @param seq_init_param  Interval sequence enumeration parameter
   @param n_ranges        Number of ranges in the sequence.
@@ -4876,11 +4895,9 @@
                            HANDLER_BUFFER *buf)
 {
   uint elem_size;
-  Item *pushed_cond= NULL;
-  handler *new_h2= 0;
   int retval= 0;
   DBUG_ENTER("DsMrr_impl::dsmrr_init");
-  THD *thd= current_thd;
+  THD *thd= h_arg->table->in_use;     // current THD
 
   /*
     index_merge may invoke a scan on an object for which dsmrr_info[_const]
@@ -4928,94 +4945,101 @@
   rowids_buf_end= rowids_buf_last;
 
   /*
-    There can be two cases:
-    - This is the first call since index_init(), h2==NULL
-       Need to setup h2 then.
-    - This is not the first call, h2 is initalized and set up appropriately.
-       The caller might have called h->index_init(), need to switch h to
-       rnd_pos calls.
+    The DS-MRR scan uses a second handler object (h2) for doing the
+    index scan. Create this by cloning the primary handler
+    object. The h2 handler object is deleted when DsMrr_impl::reset()
+    is called.
   */
   if (!h2)
   {
-    /* Create a separate handler object to do rndpos() calls. */
+    handler *new_h2;
     /*
       ::clone() takes up a lot of stack, especially on 64 bit platforms.
       The constant 5 is an empiric result.
+      @todo Is this still the case? Leave it as it is for now but could
+            likely be removed?
     */
     if (check_stack_overrun(thd, 5*STACK_MIN_SIZE, (uchar*) &new_h2))
       DBUG_RETURN(1);
+
+    if (!(new_h2= h->clone(h->table->s->normalized_path.str, thd->mem_root)))
+      DBUG_RETURN(1);
+    h2= new_h2; /* Ok, now can put it into h2 */
+    table->prepare_for_position();
+  }
+
+  /*
+    Open the index scan on h2 using the key from the primary handler.
+  */
+  if (h2->active_index == MAX_KEY)
+  {
     DBUG_ASSERT(h->active_index != MAX_KEY);
-    uint mrr_keyno= h->active_index;
-
-    if (!(new_h2= h->clone(h->table->s->normalized_path.str, thd->mem_root)) ||
-        new_h2->ha_external_lock(thd, h->m_lock_type))
-    {
-      delete new_h2;
-      DBUG_RETURN(1);
-    }
-
+    const uint mrr_keyno= h->active_index;
+
+    if ((retval= h2->ha_external_lock(thd, h->m_lock_type)))
+      goto error;
+
+    if ((retval= h2->extra(HA_EXTRA_KEYREAD)))
+      goto error;
+
+    if ((retval= h2->ha_index_init(mrr_keyno, false)))
+      goto error;
+
+    // Transfer ICP from h to h2
     if (mrr_keyno == h->pushed_idx_cond_keyno)
-      pushed_cond= h->pushed_idx_cond;
-
-    /*
-      Caution: this call will invoke this->dsmrr_close(). Do not put the
-      created secondary table handler into this->h2 or it will delete it.
-    */
-    if ((retval= h->ha_index_end()))
-    {
-      h2=new_h2;
-      goto error;
-    }
-
-    h2= new_h2; /* Ok, now can put it into h2 */
-    table->prepare_for_position();
-    h2->extra(HA_EXTRA_KEYREAD);
-
-    if ((retval= h2->ha_index_init(mrr_keyno, FALSE)))
-      goto error;
-
-    use_default_impl= FALSE;
-    if (pushed_cond)
-      h2->idx_cond_push(mrr_keyno, pushed_cond);
+    {
+      const Item* ret= h2->idx_cond_push(mrr_keyno, h->pushed_idx_cond);
+      DBUG_ASSERT(ret == NULL);
+    }
+    else
+    {
+      // Cancel any potentially previously pushed index conditions
+      h2->cancel_pushed_idx_cond();
+    }
   }
   else
   {
     /*
-      We get here when the access alternates betwen MRR scan(s) and non-MRR
-      scans.
-    */
-
-    /* 
-      Verify consistency between the two handler objects:
-      1. The main handler should either use the primary key or not have an
-         active index at this point since the clone handler (h2) is used for
-         reading the index.
-      2. The index used for ICP should be the same for the two handlers or
-         it should not be set on the clone handler (h2).
-      3. The ICP function should be the same for the two handlers or it should
-         not be set for the clone handler (h2).
-    */
-    DBUG_ASSERT(h->active_index == table->s->primary_key ||
-                h->active_index == MAX_KEY);
-    DBUG_ASSERT(h->pushed_idx_cond_keyno == h2->pushed_idx_cond_keyno || 
-                h2->pushed_idx_cond_keyno == MAX_KEY);
-    DBUG_ASSERT(h->pushed_idx_cond == h2->pushed_idx_cond || 
-                h2->pushed_idx_cond == NULL);
-
+      h2 has already an open index. This happens when the DS-MRR scan
+      is re-started without closing it first. In this case the primary
+      handler must be used for reading records from the table, ie. it
+      must not be opened for doing a new range scan. In this case
+      the active_index must either not be set or be the primary key.
+    */
+    DBUG_ASSERT(h->inited == handler::RND);
+    DBUG_ASSERT(h->active_index == MAX_KEY || 
+                h->active_index == table->s->primary_key);
+  }
+
+  /*
+    The index scan is now transferred to h2 and we can close the open
+    index scan on the primary handler.
+  */
+  if (h->inited == handler::INDEX)
+  {
     /*
-      Calling h->index_end() will invoke dsmrr_close() for this object,
-      which will delete h2. We need to keep it, so save put it away and dont
-      let it be deleted:
+      Calling h->ha_index_end() will invoke dsmrr_close() for this object,
+      which will close the index scan on h2. We need to keep it open, so 
+      temporarily move h2 out of the DsMrr object.
     */
     handler *save_h2= h2;
     h2= NULL;
-    int res= (h->inited == handler::INDEX && (retval= h->ha_index_end()));
+    retval= h->ha_index_end();
     h2= save_h2;
-    use_default_impl= FALSE;
-    if (res)
+    if (retval)
       goto error;
   }
 
+  /*
+    Verify consistency between h and h2.
+  */
+  DBUG_ASSERT(h->inited != handler::INDEX);
+  DBUG_ASSERT(h->active_index == MAX_KEY || 
+              h->active_index == table->s->primary_key);
+  DBUG_ASSERT(h2->inited == handler::INDEX);
+  DBUG_ASSERT(h2->active_index != MAX_KEY);
+  DBUG_ASSERT(h->m_lock_type == h2->m_lock_type);
+
   if ((retval= h2->handler::multi_range_read_init(seq_funcs, seq_init_param, 
                                                   n_ranges, mode, buf)))
     goto error;
@@ -5048,7 +5072,7 @@
   DBUG_RETURN(0);
 error:
   h2->ha_index_or_rnd_end();
-  h2->ha_external_lock(current_thd, F_UNLCK);
+  h2->ha_external_lock(thd, F_UNLCK);
   h2->close();
   delete h2;
   h2= NULL;
@@ -5060,15 +5084,32 @@
 void DsMrr_impl::dsmrr_close()
 {
   DBUG_ENTER("DsMrr_impl::dsmrr_close");
-  if (h2)
+
+  // If there is an open index on h2, then close it
+  if (h2 && h2->active_index != MAX_KEY)
   {
     h2->ha_index_or_rnd_end();
     h2->ha_external_lock(current_thd, F_UNLCK);
+  }
+  use_default_impl= true;
+  DBUG_VOID_RETURN;
+}
+
+
+void DsMrr_impl::reset()
+{
+  DBUG_ENTER("DsMrr_impl::reset");
+
+  if (h2)
+  {
+    // Close any ongoing DS-MRR scan 
+    dsmrr_close();
+
+    // Close and delete the h2 handler
     h2->close();
     delete h2;
     h2= NULL;
   }
-  use_default_impl= TRUE;
   DBUG_VOID_RETURN;
 }
 

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2012-03-06 14:29:42 +0000
+++ b/sql/handler.h	2012-03-15 10:13:19 +0000
@@ -2533,6 +2533,7 @@
 
   DsMrr_impl()
     : h2(NULL) {};
+  ~DsMrr_impl() { DBUG_ASSERT(h2 == NULL); }
   
   /*
     The "owner" handler object (the one that calls dsmrr_XXX functions.
@@ -2565,6 +2566,18 @@
   int dsmrr_init(handler *h, RANGE_SEQ_IF *seq_funcs, void *seq_init_param, 
                  uint n_ranges, uint mode, HANDLER_BUFFER *buf);
   void dsmrr_close();
+
+  /**
+    Resets the DS-MRR object to the state it had after being intialized.
+
+    If there is an open scan then this will be closed.
+    
+    This function should be called by handler::ha_reset() which is called
+    when a statement is completed in order to make the handler object ready
+    for re-use by a different statement.
+  */
+
+  void reset();
   int dsmrr_fill_buffer();
   int dsmrr_next(char **range_info);
 

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2012-03-15 04:58:31 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2012-03-15 10:13:19 +0000
@@ -10997,7 +10997,7 @@
 	}
 
 	reset_template();
-	ds_mrr.dsmrr_close();
+	ds_mrr.reset();
 
 	/* TODO: This should really be reset in reset_template() but for now
 	it's safer to do it explicitly here. */

=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc	2012-03-06 14:29:42 +0000
+++ b/storage/myisam/ha_myisam.cc	2012-03-15 10:13:19 +0000
@@ -1841,7 +1841,7 @@
   DBUG_ASSERT(pushed_idx_cond == NULL);
   DBUG_ASSERT(pushed_idx_cond_keyno == MAX_KEY);
   mi_set_index_cond_func(file, NULL, 0);
-  ds_mrr.dsmrr_close();
+  ds_mrr.reset();
   return mi_reset(file);
 }
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (olav.sandstaa:3779 to 3780) Bug#12365385Olav Sandstaa15 Mar