List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 22 2011 12:46pm
Subject:Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926
Bug#12619510 Bug#12619868
View as plain text  
Hi Guilhem,

thank you for a clever bugfix, it certainly makes the logic of the optimizer 
simpler and clearer.

I have a few typographical comments, and a suggestion for a followup patch 
below, otherwise the bugfix is approved.

On 17.06.11 14.46, Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on
> revid:jorgen.loland@stripped
>
>   3385 Guilhem Bichot	2011-06-17
>        Fix for
>        BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH JCL>=5 AND MRR
> ENABLED"
>        BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
>        BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5":

I'd appreciate it if the commit log lines were max. 70 characters wide. Some 
tools have a nasty habit of wrapping lines wider than that.

>        the optimizer uses join buffering but doesn't realize that, as a consequence
>        of join buffering, doing GROUP BY by getting rows in order from the first
> table
>        is not going to work (join buffering shuffles rows). Cause of this
> optimizer's
>        oversight is that JOIN::sort_by_table is not fully exact; it may be good
> enough
>        for cost calculations, but not when some logic must be fully exact.
>       @ mysql-test/include/join_cache.inc
>          need innodb table for one testcase; but ENGINE=INNODB won't work
>          without include/have_innodb.inc. But the presence of that include
>          will automatically use innodb as default engine for the entire test,
>          which changes EXPLAIN output of many tests; this is unwanted,
>          so we restore the default engine to myisam, to preserve the test.
>       @ mysql-test/r/join_cache_jcl1.result
>          Effect of the bugfix: join buffering is not used anymore for
>          SELECT t1.col_int_key, t1.col_datetime
>          FROM t1 force index (col_int_key), t2 ignore index (col_int_key)
>          WHERE t2.col_int_key = 1 AND t2.col_int>= 3
>          GROUP BY t1.col_int_key
>          ORDER BY t1.col_int_key, t1.col_datetime
>          LIMIT 2;
>          This makes sense as FORCE INDEX for t1.col_int_key, by definition of FORCE
> INDEX,
>          should apply to "GROUP BY t1.col_int_key" too and thus GROUP BY should use
> the
>          index order, which makes join buffering impossible.
>          "Temporary" and "filesort" remain needed for ORDER BY.
>       @ mysql-test/r/join_cache_jcl2.result
>          see comment for the jcl1 result
>       @ mysql-test/r/join_cache_jcl3.result
>          see comment for the jcl1 result
>       @ mysql-test/r/join_cache_jcl4.result
>          see comment for the jcl1 result
>       @ mysql-test/r/join_cache_jcl5.result
>          Bugs started at jcl>=5.
>          Effects of the bugfix, in order:
>
>          1) same as in jcl1 result file
>
>          2) In testcase of BUG#12586926, we had a wrong extra "NULL,4" row; the fix
> also changes
>          EXPLAIN output:
>          - 1	SIMPLE	t1	index	NULL	col_int_key	4	NULL	3	Using temporary; Using
> filesort
>          + 1	SIMPLE	t1	index	NULL	col_varchar_key	7	NULL	3	Using index; Using
> temporary; Using filesort
>          Let's study how, without the bugfix, the bug happened.
>          The optimizer chooses to do an index scan on the "col_varchar_key" index,
> then calls
>          make_join_readinfo() which decides to do join buffering. At end of
> make_join_readinfo(),
>          the function takes at attempt at disabling the choice of index ordering for
> GROUP BY

"an attempt"

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

I do not agree to this. There is good reason to have join buffering considered 
as early as possible.

If we have a join of tables t1, t2 and t3 and force to use an index for t1 
(which effectlively makes the table first in the join order), efficient 
calculation of the join plan for t2 and t3 still matters. By applying this 
information at the earlier stage inside the greedy optimizer, would make the 
cost estimates more accurate and hence there is a better chance of getting an 
optimal plan.

It think it would be possible to do this as follows, in a separate followup patch:

1. When a table has a FORCE INDEX FOR GROUP BY/ORDER BY on it, make it first in 
the join plan by setting all other tables to be dependent on this table (unless 
the table itself has dependencies to it).

