#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
