List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:December 10 2010 2:30pm
Subject:Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782
View as plain text  
On Fri, Dec 10, 2010 at 11:10 AM, Jorgen Loland <jorgen.loland@stripped>wrote:

> Hi Tor,
>
> Thank you for the patch. I agree that it fixes the problem, but I think we
> can do better :-)
>
> In this bug, we have
>
> SELECT *
> FROM ot
>
> WHERE (field1, field2) IN (
>  SELECT pk AS field1, col_int_nokey AS field2
>  FROM t1
>  WHERE col_int_nokey > 0
>  GROUP BY field1, field2
> );
>
> and in JOIN::optimize() for the subquery, we recognize that this will
> create groups with exactly one record in each (select fields == group by
> fields). In such cases, we only need to keep the "ordering" part of GROUP
> BY, so the query is rewritten to
>
> SELECT *
> FROM ot
>
> WHERE (field1, field2) IN (
>  SELECT pk AS field1, col_int_nokey AS field2
>  FROM t1
>  WHERE col_int_nokey > 0
>  ORDER BY field1, field2
> );
>
>
> Explain confirms that we do ordering on the subquery:
> id    select_type  table  type  possible_keys (...) Extra
> 1     PRIMARY      ot     ALL   NULL                Using where
> 2     DEPENDENT SUBQUERY t1 ALL NULL                Using where; Using
> filesort
>
> However, ORDER BY makes no sense in subqueries. This fact is recognized if
> you run this instead:
>
> SELECT *
> FROM ot
>
> WHERE (field1, field2) IN (
>  SELECT pk AS field1, col_int_nokey AS field2
>  FROM t1
>  WHERE col_int_nokey > 0
>  ORDER BY field1, field2
> );
>
> In this query, join->order is removed as part of resolve_subquery() which
> is called from JOIN::prepare().
>
> Thus, I suggest that we do not require ordering if the optimized-away GROUP
> BY is in a subselect, e.g. like this:
>
>
> === modified file 'sql/sql_select.cc'
> --- sql/sql_select.cc   2010-12-09 11:54:39 +0000
> +++ sql/sql_select.cc   2010-12-10 10:00:21 +0000
> @@ -2110,7 +2110,10 @@ JOIN::optimize()
>           'order' as is.
>        */
>       if (!order || test_if_subpart(group_list, order))
> -          order= skip_sort_order ? 0 : group_list;
> +        order= (skip_sort_order || select_lex->master_unit()->item) ?
> +          0 : group_list;
> +
>
>
>
nice!!


> --
> Jørgen Løland | Senior Software Engineer | +47 73842138
> Oracle MySQL
> Trondheim, Norway
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:
> http://lists.mysql.com/commits?unsub=1
>
>

Thread
bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782Tor Didriksen9 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)Bug#58782Jorgen Loland10 Dec
    • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782Tor Didriksen10 Dec