List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:December 10 2010 10:10am
Subject:Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)
Bug#58782
View as plain text  
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
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