From: Jorgen Loland
Date: January 3 2011 8:13am
Subject: Re: bzr commit into mysql-trunk-bugfixing branch (olav.sandstaa:3312)
 Bug#58463
List-Archive: http://lists.mysql.com/commits/127763
Message-Id: <4D218518.4070703@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi Olav,

Patch approved.

On 12/29/2010 01:03 PM, Olav Sandstaa wrote:
> Hi Jørgen,
>
> Thanks for looking at the patch. See replies inline:
>
> On 17/12/2010 12:15, Jorgen Loland wrote:
>> Hi Olav,
>>
>> Thank you for the patch. See comments inline
>>
>> On 12/09/2010 03:06 PM, Olav Sandstaa wrote:
>>> #At file:///export/home/tmp/mysql2/opt-bug58463-fix1/ based on
>>> revid:tor.didriksen@stripped
>>>
>>> 3312 Olav Sandstaa 2010-12-09
>>> Patch for Bug#58463 Error Can't find record on SELECT with JOIN and
>>> ORDER BY
>>>
>>> This problem was caused by the server pushing down an index condition on
>>> the primary key as part of the optimization phase. Later, during the
>>> execution phase the server (due to a bug) changed to use a secondary
>>> index
>>> instead of the primary index for accessing the table. This made MRR fail
>>> in phase 3 due to not finding the expected records when retrieving them
>>> using the primary key due to the pushed index condition filtering
>>> them away.
>>>
>>> This problem with the server accidentally switching to using a
>>> secondary index will
>>> be fixed by the fix for Bug#58456.
>>>
>>> This patch contains a test case based on the bug report and an added
>>> assert to
>>> the DS-MRR implementation that will trigger if a similar situation
>>> should occur
>>> again (DS-MRR used on a secondary index while an index condition has
>>> been pushed
>>> on the primary index for the same handler object).
>>>
>> (...)
>>> === modified file 'sql/handler.cc'
>>> --- a/sql/handler.cc 2010-11-18 16:34:56 +0000
>>> +++ b/sql/handler.cc 2010-12-09 14:06:48 +0000
>>> @@ -4683,6 +4683,16 @@
>>> n_ranges, mode, buf);
>>> DBUG_RETURN(retval);
>>> }
>>> +
>>> + /*
>>> + This assert will hit if we have pushed an index condition to the
>>> + primary key index and then "changes our mind" and uses a different
>>> + index for retrieving data with MRR.
>>> + */
>>
>> typo: "change our mind" and use
>
> I will correct this in an updated version of the patch.
>
>>> + DBUG_ASSERT(!h->pushed_idx_cond ||
>>> + h->pushed_idx_cond_keyno == h->active_index ||
>>> + h->pushed_idx_cond_keyno != table->s->primary_key);
>>> +
>>
>> Is this a problem when we switch from PK to secondary index only?
>> Could it also be the case that we switch from one secondary index to
>> another secondary index?
>
> It is a problem for MRR only when switching from PK to a secondary
> index. The reason for this is that MRR will use the PK in its last phase
> for retrieving the complete records.
>
> If we switch from one secondary index to another secondary index it
> would not be a problem for MRR. If an index condition was pushed on the
> first of these two secondary indexes it would not be used when MRR
> retrieved the records since this first secondary index would not be used
> by MRR (and thus, the pushed index condition would not be used).
>
> Olav
>
>
>
>
>

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