2. When considering the first table in best_access_path(), set a flag in the 
JOIN object that tells whether to consider join buffering for subsequent tables 
or not.

3. Take this flag into consideration when determining whether to use join 
buffering for all tables.
>
>          4) make_join_orderinfo() and get_sort_by_join_tab() are not needed anymore.
>       @ sql/sql_select.h
>          not needed function (good, it was hard to understand)
>
>      modified:
>        mysql-test/include/join_cache.inc
>        mysql-test/r/join_cache_jcl0.result
>        mysql-test/r/join_cache_jcl1.result
>        mysql-test/r/join_cache_jcl2.result
>        mysql-test/r/join_cache_jcl3.result
>        mysql-test/r/join_cache_jcl4.result
>        mysql-test/r/join_cache_jcl5.result
>        mysql-test/r/join_cache_jcl6.result
>        mysql-test/r/join_cache_jcl7.result
>        mysql-test/r/join_cache_jcl8.result
>        mysql-test/r/order_by_all.result
>        mysql-test/r/order_by_icp_mrr.result
>        mysql-test/r/order_by_none.result
>        sql/sql_select.cc
>        sql/sql_select.h
> === modified file 'mysql-test/include/join_cache.inc'
> --- a/mysql-test/include/join_cache.inc	2011-06-08 13:24:56 +0000
> +++ b/mysql-test/include/join_cache.inc	2011-06-17 12:45:22 +0000
> @@ -1,3 +1,7 @@
> +--source include/have_innodb.inc
> +
> +set default_storage_engine=myisam;
> +
>   --disable_warnings
>   DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
>   DROP DATABASE IF EXISTS world;
> @@ -1647,3 +1651,153 @@ left join t8 on t3.col582<=  1;
>   drop table t1,t2,t3,t4,t5,t6,t7,t8;
>
>   set @@join_buffer_size=default;
> +
> +--echo
> +--echo # BUG#12586926 "EXTRA ROW WITH JOIN + GROUP BY + ORDER BY WITH
> +--echo # JCL>=5 AND MRR ENABLED"
> +--echo
> +
> +SET @old_optimizer_switch=@@optimizer_switch;
> +SET optimizer_switch='mrr=on,mrr_cost_based=off';
> +
> +CREATE TABLE t1 (  col_int_key int(11) NOT NULL,
> +   col_varchar_key varchar(1) NOT NULL,
> +   KEY col_int_key (col_int_key),
> +   KEY col_varchar_key (col_varchar_key,col_int_key)
> +) ENGINE=innodb;
> +
> +INSERT INTO t1 VALUES (0,'j'),(4,'b'),(4,'d');
> +
> +CREATE TABLE t2 (
> +   col_datetime_key datetime NOT NULL,
> +   col_varchar_key varchar(1) NOT NULL,
> +   KEY col_varchar_key (col_varchar_key)
> +) ENGINE=innodb;
> +
> +INSERT INTO t2 VALUES ('2003-08-21 00:00:00','b');
> +
> +# need to force the index, or it picks BNL for t2 (lower cost),
> +# whereas we want to test BKA
> +let $query=
> +SELECT MIN(t2.col_datetime_key) AS field1,
> +       t1.col_int_key AS field2
> +FROM t1
> +  LEFT JOIN t2 force index (col_varchar_key)
> +  ON t1.col_varchar_key = t2.col_varchar_key
> +GROUP BY field2
> +ORDER BY field1;
> +
> +eval explain $query;
> +eval $query;
> +
> +DROP TABLE t1,t2;
> +SET @@optimizer_switch=@old_optimizer_switch;
> +
> +--echo
> +--echo # BUG#12619510 "JCL: MORE ROWS AND DIFFERENT OUTPUT WITH JCL>=5"
> +--echo
> +
> +SET @old_optimizer_switch=@@optimizer_switch;
> +SET optimizer_switch='mrr=on,mrr_cost_based=off';
> +
> +CREATE TABLE t1 (
> +col_int_key int(11) NOT NULL,
> +col_datetime_key datetime NOT NULL,
> +col_varchar_nokey varchar(1) NOT NULL,
> +KEY col_int_key (col_int_key),
> +KEY col_datetime_key (col_datetime_key)
> +);
> +INSERT INTO t1 VALUES (7,'2004-06-06 04:22:12','v');
> +INSERT INTO t1 VALUES (0,'2005-11-13 01:12:31','s');
> +INSERT INTO t1 VALUES (9,'2002-05-04 01:50:00','l');
> +INSERT INTO t1 VALUES (3,'2004-10-27 10:28:45','y');
> +INSERT INTO t1 VALUES (4,'2006-07-22 05:24:23','c');
> +INSERT INTO t1 VALUES (2,'2002-05-16 21:34:03','i');
> +INSERT INTO t1 VALUES (5,'2008-04-17 10:45:30','h');
> +INSERT INTO t1 VALUES (3,'2009-04-21 02:58:02','q');
> +INSERT INTO t1 VALUES (1,'2008-01-11 11:01:51','a');
> +INSERT INTO t1 VALUES (3,'1900-01-01 00:00:00','v');
> +INSERT INTO t1 VALUES (6,'2007-05-17 18:24:57','u');
> +INSERT INTO t1 VALUES (7,'2007-08-07 00:00:00','s');
> +INSERT INTO t1 VALUES (5,'2001-08-28 00:00:00','y');
> +INSERT INTO t1 VALUES (1,'2004-04-16 00:27:28','z');
> +INSERT INTO t1 VALUES (204,'2005-05-03 07:06:22','h');
> +INSERT INTO t1 VALUES (224,'2009-03-11 17:09:50','p');
> +INSERT INTO t1 VALUES (9,'2007-12-08 01:54:28','e');
> +INSERT INTO t1 VALUES (5,'2009-07-28 18:19:54','i');
> +INSERT INTO t1 VALUES (0,'2008-06-08 00:00:00','y');
> +INSERT INTO t1 VALUES (3,'2005-02-09 09:20:26','w');
> +
> +CREATE TABLE t2 (
> +pk int(11) NOT NULL AUTO_INCREMENT,
> +col_varchar_key varchar(1) NOT NULL,
> +PRIMARY KEY (pk)
> +);

