List:Commits« Previous MessageNext Message »
From:Olav Sandstaa Date:August 2 2010 12:28pm
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)
Bug#54481
View as plain text  
Hi Guilhem,

Thanks for the GREAT comments to the patch. The patch looks fine and 
correct.

Minor comments (that you can feel free to ignore):

1. Add a "#" to the third line in the test:

--echo #
--echo # BUG#54481 "GROUP BY loses effect with JOIN + ORDER BY + LIMIT
--echo and join_cache_level=5-8"
--echo #

2. Add a short per-file-commit message for your change to sql_select.h?

3. I understand the motivation for your "second code change" where you 
remove the code from reverting from JT_NEXT to JT_ALL. Ideally, I would 
have preferred this to be a separate change/patch.

Question: This bug is reported as a bug in mysql-next-mr-opt-bugfixing 
tree and tagged as a "new optimizer feature bug". With your second test 
query where you use force/ignore indexes you also show that this is an 
issue with non-BKA implementation. I have run the query against 
mysql-next-mr-bugfixing and see the same "wrong result" issue there. 
Which tree do you plan to push this fix into?

Olav


On 07/30/10 16:54, Guilhem Bichot wrote:
> #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;
>     }
>
>    
>
>
>
>    


Thread
bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)Bug#54481Guilhem Bichot30 Jul
Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)Bug#54481Olav Sandstaa2 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)Bug#54481Guilhem Bichot2 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)Bug#54481Guilhem Bichot6 Aug