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
>
>
