From: Roy Lyseng
Date: June 16 2011 9:41am
Subject: Re: bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12603200
List-Archive: http://lists.mysql.com/commits/139307
Message-Id: <4DF9CFBE.3080802@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

On 16.06.11 11.37, Øystein Grøvlen wrote:
> On 16/06/2011 11:25, Roy Lyseng wrote:
>> On 16.06.11 11.21, Øystein Grøvlen wrote:
>>> On 16/06/2011 10:48, Roy Lyseng wrote:
>>>> On 16.06.11 10.03, Øystein Grøvlen wrote:
>>>>> On 15/06/2011 15:57, Roy Lyseng wrote:
>>>>>> #At file:///home/rl136806/mysql/repo/mysql-work0/ based on
>>>>>> revid:jorgen.loland@stripped
>>>>>>
>>>>>> 3385 Roy Lyseng 2011-06-15
>>>>>> Bug#12603200: Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
>>>>>>
>>>>>> The problematic query is semi-join transformed and a LooseScan
>>>>>> strategy is selected. setup_semijoin_dups_elimination() inspects
>>>>>> the provided quick select object and attempts to set it to require
>>>>>> ordering of output rows. However, the quick select object was not
>>>>>> selected in the first place (see
>>>>>> Loose_scan_opt::check_ref_access_part1()), hence there is a missing
>>>>>> check that the index covered by the quick select matches the index
>>>>>> selected for the loose scan access.
>>>>>>
>>>>>> Fixed by adding this check, and also deleting the quick select object
>>>>>> if it was not chosen for accessing this table.
>>>>>
>>>>> Hi,
>>>>>
>>>>> Did you consider deleting the quick select object at the point where
>>>>> it is
>>>>> decided to not use it?
>>>>
>>>> IMHO this is the place where the decision is taken. Do you have another
>>>> view?
>>>>
>>>
>>> You say in it the commit comments that the quick select object was not
>>> selected
>>> in Loose_scan_opt::check_ref_access_part1(). Sounds to me like that is
>>> where the
>>> decision is made. Maybe that is where the need_sorted_output call
>>> should have
>>> been put in the first place.
>>
>> Loose_scan_opt::check_ref_access_part1( is called from
>> best_access_part() which explores potential uses of various access
>> methods. The quick object might be used in a different context later, so
>> it would be wrong to delete it at this stage.
>> setup_semijoin_dups_elimination() is called from make_join_readinfo()
>> which finalizes the plan after the optimal order has been chosen, so I
>> think this is the right place to either select or discard the quick object.
>
> That's a very good point. My next question is then whether loose_scan is the
> only case where the quick object may not be selected, or should it be deleted in
> other cases, too?
>
loose_scan is "special", because it requires ordered access to the underlying 
tables. I do not think that any other access method needs the same ordering. But 
I am not an expert here. I CC'ed some experts in the area of range optimizer 
that might fill in the details here.

Thanks,
Roy