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
>>> 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
>>> 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);
>>> + /*
>>> + 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).
Jørgen Løland | Senior Software Engineer | +47 73842138