Perhaps delete AUTO_INCREMENT.

> +INSERT INTO t2 VALUES
> +(1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),(6,'d'),(7,'y'),
> +(8,'t'),(9,'d'),(10,'s'),(11,'r'),(12,'m'),(13,'b'),(14,'x'),
> +(15,'g'),(16,'p'),(17,'q'),(18,'w'),(19,'d'),(20,'e');
> +
> +let $query=SELECT t2.col_varchar_key AS field1 , COUNT(DISTINCT
> t1.col_varchar_nokey), t2.pk AS field4
> +FROM t1
> +RIGHT JOIN t2 ON t2.pk = t1.col_int_key
> +GROUP BY field1 , field4
> +ORDER BY t1.col_datetime_key ;
> +
> +eval explain $query;
> +# even though there is ORDER BY, it does not cover all columns, so
> +# there is still randomness, so we have to sort client-side:
> +--sorted_result
> +eval $query;
> +
> +DROP TABLE t1,t2;
> +SET @@optimizer_switch=@old_optimizer_switch;
> +
> +--echo
> +--echo # BUG#12619868 "JCL: MORE ROWS OF OUTPUT WHEN JCL>=5"
> +--echo
> +
> +SET @old_optimizer_switch=@@optimizer_switch;
> +SET optimizer_switch='mrr=on,mrr_cost_based=off';
> +
> +CREATE TABLE t1 (
> + col_varchar_key varchar(1))
> + ENGINE=MyISAM DEFAULT CHARSET=latin1;
> +CREATE TABLE t2 (
> + pk int(11) NOT NULL AUTO_INCREMENT,
> + col_int_nokey int(11) NOT NULL,
> + col_int_key int(11) NOT NULL,
> + PRIMARY KEY (pk),
> + KEY col_int_key (col_int_key)
> +) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

I think that you can delete AUTO_INCREMENT and DEFAULT CHARSET above.

