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

Hi Øystein,

On 12/11/2010 09:21 AM, Øystein Grøvlen wrote:
> Thanks Tor,  Looks good.  Approved.
> See below for one question:
...
>> === modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
>> --- a/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-06 13:10:10 +0000
>> +++ b/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-10 15:00:25 +0000
>> @@ -347,7 +347,7 @@ GROUP BY t2.pk
>> );
>> id select_type table type possible_keys key key_len ref rows filtered
>> Extra
>> 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
>> -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index
>> condition; Using where; Using filesort
>> +2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition
>> Warnings:
>> Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from
>> `test`.`t1` where 0
>> DROP TABLE t1, t2;
>
> For this query, if I replace GROUP BY with ORDER BY, "Using filesort" is
> still there. Is that a reason for concern?

It makes no sense to maintain ordering in an EXISTS subquery because we're only 
interested in whether or not there is (at least) one record in the subquery 
result. Hence, the rewritten GROUP BY is actually more correct than the plan for 
ORDER BY.

Tor, a possible extension to your patch is to unconditionally remove order by 
from exists subqueries like we do for IN/ALL/ANY, but I understand if you don't 
want to do this as part of this bugfix.

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway