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;
+
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
