List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:July 30 2010 2:54pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (guilhem.bichot:3219)
Bug#54481
View as plain text  
#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
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