List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 17 2011 12:46pm
Subject:bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926
Bug#12619510 Bug#12619868
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on revid:jorgen.loland@stripped

 3385 Guilhem Bichot	2011-06-17
      Fix for
      BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH JCL>=5 AND MRR ENABLED"
      BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
      BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5":
      the optimizer uses join buffering but doesn't realize that, as a consequence
      of join buffering, doing GROUP BY by getting rows in order from the first table
      is not going to work (join buffering shuffles rows). Cause of this optimizer's
      oversight is that JOIN::sort_by_table is not fully exact; it may be good enough
      for cost calculations, but not when some logic must be fully exact.
     @ mysql-test/include/join_cache.inc
        need innodb table for one testcase; but ENGINE=INNODB won't work
        without include/have_innodb.inc. But the presence of that include
        will automatically use innodb as default engine for the entire test,
        which changes EXPLAIN output of many tests; this is unwanted,
        so we restore the default engine to myisam, to preserve the test.
     @ mysql-test/r/join_cache_jcl1.result
        Effect of the bugfix: join buffering is not used anymore for
        SELECT t1.col_int_key, t1.col_datetime 
        FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
        WHERE t2.col_int_key = 1 AND t2.col_int >= 3
        GROUP BY t1.col_int_key
        ORDER BY t1.col_int_key, t1.col_datetime
        LIMIT 2;
        This makes sense as FORCE INDEX for t1.col_int_key, by definition of FORCE INDEX,
        should apply to "GROUP BY t1.col_int_key" too and thus GROUP BY should use the
        index order, which makes join buffering impossible.
        "Temporary" and "filesort" remain needed for ORDER BY.
     @ mysql-test/r/join_cache_jcl2.result
        see comment for the jcl1 result
     @ mysql-test/r/join_cache_jcl3.result
        see comment for the jcl1 result
     @ mysql-test/r/join_cache_jcl4.result
        see comment for the jcl1 result
     @ mysql-test/r/join_cache_jcl5.result
        Bugs started at jcl>=5.
        Effects of the bugfix, in order:
        
        1) same as in jcl1 result file
        
        2) In testcase of BUG#12586926, we had a wrong extra "NULL,4" row; the fix also changes
        EXPLAIN output:
        - 1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
        + 1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using temporary; Using filesort
        Let's study how, without the bugfix, the bug happened.
        The optimizer chooses to do an index scan on the "col_varchar_key" index, then calls
        make_join_readinfo() which decides to do join buffering. At end of make_join_readinfo(),
        the function takes at attempt at disabling the choice of index ordering for GROUP BY
        or ORDER BY: but this attempt fails, because JOIN::sort_by_table is NULL. It is NULL
        because get_sort_by_table() searches for an index usable _both_ for GROUP BY and INDEX BY,
        which is impossible ("field1" != "field2"): so the code at end of make_join_readinfo()
        believes that no index will be used for GROUP BY *or* ORDER BY and there is thus no
        need to prevent the choice of an index for ordering.
        After this failed attempt, because JOIN::simple_group is still true, test_if_skip_sort_order()
        is called, which searches for another index which would produces rows ordered for GROUP BY;
        that function decides to use the "col_int_key" index instead. GROUP BY is set up
        to use a function which relies on incoming rows being ordered (end_write_group()),
        which is wrong as join buffering changed the order. Filesort is finally done for
        ORDER BY, but the result is still wrong.
        After the bugfix, at end of make_join_readinfo(), join buffering correctly
        prevents the use of index ordering, so we don't call test_if_skip_sort_order()
        and keep the "col_varchar_key" index. A function suitable for non-ordered
        incoming rows (end_update()) is chosen to do the groupping.
        Filesort is still used for ORDER BY.
        
        3) In testcase of BUG#12619510, rows were wrongly not aggregated, for example
        we had two rows with "y,1,7". EXPLAIN doesn't change though.
        Here's what happened before the bugfix. sort_by_tab is NULL because GROUP BY and
        ORDER BY are incompatible. So make_join_readinfo() doesn't set simple_group
        to "false". Filesort is done on t2 (the first table of the plan) to produce
        rows in order for GROUP BY, end_write_group() is chosen; this is wrong, because when
        joined with t1 (which does join buffering) rows gets shuffled so end_write_group()
        produces bad groups. Result goes to a tmp table, on which a second filesort is done
        for ORDER BY.
        After the bugfix: no filesort is done on t2; end_write() is used (which
        writes to a tmp table without doing any groupping). Filesort is done on the tmp table,
        which is sent to end_write_group() (to do the groupping), then another filesort
        is done for ORDER BY.
        So the difference, invisible in EXPLAIN, is when the temporary table
        comes into play, and on what table filesort operates.
        
        4) In testcase of BUG#12619868, we had two wrong extra rows "NULL,4" and "NULL,8".
        - 1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
        - 1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	16	Using temporary; Using filesort
        Before the bufxig: as earlier, JOIN::sort_by_table is NULL, because when
        get_sort_by_table() is called, GROUP BY contains columns of _two_ tables.
        So the search for an index for GROUP BY is not disabled.
        But after that, t1 is identified as a const table by make_join_statistics(),
        so remove_const() sees that GROUP BY is finally on one single (non-const) column,
        and so sets "simple_group" to "true", so test_if_skip_sort_order() is called,
        which decides to use the index ordering of col_int_key.
        That particular bug could also be fixed by moving the call of get_sort_by_table()
        into make_join_statistics(), right after discovery of const tables. This way,
        get_sort_by_table() would know about const tables, and understand that
        "GROUP BY field1, field4" is like "GROUP BY field4" (see Item_field::used_tables()),
        thus sort_by_table would be set to t2, which then would make make_join_readinfo()
        correctly disable index ordering due to join buffering.
        This alternative fix would have improved on the correctness of sort_by_table;
        but sort_by_table has other problems as seen in the other two bugs, so the chosen
        approach here is rather to stop relying on it in make_join_readinfo().
        But moving get_sort_by_table() can still be done as a "gratuitous improvement"
        if reviewers agree.
     @ mysql-test/r/join_cache_jcl6.result
        see comment for the jcl6 result
     @ mysql-test/r/join_cache_jcl7.result
        see comment for the jcl1 result
     @ mysql-test/r/join_cache_jcl8.result
        see comment for the jcl1 result
     @ mysql-test/r/order_by_all.result
        Now, it's not using filesort. It's a good thing: the test was prefixed with:
        # shouldn't have "using filesort"
        but it had filesort!!
        It's also logical: the user asked to use index "t1.a" for ORDER BY t1.a
        so we should use it instead of filesort, and so we cannot do join buffering.
     @ mysql-test/r/order_by_icp_mrr.result
        see comment for the order_by_all result
     @ mysql-test/r/order_by_none.result
        see comment for the order_by_all result
     @ sql/sql_select.cc
        Plain "diff" isn't very readable, suggest to use a GUI diff to view this file.
        Changes are:
        
        1) removal of the block "// Can't use sort on head table if using join buffering".
        It looks like a duplicate of what was done at end of make_join_readinfo().
        It also wanted to handle FORCE INDEX, but did it badly; we
        replace it with clearer code dedicated to FORCE INDEX, which turns
        join buffering off so that the forced index can be used;
        the effect is the good plan change in order_by*.result.
        
        2) Change at end of make_join_readinfo().
        JOIN::sort_by_table isn't an exact measure of whether the optimizer
        will rely on some index order to do GROUP BY _or_ ORDER BY; for example,
        sort_by_table is NULL when no table was found which could give an order
        satisfying _both_ GROUP BY and ORDER BY. See the difference between
        "or" and "both": in BUG#12586926 and BUG#12619510, no single table can serve
        both clauses (so sort_by_table is NULL), but the optimizer was still trying
        to do GROUP BY through ordering of the first table (through
        either an index scan of, or a filesort of, the first table).
        Also, sort_by_table is computed at a moment when const tables are not known,
        which is another reason why it's inexact.
        So this code block was not always working: by relying on sort_by_table!=NULL,
        it missed cases where ordering-after-first-table had to be disabled. We replace
        it with simpler code: if one table does join buffering, and there's
        GROUP BY (resp. ORDER BY), this cannot be handled as a "simple GROUP BY" (resp.
        "simple ORDER BY").
        We don't need to set need_tmp in that block anymore, because we move the call to
        make_join_readinfo() to before the complex expression calculating
        need_tmp. This move is a good change as having less places where need_tmp
        is changed is easier to maintain. The idea is: need_tmp depends on a complex
        formula, we should set the arguments of this formula, and then compute the formula,
        and not change need_tmp later; instead of computing the formula, and changing
        need_tmp again later.
        
        3) The recently added assertion in setup_join_buffering() is removed;
        this assertion probably intended to say that if we disable join buffering in this
        function we should already have known it before, when we computed costs; but
        - it could never fire before, because no_jbuf_after was always bigger than tableno
        - now it can fire, due to "FORCE INDEX FOR (GROUP|ORDER) BY" (which now
        properly disables join buffering); in that case it's ok that the cost
        calculation was not aware: when the user uses FORCE INDEX she/he intentionally
        ignores cost calculations.
        
        4) make_join_orderinfo() and get_sort_by_join_tab() are not needed anymore.
     @ sql/sql_select.h
        not needed function (good, it was hard to understand)

    modified:
      mysql-test/include/join_cache.inc
      mysql-test/r/join_cache_jcl0.result
      mysql-test/r/join_cache_jcl1.result
      mysql-test/r/join_cache_jcl2.result
      mysql-test/r/join_cache_jcl3.result
      mysql-test/r/join_cache_jcl4.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
      mysql-test/r/order_by_all.result
      mysql-test/r/order_by_icp_mrr.result
      mysql-test/r/order_by_none.result
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc	2011-06-08 13:24:56 +0000
+++ b/mysql-test/include/join_cache.inc	2011-06-17 12:45:22 +0000
@@ -1,3 +1,7 @@
+--source include/have_innodb.inc
+
+set default_storage_engine=myisam;
+
 --disable_warnings
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
@@ -1647,3 +1651,153 @@ left join t8 on t3.col582 <=  1;
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 
 set @@join_buffer_size=default;
