#At file:///home/mysql_src/bzrrepos_new/54481/ based on revid:epotemkin@stripped
3219 Guilhem Bichot 2010-07-30
Fix for BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
and join_cache_level=5-8". Join buffering yields tuples in non-sorted
order, needs an explicit sort. See comment of sql_select.cc for details.
@ mysql-test/include/join_cache.inc
test for bug
@ mysql-test/r/join_cache_jcl1.result
before the fix, the final SELECT would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
Note how this final SELECT, which has FORCE INDEX, does a table scan on t1,
whereas we could have expected an index scan due to FORCE INDEX.
This is explained like this:
- index scan is done for GROUP BY only if JOIN::simple_group is
true; indeed around line sql_select.cc:2286,
test_if_skip_sort_order(), which may pick index scan
(JT_NEXT), is not called if simple_group is false
- with the code patch, make_join_readinfo() now sets
simple_group to "false" for this query due to join buffering,
in order to force an explicit sort, thus index scan isn't picked.
@ mysql-test/r/join_cache_jcl2.result
before the fix, the final SELECT would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl3.result
before the fix, the final SELECT would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl4.result
before the fix, the final SELECT would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl5.result
before the fix, the two final SELECTs would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl6.result
before the fix, the two final SELECTs would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl7.result
before the fix, the two final SELECTs would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ mysql-test/r/join_cache_jcl8.result
before the fix, the two final SELECTs would return 0 and 0 in the first column
of the two rows, instead of 0 and 2.
@ sql/sql_select.cc
Here is the scenario of the bug's testcase.
First, assume join buffering is disabled.
We read table t1 with index scan on the "t1.col_int_key" index, this
yields tuples ordered by t1.col_int_key, which is the GROUP BY column:
such tuple is joined with all tuples from t2, using nested-loop join
(non-block) and the result is sent to end_write_group(): this function
receives tuples in this order:
tuple1_from_t1 | tuple1_from_t2
tuple1_from_t1 | tuple2_from_t2
tuple2_from_t1 | tuple1_from_t2
tuple2_from_t1 | tuple2_from_t2
tuple3_from_t1 | tuple1_from_t2
tuple3_from_t1 | tuple2_from_t2
GROUP BY is done with end_write_group(): each new tuple, if its GROUP BY
column value is different from the previous tuple's, defines a new
group. So we get three groups:
tuple1_from_t1 | some_tuple_from_t2
tuple2_from_t1 | some_tuple_from_t2
tuple3_from_t1 | some_tuple_from_t2
and after applying "ORDER BY" (a sort on t1.col_int_key and
t1.col_datetime) and "LIMIT 2" and keeping only desired columns, we get
tuple1_from_t1
tuple2_from_t1
Now, assume table t2 does join buffering (BKA in the bug's case,
but bug exists also with block-nested-loop, see join_cache_jcl1.result).
t2 caches tuples from t1, then all tuples from t2 are read, then each
tuple from t2 is joined with tuples from t1 found in the cache
(JOIN_CACHE_BKA::join_matching_records()), so what end_write_group()
receives is (note the order: we join one tuple from t2 with all tuples
from t1):
tuple1_from_t1 | tuple1_from_t2
tuple2_from_t1 | tuple1_from_t2
tuple3_from_t1 | tuple1_from_t2
tuple1_from_t1 | tuple2_from_t2
tuple2_from_t1 | tuple2_from_t2
tuple3_from_t1 | tuple2_from_t2
and end_write_group() produces those groups:
tuple1_from_t1 | tuple1_from_t2
tuple2_from_t1 | tuple1_from_t2
tuple3_from_t1 | tuple1_from_t2
tuple1_from_t1 | tuple2_from_t2
tuple2_from_t1 | tuple2_from_t2
tuple3_from_t1 | tuple2_from_t2
After "ORDER BY":
tuple1_from_t1 | tuple1_from_t2
tuple1_from_t1 | tuple2_from_t2
tuple2_from_t1 | tuple1_from_t2
tuple2_from_t1 | tuple2_from_t2
tuple3_from_t1 | tuple1_from_t2
tuple3_from_t1 | tuple2_from_t2
and after "LIMIT 2":
tuple1_from_t1 | tuple1_from_t2
tuple1_from_t1 | tuple2_from_t2
which is wrong.
The problem is that join buffering changes the order of tuples in a way
which is not suitable for end_write_group() (a GROUP BY column value
different from previous does not necessarily signal an unseen
value!). When such order change happens, end_write_group() shouldn't be
used: we should first send joined tuples to a temporary table using
end_write(), then do a sorting pass to sort on the GROUP BY column, and
then do groupping (and then do ORDER BY and LIMIT).
There is already code to handle this situation, in make_join_readinfo()
(added by the fix for BUG 42955, which is a bug similar to ours):
/*
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 (i=join->const_tables ; i < join->tables ; i++)
{
JOIN_TAB *tab=join->join_tab+i;
if (tab->use_join_cache)
{
JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab();
if (sort_by_tab && !join->need_tmp)
{
join->need_tmp= 1;
join->simple_order= join->simple_group= 0;
if (sort_by_tab->type == JT_NEXT)
{
sort_by_tab->type= JT_ALL;
sort_by_tab->read_first_record= join_init_read_record;
}
}
break;
}
}
When this code works, it instructs, with
need_tmp=1,simple_order=simple_group=0, to do an explicit sorting of
joined tuples, not relying on the order returned by the index (as this
order is lost by join buffering of the next tables). Note how this means
that join buffering wins over index ordering, whereas in 5.1 it's the
opposite choice (see make_join_readinfo() in 5.1).
In our scenario the code doesn't work, because join->need_tmp is already
true. The code believes that as need_tmp is true, sorting is already
programmed to happen, so index order will not be relied upon anyway. In
our case, need_tmp is true because we have GROUP BY and ORDER BY on
different columns (see how need_tmp is set around comment "Check if we
need to create a temporary table" in JOIN::optimize()). We need to
buffer results of GROUP BY in a temporary table so that we can later
sort them for ORDER BY (this is why we use end_write_group(), writing to
a temporary table, instead of end_send_group() which would send groups
directly to the client). We are in a case where we use a temporary table
(for ORDER BY) but also plan to rely on index order (for GROUP BY).
The fix is to remove the dependency of this code on join->need_tmp. This
way, explicit sorting is done for GROUP BY.
A similar change is done to make_join_orderinfo(), even though this
function is currently unused.
After this change, all tests pass except that for this query in
group_min_max.test:
EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
table scan for t1_1 is now picked instead of index scan.
This is because the "if(sort_by_tab)" block is now entered, and so
JT_NEXT (index scan) is changed to JT_ALL (table scan).
While this may be ok, a second code change is proposed: don't switch
back to JT_ALL. After all, JT_NEXT may be a better choice, for example
if we are using index condition pushdown. Even for
SELECT COUNT(*) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
and without any code change, MySQL picks index scan, so it may have a
good reason. The proposal here is to respect this decision. There is no
obvious reason why the need to do explicit sorting would mandate a table
scan instead of an index scan. Not relying on index order shouldn't mean
refusing to do index scan. As a result of this second code change,
group_min_max.test passes, but this query in join_cache_jcl*.test:
explain select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
switches from "table scan" to "index scan". It actually had "index scan"
before the fix for BUG 42955 changed it to "table scan"; that result
file change had comment "Adjusted results for a test case", so it is
assumed that reverting to the old result is not a problem; the SELECT
(without EXPLAIN) at least returns unchanged results.
Unrelated change: the unlikely(s->keyuse) was lost by a merge
(epotemkin@stripped), we restore it
as it has speed advantages (was added as part of BUG 50595).
modified:
mysql-test/include/join_cache.inc
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
sql/sql_select.cc
sql/sql_select.h
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc 2010-07-04 15:46:57 +0000
+++ b/mysql-test/include/join_cache.inc 2010-07-30 14:54:32 +0000
@@ -1526,3 +1526,54 @@ eval explain $query_i;
eval $query_i;
drop table t1;
+
+--echo #
+--echo # BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+--echo and join_cache_level=5-8"
+--echo #
+
+CREATE TABLE t1 (
+ `col_int_key` int,
+ `col_datetime` datetime,
+ KEY `col_int_key` (`col_int_key`)
+);
+
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+
+CREATE TABLE t2 (
+ `col_int` int,
+ `col_int_key` int,
+ KEY `col_int_key` (`col_int_key`)
+);
+
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+
+# The WHERE clause is true for all rows of t2
+# but is needed to trigger the desired plan.
+# Query uses BKA.
+let $query=SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+
+eval explain $query;
+eval $query;
+
+# by disabling one index and forcing another, we hit
+# block-nested-loop join and see the same bug
+let $query=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;
+
+eval explain $query;
+eval $query;
+
+drop table t1,t2;
=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2010-07-30 14:54:32 +0000
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL col_int_key 5 NULL 2 Using temporary; Using filesort
+1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl2.result 2010-07-30 14:54:32 +0000
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL col_int_key 5 NULL 2 Using temporary; Using filesort
+1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl3.result 2010-07-30 14:54:32 +0000
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL col_int_key 5 NULL 2 Using temporary; Using filesort
+1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl4.result 2010-07-30 14:54:32 +0000
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL col_int_key 5 NULL 2 Using temporary; Using filesort
+1 SIMPLE t2 ref col_int_key col_int_key 5 const 1 Using where
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl5.result 2010-07-30 14:54:32 +0000
@@ -1448,7 +1448,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+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 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA, regular buffers)
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl6.result 2010-07-30 14:54:32 +0000
@@ -1448,7 +1448,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+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 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA, incremental buffers)
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl7.result 2010-07-30 14:54:32 +0000
@@ -1448,7 +1448,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+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 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result 2010-07-04 15:46:57 +0000
+++ b/mysql-test/r/join_cache_jcl8.result 2010-07-30 14:54:32 +0000
@@ -1448,7 +1448,7 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 8 Using index; Using temporary; Using filesort
1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -2193,4 +2193,60 @@ col_int_key
9
9
drop table t1;
+#
+# BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
+and join_cache_level=5-8"
+#
+CREATE TABLE t1 (
+`col_int_key` int,
+`col_datetime` datetime,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t1 VALUES (2,'2003-02-11 21:19:41');
+INSERT INTO t1 VALUES (3,'2009-10-18 02:27:49');
+INSERT INTO t1 VALUES (0,'2000-09-26 07:45:57');
+CREATE TABLE t2 (
+`col_int` int,
+`col_int_key` int,
+KEY `col_int_key` (`col_int_key`)
+);
+INSERT INTO t2 VALUES (14,1);
+INSERT INTO t2 VALUES (98,1);
+explain SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+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 ref col_int_key col_int_key 5 const 1 Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT t1.col_int_key, t1.col_datetime
+FROM t1,t2
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+explain 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;
+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)
+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;
+col_int_key col_datetime
+0 2000-09-26 07:45:57
+2 2003-02-11 21:19:41
+drop table t1,t2;
set optimizer_join_cache_level = default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-07-26 11:34:07 +0000
+++ b/sql/sql_select.cc 2010-07-30 14:54:32 +0000
@@ -6634,7 +6634,7 @@ best_access_path(JOIN *join,
This isn't unlikely at all, but unlikely() cuts 6% CPU time on a 20-table
search when s->keyuse==0, and has no cost when s->keyuse!=0.
*/
- if (s->keyuse)
+ if (unlikely(s->keyuse != NULL))
{ /* Use key if possible */
TABLE *table= s->table;
KEYUSE *keyuse;
@@ -9942,8 +9942,6 @@ pick_table_access_method(JOIN_TAB *tab)
static uint make_join_orderinfo(JOIN *join)
{
JOIN_TAB *tab;
- if (join->need_tmp)
- return join->tables;
tab= join->get_sort_by_join_tab();
return tab ? tab-join->join_tab : join->tables;
}
@@ -10825,15 +10823,10 @@ make_join_readinfo(JOIN *join, ulonglong
if (tab->use_join_cache)
{
JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab();
- if (sort_by_tab && !join->need_tmp)
+ if (sort_by_tab)
{
join->need_tmp= 1;
join->simple_order= join->simple_group= 0;
- if (sort_by_tab->type == JT_NEXT)
- {
- sort_by_tab->type= JT_ALL;
- sort_by_tab->read_first_record= join_init_read_record;
- }
}
break;
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2010-07-16 12:21:31 +0000
+++ b/sql/sql_select.h 2010-07-30 14:54:32 +0000
@@ -1929,7 +1929,7 @@ public:
*/
JOIN_TAB *get_sort_by_join_tab()
{
- return (need_tmp || !sort_by_table || skip_sort_order ||
+ return (!sort_by_table || skip_sort_order ||
((group || tmp_table_param.sum_func_count) && !group_list)) ?
NULL : join_tab+const_tables;
}
Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20100730145432-zgwmyqnq8yyhawz6.bundle