> +INSERT INTO t2 VALUES (5,3,9);
> +INSERT INTO t2 VALUES (6,246,24);
> +INSERT INTO t2 VALUES (7,2,6);
> +INSERT INTO t2 VALUES (8,9,1);
> +INSERT INTO t2 VALUES (9,3,6);
> +INSERT INTO t2 VALUES (10,8,2);
> +INSERT INTO t2 VALUES (11,1,4);
> +INSERT INTO t2 VALUES (12,8,8);
> +INSERT INTO t2 VALUES (13,8,4);
> +INSERT INTO t2 VALUES (14,5,4);
> +INSERT INTO t2 VALUES (15,7,7);
> +INSERT INTO t2 VALUES (16,5,4);
> +INSERT INTO t2 VALUES (17,1,1);
> +INSERT INTO t2 VALUES (18,6,9);
> +INSERT INTO t2 VALUES (19,2,4);
> +INSERT INTO t2 VALUES (20,9,8);
> +
> +let $query=SELECT t1.col_varchar_key AS field1, alias2.col_int_key AS field4
> +FROM t2 AS alias2 STRAIGHT_JOIN t2 AS alias3 ON alias3.pk =
> +alias2.col_int_nokey
> +left join t1
> +ON alias3.col_int_nokey
> +GROUP BY field1, field4
> +LIMIT 15;
> +
> +eval explain $query;
> +--sorted_result
> +eval $query;
> +
> +DROP TABLE t1,t2;
> +SET @@optimizer_switch=@old_optimizer_switch;
>
<  Result files clipped away>


> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc	2011-06-11 13:38:32 +0000
> +++ b/sql/sql_select.cc	2011-06-17 12:45:22 +0000
> @@ -242,7 +242,6 @@ static bool test_if_ref(Item *root_cond,
>
>   void get_partial_join_cost(JOIN *join, uint idx, double *read_time_arg,
>                              double *record_count_arg);
> -static uint make_join_orderinfo(JOIN *join);
>   static int
>   join_read_record_no_init(JOIN_TAB *tab);
>   static
> @@ -1829,7 +1828,7 @@ JOIN::optimize()
>   {
>     bool need_distinct;
>     ulonglong select_opts_for_readinfo;
> -  uint no_jbuf_after;
> +  uint no_jbuf_after= UINT_MAX;
>
>     DBUG_ENTER("JOIN::optimize");
>     // to prevent double initialization on EXPLAIN
> @@ -2328,24 +2327,26 @@ JOIN::optimize()
>         sort_and_group= 0;
>     }
>
> -  // Can't use sort on head table if using join buffering
> -  if (full_join)
> +  select_opts_for_readinfo=
> +    (select_options&  (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
> +    (select_lex->ftfunc_list->elements ?  SELECT_NO_JOIN_CACHE : 0);
> +
> +  /*
> +    If the hint FORCE INDEX FOR ORDER BY/GROUP BY is used for the first
> +    table (it does not make sense for other tables) then we cannot do join
> +    buffering.
> +  */
> +  if (const_tables<  tables)
>     {
> -    TABLE *stable= (sort_by_table == (TABLE *) 1 ?
> -      join_tab[const_tables].table : sort_by_table);
> -    /*
> -      FORCE INDEX FOR ORDER BY can be used to prevent join buffering when
> -      sorting on the first table.
> -    */
> -    if (!stable || !stable->force_index_order)
> -    {
> -      if (group_list)
> -        simple_group= 0;
> -      if (order)
> -        simple_order= 0;
> -    }
> +    const TABLE * const first= join_tab[const_tables].table;
> +    if ((first->force_index_order&&  order) ||
> +        (first->force_index_group&&  group_list))
> +      no_jbuf_after= 0;
>     }

Please see my comment above on the placement of this logic.

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

Thanks,
Roy
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot19 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Jorgen Loland20 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot25 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Roy Lyseng23 Jun
    • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Guilhem Bichot25 Jun
      • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3385) Bug#12586926Bug#12619510 Bug#12619868Roy Lyseng25 Jun