+
+--echo
+--echo # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+--echo # JCL>=5 AND MRR ENABLED"
+--echo
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+   col_varchar_key varchar(1) NOT NULL,    
+   KEY col_int_key (col_int_key),
+   KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+  
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+
+CREATE TABLE t2 (
+   col_datetime_key datetime NOT NULL,
+   col_varchar_key varchar(1) NOT NULL,
+   KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+
+# need to force the index, or it picks BNL for t2 (lower cost),
+# whereas we want to test BKA
+let $query=
+SELECT MIN(t2.col_datetime_key) AS field1,
+       t1.col_int_key AS field2  
+FROM t1
+  LEFT JOIN t2 force index (col_varchar_key)
+  ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+--echo
+--echo # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+--echo
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+
+let $query=SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+
+eval explain $query;
+# even though there is ORDER BY, it does not cover all columns, so
+# there is still randomness, so we have to sort client-side:
+--sorted_result
+eval $query;
+
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+--echo
+--echo # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+--echo
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+
+CREATE TABLE t1 (
+ col_varchar_key varchar(1))
+ ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+ pk int(11) NOT NULL AUTO_INCREMENT,
+ col_int_nokey int(11) NOT NULL,
+ col_int_key int(11) NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+
+let $query=SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+
+eval explain $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;

=== modified file 'mysql-test/r/join_cache_jcl0.result'
--- a/mysql-test/r/join_cache_jcl0.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl0.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 0;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl1.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 1;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl2.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 2;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl3.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 3;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl4.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 4;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	index	NULL	col_int_key	4	NULL	15	
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl5.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 5;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	Using join buffer (BKA, regular buffers)
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	Using join buffer (BKA, regular buffers)
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	16	Using temporary; Using filesort
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	Using join buffer (BKA, regular buffers)
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl6.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 6;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	Using join buffer (BKA, incremental buffers)
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	Using join buffer (BKA, incremental buffers)
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	16	Using temporary; Using filesort
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	Using join buffer (BKA, incremental buffers)
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl7.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 7;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	16	Using temporary; Using filesort
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl8.result	2011-06-17 12:45:22 +0000
@@ -1,5 +1,6 @@
 set optimizer_join_cache_level = 8;;
 set optimizer_switch='mrr_cost_based=off';
+set default_storage_engine=myisam;
 DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
 DROP DATABASE IF EXISTS world;
 set names utf8;
@@ -2237,8 +2238,8 @@ GROUP BY t1.col_int_key
 ORDER BY t1.col_int_key, t1.col_datetime
 LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	index	NULL	col_int_key	5	NULL	3	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT t1.col_int_key, t1.col_datetime 
 FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
 WHERE t2.col_int_key = 1 AND t2.col_int >= 3
@@ -2304,5 +2305,180 @@ count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 set @@join_buffer_size=default;
+
+# BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
+# JCL>=5 AND MRR ENABLED"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
+col_varchar_key varchar(1) NOT NULL,    
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
+CREATE TABLE t2 (
+col_datetime_key datetime NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) ENGINE=innodb;
+INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
+explain SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using temporary; Using filesort
+1	SIMPLE	t2	ref	col_varchar_key	col_varchar_key	3	test.t1.col_varchar_key	1	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT MIN(t2.col_datetime_key) AS field1,
+t1.col_int_key AS field2  
+FROM t1
+LEFT JOIN t2 force index (col_varchar_key)
+ON t1.col_varchar_key = t2.col_varchar_key  
+GROUP BY field2
+ORDER BY field1;
+field1	field2
+NULL	0
+2003-08-21 00:00:00	4
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_int_key int(11) NOT NULL,
+col_datetime_key datetime NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL,
+KEY col_int_key (col_int_key),
+KEY col_datetime_key (col_datetime_key)
+);
+INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
+INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
+INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
+INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
+INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
+INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
+INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
+INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
+INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
+INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
+INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
+INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
+INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
+INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
+INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
+INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
+INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
+INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
+INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
+INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_varchar_key varchar(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+INSERT INTO t2 VALUES
+(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
+(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
+(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
+explain SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	20	Using temporary; Using filesort
+1	SIMPLE	t1	ref	col_int_key	col_int_key	4	test.t2.pk	3	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT t1.col_varchar_nokey), t2.pk AS field4
+FROM t1
+RIGHT JOIN t2 ON t2.pk = t1.col_int_key 
+GROUP BY field1 , field4 
+ORDER BY t1.col_datetime_key ;
+field1	COUNT(DISTINCT t1.col_varchar_nokey)	field4
+b	0	13
+c	4	3
+d	0	19
+d	1	6
+d	2	9
+d	3	5
+e	0	20
+g	0	15
+j	2	1
+m	0	12
+m	1	4
+p	0	16
+q	0	17
+r	0	11
+s	0	10
+t	0	8
+v	1	2
+w	0	18
+x	0	14
+y	2	7
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
+
+# BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
+
+SET @old_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch='mrr=on,mrr_cost_based=off';
+CREATE TABLE t1 (
+col_varchar_key varchar(1))
+ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_nokey int(11) NOT NULL,
+col_int_key int(11) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (5,3,9);
+INSERT INTO t2 VALUES (6,246,24);
+INSERT INTO t2 VALUES (7,2,6);
+INSERT INTO t2 VALUES (8,9,1);
+INSERT INTO t2 VALUES (9,3,6);
+INSERT INTO t2 VALUES (10,8,2);
+INSERT INTO t2 VALUES (11,1,4);
+INSERT INTO t2 VALUES (12,8,8);
+INSERT INTO t2 VALUES (13,8,4);
+INSERT INTO t2 VALUES (14,5,4);
+INSERT INTO t2 VALUES (15,7,7);
+INSERT INTO t2 VALUES (16,5,4);
+INSERT INTO t2 VALUES (17,1,1);
+INSERT INTO t2 VALUES (18,6,9);
+INSERT INTO t2 VALUES (19,2,4);
+INSERT INTO t2 VALUES (20,9,8);
+explain SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	alias2	ALL	NULL	NULL	NULL	NULL	16	Using temporary; Using filesort
+1	SIMPLE	alias3	eq_ref	PRIMARY	PRIMARY	4	test.alias2.col_int_nokey	1	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4 
+FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
+alias2.col_int_nokey 
+left join t1 
+ON alias3.col_int_nokey 
+GROUP BY field1, field4
+LIMIT 15;
+field1	field4
+NULL	1
+NULL	2
+NULL	4
+NULL	7
+NULL	8
+NULL	9
+DROP TABLE t1,t2;
+SET @@optimizer_switch=@old_optimizer_switch;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/order_by_all.result	2011-06-17 12:45:22 +0000
@@ -2489,8 +2489,8 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	
 # should have "using filesort"
 EXPLAIN 
 SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;

=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result	2011-06-17 12:45:22 +0000
@@ -2489,8 +2489,8 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition; Using MRR; Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using index condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	
 # should have "using filesort"
 EXPLAIN 
 SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;

=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/order_by_none.result	2011-06-17 12:45:22 +0000
@@ -2488,8 +2488,8 @@ INSERT INTO t2 SELECT a+4, b FROM t2;
 EXPLAIN 
 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	a	a	5	NULL	2	Using where; Using temporary; Using filesort
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (BNL, incremental buffers)
+1	SIMPLE	t1	range	a	a	5	NULL	2	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	
 # should have "using filesort"
 EXPLAIN 
 SELECT * FROM t1 USE INDEX FOR ORDER BY (a), t2 WHERE t1.a < 2 ORDER BY t1.a;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-06-11 13:38:32 +0000
+++ b/sql/sql_select.cc	2011-06-17 12:45:22 +0000
@@ -242,7 +242,6 @@ static bool test_if_ref(Item *root_cond,
 
 void get_partial_join_cost(JOIN *join, uint idx, double *read_time_arg,
                            double *record_count_arg);
-static uint make_join_orderinfo(JOIN *join);
 static int
 join_read_record_no_init(JOIN_TAB *tab);
 static
@@ -1829,7 +1828,7 @@ JOIN::optimize()
 {
   bool need_distinct;
   ulonglong select_opts_for_readinfo;
-  uint no_jbuf_after;
+  uint no_jbuf_after= UINT_MAX;
 
   DBUG_ENTER("JOIN::optimize");
   // to prevent double initialization on EXPLAIN
@@ -2328,24 +2327,26 @@ JOIN::optimize()
       sort_and_group= 0;
   }
 
-  // Can't use sort on head table if using join buffering
-  if (full_join)
+  select_opts_for_readinfo= 
+    (select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
+    (select_lex->ftfunc_list->elements ?  SELECT_NO_JOIN_CACHE : 0);
+
+  /*
+    If the hint FORCE INDEX FOR ORDER BY/GROUP BY is used for the first
+    table (it does not make sense for other tables) then we cannot do join
+    buffering.
+  */
+  if (const_tables < tables)
   {
-    TABLE *stable= (sort_by_table == (TABLE *) 1 ? 
-      join_tab[const_tables].table : sort_by_table);
-    /* 
-      FORCE INDEX FOR ORDER BY can be used to prevent join buffering when
-      sorting on the first table.
-    */
-    if (!stable || !stable->force_index_order)
-    {
-      if (group_list)
-        simple_group= 0;
-      if (order)
-        simple_order= 0;
-    }
+    const TABLE * const first= join_tab[const_tables].table;
+    if ((first->force_index_order && order) ||
+        (first->force_index_group && group_list))
+      no_jbuf_after= 0;
   }
 
+  if (make_join_readinfo(this, select_opts_for_readinfo, no_jbuf_after))
+    DBUG_RETURN(1);
+
   /*
     Check if we need to create a temporary table.
     This has to be done if all tables are not already read (const tables)
@@ -2363,23 +2364,6 @@ JOIN::optimize()
 	      test(select_options & OPTION_BUFFER_RESULT))) ||
              (rollup.state != ROLLUP::STATE_NONE && select_distinct));
 
-  /*
-    If the hint FORCE INDEX FOR ORDER BY/GROUP BY is used for the table
-    whose columns are required to be returned in a sorted order, then
-    the proper value for no_jbuf_after should be yielded by a call to
-    the make_join_orderinfo function. 
-    Yet the current implementation of FORCE INDEX hints does not
-    allow us to do it in a clean manner.
-  */   
-  no_jbuf_after= 1 ? tables : make_join_orderinfo(this);
-  select_opts_for_readinfo= 
-    (select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
-    (select_lex->ftfunc_list->elements ?  SELECT_NO_JOIN_CACHE : 0);
-
-  // No cache for MATCH == 'Don't use join buffering when we use MATCH'.
-  if (make_join_readinfo(this, select_opts_for_readinfo, no_jbuf_after))
-    DBUG_RETURN(1);
-
   /* Perform FULLTEXT search before all regular searches */
   if (!(select_options & SELECT_DESCRIBE))
     init_ftfuncs(thd, select_lex, test(order));
@@ -10591,37 +10575,6 @@ pick_table_access_method(JOIN_TAB *tab)
 }
 
 
-
-/*
-  Determine {after which table we'll produce ordered set} 
-
-  SYNOPSIS
-    make_join_orderinfo()
-     join
-
-   
-  DESCRIPTION 
-    Determine if the set is already ordered for ORDER BY, so it can 
-    disable join cache because it will change the ordering of the results.
-    Code handles sort table that is at any location (not only first after 
-    the const tables) despite the fact that it's currently prohibited.
-    We must disable join cache if the first non-const table alone is
-    ordered. If there is a temp table the ordering is done as a last
-    operation and doesn't prevent join cache usage.
-
-  RETURN
-    Number of table after which the set will be ordered
-    join->tables if we don't need an ordered set 
-*/
-
-static uint make_join_orderinfo(JOIN *join)
-{
-  JOIN_TAB *tab;
-  tab= join->get_sort_by_join_tab();
-  return tab ? tab-join->join_tab : join->tables;
-}
-
-
 /*
   Deny usage of join buffer for the specified table
 
@@ -10857,10 +10810,8 @@ static bool setup_join_buffering(JOIN_TA
 
   /* No join buffering if prevented by no_jbuf_after */
   if (tableno > no_jbuf_after)
-  {
-    DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
     goto no_join_cache;
-  }
+
   /* Non-linked join buffers can't guarantee one match */
   if (force_unlinked_cache &&
       tab->is_inner_table_of_outer_join() &&
@@ -11508,24 +11459,23 @@ make_join_readinfo(JOIN *join, ulonglong
   }
   join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
 
-  /* 
-    If a join buffer is used to join a table the ordering by an index
-    for the first non-constant table cannot be employed anymore.
-  */
   for (uint i= join->const_tables; i < join->tables; i++)
   {
-    JOIN_TAB *const tab=join->join_tab + i;
-    if (tab->use_join_cache != JOIN_CACHE::ALG_NONE)
+    if (join->join_tab[i].use_join_cache != JOIN_CACHE::ALG_NONE)
     {
-      JOIN_TAB *const sort_by_tab= join->get_sort_by_join_tab();
-      if (sort_by_tab)
-      {
-        join->need_tmp= 1;
-        join->simple_order= join->simple_group= 0;
-      }
+      /*
+        A join buffer is used for this table. We here inform the optimizer
+        that it should not rely on rows of the first non-const table being in
+        order thanks to an index scan; indeed join buffering of the present
+        table subsequently changes the order of rows.
+      */
+      if (join->order != NULL)
+        join->simple_order= false;
+      if (join->group_list != NULL)
+        join->simple_group= false;
       break;
     }
-  } 
+  }
 
   DBUG_RETURN(FALSE);
 }

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-06-08 13:24:56 +0000
+++ b/sql/sql_select.h	2011-06-17 12:45:22 +0000
@@ -2041,16 +2041,6 @@ public:
                                         select_lex == unit->fake_select_lex));
   }
   void cache_const_exprs();
-  /* 
-    Return the table for which an index scan can be used to satisfy 
-    the sort order needed by the ORDER BY/(implicit) GROUP BY clause 
-  */
-  JOIN_TAB *get_sort_by_join_tab()
-  {
-    return (!sort_by_table || skip_sort_order ||
-            ((group || tmp_table_param.sum_func_count) && !group_list)) ?
-              NULL : join_tab+const_tables;
-  }
 private:
   /**
     TRUE if the query contains an aggregate function but has no GROUP


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110617124522-wxql1pyva50x141z.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot19 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Jorgen Loland20 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot25 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Roy Lyseng23 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot25 Jun
      • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Roy Lyseng25 Jun