From: Jorgen Loland
Date: December 10 2010 10:10am
Subject: Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)
 Bug#58782
List-Archive: http://lists.mysql.com/commits/126512
Message-Id: <4D01FC9E.2010206@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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